Our game has largely allowed whatever spells we wanted to bring in. We're an oddly-balanced party, however, with mostly clerics, and some non-optimal choices ... most of the blasting from levels 3-14 was done by a full-class Cleric ... so having additional cleric spells with some bang kept us moving.
Even with a wide open boat, I would say about 2/3 of the spells my cleric casts are Core spells (Enlarge Person, Righteous Might, Divine Power, Stoneskin, Divine Favor, etc).
And we get challenged. We use two spells, Close Wounds and Delay Death, quite often. Both are Immediate action spells, one does a small amount of healing and the other keeps characters from dying from HP loss for a short period of time. Without those spells our 3 character deaths in Age of Worms would be more like 7 deaths. And we're okay with that ... we like the story, and it's more disruptive to the story to have to write in a new PC. Somebody out-but-not-dead for the cost of a 4th level slot is perfectly fine with all of us (seems like we have to use that one every combat anymore).
Spell Compendium also solved a D&D problem ... the Buff'n'Port, with the Anticipate Teleportation spells. We take that one daily and, after attempting to buff and port, found that the enemy also was using it (almost got killed for that hubris) ... leading to a nice little cold war.
Our Force-specialized cohort utilizes a lot of non-core spells, to maximize his Forceiness. (We didn't have a wizard at all, so I broke him as much as possible, really, to off-set the two-level drop). Many of those are from Races of the Dragon, though (he's a kobold).
In my own games in the future I would probably allow Spell Compendium in, with the option to take out anything I didn't like in play.
--fje