Ramblings of a DBA

Defragment Indexes stored procedure

UPDATE: This stored procedure has been updated.

This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels.

If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot.  Kalen Delaney mentions this in the May 2006 edition of SQL Server Magazine (InstantDoc ID 49769).

-------------------------------------------------------------------------------------------
-- OBJECT NAME           : isp_ALTER_INDEX
--
-- AUTHOR                : Tara Kizer
--
-- DATE                  : February 27, 2007
--
-- INPUTS                : @dbName         - name of the database
--                         @statsMode      - mode to use for sys.dm_db_index_physical_stats 
--                         @defragType     - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
--                         @minFragPercent - minimum fragmentation level
--                         @maxFragPercent - maximum fragmentation level
--                         @minRowCount    - minimum row count
--
-- OUTPUTS               : None
--
-- DEPENDENCIES          : sys.dm_db_index_physical_stats, sys.objects, 
--                         sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION           : Defragments indexes
/*
                           EXEC isp_ALTER_INDEX 
                            @dbName = 'DatabaseName', 
                            @statsMode = 'SAMPLED', 
                            @defragType = 'REBUILD', 
                            @minFragPercent = 10,
                            @maxFragPercent = 100,
                            @ignoreRowCount = 1000
*/
-------------------------------------------------------------------------------------------
CREATE PROC dbo.isp_ALTER_INDEX
(
    @dbName sysname, 
    @statsMode varchar(8) = 'SAMPLED', 
    @defragType varchar(10) = 'REORGANIZE', 
    @minFragPercent int = 25, 
    @maxFragPercent int = 100, 
    @minRowCount int = 0
)
AS

SET NOCOUNT ON

DECLARE 
    @i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname, 
    @schemaName sysname, @partitionNumber int, @partitionCount int, @recordCount bigint, 
    @sql nvarchar(4000), @edition int, @parmDef nvarchar(500)

SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

SELECT 
    IDENTITY(int, 1, 1) AS FragIndexId, 
    [object_id] AS ObjectId, 
    index_id AS IndexId, 
    avg_fragmentation_in_percent AS FragPercent, 
    record_count AS RecordCount, 
    partition_number AS PartitionNumber
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE 
    avg_fragmentation_in_percent > @minFragPercent AND 
    avg_fragmentation_in_percent < @maxFragPercent AND 
    index_id > 0 AND 
    record_count > @minRowCount
ORDER BY ObjectId

SELECT 
    @objectId = ObjectId, 
    @indexId = IndexId, 
    @recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i

WHILE @@ROWCOUNT <> 0
BEGIN
    SET @sql = '
        SELECT @objectName = o.[name], @schemaName = s.[name]
        FROM ' + @dbName + '.sys.objects o
        JOIN ' + @dbName + '.sys.schemas s 
        ON s.schema_id = o.schema_id
        WHERE o.[object_id] = @objectId'

    SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

    EXEC sp_executesql 
        @sql, @parmDef, @objectId = @objectId, 
        @objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

    SET @sql = '
        SELECT @indexName = [name]
        FROM ' + @dbName + '.sys.indexes
        WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0'

    SET @parmDef = N'@objectId int, @indexId int, @indexName sysname OUTPUT'

    EXEC sp_executesql 
        @sql, @parmDef, @objectId = @objectId, @indexId = @indexId, 
        @indexName = @indexName OUTPUT

    SET @sql = '
        SELECT @partitionCount = COUNT(*)
        FROM ' + @dbName + '.sys.partitions
        WHERE [object_id] = @objectId AND index_id = @indexId'

    SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'

    EXEC sp_executesql 
        @sql, @parmDef, @objectId = @objectId, @indexId = @indexId, 
        @partitionCount = @partitionCount OUTPUT

    SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' + 
        @schemaName + '].[' + @objectName + '] ' + @defragType

    -- Developer and Enterprise have the ONLINE = ON option for REBUILD
    IF @defragType = 'REBUILD' AND @edition = 3
        SET @sql = @sql + ' WITH (ONLINE = ON)'

    IF @partitionCount > 1
        SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

    EXEC (@SQL)

    SET @i = @i + 1

    SELECT 
        @objectId = ObjectId, 
        @indexId = IndexId, 
        @recordCount = RecordCount
    FROM #FragIndex
    WHERE FragIndexId = @i
END

DROP TABLE #FragIndex

Print | posted on Wednesday, March 07, 2007 4:10 PM | Filed Under [ SQL Server - Database Administration ]