I think you should qualify that question with a price range.
The BEST video card setup in the word right now is 2 ATI Radeon 1900XTX PCI-E boards. That setup would cost you something like $1400US just for the graphics cards.
Something more practical but still pretty high end would be an ATI Radeon 1800, or Nvidia 7800. These would be less than $400US.
Good medium range cards are Nvidia 6600GT with 256MB, 6800GT or ATI x800xl or x850pro or x800pro. I've seen the 6600GT 256MB for $150US, the Radeons are a bit more (170?), 6800gt is about $250 last time I looked.
I play on an x800xl AGP at 1280x1024 with 8xAF and 4xAF. No slow downs at all that I can see. (I bought my card for $270US about 6 months ago.

)
(OK I was wrong about the BEST setup. Nvidia recently announced Quad SLI Geforce 7900GTX enabled systems. So that would be about $2400US in video cards for your computer! Good Lord! It would take two power supplies just to run the thing.)