Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Defragment Indexes stored procedure - new version

UPDATE: This stored procedure has been updated.

I have fixed a bug in my isp_ALTER_INDEX stored procedure.  My old stored procedure was not handling the online option correctly for clustered indexes when there were LOB data types anywhere in the table.  Use this new version instead of the old one.

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      - LIMITED, SAMPLED or DETAILED
--                         @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,
                            @minRowCount = 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

IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
BEGIN
    RAISERROR('@statsMode must be LIMITED, SAMPLED or DETAILED', 16, 1)
    RETURN
END

IF @defragType NOT IN ('REORGANIZE', 'REBUILD')
BEGIN
    RAISERROR('@defragType must be REORGANIZE or REBUILD', 16, 1)
    RETURN
END

DECLARE 
    @i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname, 
    @schemaName sysname, @partitionNumber int, @partitionCount int,
    @sql nvarchar(4000), @edition int, @parmDef nvarchar(500), @allocUnitType nvarchar(60),
    @indexType nvarchar(60), @online bit, @disabled bit, @dataType nvarchar(128),
    @charMaxLen int

SELECT @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,
    index_type_desc AS IndexType,
    alloc_unit_type_desc AS AllocUnitType,
    0 AS Online
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
ORDER BY ObjectId

-- LIMITED does not include data for record_count
IF @statsMode IN ('SAMPLED', 'DETAILED')
    DELETE FROM #FragIndex
    WHERE RecordCount < @minRowCount

-- Developer and Enterprise have the ONLINE = ON option for REBUILD
-- Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:
-- Disabled indexes, XML indexes, Indexes on local temp tables, Partitioned indexes,
-- Clustered indexes if the underlying table contains LOB data types,
-- Nonclustered indexes that are defined with LOB data type columns
IF @defragType = 'REBUILD' AND @edition = 3
BEGIN
    UPDATE #FragIndex
    SET Online = 1

    UPDATE #FragIndex
    SET Online = 
            CASE
                WHEN IndexType = 'XML INDEX' THEN 0
                WHEN IndexType = 'NONCLUSTERED INDEX' AND AllocUnitType = 'LOB_DATA' THEN 0
                ELSE 1
            END

    -- we can't determine if the indexes are disabled or partitioned yet,
    -- so we'll need to figure that out during the loop
    -- we also have to figure out if the table contains lob_data when
    -- a clustered index exists during the loop
END

SELECT @i = MIN(FragIndexId) 
FROM #FragIndex

SELECT 
    @objectId = ObjectId, 
    @indexId = IndexId, 
    @partitionNumber = PartitionNumber,
    @indexType = IndexType,
    @online = Online
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

    IF @indexType = 'CLUSTERED INDEX'
    BEGIN
        -- can't use online option if index is clustered and table contains following 
        -- data types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml
        -- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml
        SET @sql = '
            SELECT @online = 0
            FROM ' + @dbName + '.INFORMATION_SCHEMA.COLUMNS c
            WHERE    TABLE_NAME = @objectName AND
                    (DATA_TYPE IN (''text'', ''ntext'', ''image'') OR 
                    CHARACTER_MAXIMUM_LENGTH = -1)'

        SET @parmDef = N'@objectName sysname, @online bit OUTPUT'

        EXEC sp_executesql 
            @sql, @parmDef, @objectName = @objectName, @online = @online OUTPUT
    END

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

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

    EXEC sp_executesql 
        @sql, @parmDef, @objectId = @objectId, @indexId = @indexId, 
        @indexName = @indexName OUTPUT, @disabled = @disabled 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

    IF    @online = 1 AND @disabled = 0 AND @partitionCount = 1
        SET @sql = @sql + ' WITH (ONLINE = ON)'

    IF @partitionCount > 1 AND @disabled = 0 AND @indexType <> 'XML INDEX'
        SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

    EXEC (@SQL)

    SELECT @i = MIN(FragIndexId) 
    FROM #FragIndex
    WHERE FragIndexId > @i

    SELECT 
        @objectId = ObjectId, 
        @indexId = IndexId, 
        @partitionNumber = PartitionNumber,
        @indexType = IndexType,
        @online = Online
    FROM #FragIndex
    WHERE FragIndexId = @i
END

DROP TABLE #FragIndex

Print | posted on Tuesday, November 27, 2007 12:09 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Defragment Indexes stored procedure - new version

Instead of lines like

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

Why not use

SET @sql = 'ALTER INDEX ' + QUOTENAME(@indexName) + ' ON ' + QUOTENAME(@dbName) + '.' +
QUOTENAME(@schemaName) + '.' + QUOTENAME(@objectName) + ' ' + @defragType
11/28/2007 1:50 AM | Peter
Gravatar

# re: Defragment Indexes stored procedure - new version

Because mine is shorter. ;-)

It's just a coding style though. I find mind easier to read.
11/28/2007 8:44 AM | Tara
Gravatar

# re: Defragment Indexes stored procedure - new version

hours of help; No Doubt!!

but instead of
avg_fragmentation_in_percent < @maxFragPercent
how bout
avg_fragmentation_in_percent <= @maxFragPercent
12/31/2007 1:23 PM | adam
Gravatar

# re: Defragment Indexes stored procedure - new version

sql server query
1/4/2008 12:51 AM | vijetha
Gravatar

# re: Defragment Indexes stored procedure - new version

about Peter's comment - using QUOTENAME is a bit safer than adding the brackets yourself because it's safe against the case some bonehead puts a [ or ] character in their object name.
1/4/2008 7:44 PM | Jesse
Gravatar

# re: Defragment Indexes stored procedure - new version

I would like to share some code for optimizing indexes that works a little differently than Tara's. You can for example choose to do online rebuild on indexes with high fragmentation, reorganize indexes with high fragmentation that cannot be rebuilt online, reorganize indexes with medium fragmentation and ignore indexes with low fragmentation. The code is available on http://ola.hallengren.com.
1/9/2008 11:05 AM | Ola Hallengren
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET