For me, the best "shit just got real moments" occur around 2/3 through the story. It doesn't have the same weight if it's at the very beginning or very end. So far, the Mass Effect series is paramount in this regard, with both installments provoking me literally to say the phrase in question. Both spoilers:
In Mass Effect, Virmire. Losing Ash or Kaiden and possibly Wrex AND figuring out the truth about the Reapers from Sovereign. In Mass Effect 2, Joker's mission when the Collectors raid the Normandy.