The problem with the pro-performance viewpoint is that it always seems to present the "Why" (performance), often without the "How", and never "Why Not".
CSS Sprites do have a positive impact on performance, for reasons that other posters here have gone into in detail. However, they do have a downside: maintainability; removing, changing, and particularly resizing images becomes more difficult - mostly because of the alterations that need to be made to the background-position-riddled stylesheet along with every change to the size of a sprite, or to the shape of the map.
I think it's a minority view, but I firmly believe that maintainability concerns should outweigh performance concerns in the vast majority of cases. If the performance is necessary, then go ahead, but be aware of the cost.
That said, the performance impact is massive - particularly when you're using rollovers and want to avoid that effect you get when you mouseover an image then the browser goes away to request the rollover. It's appropriate to refactor your images into a sprite map once your requirements have settled down - particularly if your site is going to be under heavy traffic (and certainly the big examples people have been pulling out - facebook, amazon, yahoo - all fit that profile).
There are a few cases where you can use them with basically no cost. Any time you're slicing an image, using a single image and background-position tags is probably cheaper. Any time you've got a standard set of icons - especially if they're of uniform size and unlikely to change. Plus, of course, any time when the performance really matters, and you've got the budget to cover the maintenance.
If at all possible, use a tool and document your use of it so that whoever has to maintain your sprites knows about it. http://csssprites.org/ is the only tool I've looked into in any detail, but http://spriteme.org/ looks seriously awesome.