I haven't really played many, but I do enjoy them. I've mainly just played the Age of Empires series, Command & Conquer and Battle for Middle-Earth.
Personally, I loved BFME and still play it every now and then. I'm really hoping that they make a BFME3 when the Hobbit comes out but....I'm not getting my hopes up. Sure, it had a lot of issues, but that game, plus some good mods, was fantastic.
My problem with RTS games is that they do get tedious - but they the same could be said about a lot of other genres (FPSs, in my opinion). Also balancing issues. Both players and AI often tend to rush, which isn't fun for anyone.
Storywise....well, out of the ones I've played, the stories are all a bit average, but they suffice.
I'd love a Mass Effect universe RTS. I think that would be brilliant.