I’ve been doing some index tuning on a large database, and I’ve been keeping tabs on the usage of the indexes as I have run tests. On a side note, if you want to see how I was monitoring the usage, check out my earlier post of the stored procedure I created.
Anyway, I noticed quite a few lookup calls against the tables primary key index (PK). Huh? This was confusing because I had changed a few indexes and I was pretty confident the PK should not have been required.
So, before I went on the hunt, I first refreshed the query plan cache for the database. It is a test database, and as I said I have made a few changes, so I figured it would be a good idea to clean the slate.
To refresh the cache, run the following query.
CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO
For more information on how this works, check this out.
I hope this helps!