I've been gaming since I was 9, back in 1983, and to this day I don't "get" mega-dungeons in D&D. I've had people try to explain it to me, and I've just. Never. Gotten it. I find even normal-length dungeons boring more often than not.
So, I'm going to try to ask it a different way.
What do you enjoy in a mega-dungeon? What do you get out of it that you can't get--or at least not get as much--in other D&D games/settings? Why do you enjoy those aspects?
(Important background: My tastes run to plot-heavy games, where the combats are almost all plot-related; and to fewer, more dangerous combats. I don't care for random encounters. I don't care for combat for its own sake. I prefer mystery-solving and social/political interaction to seeing what's in the next treasure chest.)
Pro: It's an excuse for mindless killing and treasure hunting decoupled from any real plot.
Con: It's just mindless killing and treasure hunting decoupled from any real plot.
In a properly-constructed megadungeon, the DM can totally get away with inexplicably placing a dracolich in close proximity to approximately a bajillion mind flayers and a nest of beholders, and the players can have fun testing themselves against both of these things in quick succession, without a lot of foreshadowing about political conflicts between the dracolich and its hierarchy vs. the mind flayers vs. the beholder hive queen vs. the PCs' faction. So, you tend to get a lot of stuff happening all at once, and you may walk out of the megadungeon with oodles and oodles of treasure and XP which empowers you to affect the plot
outside the megadungeon in new ways. "Into the Woods/You go again/You have to every now and then/etc." The megadungeon, if you survive your foray into it, serves as a transformation catalyst; and the campaign proper serves as the motivation to seek empowering transformations.
I tend to think that a campaign which is all about a megadungeon is boring. But a campaign is often improved by having a megadungeon or three in it.
Let me add as an aside here that time travel and megadungeons go well together. If you want a good reason why nobody (except the PCs) ever goes into the megadungeon--well, maybe it's because nobody ever comes out! Or rather, people come out sometimes, but never quite the same ones that went in--and the underlying reason is because of temporal mechanics and the butterfly effect. If you go in, you wind up in the past, and the things you do there change events so that the future you return to is never quite the future you came from (maybe Ned the Milkman is now Ned the Mayor, and everybody thinks you're soft in the head for thinking he's a milkman and for no longer recognizing your own younger brother any more--and that keeps more people from ever entering the megadungeon). Depending on whether you want PCs to be tightly coupled to the outside world, or detached from it in kind of an episodic "Quantum Leap" fashion, you might or might not give PCs a way to find their way back to their original timeline a la "each time you venture into the megadungeon, you have twenty-four hours before your Potion of Temporal Anchoring runs out and the girl whose love you're trying to win (probably) ceases to have ever existed".