Does an index continue to be used after it is dropped (Database Polling investigation)

by Asher Rankin   Last Updated April 06, 2018 03:06 AM

The folly of developers who use database polling is shown below.

I have identified a query below which did not have an index in place on a particular column

In addition to polling the database every second with the below query, the developer did not utilise indexes on the CloseDateTime, so a scan of about 50,000 rows was hitting the database constantly. (Keep in mind this was in Development, so the dataset is smaller)

After giving the developer an inkling of why we were experiencing high CPU utilisation on the DB, by showing them a query - they came back with miscellaneous excuses for the cause such as a virus, etc.

I then had to investigate this further, which involved correlating the CPU utilisation using Perfmon, using SentryOne Plan Explorer to look at the impact of the query closer, and then finally - watching what CPU utilisation did when I added a new index that appeared to be missing.

Output of SPBlitzFirst helped me identify the query. output of spblitzfirst

I then investigated it further in Sentry One Plan Explorer. I noticed firstly that there was no Index on 'CloseDateTime' (in the where clause of the inner query)

enter image description here

I tested my hypothesis by creating an index on this column, and watched the CPU utilisation suddenly drop. I then tried to drop this newly created index, expecting to see CPU utilisation increase again, but it did not.

My question is, will the database continue to use this index even after we remove it?

enter image description here

    Sum(RemovedAmount) as Amount,
    Sum(RemovedQty) as Qty,
    from vm_config..HospitalityTransaction HT with (NOLOCK)
    where exists (Select * from vm_warehouse..TransHeader TH with (NOLOCK) where AssociatedCheckNo = HT.CheckNo and  CloseDateTime between HT.ProcessedDT -1 and HT.ProcessedDT + 1)
      Group By     MemberId, CheckNo, PromotionId, ProcessedDT

Answers 2

As Peter mentioned, the query may still be using a far better execution plan - and since you know how to check that, I'd suggest doing so - but it is not using the index.

When you drop an index, it is gone. There isn't any way it can linger around once your DROP command returns successfully.

I feel like I should write more, but its really just that simple. Check your execution plan and you should find your answer pretty easily.

April 06, 2018 04:00 AM

An index that is not there is simply not there.

One possibility is that the plan you had originally was sub-optimal, potentially due to bad (or just different-at-the-time) stats. So you went through this process:

  • You had a plan that wasn't optimal.
  • You created the index, which invalidated the existing plans referencing the table. The new plan is great! Using stats from the index and a skinnier index.
  • You dropped the index, forcing a new plan again. This time, the plan is better, not due to the index, but due to better stats, maybe table/CI rebuilds or reorgs in the meantime, data changes, etc.

This is just a theory. It's impossible to know without capturing plans from all three states, but just because performance is better even after you've dropped the index does not mean that there is some ghost index hanging around helping you out.

Aaron Bertrand
Aaron Bertrand
April 06, 2018 11:56 AM

Related Questions

Performance differences between RID Lookup vs Key Lookup?

Updated September 10, 2018 19:06 PM

Filtered index not chosen, and rejected with hint

Updated December 04, 2018 19:06 PM

Sql Server 2016 not using index used in 2012

Updated October 18, 2017 21:06 PM