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