Note: Blogger has been down for awhile (at least mine was), so this is later than usual going up.
Unless you don't like your character and your DM won't let you roll up something new, death sucks. In 2nd you lost a point of Constitution, while 3rd Edition taxed you a level, only gouging your Constitution if you were 1st-level. Losing an ability score point didn't amount to much in 2nd Edition and in 3rd Edition it depended on if your score was an even or odd number (since for example 10 and 11 meant the same thing), and if you need to keep boosting it for some reason.
Now a level? That was a much bigger deal, and basically meant you were easier to kill the next time around. I remember a 3rd Edition campaign where one guy got killed by falling off of a particularly high cliff, then by spiders (stacking poison sucks ass), then lost a bunch of levels because of undead (though he got lucky and shook some of them off). At the end of the campaign's fairly short lifespan he ended up three levels beneath us, which was made worse by the fact that he'd multiclassed into sorcerer.
4th Edition is more lax, giving you a temporary penalty to most of your rolls until you hit three milestones. The penalty isn't much, which is good because it makes death undesirable without making it much more likely to be a repeat offense. Plus, if you are really afraid that a player is going to die, you could always add a Raise Dead scroll to a treasure parcel (or allow a NPC to cast it since NPC abilities are no longer restricted by level). That being said just because it isn't nearly as harsh as it used to be, doesn't mean it is anything to laugh at, especially because of a few extra side effects.
First, it takes you out of the game. How long death puts you in timeout depends on when and where it happens. If you're mid-to-high level, you can generally get a raise and be on your way...so long as someone has the Raise Dead ritual and components to cast it. At lower levels this can be very difficult (especially since you need to be 8th-level to cast Raise Dead), while at higher levels (21+) most characters have some sort of auto-raise ability. If no one can raise you, then you might have to roll up a new character if having someone join the party is feasible. If worse comes to worse, you could take control of a NPC if any are around.
Second, it can damage the storyline. How much depends on how well invested your character is. I know that I've planned adventures and adventure arcs around events or elements of a character's history and goals. If that character goes away then you risk having potentially wasted time and materials. Though it could be possible to salvage the materials, it could be a manner of timing. Perhaps you wanted to introduce a NPC, whose entrance would have made a greater emotional impact if the character you'd planned this meeting around wasn't dead. Perhaps the dead character was supposed to discover or recognize something important?
Despite all of the above, I don't want death as a consequence of failure to go away. It is one of those globally inclusive things that makes an easy and logical trigger for most any character's fight or flight response. It is interesting to see how death and resurrection has changed over time, and I'm happy with the equilibrium that has been reached: it's still bad, and it is a lot easier for a DM to tackle.