Short version: I have an Opteron 175 and two 7950s, SLI. Would an upgrade to something like a GTX 260 be worthwhile, or would it just be too CPU bound?
Long version: My full specs are an Asus A8N-SLI mobo, Opteron 175, 2 GB ram, and the 256 MB 7950s w/256 pushing a 22" LCD, native res 1680x1050. It's socket 939, so upgrading the CPU without pretty much a total system rebuild isn't a realistic possibility, it fact it was originally a 3700. I grabbed one of the last 939 dual-cores that I could afford before they became Ebay items.
For the most part I've been happy with the performance of this machine. Source games run great; I can play L4D with mostly full settings at native res, 2X AA, but during the occasional horde rush I'll get a little stuttering. Bioshock ran great. Oblivion was wildly inconsistent - FPS in the mid-teens outdoors with some heavy tweaking. Even the Crysis demo ran surprisingly well. Flight Simulator X runs at a fairly consistent 20+ FPS, lower in NYC.
The only games which ran like absolute crap have been Neverwinter Nights 2 and Warhammer Online. I get a 3DMark06 ver 1.0.2 score of 7690, SM 2.0 score 3572, HDR/SM3.0 3545, and a CPU score of 1742.
I really am not a fan of SLI; I feel like I spend more time goofing around with profiles and bugs than I do playing playing games sometimes. In the future I'll gravitate towards single-card solutions.
My wife has a near-identical PC, only with an X2 4200 (which I WAY overpaid on Ebay for) and a single 8600 GTS, 256 MB. She mostly plays WoW and gets well over 60 FPS, but in some of the expansion zones the game slows a good bit.
One more twist - I have a $200 gift card to NewEgg. I REALLY don't want to build a new computer this year - I'd rather take the money I saved and buy some outdoor gear. I need a new tent, bike needs some work, etc. But if I don't use this gift card for a new video card, I really don't know what I'll use it for unless I build a new system. Possibly some sort of NAS device.
I suppose the big question is, do you need to make an improvement right now? If so, the best single-card solution GPUs (I too can't stand the hassle of dual-card setups at times) available within the budget of the gift card are the GTX260 and HD4870. Both would offer excellent performance gains. A bit of review research into which would offer you more performance in specific titles you play should help determine which is better for you. The other option is to just wait, but for two different reasons.
Looking over these forums, there are quite a few people in similar situations, myself being one of them. (Find my "Dead 8800GT" post) To make things worse, there's mixed opinions on what to do - go for it cause they are better cards, or wait and see where prices go with the release of the 4890 (and eventually GTX270). Waiting to see if/how these new releases affect the pricing of current cards is short-term, meaning we shouldn't have to wait too long. Waiting til later in the year to replace the entire machine is long-term. Personally, I hate waiting. I've been tempted to just run out to Best Buy this last week I've spent with my dead 8800GT to just get something to stick in this thing so I can do more than just check email and read forums. LOL
One last thing to consider is power consumption. Many people don't really think about the energy expense of their PC long-term at all. Would a single-card like a GTX260 start saving you any money by replacing the dual-7950 setup? Would it also provide an increase in performance? Odds are the answer is yes to both questions. (4870s seem a bit on the power hungry side, so one of them might not.)
I really don't see any of the cards in the ~$130 to $200 range being "bottlenecked" by your system at all unless you play at lower resolutions. CPUs have a greater impact on graphics performance at lower resolutions, while at higher resolutions, the GPU has a far greater effect on framerates than the CPU does. And with a change to a single-card solution from a dual-card solution, the load on the system (CPU) might actually change for the better.
All things considered, unless you can't stand the performance limits of your present setup, I'd wait and replace the whole thing when you're able. Maybe a nice X-mas present to yourself, or something along those lines.
Yeah I would suggest you to update the CPU.
I had a Opteron 180 running at 3.0 Ghz and I was still bottle necked. I had a gt9600 setup.
Trust me you will be surprised of how much performance gain you will get when you switch to a new CPU.
I upgraded to a Phenom II 940. If you can afford it I would absolutely do it before upgrading your graphics card.
The memory and CPU upgrades would be an ideal route, but with a socket 939 motherboard it gets prohibitively expensive. A 4GB ram kit for my mobo is $130 ... no thanks!
I'm thining about a 512 Geforce 250 for myself and my wife. Hopefully the CPU won't be too much of a bottleneck and we'll see some improvements, and this can tide us over until we can do a proper system upgrade.