When death is used properly, it can be a highly effective storytelling mechanic. The example I’d use is the Forlorn Hope in Guns of Infinity: not only is it a high risk, high reward scenario, but the fact that it is a segment which is extraordinarily difficult to survive serves narrative purposes as well.
However, that requires you to use death “properly” in the context of a game where survival is preferable to death, which means it needs to be a “fair” death.
What do I mean by “fair death”? It means that at the very least, the game has to do three things: tell the player why they died, give the player a fair opportunity to avoid death when they’re playing “blind” (as in, without foreknowledge of the plot) and “in good faith” (that’s to say, when they are playing a plausible character within the internal logic of the setting), and to serve a higher purpose within the narrative, even if that purpose be to hammer home how meaningless and ignominious a single person’s death might be.
However, there are exceptions: a game centred around arbitrary or seemingly pointless deaths as a theme is one. A story which intentionally throws you into a doomed situation, where the narrative centres around not survival, but doing something meaningful before death is another.
Death is a mechanic just like any other: it can be done well, it can be done poorly. It can enhance a story, or entirely ruin one. If a certain death mechanic fits, use it. If it doesn’t, don’t.