This is a hard question, because I could probably think of another one later, but I have always loved the Resident Evil 4 intro, even if I am not the biggest fan of that game. It sets the atmosphere perfectly, it gives you the very basics of the game, and then has the audacity to throw you inside the village and expect to survive or bite the dust. And trying to hide or run will only penalize you with secret cutscenes and harder enemies.
By the time you beat the opening, not only you have learned what to do, you are also conscious on what's about to expect. It's fantastic.
A different one I liked was Alone in the Dark from 2008. The game was extremely mediocre all around, but it was one of my first 360 games and that point where you are just blinking like crazy to escaping a collapsing city in a taxi was just plain awesome, honestly.
