Which is a real meta-complaint, as Earth will never be invaded and the heroes won't fail.
In fiction (including novels), sure. But that knowledge nukes the fun of the
game for a lot of players. In-character, you have no reason to expect that Earth WON'T be invaded. But you do have this guy a few miles away who has staved off at least a half-dozen alien invasions already.
But, really, it could be said about any genre where a few plucky heroes stand against seeming impossible odds. How come the Enterprise doesn't call in half of Starfleet to come help? How come Riggs and Murtaugh never call for back-up? Why doesn't Angel call an entire army of slayers for assistance? Why doesn't MI:6 dispatch an entire squad of double-ohs to handle a problem? Or why Harry Potter didn't go right to Dumbledore with half his problems. Or even, why doesn't the plucky band of adventurers recruit every member of the town guard and local militia to come into the dungeon and zerg rush the cultists?
This is why it's a "transmedia" issue, so I think it gets close to the heart of things. In linear fiction such as movies and novels, none of that is much of an issue, especially when it's self-contained within the story - when the protagonists are supposed to be unique, exceptional characters whose adventures you passively absorb. But games have different requirements, different rules they should follow, and in D&D one of those rules is "your character can end permanently in an underwhelming way." None of those exceptional protagonists is allowed to do that by the rules of their media, but your PC's are, because they're game constructs, not fictional constructs, and so they have more freedom. And when those exceptional NPC's exist in your RPG, they bring with them assumptions from their other medium.
Put another way, the uber-NPC's were written as exceptional characters, but any PC ranger is a ranger in a world where Drizz'zt is already a ranger, any PC wizard is a wizard in a world where Elminster is already a wizard. And neither of those two yobbos is supposed to be what the story you're telling at
your table is about, but they're also kind of impossible to avoid.
I think this is why I'm most likely, were any of my players to seek out these NPC's, to say frankly, "They don't exist."
How about Dragonlance?
The Heroes of the Lance pretty much stole the thunder for everyone following the War of the Lance.
Yep. I'm playing a DL game, and while I'm not familiar with the setting in much detail, I think in his telling, the War of the Lance never happened, so none of those exceptional narrative characters exist, so there's exactly ZERO chance of them doing anything that our party would have to pay attention to.
The Realms developed a lot of high level NPCs. Because there's a bajillion books written there. But not every NPC really has a place in the campaign setting. The novel line is its own entity, and you're not bound to include someone's novel protagonist any more than you're forced to include their plot.
It feels like you're working against the assumptions of the game as it's presented, though. There's stats for Elminster, and Drizz'zt is gonna fight demons with you this year, so WotC wants you to pay attention to their characters. The default is "Elminster switch is ON."
And, really, the characters are half the fun of the setting. Without them the Realms is a little less interesting. As seen by the 4e Realms where they jumped ahead 100 years to kill off all the human NPCs and then made Elminster unable to cast magic. It was just lacking personality.
That's also a problem - if the most interesting thing about your game setting has nothing to do with
game elements, but you push them into the game anyway, they're going to be an awkward fit. Uber-NPC's are nearly
always an awkward fit in your D&D games. If there's nothing interesting when they've left, then there's a big hole in the core of your setting because while it might be fun to read about brooding, rebellious badasses, and it might be fun to play
as a brooding, rebellious badass, it's not fun to play as a character where the brooding, rebellious badass is clearly the exceptional protagonist of the world, and you're just scene decoration for it. If the most interesting thing about your game setting is novel characters, you've got a pretty weak game setting.
(I'm not sure that's 100% true about FR, but perhaps some folks do!)