Elementary: Perfectly captures the central conceit of the show, sometimes better than the show itself. Then that generic title card at the end ruins it. What's with that anyway? Are all shows set in NYC somehow contractually bound to show a brown-tinted skyline title card?
Northern Exposure: I just love everything about it - the moose, the music, the fact that it tells you everything you need to know about the show without any shots of the characters.
The Rockford Files: Again, the music accounts for a lot of this. But those slides do a great job of not just showing the characters but also introducing them and the kind of life they live.
Fringe: I always wanted to do a spoof where there's a zoom out from some molecules to microcultures turning out to be mold and as we pan back, we learn where those cultures are and the title appears: FRIDGE. I also like how they varied it.
American Horror Story: I stopped watching after two seasons, but boy were those credits unsettling!
Honorary mention: Cowboy Bebop, mostly for the music but the visuals are fine, too. What's more impressive, though, are the end credits telling us an independent story the significance of which gets revealed way into the series.