Well I know that some games suffer an fps drop if they are not optimized to use dual core. You can set it to use one though. I plan on upgrading to Pentium D 9xx series myself. I’m using a Celeron D 352 3.2Ghz and an ATI X800 GTO with GDDR3 RAM and getting 30fps in Oblivion on average. BTW you can get the X800 GTO on newegg right now for $87 if you are looking for a decent card.
I don’t think that the Pentium D will show much improvement in current games performance solely because most games are not designed to be multi-threaded and take use of multiple processors.
I’m sure this will change in the near future, but I would rather get a Core 2 Duo Conroe.