Its pretty awesome when its well done and used sparingly. I noticed I tend to love games that have the more "not so happy ending" (SPOILER)Persona 3 just happened to do it the best cause that game did so much to build the relationships your characters have. The very last stretch of the title is just amazing.

Shadow of the Colossus was also seriously epic. Even titles tha just flirted with it tended to do it well with BoFV and CT.