Defragment Indexes stored procedure - new version

Tue Nov 27, 2007 by Tara Kizer in sql-server-database-administration

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
<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="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>
<span class="rem">-- we also have to figure out if the table contains lob_data when</span>
<span class="rem">-- a clustered index exists during the loop</span>

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'
<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">IF</span> @indexType = <span class="str">&#39;CLUSTERED INDEX&#39;</span>
<span class="kwrd">BEGIN</span>
    <span class="rem">-- can&#39;t use online option if index is clustered and table contains following </span>
    <span class="rem">-- data types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml</span>
    <span class="rem">-- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml</span>
    <span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;
        SELECT @online = 0
        FROM &#39;</span> + @dbName + <span class="str">&#39;.INFORMATION_SCHEMA.COLUMNS c
        WHERE    TABLE_NAME = @objectName AND
                (DATA_TYPE IN (&#39;</span><span class="str">&#39;text&#39;</span><span class="str">&#39;, &#39;</span><span class="str">&#39;ntext&#39;</span><span class="str">&#39;, &#39;</span><span class="str">&#39;image&#39;</span><span class="str">&#39;) OR 
                CHARACTER_MAXIMUM_LENGTH = -1)&#39;</span>

    <span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;@objectName sysname, @online bit OUTPUT&#39;</span>

    <span class="kwrd">EXEC</span> sp_executesql 
        @<span class="kwrd">sql</span>, @parmDef, @objectName = @objectName, @online = @online <span class="kwrd">OUTPUT</span>
<span class="kwrd">END</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">SELECT</span> @i = <span class="kwrd">MIN</span>(FragIndexId) 
<span class="kwrd">FROM</span> #FragIndex
<span class="kwrd">WHERE</span> FragIndexId &gt; @i

<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


Peter 2007-11-28	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

Tara 2007-11-28	re: Defragment Indexes stored procedure - new version Because mine is shorter. ;-) It's just a coding style though. I find mind easier to read.

adam 2007-12-31	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

vijetha 2008-01-04	re: Defragment Indexes stored procedure - new version sql server query

Jesse 2008-01-04	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.

Ola Hallengren 2008-01-09	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.