Well, it all depends on the genre really. FPS games usually have the best technology in terms of overall visual effects, but there\'s so many others out there you\'d be ignoring if you left it to that. I\'ll give the best looking ones in a couple of genres.
RTS -- Command and Conquer Generals (Zero Hour)
FPS -- This is a tough call as there are many not only graphically impressive, but also awesome fun. I\'d say go with Rainbow Six 3 Raven Shield (the expansion, Athena Sword, is coming VERY soon), Battlefield Vietnam (not necessarily a graphics hog, but has the potential to be great -- coming in march), Call of Duty (Also amazing playing, but hardly the best polygon pusher)
Action -- Planetside (it\'s pay to play like an mmorpg, but the community is great if you team up with people and the games are friggin massive with land, air, sea, and ground units), Max Payne 2 the Fall of Max Payne (just pure wholesome action and it\'s cool)
If you just want to push your system, without having fun, you can always play Unreal II.