The two times I've finished Gauntlet mode in Wing Commander Academy in over 20 years of playing it are certainly the greatest moments.
As for whole games as a total experience, Myst and Loom are among the top ones.
Fool's Errand was amazing with its meta puzzles, simply for human mind being able to create something like that.
There are some games that have great atmosphere to make them good experience, even if gameplay or story may have been lacking in some parts: Max Payne, Privateer 2, Shadow Man, Nocturne, S.T.A.L.K.E.R, Okami...
The problem with defining best game experience is that the most rewarding moments result from long frustration and raging. Overall enjoyable game like Myst or Loom won't give you that.