Doom 3
Doom 3 has shown some entirely different results compared to 3DMark 06, and they are complex. Let's start with the Pentium 4 670 with and without SLI.
Right off the bat, it is clear that the 670 is not enough to push SLI'd 7800 GTXs in Doom 3. There is a performance plateau all the way down until 1600x1200 with AA/AF, meaning that we are CPU bound. With a single 7800 GTX, we are witness to some truer scaling, even though the 1024x768 no AA/no AF score still looks CPU bound. Due to the slight CPU overhead of SLI, the 1024x768 score is actually higher with a single card than with two cards.
Moving on to the Pentium D 840, it is clear that the second core improves overall performance, but not because of Doom 3 itself. With two cores available, and one core being pushed to the limits with a single-threaded application, Windows is intelligent enough to move "housekeeping" tasks over to the second core. With NVIDIA's ForceWare drivers leveraging multi-core processors to some extent, this "loosening of the grip" on the first core allows it to focus entirely on running Doom 3. That's where we see the overall plateau being raised. However, the plateau is still more or less there, meaning that even an 840 is not quite enough to push SLI'd 7800 GTXs.
It's interesting to note that the single-card system still scored higher at 1024x768 no AA/no AF than then SLI system -- meaning that NVIDIA's drivers are not yet as multi-core optimized as they could be. SLI processing could have been moved over to the "spare" core.
Call of Duty 2
With Call of Duty 2 we see no gains with the dual-core CPU at all, simply because the game is taxing the video cards so hard that the CPU is probably sitting there waiting on them considerably more than the video cards are waiting on the CPU.