Update: Since the new-look SLI first hit the scene, some folks have been kicking about odd ideas about SLI's likely impact, talking about an expected 50% performance gain or the like. Truth is, SLI ought to deliver a 100% performance gain fairly often, so long as performance isn't limited by the host system or CPU. This is not like SMP with microprocessors, where the general impact of adding a second CPU isn't so powerful. Graphics is an inherently parallelizable task, and even dual processors get nearly twice the performance in rendering.
At high resolutions with lots of edge and texture antialiasing, where fill rate is king, dual-GPU solutions should really shine, because dual-card SLI offers 2X the fill rate, pixel shading power, and memory bandwidth of a single-card setup. The secondary impact of SLI is twice the vertex shader power, for double the polygon throughput. Because vertex processing is decoupled from the rest of the pixel pipeline, the additional vertex shader power can help even at low resolutions. SLI won't deliver twice the performance in older games or if the user doesn't crank up the eye candy, but when polygon throughput or pixel-pushing power becomes the issue, a dual-card config can be twice as good.