Gears of War... oh wait. 😛
Seriously, try Flight Simulator X. There is also Need for Speed Carbon. And since you asked not to mention RPGs I won't mention NWN2, Gothic 3 or Oblivion heh. Apparently Company of Heroes is quite good looking. But obviously you are looking for shooters, and there hasn't been anything out recently to test the 8800GTX. Crysis is still 12 months away it looks like, so you will have to settle for games like F.E.A.R. and Prey.
Unfortunately I think that F.E.A.R. has a ****** engine, though it has been patched to utilize dual core systems. Prey really shows the short comings of the Doom III engine, with ugly textures up close and weird looking open environments.
Splinter Cell: Double Agent is good looking but extremely buggy. Oh I know what! You can try Rainbow Six: Vegas. I am a massive R6 fan, but that was back in the day when it wasn't consolized. I am not sure how heavy it is on the tactics, but is apparently gorgeous looking.