I wanted to make my old computer (Athlon XP 2600+, 768MB RAM, NVidia FX5600 with 128MB) run Second Life reasonably well; I also have a newer computer, but I occasionally need two! So I decided to take the plunge on a new video card for it; what I chose was an XFX NVidia 7600GS AGP8X with 256MB RAM. The final net cost (from buy.com) was $110, after a $10 rebate and a $10 deduction for using Google Checkout; not an unreasonable amount to spend on upgrading this system.
It's a big improvement. With the same version of NVidia driver (the brand new 93.71 that was just posted today), my frame rate in SL more than doubled; looking off the balcony at my house, it went from 7.5fps to 16fps. Then I tried turning on some more eye candy (local lighting, bump mapped and cloth avatar rendering, anisotropic filtering) -- STILL 16fps, so adding those video rendering features didn't slow things down at all. And as an added bonus, everything looks a little better, even without the extra features turned on.
The card has a fan, but it's not very loud. It needs one power supply connection, but it comes with a Y cable in case you need it. (I didn't, so it went to the spare parts collection.)
In summary, lots of bang for a bit over a hundred bucks. Sure, a 7800GS would be even better, but I can't see spending close to $300 on an upgrade for most AGP systems. This one hits the sweet spot in my view, and it's what I'll be recommending to people with AGP who are looking to get a little more life out of their computers.