Does an index continue to be used after it is dropped (Database Polling investigation) 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.


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)



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?



  MemberId,
    Sum(RemovedAmount) as Amount,
    Sum(RemovedQty) as Qty,
    PromotionId,
    CheckNo,
    ProcessedDT
    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
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.