I've got a Riva TNT2 32MB card in my Pentium II build with Windows 98, but I received my "new" Pentium III Katmai processor a few days ago. It took me almost half an hour to remove the thermal paste residue from the die with alcohol, but I got it clean. I'm just waiting on the heatsink and some thermal pads (for the cache chips) before I mount everything, swap it out for my P2, and cross my fingers as I hit the power button. If it all works well, I'll have a 600MHz processor instead of a 350MHz one to push the TNT2 and the dual Voodoo2 cards in SLI. I hope I double my framerates in Quake 1/2, Unreal, and Half-Life.
For the time-period (~1999), the TNT2 card seems quite powerful when playing the games of its time. That is why I chose that card to go into that system. I also had to deal with the AGP slot only being 2x (5v), and the processor being only 350-600MHz limiting the games that could be played anyway. So the TNT2 seemed to be the best fit for that slot. Also including the dual V2 cards in SLI for pretty much total Glide support and I've got a pretty powerful period system (even though I'm putting a 2000 processor in it).
But yeah, I agree the TNT2 would be woefully underpowered for a high-speed Athlon XP processor. My guess for the best card is as I said above, a Radeon 9700 Pro or 9800 or 9800XL. From what I've been able to read, those are the fastest graphics cards of the time period that worked on a 4x AGP bus. Sure, nVidia put out the 6xxx and 7xxx series with AGP support, but the only way you'd see the performance increase is with a full 8x AGP slot or the move to PCI-Express. My laptop has a PCI-Express Go 7800GTX with 256MB of VRAM and it absolutely smoked - it was so beast. it needed that power to push the 1920x1200 resolution 17" LCD panel screen. That was the first time I had seen 1080p resolutions and it was glorious.
Of course, now 1080 is, like, bare-bones for gaming. Everyone has a 1080 display. I just got a 1440p 144Hz G-sync display, the Acer Predator. And it is such a bad-ass monitor...it better be for the $800 I paid for it. I hope it lasts me at least 10 years.
EDIT: Yes, I will be doing full benchmarks with Quake I, Quake 2, and 3DMark99 so you will be able to see the difference from the processor swap. I'm sure people here will be very interested in that.