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