I don’t know, I don’t think most character deaths make for dull stories. The do mark the end of that character’s story, but they can be thrilling, tragic, memorable parts of the larger story of the campaign. Some deaths are anticlimactic, but I don’t find that most are.
Tragic, as noted in...one of these threads, I don't remember which, requires that the characters grieve, process the loss, deal with the consequences, struggle to move on (or struggle with how
easily they could move on), etc.--or time and attention are given to showing the absence of these things. Or at least I can't call something a "tragedy" without those elements. I don't find D&D deaths work that way. There is no tragedy, because the group just moves on, it isn't particularly
fun or
interesting to dwell on the death, short-circuiting the roleplay events that would be required for the tragedy to have teeth. Instead, it's a lot more like
a character being fridged. As usual,
not the same because that's a writing trope, and thus an authorial-fiat choice rather than a constellation of player behaviors, but the shallowness of the "tragedy" is very similar. Like, as the linked video notes, Luke basically having zero emotional impact (beyond a brief tearful scene) from having his beloved adoptive parents, Uncle Owen and Aunt Beru, horrifically burned to death along with their moisture farm, making him an orphan twice over. It bears a strong similarity to the "Bob died, and then we met Roberta" stuff.
"Memorable" is...variable. A death can be very memorable, but I find that it's a particular
kind of memorable that is kind of...empty. It's an event that happened, and you don't
forget that it happened, but it just doesn't end up connecting to anything for the already-stated reasons. Especially if it is, as noted, of the "random orc #6 got a crit" or, in my case, "random massive war-hulk creature got a crit," since that means the
only memorable thing about it is that it happened.
"Thrill," which I intentionally skipped over, is
hella complicated because....well, death itself is actually kind of the antithesis of "thrilling," at least as I understand the term. Thrilling things are dynamic or highly inconclusive, you don't know where the coin will fall. The moment death itself happens, however, the thrill is gone, because you
know what's happened, and by definition it
cannot be dynamic for that player anymore, their participation is terminated. Literally. So there is thrill in the
risk, thrill in the
chase, thrill in the
unknown, but not thrill in the death itself, much as there is thrill when you
make a gamble, which resolves when you show your cards.
And, worth noting, you can still get all these benefits--the memorability, the tragedy, the thrill--with deaths that are temporary (like Gandalf's) or fixable (like my character's death was.) In fact, I find that it is
more memorable and thrilling, and in a very specific sense also more tragic despite the potential happier ending, to have those temporary or fixable deaths. It's more memorable because many more events will be attached to it, and the death can become the spark for a whole arc or even set the tone for an entire campaign in some cases--the death becomes bound up in a whole arc, rather than being a single point. It's more thrilling because, instead of terminating all unknowns and risks, the death creates new unknowns and risks, and new ways that the party could potentially fail. In other words, the story
grows as a result of the death, rather than withering.
As I mentioned, the "more tragic" thing is somewhat nuanced. Above, I said that there isn't really an opportunity for tragedy in a lot of these deaths because most groups just breeze through the grieving process and either don't examine the impact of the death, or only do so in a relatively shallow way. But if this cruel fate
could possibly change, the party has a reason to keep dwelling on it, to engage in the grieving process, to keep the death centered in their minds. As a result, you can get poignant moments that just wouldn't really happen in most games, because the focus is on integrating Roberta, not on mourning Bob.
Ultimately, the final ending will of course be "less tragic" in the sense that the dead person comes back to life, which is generally a joyful thing--the prodigal son returns. But the journey to that end, particularly if it is (appropriately) fraught with peril and uncertain as to its final shape, is where the feeling of tragedy can live and breathe and sink its teeth into your heart. What price will, in the end, be paid for a life? Often that price is a great pain ever after.