Defragment Indexes stored procedure - new version

Tue Apr 17, 2007 by Tara Kizer in sql-server-database-administration

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
<span class="kwrd">UPDATE</span> #FragIndex
<span class="kwrd">SET</span> Online = 
        <span class="kwrd">CASE</span>
            <span class="kwrd">WHEN</span> IndexType = <span class="str">&#39;XML INDEX&#39;</span> <span class="kwrd">THEN</span> 0
            <span class="kwrd">WHEN</span> IndexType = <span class="str">&#39;NONCLUSTERED INDEX&#39;</span> <span class="kwrd">AND</span> AllocUnitType = <span class="str">&#39;LOB_DATA&#39;</span> <span class="kwrd">THEN</span> 0
            <span class="kwrd">ELSE</span> 1
        <span class="kwrd">END</span>

<span class="kwrd">UPDATE</span> f
<span class="kwrd">SET</span> Online = 0
<span class="kwrd">FROM</span> #FragIndex f
<span class="kwrd">JOIN</span>
(
    <span class="kwrd">SELECT</span> [object_id] <span class="kwrd">AS</span> ObjectId, alloc_unit_type_desc <span class="kwrd">AS</span> AllocUnitType
    <span class="kwrd">FROM</span> sys.dm_db_index_physical_stats (DB_ID(@dbName), <span class="kwrd">NULL</span>, <span class="kwrd">NULL</span>, <span class="kwrd">NULL</span>, <span class="kwrd">NULL</span>)
) s
<span class="kwrd">ON</span> f.ObjectId = s.ObjectId
<span class="kwrd">WHERE</span> 
    f.IndexType = <span class="str">&#39;CLUSTERED INDEX&#39;</span> <span class="kwrd">AND</span> 
    s.AllocUnitType = <span class="str">&#39;LOB_DATA&#39;</span>

<span class="rem">-- we can&#39;t determine if the indexes are disabled or partitioned yet,</span>
<span class="rem">-- so we&#39;ll need to figure that out during the loop</span>

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'
<span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT&#39;</span>

<span class="kwrd">EXEC</span> sp_executesql 
    @<span class="kwrd">sql</span>, @parmDef, @objectId = @objectId, 
    @objectName = @objectName <span class="kwrd">OUTPUT</span>, @schemaName = @schemaName <span class="kwrd">OUTPUT</span>

<span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;
    SELECT @indexName = [name], @disabled = is_disabled
    FROM &#39;</span> + @dbName + <span class="str">&#39;.sys.indexes
    WHERE [object_id] = @objectId AND index_id = @indexId&#39;</span>

<span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;
    @objectId int, @indexId int, @indexName sysname OUTPUT, @disabled bit OUTPUT&#39;</span>

<span class="kwrd">EXEC</span> sp_executesql 
    @<span class="kwrd">sql</span>, @parmDef, @objectId = @objectId, @indexId = @indexId, 
    @indexName = @indexName <span class="kwrd">OUTPUT</span>, @disabled = @disabled <span class="kwrd">OUTPUT</span>

<span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;
    SELECT @partitionCount = COUNT(*)
    FROM &#39;</span> + @dbName + <span class="str">&#39;.sys.partitions
    WHERE [object_id] = @objectId AND index_id = @indexId&#39;</span>

<span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;@objectId int, @indexId int, @partitionCount int OUTPUT&#39;</span>

<span class="kwrd">EXEC</span> sp_executesql 
    @<span class="kwrd">sql</span>, @parmDef, @objectId = @objectId, @indexId = @indexId, 
    @partitionCount = @partitionCount <span class="kwrd">OUTPUT</span>

<span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;ALTER INDEX [&#39;</span> + @indexName + <span class="str">&#39;] ON [&#39;</span> + @dbName + <span class="str">&#39;].[&#39;</span> + 
    @schemaName + <span class="str">&#39;].[&#39;</span> + @objectName + <span class="str">&#39;] &#39;</span> + @defragType

<span class="kwrd">IF</span> @online = 1 <span class="kwrd">AND</span> @disabled = 0 <span class="kwrd">AND</span> @partitionCount = 1
    <span class="kwrd">SET</span> @<span class="kwrd">sql</span> = @<span class="kwrd">sql</span> + <span class="str">&#39; WITH (ONLINE = ON)&#39;</span>

<span class="kwrd">IF</span> @partitionCount &gt; 1 <span class="kwrd">AND</span> @disabled = 0 <span class="kwrd">AND</span> @indexType &lt;&gt; <span class="str">&#39;XML INDEX&#39;</span>
    <span class="kwrd">SET</span> @<span class="kwrd">sql</span> = @<span class="kwrd">sql</span> + <span class="str">&#39; PARTITION = &#39;</span> + <span class="kwrd">CAST</span>(@partitionNumber <span class="kwrd">AS</span> <span class="kwrd">varchar</span>(10))

<span class="kwrd">EXEC</span> (@<span class="kwrd">SQL</span>)

<span class="kwrd">SET</span> @i = @i + 1

<span class="kwrd">SELECT</span> 
    @objectId = ObjectId, 
    @indexId = IndexId, 
    @partitionNumber = PartitionNumber,
    @indexType = IndexType,
    @online = Online
<span class="kwrd">FROM</span> #FragIndex
<span class="kwrd">WHERE</span> FragIndexId = @i

END
DROP TABLE #FragIndex

Legacy Comments


jayson knight 2007-04-19	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?

Tara 2007-04-20	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.

pootle_flump 2007-04-27	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

Tara 2007-04-27	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!