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

I have fixed some bugs in my isp_ALTER_INDEX stored procedure. Use this 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

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,
    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

    UPDATE f
    SET Online = 0
    FROM #FragIndex f
    JOIN
    (
        SELECT [object_id] AS ObjectId, alloc_unit_type_desc AS AllocUnitType
        FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, NULL)
    ) s
    ON f.ObjectId = s.ObjectId
    WHERE 
        f.IndexType = 'CLUSTERED INDEX' AND 
        s.AllocUnitType = 'LOB_DATA'

    -- we can't determine if the indexes are disabled or partitioned yet,
    -- so we'll need to figure that out during the loop
END

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

    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)

    SET @i = @i + 1

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

DROP TABLE #FragIndex

Print | posted on Tuesday, April 17, 2007 10:29 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Defragment Indexes stored procedure - new version

Getting the following error:

Msg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 62
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 94
Incorrect syntax near '('.

THoughts?
4/19/2007 11:23 PM | jayson knight
Gravatar

# re: Defragment Indexes stored procedure - new version

Jayson,

I just ran this on a system where I don't have this stored procedure already and I don't encounter any errors when I do a direct copy/paste from this weblog.

If you can't figure out what the problem is, send me a message and I'll send you the script.
4/20/2007 6:01 AM | Tara
Gravatar

# Fragmentation on the database - close friend of a DBA

On day-to-day basis a DBA might come across with the issues on the fragmentation on the database, it
4/24/2007 12:41 PM | SSQA- Users &amp; SQL tools
Gravatar

# re: Defragment Indexes stored procedure - new version

Hi Tara

I am putting together our own defragging routine so I have been referring to yours :-) Thanks for the resource.

Just a couple of points - if stats mode is LIMITED you get NULL row counts, so nothing will get defragged (....record_count > @minRowCount). Also, if stats mode is DETAILED you get one row per B-Tree level that meets the fragmentation threshold. I noticed this when my logging procedure was defragging indexes several times for each run.

As such I don't have a stats mode parameter in my proc any more - it is hard coded as SAMPLED (like your default).

HTH
4/27/2007 9:28 AM | pootle_flump
Gravatar

# re: Defragment Indexes stored procedure - new version

Perhaps that's why my code is having issues on one particular database. I have had to disable it for now.

Thanks for the info!
4/27/2007 10:07 AM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET