I think it depends on the Origin, most of all. I like playing a mage for this game, a twin blade wielding mage, for that matter, haha. But when I'm doing Mage Origins, it's always going to be the The Broken Circle, or anything relating to Jowan. Since that is the origins, the very start of the character, and he/she will never forget their roots. Unless they hit their head... Alistair?
With the Mage Origins what got me most was during the Sacred Ashes quest, when you're up looking for the ashes. After you pass the Guardian, and all the riddles, you come to a brief moment where you see Jowan. And what he says really seems to be a punch to the gut there from my character's perspective. If anything, he was the first friend of the character, or even love interest (not that his oblivious self will realize it anyhow). Or with Uldred, how he just wanted to get out but ended up making things worse. Cullen, subjected to be tormented while his fellows perished. And the tower never gets finished (minus Awakenings). I think this one is my favorite.
Human Noble, course it's going to be with the family. First time I played all I wanted to know what "Where the hell is her brother?!" and seeing the death of the wife and son of the brother, it sucked. Confronting how probably sucked even more, it could of made more of an impact if it was a personal duel of some kind. I didn't feel like my Human Noble avenged them at all. Not to mention in that role it seemed more like I had the duty of making sure Alistair was alright, crowned, or whoever got the crown in general was someone who would lead well, in better representation of the humans, etc.
Dalish Elf, definitely Tamlen's encounter at the campsite. That... Was awful. Especially since the entire freaking party gangs up on him, you can't even give the final blow unless you just jump him quickly. But that was a lot of good dialogue, that felt like an actual closing.
I'd love to say helping Connor, but considering there isn't much afterwards, conversation-wise and what not from him or the family, it's just not as fulfilling. Saving Redcliff was great, though. But the doomed Lothering just sucked. Poor people.
To me, I separate myself from the character that is made in the game. I am a writer (big whoop), character creation and customization is important, as is the persona, relations, and story. I'm not going to do playthroughs with the same intentions of being a dick or an angel. Do it how the character wants to be done. So it varies with me and the characters I have.
If I have to say anything, my rogue Dalish Elf, is there is any point of his "touchy feely" sort of moments... It's with Morrigan. Not just with Tamlen, but definitely the Morrigan moments, when she vanishes, or in Witch Hunt when he goes through the mirror with her. Definitely those.
I like all your replies though, definitely a lot of views. Very interesting, folks.