1

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

  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
Improve this question
2
  • 1
    Nice detective work :) I would say no that's once it's gone it's gone. However my hunch is that adding the index or querying it may have updated or altered the statistics for the table, causing SQL to use a different query plan. Did you capture the query plans before/after?
    – Sir Swears-a-lot
    Commented Apr 6, 2018 at 3:23
  • Did you make any other changes? You need to look at the query plan to see which indexes it's actually using.
    – Sir Swears-a-lot
    Commented Apr 6, 2018 at 3:24

2 Answers 2

Reset to default
0

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.

Improve this answer
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.

Improve this answer

Your Answer

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