I don't think every character should have a heroic death, but it should be dramatic. If its not going to be heroic it should be unexpected. There were a few unexpected non-heroic deaths on Buffy that worked well because it made it more sad.
Well I would say Dax's death was "unexpected non-heroic". Though her murderer being Dukat kind of upped it's level of drama. If she had just died from a random energy spike it would have pissed people off I think.
For some reason it looked kind of lame to me. I'd have preferred it if he'd just snapped her neck or something. They also had a deathbed scene to really milk it. I was sad enough anyway
I'd inadvertently seen a spoiler for Jadzia's death so it wasn't unexpected for me. I was mad with Sisko for not listening to the wormhole aliens though.
Yes I think it would have been much more powerful if Dukat had snapped her neck with his own hands rather than it being Pah Wraith energy.. then we could be debating how much of that action was Dukat's free will.