I play story heavy games all the time. I prefer rpgs because of the stories. I have played countless, Chrono Trigger, the Personas, including nearly every single mainline FF game to name a few. Only FF I haven't finished are 2, 3 and 11. I only started this game because I wanted to see its story.
In my experience with a focus on story heavy games, FF14 has one of the best. I've always viewed it as a single player game wrapped in an mmo. Each expansion is another game in the 14 series.
People always go on about wanting everyone to die, which would only make the story worse. Why get attached to any characters when you know they won't live for long? Death for the sake of it is bad writing. I much prefer good stories, like the one in this game.