I'm a recording engineer, here's some of what I have observed.
For most sources and with good converters, I can't hear a difference between sample rates on an individual track; an exception would be a solo instrument in a very nice room. I get stems recorded at various sample rates to put in a master session and the original sample rate doesn't seem to make a difference, especially once it's all mixed.
As far as sessions go, I hear a difference. There are many factors - the converters, the DAW's summing, the plug ins, etc. For whatever reason, 44.1k sounds a bit harsh to me, 48k has a sweetness I like, so if I'm doing demos I can happily work at 48k.
I don't use 88.2, probably because of an outboard reverb I used to have that DID NOT LIKE 88.2k. Habits.
I use 96k for most serious sessions, disk space is cheap so I don't worry about it. I asked another engineer friend why he uses 96k, if he could really hear a difference. His answer was that he wasn't sure he could hear a difference but that "he was in a better mood at the end of the day working at 96k." That isn't a joke, I know what he means.
Theoretically, 44.1k should be all we need, but the execution of perfection is elusive!
Best...H