Edit: +1 works in this situation because it turns out that
FILE_NUMBER is a zero-padded string version of an integer. A better solution here for strings is to append
'' (the empty string), as appending a value can affect order, or for numbers to add something which is a constant but contains a non-deterministic function, such as
sign(rand()+1). The idea of 'breaking the sort' is still valid here, it's just that my method wasn't ideal.
No, I don't mean I'm agreeing with anything, I mean that as a solution. If you change your query to
ORDER BY cj.FILE_NUMBER + 1 then the
TOP 1 will behave differently.
You see, with the small row goal in place for an ordered query, the system will try to consume the data in order, to avoid having a Sort operator. It will also avoid building a hash table, figuring that it probably doesn't have to do too much work to find that first row. In your case, this is wrong - from the thickness of those arrows, it looks like it's having to consume a lot of data to find a single match.
The thickness of those arrows suggests that your
DOCUMENT_QUEUE (DQ) table is much smaller than your
CORRESPONDENCE_JOURNAL (CJ) table. And that the best plan would actually be to check through the DQ rows until a CJ row is found. Indeed, that's what the Query Optimizer (QO) would do if it didn't have this pesky
ORDER BY in there, that's nicely supported by a covering index on CJ.
So if you dropped the
ORDER BY completely, I expect you'd get a plan which involved a Nested Loop, iterating over the rows in DQ, seeking into CJ to make sure the row exists. And with
TOP 1, this would stop after a single row had been pulled.
But if you do actually need the first row in
FILE_NUMBER order, then you could trick the system into ignoring that index which seems (incorrectly) to be so helpful, by doing
ORDER BY CJ.FILE_NUMBER+1 - which we know will keep the same order as before, but importantly the QO doesn't. The QO will focus on getting the whole set out, so that a Top N Sort operator can be satisfied. This method should produce a plan which contains a Compute Scalar operator to work out the value for ordering, and a Top N Sort operator to get the first row. But to the right of these, you should see a nice Nested Loop, doing lots of Seeks on CJ. And better performance than running through a large table of rows which don't match anything in DQ.
The Hash Match isn't necessarily awful, but if the set of rows you're returning from DQ is way smaller than CJ (as I would expect it to be), then the Hash Match is going to be scanning a lot more of CJ than it needs.
Note: I used +1 instead of +0 because the query optimizer is likely to recognise that +0 changes nothing. Of course, the same thing might apply to the +1, if not now, then at some point in the future.