It's not bleeding edge and if you're a gamer, just pass this one by. But as we said on the show a couple weeks ago, the Pentium D 805 will give you a LOT of processing power at bargain-basement prices. And a few more bucks will give you a Pentium D 850 with a bit more punch.
While I'm not a fan of either processor, there is no doubt that they can and do give a huge bang for the buck. A wide variety of motherboards are available and they use rather mundane (meaning inexpensive) Intel chipsets, on most of them.
If I had the change in my pocket for a moderately priced dual core workstation, this is the chip I would buy. I won't have as much power as possible, but I'll get a really capable and stable system for about what I'd pay for a single core Athlon 64 box off the shelf. It's almost a no-brainer.