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.


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, 2016 at 23:32
  • 1
    You might find this blog post useful; brentozar.com/archive/2016/11/… Dec 1, 2016 at 10:16

1 Answer 1


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, 2016 at 23:48
  • 1
    @NateJ That theory may be tested by CREATE STATISTICS instead of CREATE INDEX. Dec 1, 2016 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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