I recently finished Heavy Rain for the first time, and I have to say, I thoroughly enjoyed myself.  The game isn't perfect; the movement controls are some of the worst in gaming, and there's a glaring plot thread that goes unexplained, but looking past its flaws, I found that Heavy Rain made me feel tense.  Normally when a story unfolds in a game, you're just watching it.  Even a game like Mass Effect, with its constant dialogue choices still gave me the security that its choices really wouldn't matter, because I know there's another game on the way, and it's impossible for my every decision to have a huge impact on what's coming next.  As a result, responses to my past decisions are less shocking, and more "Oh hey, I remember doing that". 
 
Heavy Rain changed all that. 
 
Simply knowing that my each and every decision could have serious consequences left me questioning what I should and shouldn't do.  I left several scenes wondering "what if", but I'm almost too scared to go back through the game again and see.  I almost feel like I want the game to be my own experience, one that replaying would diminish the effect.  Knowing that I can screw up and the game can go on regardless added much more tension to a scene than expecting a game over screen and trying again.  In Mass Effect, you are given as much time as you want to choose any given dialogue option, but in Heavy Rain, many decisions are timed.  You have a limited time to decide the best course of action, and not only that, but sometimes your options are intentionally difficult to read and follow, because that's just how freaking tense the situation is. 
 
Some spoilers for Heavy Rain follow.  Nothing too major, but be warned.
 
Alright? Good.  For all the negative gamer stereotypes out there, where all we play are constant gore-filled shooters, there is a definite truth to it.  We can all accept that games like Assassin's Creed: Brotherhood, Mass Effect 2, and Red Dead Redemption are well-designed games with mature stories, but you can't shake the fact that all three of those games have killing as a common gameplay mechanic.  These are nameless, faceless enemies we're gunning down, with no emotional attachment or remorse whatsoever.  But Heavy Rain takes death very, very seriously.  Right from the start, Jason's death kicks off the game, and Shawn's kidnapping soon after.  We're given the options to kill a crazy religious extremist and a shotgun-wielding drug dealer, but in both cases, it's a very serious offense, one the player is encouraged to ponder, even if the people in question aren't what you would call "innocent". 
 
Yet the game is fully aware of that, and a later action sequence has you gunning down nameless, faceless henchmen on the other end of a quick-time event.  I paradoxically enjoyed the scene, but the implications of my doing so eventually became very clear.  You can play a game like Just Cause 2, which gives you all sorts of crazy ways to torture innocent civilians, but then we see just what it's like when the protagonist is forced to cut off a finger.  When I first heard about this scene, I had no idea just how intense it would be.  On paper it sounds bad, but still manageable, story-wise.  But in the scene where you do so, you have the chance to explore a small apartment.  I found a pair of scissors, a hacksaw, and a butcher knife.  Obviously, the knife would be the best choice for cutting off a WHY THE HELL IS THIS EVEN A CHOICE HOLY CRAP.  The mere fact that I have the option to choose how to mutilate myself just makes the scene crazier.  Not only that, but I found a piece of wood to place in my mouth just to have something to grit my teeth again when I actually do the deed.  The attention to detail in this scene is crazy.  The slightly misleading button inputs caused me to screw up the sequence a few times, but it served to enhance my immersion rather than detract from it.  It gave the sense that I was bracing myself for it, only to chicken out at the last moment.  You can't get that kind of tension from any other game. 
 
There are plenty of memorable moments like this in Heavy Rain, but I think it speaks for itself.  The game has a sizeable list of problems, and I've heard many people complain of its quality.  They're justified, but I still feel this is a game you need to play for yourself.  It's gritty and dark, and left a huge impression on me.