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.
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.
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:
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.