asked
Detecting bad Indexes
How can indexes, which need to be recreated, be detected? Of course a query taking a long time to run can signal trouble with an index, but how do you detect them before that happens?
Do you mean an index that is fragmented? Or do you mean an index that is not used becaues it's not "good enough" If you need to see indexes that are fragmented you can use sys.dm_db_index_physical_stats Read more at : [
http://msdn.microsoft.com/en-us/library/ms188917.aspx][1] I also recommend you to take a look at Ola Hallengrens "maintenance solution", that contains an excellent index maintenance solution. [
http://ola.hallengren.com/][2] If you mean un-used indexes take a look at sys.dm_db_index_usage_stats Read more at :[
http://msdn.microsoft.com/en-us/library/ms188755.aspx][3] [1]:
http://msdn.microsoft.com/en-us/library/ms188917.aspx [2]:
http://ola.hallengren.com/ [3]:
http://msdn.microsoft.com/en-us/library/ms188755.aspx
Keep in mind that sometimes that performance issues are caused by statistics that is out of date. The first thing you can try when queries suddenly takes long time is to update the statistics. The statistics should be updated automatically, unless turned off, but only if more than 20% of the data has been modified.
You will get different results depending on the parameters you specify. Can't remember right now, but I think it is database Id, table Id, index id, partition number and mode. If you use null in any of the three first parameters you will get records for more then one table.
I have the same questions as Håkan..... But to add to those useful links: - Michelle Ufford's fantastic [index defrag scripts][1] - plenty of scripts over on SSC for finding [unused indexes][2] - Paul Neilsen's [duplicate index script][3] - and finally for good measure, Jesse Roberge's [missing index finder script][4] [1]:
http://sqlfool.com/2010/04/index-defrag-script-v4-0/ [2]:
http://www.sqlservercentral.com/search/?q=unused+indexes&t=s [3]:
http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx [4]:
http://www.sqlservercentral.com/scripts/Index+Management/63937/
