With SLI you use 2 cards to get about the same performace you would out of a single high end card. The problem is for the price of the 2 cards you could buy a single that would out perform the SLI setup for the same or lower price. It's only worht SLIing witht the High end cards to get max performance out of games. But you only really need it if your gaming in resolutions greater then 1600x1200.
That is what I have been seeing. I looked at reviews where cards were SLI'd and only saw a noticeable improvement in high res.
The only reason to use SLI is if you need greater performance than the fastest graphics card on the market, so unless your getting dual 8800GTX's dont bother using SLI because a single 8800GTX will be faster.
I used my SLI for 1280x1024 res with all games settings turn up with 4xAA. Get smooth frames all the time even with Oblivion.
That would be an example where SLI actually gets a noticeable increase over 1 card. I am looking at running 1440x900 with no eye candy. SLI might get a 5-10 FPS increase for me, not worth the money though.