No One Live Forever 2 is good. Not much carnage, but it's fun to play. Great dialogue and modeling (they really use the Lithtech engine well). UT 2003 should fulfill your need for carnage, but the system specs are a little high.
SOF 2 is definitely carnage-laden, but it gets a bit boring, if you can believe it.
Battlefield 1942 is mostly multiplayer, but the single player option is fun. You can tweak the settings to provide more of a challenge. Graphics aren't top notch, but it's still fun.
That's off the top of my head, and they're all FPSs. If you want RPGs, let me know. I have some suggestions from fellow gamers on those since I'm not a big fan.
