Dungeons are actually my least favorite part of RPGs these days. They just don't make any sense.
That exactly how I feel, they make no sense at all and for me take away from the immersion.
With that said the best and the worst were in the same series.
Daggerfall was the worst huge unnatural dungeon with a horrendous 3D automap
and the best was for the most part Morrowind - most of the dungeons were small caves but the sameness of all those caves took away from the immersion
However, the one I remember most was Arx Fatalis - while the dungeons were the typical long and and curvy passages. The ambient audio of the opening dungeion was the absolute creepiest and best of all time.