I've used a CS-2, which is great for smooth warm sustain. Have a Visual sound comp now which is much more tonally transparent and has a bit more obvious squash.
I don't tend to feel the need for them. There's one original song in our set where in the verse my playing is very sparse with a clean tone and I like a compressor to give me sustain between the notes where it'd otherwise appear empty sounding. But that's one original song out of 30.
I guess the thing is for most people you've got pickups that compress, maybe an overdrive pedal or something that compresses, then you run it through a bunch of preamp tubes that compress, a power section that compresses, an output transformer that compresses, and speakers that compress. All depending on how loud you run it of course. But for most guitarists, there's enough compression in there that you shouldn't need more.