Fiercely talented, wickedly funny but dead at 40 — Amy Francombe on the doomed life of a Nineties icon