December 12, 2011 at 7:58 am
Hello,
I'm thinking of evaluating the index usage of my database to my Index maintenance script but I am not sure what a good threshold would be to determine what action should be taken. Is anyone currently doing this with their index maintenance, and if so could you let me know how you are doing it?
Thanks
December 12, 2011 at 8:03 am
Determining if an index is in use is a lot more complex than it might appear.
The index usage stats are only since the last start of the database, so if your server is rebooted weekly, you have, at most, a week of data. What about indexes that might be essential to a month-end process.
If can be done, but I'd advise that dropping indexes should be something done manually, based on index usage stats over a period of time and analysis of the usage of the tables, not something that runs automatically.
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
December 12, 2011 at 9:37 am
I regularly look at our index usage stats (we rarely restart our production server) and on occasion identify what would appear to be an unneeded (either never used or maintenance does not justify the benefits) index.
Whats the best way to identify queries that use such an index.
December 12, 2011 at 1:16 pm
Nick - Check out this blog post by Jonathan Kehayias. Should give you what you're looking for?
December 13, 2011 at 4:09 am
Great article, thanks for the link 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply