Rebuild index in Maintenance Plan

Question

question

Chitra asked Dec 1, '09

Rebuild index in Maintenance Plan

Do I need to rebuild index in maintenance plan if the following query gives null values:

http://www.sqlskills.com/BLOGS/PAUL/post/Indexes-From-Every-Angle-How-can-you-tell-if-an-index-is-being-used.aspx

                    
SELECT object_name(b.object_id) as obj_name, b.name as index_name, a.*                    
FROM sys.dm_db_index_usage_stats a                    
right outer join sys.indexes b on a.object_id = b.object_id                     
      and a.index_id = b.index_id and a.database_id = 5                    
where b.object_id > 100                    
order by user_seeks + user_scans

Regards

Chitra

indexes maintenance-plans

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-08-03T10:17:28Z

Håkan Winther answered Aug 3, '12

First of all, You don't need to rebuild indexes that doesn't have fragmentation, but this query doesn't tell you if the index is fragmented or not. You should replace `dm_db_index_usage_stats` with `dm_db_index_physical_stats` to get the fragmentation stats. Second, the DMW:s will be cleared when the instance is restarted etc, and that means that your indexes that hasn't been used after the restart may have been used before and might be used later on. (I know, it's an old question and you probably found the answer elsewhere, but... someone else might benefit from this answer)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question