13

This is a strange situation, but I'm hoping someone has an answer.

During some performance troubleshooting, we added a NONCLUSTERED INDEX to a table, as was requested by sp_BlitzIndex. We checked its usage the next day, and it showed 0 reads (0 scans/seeks, 0 singleton lookups), so we disabled it.

The very next minute, we get a complaint of the same app-slowness (performance trouble) that we were trying to check out & resolve in the first place when we added the INDEX.

Now, I know in theory, this sounds purely coincidental. The INDEX was provably, measurably, NOT USED. Disabling it SHOULD NOT have caused query performance degradation. But it's almost TOO coincidental.

Question

So my question, simply enough, is thus:

Is it at all possible, that a NONCLUSTERED INDEX, whose usage-stats (from the DMVs / sp_BlitzIndex) show NO usage, still have been helping query performance somehow on the affected table?

  • PS: Please spare me the proverbial "Go test it!" response; I have to wait until tomorrow to test it because the table is heavily used and the index won't be re-enabled (re-built) until the nightly maintenance window. And the nature of the performance troubles has been sporadic & difficult to reproduce anyway; I'm not saying "No, I won't test it", I'm saying I know we have to test, and plan on it, but I'm asking for theory and/or experiential knowledge. – NateJ Nov 30 '16 at 23:32
  • 1
    You might find this blog post useful; brentozar.com/archive/2016/11/… – Rich Benner Dec 1 '16 at 10:16
13

Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan.

I have come across situations when SQL Server has decided to use stats from one index and scan/seek another index.

Edit - This might not be applicable cause I just realized that you have disabled the index. I have not tested that scenario.

  • Thank you for the clear, concise response! Yeah no, it wasn't disabled immediately, it was enabled & fully "live" until I disabled it seconds before the user-complaint / performance-slowdown. You're good :) – NateJ Nov 30 '16 at 23:48
  • 1
    @NateJ That theory may be tested by CREATE STATISTICS instead of CREATE INDEX. – Jakub Kania Dec 1 '16 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.