To me a good dungeon is designed as a believable place first and offers the feeling of a descent through somewhere that his its own sort of ecology and internal logic.
Blackrock Spire in World of Warcraft (Classic) is one that comes to mind. A giant city of evil orcs, dwarves, and dragons carved out inside of a volcano. Multiple entrances, multiple areas, huge underground spaces, crazy navigation and verticality, tons of side areas and optional bosses. It felt like a realization of 1st edition D&D style dungeon, where it wasn't really designed to feel like a game, more just like a big place that was there already doing its thing, that you're intruding into.
Dark Souls and Bloodborne, to me, are also sort of mega-dungeons. Even though you're ostensibly in different areas, the whole world is a hostile obstacle and not somewhere that you're really ever meant to "clear" or "conquer," only survive and pass through. The scale and the navigation are constantly toyed with, you meet strange NPCs along the way but never really make "friends," and the worlds have that same sort of labyrinthine, haphazard, interlocking nature that always makes them interesting to explore.
Other favorites:
- Durlag's Tower and Watcher's Keep from Baldur's Gate II
- Ultimecia's Castle from Final Fantasy VIII
- Clockwork Mansion from Dishonored 2
- Tower of Latria from Demon's Souls
- The Spencer Mansion in Resident Evil
From a gameplay point of view I also enjoy Zelda dungeons and the like (puzzle based) but they don't really excite me in the same way as some of the above examples because there isn't that same sense of exploration, like people actually LIVE there and were actually doing things before you arrived. My feelings on what a dungeon should be are really influenced by Gary Gygax and early pen and paper games, so my tastes skew accordingly.
I feel like truly great dungeons are surprisingly rare considering how often the word gets thrown around in gaming. Most of the time they're just a mashed together bunch of rooms full of monsters, without much semblance of ecology of life or forbiddenness. There's a lot to learn from the old pre-video game classics!