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.
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP 100 [DatabaseName], [ObjectName], [IndexName], count(*) as [Frequency], CASE WHEN ([Command] LIKE '{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}REBUILD{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}') THEN 'REBUILD' ELSE 'REORGANIZE' END AS [TYPE] FROM [dbo].[CommandLog] 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?)