Any an every level from the MW2 single-player campaign up to Makarov's house counts, I think. You've got a frantic chase through the streets of a South American favela, a hazardous climb up an impossibly steep mountain, a civilian massacre at the Moscow international...
The runner-up would be Sam's standoff with Doug in Chaos Theory; especially when you put your gun up and get the secret animation.
Also, while it might not be particularly 'intense', I thought Dante meeting Agnus in DMC4 was one of the funniest things I'd seen in a long time.