When you create a nonclustered index, like this...
...you get a perfectly formed index to retrieve the userid from the comments table.
This index does, however, also include a hidden column.
This hidden column links us back to the clustered index for key lookups. But if you read from this index, the index itself is now also sorted in the database on first userid, and then on id.
So if the query you're trying to run looks like this...
...we'll get an execution plan that looks like this...
Notice the lack of a sort operator.
Now, further down the line, another developer, named Greg, performed that new development that management really wanted. They apparently only wanted the comments of a certain user WITH a particular score.
So Greg does this!
And then he also changes that index to add score!
And the execution plan has gained a bit in size... Notice the presence of a sort operator!
All of a sudden, management is furious, because this is executed thousands and thousands of times each minute, and that tiny little sort almost added a whole millisecond to each execution! And since this was executed 22 000 times each minute, that little millisecond added up to 36% decrease in performance.
Now you're furious with Greg, because he ruined your perfectly fine index that wasn't sorting for almost a whole millisecond.
...but who can blame Greg here? It's not really his fault...
If you truly INTENTIONALLY added that initial index with the hidden column sort in mind, then you should have explicitly told it to sort by that column, even if it's the clustered index key that's always included anyway.
If your initial index had looked like this
...then Greg would have seen that and would go...
"oh... there's probably a reason for that... maybe I should change my index to this instead..."
And he goes and does this instead:
Which ultimately leads to...
No sort.
In this scenario, I was Greg. I caused stuff to go a bit slower. Or a lot slower. But it wasn't my fault! It was the fault of whomever created an index that was sorting on the hidden column that was executing like an Arms warrior in the latest World of Warcraft expansion... I'm sure none of my readers are going to appreciate that joke, but I'm keeping it anyway, because it made me chuckle.
Side note:
Sort operators are not always bad and I'm definitely not trying to get you to go above and beyond to get rid of them. They can be just fine.
Often, when the query in question is being run on a very very frequent interval, however... that's usually bad.
Second side note:
Arms warriors have this ability, called "Execute", and in the latest expansion, this was buffed in a way that allowed people to spam this ability very often.
There, I explained the joke to you. Are you happy now? It's not even funny anymore after I had to do that. Go play WoW so I can make jokes without having to explain them in the future, please.