I love dungeons, to create and run and to play in. It all depends on the people running them and playing in them.
The first dungeon I ever designed had fire beetles in the natural cavern and then kobolds in the built up section with traps and over here in these rooms was a lich....
The fire beetles and the kobolds were quite tough enough and the first trap in the lich's ante-chamber (Exploding Runes on a sign-in book) did enough damage to kill a 2nd level character. the party left.
After that I realized a lich should not be on the first level of a dungeon. But I was the stubborn sort so I worked out a way to have a nutcase 3rd level wizard with a nice magic item (Wand of conjuration) who could pretend to be a lich! It was years before any characters went back to mess with section of dungeon. There were at least twice the level of the mage by then.
Three different campaigns I played in, different GMs and different styles of dungeons -
1. nice little dungeon with odd things. A room with a button outside the door, inside were toads, giant toads, lots of 'em. We found out if you pushed the button you got a new toad in the room. I had a druid character speak with the toads and tell them he was their godl proved it by bringing more of them into existance. Got a nasty wart a couple years later from the 'real' God of Toads who was irked by my druid's behavior....
The Room of the Jars, in particular, oddly fun. You went there, saw 10 gold jars on a shelf and when you reach in something
happened. You might get a magic item, nothing or your head could explode. What really was great about it was that it allowed new characters a short journey in the wilderness to get enough experience to reach 2nd level and gave the characters a common bond. We investigated later and found out there
are things man, or dwarf, were
not meant to know!
2. The party was captured by hobgoblins, tossed into an arena. Four magi, with no spells, were fighting three wolves and they only had one dagger! They won, I think we all survived. They freed their warrior and clerical help and then 'explored at full speed' as they tried to find a way out of the place! It was cool grabbing whatever weapon you found and gear to help your group survive.
3. This guy's Traveller campaign was the ancient history of his D&D campaign. That made it wonderful. Heavily fortified underground defense installations were the best thing to explore. It got to be where we no longer needed maps with some of them. I 'knew' the way to travel to get to the huge complex on top of the mountain with the gigantic doors. Into the swamp, make a right at the river and up the path. I 'knew' the rubble to follow at another dungeon to get to the mostly safe room on the 5th level of the dungeon that we always used as our base camp.
The fact that we usually had a choice as to whether to go to a dungeon or not, or the dungeon was relatively small but intense focus of the campaign made them good for me.