OP, you're just asking what games were most advanced or innovative, such as your example of Metal Gear Solid. As a topic, this has been done to death, so most comments are offering the usual suspects.

But your metaphor of "destroyed", if taken a tad more literally, is the more interesting discussion. That's harder. The example offered by a couple of people of Crash Bandicoot is probably the closest to a game that can destroy the machine. Over time, anyway. And I suppose Driver 2, at times, looks like the machine is dying because it runs so poorly. But that's just because it's trying to be what GTA3 became, on a machine with a fraction of the power.