I'm playing Need for Speed: Most Wanted on s754 PCIe right now. It's a big upgrade over Need for Speed: Hot Pursuit 2.
Need for Speed: Hot Pursuit 2 felt great compared to Need for Speed III: Hot Pursuit until you find out there are only like 3 maps with variations like reverse or different path on the same map and the campaign is quite static.
On AGP you will likely be limited to Need for Speed: Underground 2 which features only night driving in city which is quite small. It feels cheap compared to successor Need for Speed: Most Wanted.
Need for Speed: Most Wanted is quite good compared to all predecessors. Graphics looks great (weather is fantastic), map is like 3x larger than in Need for Speed: Underground 2, has both open country (you can even drive in golf course) and city, lot of objects can be destroyed while your car is indestructible, fun with police chase (where like 10 police cars can be after you) and more interesting challenges with chance to win opponent's car. I got Toyota Supra from opponent, great RWD car, won me many races, I got Mazda RX-8 from opponent too but it is useless, bought Mitsubishi Lancer as being AWD is great for police chase later in the game when WW Golf is no longer enough, I got Porsche Cayman S from opponent, being RWD is great for racing against opponents. In police chases RWDs are bad as it's easy to get stuck, FWD or AWD works best. When racing against opponents RWD is best as they tend to oversteer which can be used to advantage.
Need for Speed: Carbon can no longer be played comfortably as it stutters too much.
Pentium III 900E,ECS P6BXT-A+,384MB,GeForce FX 5600, Voodoo 2,Yamaha SM718
Athlon 64 3400+,Gigabyte GA-K8NE,2GB,GeForce GTX 275,Audigy 2 ZS
Phenom II X4 955,Gigabyte GA-MA770-UD3,8GB,GeForce GTX 780
Vishera FX-8370,Asus 990FX,32GB,GeForce GTX 980 Ti