August 27, 2013 at 9:28 am
Hi
I have an alert set up for 'Number of unused indexes' set to a number above a certain threshold.
Our servers get rebooted every month therefore flushing out the stats from the dmv's that I use to trigger these alerts.
I suppose this is a 'how long is a piece of string' question, but is 1 months worth of stats going to give me a decent idea of my index usage - i don't feel that it is but i welcome peoples opionions....
August 27, 2013 at 10:14 am
a proper length of time would be over an entire business cycle. There might be some indexes that are only used during year end and if you only look at monthly stats, 11 out of 12 times they would come up unused.
you could load index usage states into table and periodically report off of that.
August 27, 2013 at 1:23 pm
Hmm, no point in maintaining indexes for 11 months in which they are not being used.
But 30 days? That's a different q. Typically it depends on the size and volatility of the table.
For example, if the indexes are constantly being maintained, you might want to remove indexes that are only used every thirty days -- if necessary, create them when they are needed and then remove them again.
But, if the table is large and the volatility is low, you would likely leave the indexes in place even if they are infrequently used, as the cost of recreating them is relatively so high.
As always with indexes, you have to decide the appropriate level of indexing based on all usage factors.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply