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.
SELECT TOP 100 [DatabaseName], [ObjectName], [IndexName], count(*) as [Frequency],
CASE WHEN ([Command] LIKE '%REBUILD%') THEN 'REBUILD' ELSE 'REORGANIZE' END AS [TYPE]
WHERE [commandtype] = 'ALTER_INDEX'
AND [starttime] > DATEADD(d,-60,GETDATE())
GROUP BY [DatabaseName], [ObjectName], [IndexName], [Command]
ORDER BY [Frequency] DESC
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?)