The game that blew me away most visually was actually Kings Quest 6! I remember being so impressed by the introduction sequence, I just couldn't believe it was a computer game.
Doesn't really stand up so well now though!
Lately I have been impressed with Dracula: Origins and Secret Files 2.