People rightfully say World at War is a realistic, no-holds barred depiction of warfare, but I think the honor should go to Modern Warfare 2 as well. Specifically, the levels where you are fighting Russians in your home turf: the US of A. Two levels stick out to me to this day: Wolverines! and Of Their Own Accord.
Wolverines! takes place in a Virginia suburb nearby I-95. You see restaurants, homes, businesses reduced into battlegrounds, uncertain if any civilians managed to survive or evacuate from the onslaught. Imagine being a family in their home, then suddenly, Russians come in with airstrikes just bombing your neighborhood to rumble. The show, don't tell atmosphere of a neighborhood under siege gets frightening once you use your imagination.
Of Their Own Accord takes it to 11. Now, you see your nation's capital engulfed in flames. Moreover, those civilians caught in the crossfire have been evacuated into a bunker or a major stadium. Russians are advancing, now the army's mission is to regroup and retreat themselves AND those civilians before it's too late. Your playable character isn't even PART of THAT mission; your objective is to secure the White House.
The radio chatter and ominous music REALLY sells the stakes of a foreign invasion. Quite haunting, but so realistic in its portrayal.