The Core2Quad Q6600 is actually available in two revisions - the older B3 stepping and the newer, more efficient G0 stepping. I'm hoping by now the one Amazon is selling is the newer G0. I've had my Q6600 a while now, so unfortunately I have the older B3 which uses more power and runs hotter, but I'm still more than happy with it and have overclocked it to 3ghz for a bit of a speed boost.
The Q6600 is an LGA775 CPU that requires a modest 1066mhz FSB, unlike some of the newer quads that need 1333mhz or 1600mhz, so it will run happily on most chipsets from P965 or Nforce 6 or later.
If you are debating whether to get a quad core CPU or a dual core, the increase in multi-threaded software over the past few months should make the decision for you. If you are into video encoding, most video codecs are threaded these days - DivX for example will use all 4 cores and therefore finish the job in much less time than a dual core. Far Cry 2 when it is released will also use more than 2 threads, with one core dedicated to physics, another for AI and the rest for the game engine.
It'll support a 64bit OS too. I'm using Vista x64 with it now and since SP1 many of the annoying gremlins it had have gone. The days when 64bit operating systems are necessary are on their way.
If you are going to buy one, I would recommend a better heatsink than the one that comes in the box, such as the Arctic Cooling Freezer 7 Pro or the Tuniq Tower or Thermalright Ultra. It does get hot and a good heatsink will keep the noise down.