Working with SQLFool's Index Defrag Script
Last week I decided to play with SQLFool's Automated Index Defrag Script and found it very usable and worthwhile. Terrific work Michelle! The script rivals Ola Hallengren's broader maintenance scripts that I use often.
On smaller databases it ran quite quickly and with the correct and desired effects. On larger databases, due to the amount of time it takes to run, I found a couple of things lacking on the reporting side. Not serious things, mind you, and nothing that is really all that hard to work around. But I thought I would share them anyway, in case you were using the script and coming across similar issues.
What index is currently being reorganized?
There is a great output table that records the index reorganize tasks, called dbo.dba_indexDefragLog. This records an entry as each index reorganize statement completes. A great enhancement would be to record an entry when each index reorganize command *starts*, so that I can track its progress (for log entries like this I typically have a Start/End column pair, with Start populated by default, and End updated after the task is complete). In the meantime, assuming you know the session_id (SPID) that is running the script, you can figure it out easily by using the following query… which not only tells you which index is being reorganized, but also an estimated completion time (lucky us, ALTER INDEX REORGANIZE is one of the commands that shows percent_complete in sys.dm_exec_requests):
SELECT r.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP), t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.session_id = <SPID>;
Before vs. After Fragmentation
The output table dbo.dba_indexDefragLog shows the fragmentation of the index *before* the reorganize happened. An excellent addendum to the logging table would be to show the fragmentation level *after* the reorganize was complete. Currently, to see the before and after picture, I would need to go look at sys.dm_db_index_physical_stats for each index (since you cannot use CROSS APPLY against this particular DMV). Paul Randal to the rescue: he explains how to work around this issue. So taking that information, I created the same function, and then wrote the following, which queries the dbo.dba_indexDefragLog table and then runs a CROSS APPLY against the new function to get the *after* picture.
IF OBJECT_ID('dbo.dba_index_physical_stats') IS NOT NULL DROP FUNCTION dbo.dba_index_physical_stats; GO CREATE FUNCTION dbo.dba_index_physical_stats ( @database_id INT, @object_id INT, @index_id INT, @partition_number INT, @mode SYSNAME ) RETURNS @result TABLE ( database_id SMALLINT, [object_id] INT, index_id INT, partition_number INT, index_type_desc NVARCHAR(60), alloc_unit_type_desc NVARCHAR(60), index_depth TINYINT, index_level TINYINT, avg_fragmentation_in_percent FLOAT, fragment_count BIGINT, avg_fragment_size_in_pages FLOAT, page_count BIGINT, avg_page_space_used_in_percent FLOAT, record_count BIGINT, ghost_record_count BIGINT, version_ghost_record_count BIGINT, min_record_size_in_bytes INT, max_record_size_in_bytes INT, avg_record_size_in_bytes FLOAT, forwarded_record_count BIGINT ) BEGIN INSERT INTO @result SELECT * FROM sys.dm_db_index_physical_stats ( @database_id, @object_id, @index_id, @partition_number, @mode ); RETURN; END; GO -- now I can run queries like: SELECT TOP (10) l.databaseName, l.objectName, l.indexName, l.dateTimeStart, dateTimeFinish = DATEADD(SECOND, l.durationSeconds, l.dateTimeStart), l.durationSeconds, fragmentation_before = l.fragmentation, fragmentation_after = s.avg_fragmentation_in_percent, page_count_before = l.page_count, page_count_after = s.page_count FROM dbo.dba_indexDefragLog AS l CROSS APPLY dbo.dba_index_physical_stats ( l.DatabaseID, l.ObjectID, l.IndexID, l.PartitionNumber, NULL ) AS s ORDER BY indexDefrag_id DESC;
Obviously this function is pretty expensive, which is why it would be better if it were called once per index at the end of the task, instead of querying every time. But I don't want to touch Michelle's script… if she thinks these enhancements are worthwhile, I am sure she will add them.