A query runs in 2ms – can it be quicker?

“Come on, seriously?  You’re going to ask about tuning a query that’s that quick to run?”

Yes.

Here’s the query in question.

Relatively straightforward, easy to understand, some names changed to protect the innocent.

Query plan:

The tables themselves aren’t particularly large:

Not much scope for change here, except SQL Server is recommending a missing index, so maybe there is something we can do here?

After some thought, and the creation of two small indexes neither of which was the missing index suggestion, the query plan now looks like this:

We are not particularly worried about the overhead of these additional indexes, given the data in these tables is relatively static; however, we will continue to monitor performance in case things change.

The query now runs on average in 0.14ms, approximately 15x faster than before.  Logical reads are down from around 975 to around 45.

“But what’s the point?  It was quick, and it’s now a bit quicker?  So what?”

Here’s why we did this

Ah, yes.  I forgot to mention that this query runs about 900k times in the hour between 8am & 9am every day.

“Yeah, that would make a difference”

Indeed it does.  Overall CPU time for that process has dropped from 30 minutes to 2 minutes; total logical reads have dropped from 7.2TB to 360GB.

And now for some screenshots from QueryStore to back up the result:

Execution count for the last week is cruising around 880k-930k per day:

Total duration (ms) for the last week, down from over 1,800,000 milliseconds (30 minutes) down to around 120,000ms (2 minutes):

Average logical reads (in KB on the scale) down from 8MB to 460KB:

Total logical reads (in KB on the scale) down from 7.2TB to 430GB:

And the final graph – average memory consumption for the query is down from approximately 1MB to approximately zero.  No, I’m not quite sure how that happens either, but that’s what QueryStore is telling me.

Lesson Learned

Just because a query runs really quickly, doesn’t mean it’s not worth tuning.  This was, on aggregate, the second-most expensive query during the hour of 8-9am every day (the most expensive being an index maintenance operation).  It’s now so quick it doesn’t register on our monitoring system.