Can Legacy Dual-Core CPUs Drive Modern GPUs?

First, let's talk about the big picture. Can you upgrade the GPU on a six year-old dual core machine and expect to see a noticeable improvement in game performance? Yes. We've put together two graphs that summarize the situation.
Here's the average performance increase we saw when upgrading from a GTX 260 to a GTX 660. The quad-core Q6600 benefits more from the upgrade, but the E6850 gains a significant amount of performance as well. Not too shabby.

This graph shows how the Q6600 stacks up against the E6850 when both systems are equipped with the GTX 660.
The Q6600 is, on average, 21% faster than the E6850 when both chips are running at stock clocks. Overclocking the Q6600 also yields positive results. Games that are completely GPU-bound, like Civilization V, gained no performance, but frame rates in Battlefield 3, Borderlands 2, and Shogun 2 increased a further 16%.

Here's the short, non-math version:  A quad-core is significantly better than a dual-core for modern games. An overclocked Core 2 Duo quad-core is meaningfully faster than a stock-clocked variant.

The Cost of Upgrading
Intel Core 2 Q6600 chips aren't available new these days, but Ebay has a ton of them, regularly priced between $50-$70. If you plan to overclock a Q6600, you'll want the G0 stepping (SLACR). Other quad-core variants are also available, including chips based on the 45nm Yorkfield CPU. Price and availability are obviously highly variable and the usual caveats apply when buying from Ebay.

Is a new CPU worth the price? I'd say yes -- especially if you've currently got a dual-core CPU in the 2.2 - 2.6GHz range. The combined cost of a used Q6600 and a GeForce GTX 660 should still come in below $300 while delivering far better performance than any bottom-end desktop you might assemble for that price tag.