I think that when games actually DO show a realistic amount of gore it's pretty awesome. Nothing really does that except for the first 2 Soldier of Fortune games (the third one was just retarded, and also sucked bad). Shooting someone in the gut with a shotgun and having their intestines blow out is badass.
Nothing does this because it is disturbing. Real violence is fucked up. If you ever watched someone get half their face torn off with a buzzsaw you would probably throw up, and that's normal; there's all types of chunks of skin and bone and brain and other shit flying everwhere, not to mention all the blood and screaming. As videogames evolve to show off more impressive graphics I hope that they also move towards more realistic violence as well cause that will be awesome.
I know a lot of people talk about violence desensitizing us to death and stuff but I think that if we looked back a couple hundred years we'd see that people used to be SURROUNDED by death. You would slaughter animals, or watch them slaughtered in front of you. Your siblings would die at a young age from horrible shit that we cure now by accident. You might know dozens of people that died. Nowadays it's hard to relate to that level of tragedy around us, but it used to be common place and gave people a respect for the fragility of life.
In the end, though, I just want to blow a dude's head off in a way that makes me go "OH JESUS!!!!"