If Index Seeks + Scans + Lookups = 0, okay to drop the index with a lot of updates on it?

Question

Post reply

If Index Seeks + Scans + Lookups = 0, okay to drop the index with a lot of updates on it?

GabyYYZ

SSCertifiable

Points: 7913
More actions
May 28, 2013 at 8:46 am

#278242

We're trying to optimize some of our tables and notice quite a few of our indexes are getting updated frequently, but the combined total of seeks/scans/lookups is zero. Just to be safe, I'm only scanning indexes which have been updated greater than an arbitrary amount of time (say 100,000 updates sinces last restart or stats reset).
I know some people have had issues in the past dropping indexes which had scans but no seeks, and their queries slow down immediately, forcing them to re-create or re-enable the index, but in my case, these indexes are not being used at all, efficiently or inefficiently.
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Depends.

Are those indexes enforcing uniqueness?

Does the period over which those have been tracked cover an entire business cycle (including month end and year end if applicable)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 2

GilaMonster (5/28/2013)
Depends.
Are those indexes enforcing uniqueness?
Does the period over which those have been tracked cover an entire business cycle (including month end and year end if applicable)?

Oops, for the first question, I should have mentioned, I will exclude any indexes that are enforcing uniqueness.

As for the period, unfortunately, we may not get a full year out of it as maintenance/patching does occur regularly on these boxes. However, our fiscal year ended March 31 and one heavy-use server was last restarted February 3 so we at least cover critical year-end reports in that start time.

Other than those conditions, I assume there should be no reason that a query running one way just before the drop should slow down just after the drop (once a re-compile occurs if necessary), if the seek/scan/update total remains zero, correct?

Thanks very much.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No reason (and dropping the index will invalidate any plans which used it)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 4

GilaMonster (5/28/2013)
No reason (and dropping the index will invalidate any plans which used it)

Thanks very much!

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein