Working with SQLFool's Index Defrag Script

AaronBertrand — Mon, 13 Apr 2009 14:16:00 GMT

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.

Search results matching tags 'Monitoring' and 'index maintenance'

Working with SQLFool's Index Defrag Script