Finding Recurring Index Fragmentation Issues

One of the nice things about Ola Hallengren’s maintenance solution is that it has the option to log its operations to a table.  This gives you ability to do a little data mining on your maintenance operations and help identify some areas that may need your attention.  (Quick tangent: If you’re not currently using Ola’s script, you really should check it out!)

One of the things I was curious about was how many indexes have regularly recurring fragmentation issues, and how often.  So, I sat down this morning and spent about a minute coming up with this query against the CommandLog table, which is where Ola’s script logs its operations.

This query is pretty simple, it will count the number of entries for each index that has been modified over the last 60 days and specify whether it was a REBUILD or REORGANIZE operation.

To my surprise, I found that I had one nonclustered index that has been rebuilt 55 times in the last 60 days, and a clustered index that looks like it’s getting reorganize about every other day.  I also have a few that have regular occurrences of both rebuilds and reorganizations.

So, by conducting a quick review of my index maintenance operations, I now know that I have a few indexes that need further investigation.  I may need to adjust some fill factors, add some filters, or just learn to live with the fact that I have indexes and tables that stay fragmented.  (Is there a support group for that?)

 

(Visited 248 times, 1 visits today)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.