I have several duallies. My main rig, that I game on, is dual XP1600's @ 1800 MHz. It is the fastest computer I have ever used, period.
What you're asking is if games utilize both processors 100%. The simple answer is, no. For an application that needs 100% of your CPU power, it gets 50% of EACH processor. The bad thing about that is, you're not getting more CPU power than you would on a single-CPU system. The good thing is, you've got CPU cycles to spare. So, when some other application wants to run, it doesn't have to wait for CPU cycles. It gets them when it wants them. In some instances, this helps game load time and programs that have several executables that have to be run.
The key word here is "multitasking". Dual processors rock for multitasking. Even simple multitasking like burning a CD, while downloading a file, and playing a game. With dual processors, your system never feels bogged down (at least mine never have.) You know how there are some applications that use so much power that you can't even open Internet Explorer without it crawling? With dual CPU's, you don't have that problem.
Gaming, for me, is far more pleasurable, because I don't have to wait for one process to finish, before another one can load. I can leave a dozen programs running and still play games with no noticable slowdown. Game hosting and such improves GREATLY!
To my knowledge, although the Q3 engine is SMP capable, it doesn't work. It only worked for a few super-dedicated programming people to do some benchmarking and stuff. It's very finicky and games based on the Q3 engine won't run if you try to switch SMP on. I've tried, they just crash.