I’m not even a Stones fan but Gimme Shelter is complete, sweet rockin’ goodness. I have to ask, where does it go wrong for you?
I like the woo-woo part; when the guitar kicks in, it becomes generic for me.
My big one is Layla. Great intro and chorus riff, but everything else I can live without. That’s probably my biggest disappointment.
“Eye in the Sky” by the Alan Parsons Project is another one that loses me after the opening. Boston’s Foreplay/Been a Long Time (if you consider the former the intro to the latter) also fits.
I disagree with many in this thread, but I’ll second “Crazy Train.” As for “Hooked on a Feeling,” I actually hate that damned intro.
I’m sorry, but there may be something broken inside of you.
Not one of my favorites. I don’t know where it comes in chronologically, but I prefer the older stuff that highlighted the instrumental work. And I can’t pick up lyrics very well in anything, so I don’t count that for much. I’m still trying to figure out that Happy Birthday song. There’s a word in the third line that sounds different every time I hear it.
That is fucking hilarious. So Spinal Tap.
I’ve always felt this way about ‘Good Night’, the last song on the Beatles’ White Album. The first few seconds are a George Martin strings figure that’s beautiful (f you don’t mind schmaltzy), but when Ringo starts singing, it falls apart. Some have said it’s Ringo’s singing that *makes *it fall apart, but I think the melody just doesn’t go anywhere. FWIW, I always thought of ‘My Heart Will Go On’ by Celine Dion (written by James Horner and Will Jennings) as an improved version of this melody.
Re: ‘Gimme Shelter’: I consider it a masterpiece, but I think it’s more the Stones recording and performance (not to mention the others’ performances) than the Stones songwriting. I’ve never heard a cover that captured even a fraction of the magic. I’ve heard it described as a great recording of an OK song.
bwahaha – I’ll admit it gets better once the background chickie starts singing too. Still needs more woo.
“Lovely Rita” had a great climax as well! 
Fight the youth by Fishbone. One of the greatest opening parts for any album ever. And then outside of maybe one song - crap. But man that is a great opening.
back in the days of MTV showing back-to-back music videos, one opened with a not-dead-yet Dan Fogelberg skiing up to an old cabin and finding a cache of old photos.
To almost three minutes of really tasty “vintage guitar noodling”.
Now, the album’s got some great bluegrass roots to it, it’s got those tight Fogelbergian harmonies… but it doesn’t have a single note of that guitar!
*Ahh, I just read the comments on YouTube. Most are “Who did the intro?” and “Where can I find the guitar intro?” *
If you listen to it as the “Theme from Chef” it becomes more interesting. 
Funeral for a Friend by Elton John - I always thought that what followed the five and a half minute “introduction” was incongruous, both in mood and music (and ‘crap’).
Welcome Home by Coheed and Cambria rocks until about 40 seconds in, when the lead singer’s shrill, ear bleeding voice comes in and fucks everything up.
Yeah, but that new beginning isn’t really part of the song, it’s just tacked on.
Now that makes it part of the song. Thanks for posting that, casdave. I’d never hear it before.
Oh, man. That is such happy cheese. I’m sending that link around. Thanks, blondebear.
Maybe this doesn’t count because it was (understandably) unreleased at the time, but “If You’ve Got Trouble” starts very promisingly, with one of the most energetic riffs of the Beatles’ middle period… and that’s the only good thing about it.