Back in the 1990s, adventure games reached an all time high. In the more recent years, the genre has become popular again on the Nintendo DS (eg. Hotel Dusk, Secret Files and Phoenix Wright) and on PS3 with the hit Heavy Rain. My personal favourites are still the old ones, though.

In my honest opinion, the best ones have to be
The Beast Within: A Gabriel Knight Mystery (1995) and
King's Quest VI: Heir today, gone tomorrow (1992/1993).
The Beast Within is also one of the best horror games I've played. It litteraly gave me nightmares about werewolves and had an amazing atmosphere. The games in the Gabriel Knight series were controversial for their depictions of realistic blood, human sacrifices, cannibalism, swear words and other stuff that was seen as too 'obscene' to put in a computer game in the mid 90s.
King's Quest VI is absurd yet fascinating at the same time. It also featured numerous innovations in it's time, such as an animated opening, lip-sync, Windows support and full voice acting--all this in 1993.