Take a cheesy film like Armageddon, where one of the characters sacrifices himself to save the planet. I wonder which would be more powerful, when an NPC sacrifices themself whether it would be more powerful if it was a powerful, wise, that the player can "look up to". Or if it was a friend that did it. Or a player's "experience" ie. they have built up their kit to be the best in the gameworld, but they have to sacrifice it all and become a weakling again. Or opportunity, if a character has to give up on one of their dreams > going to college instead of working as a check out girl (yes, this doesn't involve dying instead it involves personal sacrifice. Any ideas?

It would really touch the player if the player character had a vested interest in the self-sacrificing npc. Aka he saved the other guys life or vice-versa. Resources were traded between the two players, be it they lent ammo or etc. If the npc is just "there" he means nothing unless the player has a connection to him.

If you want a player to care, make them lose something that they've invested in. Again I return to the example of Aeris dying in FF7. The first time, I tore my hair out, because when she died I lost the hours of play that I have invested in levelling her. Next time through the game, I didn't waste a penny on her, didn't level her unless it was accidental, and lost next to nothing when she died. No matter how dramatic the music is, an NPC is just a puppet.

Make the player choose, and have the value of what's lost be relevant to the return. If you throw some green recruit into the Maw of Doom, you don't even slow it down. If it's going to be stopped for real, you need to put your level 47 Paladin down the hole. That hurts, but it helps, too.

Or just keep track of which character has the best combat record, the highest experience, and the best gear. Kill that one off. That'll get a few sobs out of a player.