Biography
Some people die of old age, or gradually succumb to one of a number of illnesses. When death comes, the lucky among us will go out peacefully with dignity. Some of us might go out fighting the good fight, saving the lives of some unfortunate person whom our consciences cannot ignore and force us to help. They'll all die "good deaths" (and in some cases, heroic deaths).
This trope is not about those people.
Some deaths not only suck, they suck beyond the telling of it. These deaths are extremely painful, particularly gruesome, and morbidly embarrassing, so horrific that they leave the deceased absolutely no chance to retain any sort of dignity, forever. These deaths transcend from being merely "bad" into the realm of being "ugly."
This trope isn't about just any unpleasant death. Sure, it's bad to get shot in the head — your blood spills all over the floor and you may void your bladder or your bowels. But compared to... oh, being eaten alive from the inside out, getting shot is a walk in the park. It is worth noting that for the most part, creators reserve these events for use as villainous karmic deaths (where the level of horror involved with the death is often part of the karmic punishment) and to get rid of unpleasant people who are Hoist by Their Own Petards... especially the embarrassing type. If this is done to a sympathetic character, on the other hand, it is usually the point where the killer crosses the Moral Event Horizon, if he or she is not on the other side of it already. If someone kills people like this on a regular basis, chances are they're a Complete Monster.
There isn't necessarily a correlation between the visible gruesomeness of the death and the actual nature of the character's death, some deaths that fall under this trope happen offscreen, and it's the very idea of it that makes it horrific. Deaths with a high horrible factor with less visible content can just as easily fall into scary, though.
Deaths like this tend to fall into two categories: The first one is where the victim is a Hate Sink and/or rather sadistic villain who deserves it, who you really aren't going to feel sorry for (well, maybe a little...). The second one is where the killer fits that description, and the intent is to show the viewers how sadistic they are (more often than not, it's convincing). Occasionally, the two situations happen in the same work of fiction, with the killer in the second type becoming a victim in the first, which is very much a Karmic Death. Both parties will often Face Death with Despair.
Compare There is No Kill Like Overkill and Undignified Death. For when this happens in works aimed towards children, see Family-Unfriendly Death.