The whirlwind of emotions felt/portrayed by Sherlock at the end of Series 2 is best shown musically with Prepared to Do Anything. The song halfway through culminates the starting half's tension with a violin and piano section that mirror John's and Sherlock's themes in a minor key that just begs the tear ducts.
Series 3 produced plenty of memorable tracks:
How It Was Done, the rock opening signifying the return of the coolness that is Sherlock Holmes.
John is Quite a Guy is absolutely gripping, especially as it crescendos to its startlingly gripping finish.