When you're invested in the characters, I think the most intense death was Dean's at the hands of Lilith, and then seeing him chained up in Hell. That whole progression was brutal. The deaths in the first two seasons were pretty intense. I took Jo's death in season 5 pretty hard because I had a crush on Alona Tal and I really liked her character.
And there was always a story arc, it's just that they couldn't focus on it all the time and have 23 episodes per season. Season 1 and 2 - finding Dad and killing Yellow Eyes.
Intro deaths for all 10 seasons... I dunno, I've found some of the more recent ones to be pretty laughable.