Ten years after the musician's death, Alexander Larman charts the lows " and, finally, dazzling heights " of his later career