I know with Fallout 3, I could run overmax settings (editing config file to higher then ingame lets you do) with dual Nvidia 8800 GTXs. It ran quite well. However it sucked too much power from the PSU and so I had to run FO3 with a single card and had to dime back those settings a bit.
So you will get a performance boost in some cases. For me I had to disable SLI and one card to play Everquest 2. It didn't support SLI or multiple GPU's at all. And would actually slow down. But that game was made in 2004. Most newer games will run well with Crossfire or SLI.
There was a pic of a setup floating around of a 5 GPU setup, one Nvidia card for the Physix and 4 Crossfired ATI cards. I don't know how well it performed in games. Benchmarks don't tell the whole story these days. Some games will like a setup, some won't. So mileage will vary a bit.
I know MWLL specifically did alright with a dual 8800 setup I had a while back. Though once we were in Warhead... I dunno, I went to a single ATI card before that.
One thing I hate about building computers these days, you buy the best you can afford and cross your fingers lol. Though usually a standard high CPU, high GPU and loads of ram will work. Its when you do crazy stuff like SLI or Crossfire, or Eyefinity or whatnot is when stuff may or may not work as intended.