It can depend on the game.

Games like the first two Lufia titles and the Golden Sun games the puzzle design is very important, as that is the focal point.

Games like Dark Souls that don't really make use of puzzles Atmosphere is paramount, and it is wonderful for it.

Really though I find this to be a hard as smurf question to answer, as it is so much easier to point out why dungeon design is troutty, such as Xenogears or FF XIII, then to explain what makes it good.

It's kind of like how no one notices when editing in video is good, yet bad editing is glaringly obvious.