Oh! Would that I had been aware of this thread earlier! (Too much Qi, methinks.)
Pretty much any adventure game from Lucasarts will put you on a very good start. The list:
Labyrinth - Hard to track down and often forgotten, but an impressive first foray into the genre by Lucasfilm, as they were back then.
Maniac Mansion - Regarded by many as their first 'proper' adventure game. Was remade a couple of years ago by a group of fans, downloadable from here. and much recommended.
Zak McKracken and the Alien Mindbenders - Hard to track down, but much loved by many. This received a fan sequel which you can grab here.
Indiana Jones and the Last Crusade: The Graphic Adventure - Also tricky to track down, but a pretty faithful adaptation of the film.
Loom - Ask me about LoomTM.
The Secret of Monkey Island - Honestly needs no introduction.
Monkey Island 2: LeChuck's Revenge - Snap.
Indiana Jones and the Fate of Atlantis - Quite possibly the best Indiana Jones game ever, with a choice of three paths - Fists, Wits or Teamwork. All are great.
Maniac Mansion: Day of the Tentacle - A fantastic sequel that even included the original Maniac Mansion (use the computer in Eddie's room). Utterly hilarious.
Sam & Max Hit the Road - Play it. Play it right now.
Full Throttle - Has a problematic fighting sequence (which is easily overcome by picking up the yellow fertilizer and throwing it in the face of the woman with the chainsaw, a feat I discovered after much tearing out of hair) but this is instantly forgotten, if only for the Ride of the Valkyries.
The Dig - Based on a script by Steven Spielberg, I really enjoyed this and would quite happily recommend it. Has a few issues, but is a seriously playable game.
The Curse of Monkey Island - As above. This time with voices!
Grim Fandango - If you can get it working, give this a whirl. I've had nothing but problems for some reason. But it's actually a really good game, and fully recommended.
Escape from Monkey Island - As above. This time in 3D!
There are other games from the days of ore you should also look into. The Legend of Kyrandia series is a good trilogy, if hard in places (you can, and will, die in these games) and the King's Quest series (remakes of the first three located here, here and here).
Games made using AGS (Adventure Game Studio) are plentiful and should keep you going for as long as need be. I won't list them all, only some of my favourites: A Tale of Two Kingdoms, Da New Guys, Perils of Poom, Grr! Bearly Sane and the Ben Jordan series, which is actually where I got the surname for my own PI, Daniel Jordan. It's a sort of homage, as opposed to out-right theft. Other AGS games can be found here.
Other, modern adventure games you should also consider trying are the Broken Sword series, the new Sam & Max games, The Longest Journey series, the Chzo Mythos (also known as the 5 Days series, although Chzo is the author's intended title) and, to be honest, anything by Ben 'Yahtzee' Croshaw, who also does the utterly hilarious Zero Punctuation game reviews. You can find his site here.
That should get you started. Let me know if you need more sometime in 2009.