I don't think it's a huge conundrum, but rather that D&D hasn't really ever tried to address these issues. Of course, alternate systems have. For that matter, Monte's article about splitting magical effects into a bunch of always-on buffs and a select few castable spells suggests how you can more closely approximate this legend and fantasy trope in D&D.
Personally, I think you get there with something like the True Sorcery/Iron Heroes route:
1) A caster class with excellent skills, decent HD (d4+2/level), a medium BAB and defense bonus, and access to feats that allow you to heal, buff your allies, create traps and poisons, et cetera.
2) "Always on" aspects of power that you can use whenever you like to create subtle and/or minor effects. In some cases, these are a blanket substitute for the raft of lower-level attack spells like the orbs, magic missile, etc.; in others, they're just a cool effect (gain a bonus to a skill check, walk without touching the ground, go without food and drink, etc.).
3) A flexible array of difficult-to-cast spells that are moderately tough to pull off with substantial casting time, and REALLY tough to do on the fly. Spells are also fatiguing.
I think those get you pretty close to the "classic fantasy" model. You can be helpful to the party all the time, and every once in a while do something REALLY incredible.