On the surface, Fate/stay night is a story about an annoying and arguably misogynistic teenager named Shirou who teems up with a sword-wielding heroine named Saber in order to defeat six other "Servants"—great heroes of history summoned into the modern day—and win a competition of magi known as the Holy Grail War. The writing style is pretty terrible, a whole ton of exposition is thrown around and there are occasional scenes of badly written sex. So far, so iffy.
Here's the thing: like the best visual novels, Fate/stay night isn't about what you think it's about. The Servant fights, while stylish and interesting, are ultimately window dressing. The Holy Grail probably isn't the Holy Grail you know. Shirou's rampant idealism is quickly exposed to be something a lot more interesting—a pathological desire to put other people in front of himself, which ranges from inspiring quality to tragic flaw. The story isn't so much about the Epic Battles to End All Epic Battles as it is about the ideas swimming under the surface. If you don't come to terms with this than you probably will hate Fate/stay night.
Because for all the explosions and kinetic battles and magical weapons, Fate/stay night is ultimately a multi-part examination of heroism. Every facet of the story is used to explore the central message—from the main character's actions, to those of his friends, to the existence of the Servants themselves. Each consecutive route further develops Shirou's martyr complex, from the idealism of Fate to the utter demolishing of Heaven's Feel. What initially appeared to be leaden and occasionally stupid is revealed to be remarkably complex and even remarkable. It's not profound or even intellectual, but for a piece of pop philosophy/fantasy/horror Fate/stay night is arguably a masterpiece.
Granted, it is infuriatingly slow-paced and probably overlong. But when in motion, firing all cylinders, it is a beautiful thing to behold. Kinoku Nasu, the writer of Fate/stay night, is a pretty terrible writer; but when it comes down to it, he's a great storyteller. Fate/stay night might be his best story. Just be aware that it takes a lot of patience to see it through.
(Also the sex scenes only take up around half an hour of thirty-five, and can be skipped. So don't let that turn you off! The game's pretty cool otherwise)