Thanks to everyone who left a comment or sent me an e-mail regarding the Index Defrag Script. I’ve received some great feedback and requests for features. I’ve also had some questions regarding how to use it, which I will answer at the end of this post.
Changes include:
– separate version for both Enterprise and Standard editions
– Standard edition removes partitioning and online options
– output option to see fragmentation levels
– page_count added to the log table
I’ve also verified that this script works well in SQL 2008.
Enterprise Version:
IF EXISTS(SELECT OBJECT_ID FROM sys.tables
WHERE [name] = N'dba_indexDefragLog')
BEGIN
DROP TABLE dbo.dba_indexDefragLog;
PRINT 'dba_indexDefragLog table dropped!';
END
CREATE TABLE dbo.dba_indexDefragLog
(
indexDefrag_id INT IDENTITY(1,1) NOT NULL
, objectID INT NOT NULL
, objectName NVARCHAR(130) NOT NULL
, indexID INT NOT NULL
, indexName NVARCHAR(130) NOT NULL
, partitionNumber smallint not null
, fragmentation FLOAT NOT NULL
, page_count INT NOT NULL
, dateTimeStart DATETIME NOT NULL
, durationSeconds INT NOT NULL
CONSTRAINT PK_indexDefragLog
PRIMARY KEY CLUSTERED (indexDefrag_id)
);
PRINT 'dba_indexDefragLog Table Created';
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'),
N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_indexDefrag_sp
As Print ''Hello World!''');
RAISERROR('Procedure dba_indexDefrag_sp created.'
, 10, 1);
END;
Go
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
ALTER PROCEDURE dbo.dba_indexDefrag_sp
/* Declare Parameters */
@minFragmentation FLOAT = 10.0
/* in percent, will not defrag if fragmentation
less than specified */
, @rebuildThreshold FLOAT = 30.0
/* in percent, greater than @rebuildThreshold
will result in rebuild instead of reorg */
, @onlineRebuild bit = 1
/* 1 = online rebuild; 0 = offline rebuild */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @tableName VARCHAR(4000) = Null
/* Option to specify a table name */
, @printCommands BIT = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation BIT = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay CHAR(8) = '00:00:05'
/* time to wait between defrag commands */
AS
/********************************************************************
Name: dba_indexDefrag_sp
Author: Michelle F. Ufford
Purpose: Defrags all indexes for the current database
Notes: This script was designed for SQL Server 2005
Enterprise Edition.
CAUTION: Monitor transaction log if executing for the first time!
@minFragmentation defaulted to 10%, will not defrag if
fragmentation if less than specified.
@rebuildThreshold defaulted to 30% as recommended by
Microsoft in BOL;
> than 30% will result in rebuild instead
@onlineRebuild 1 = online rebuild;
0 = offline rebuild
@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only
@tableName Specify if you only want to defrag indexes
for a specific table
@printCommands 1 = print commands to screen;
0 = do not print commands
@printFragmentation 1 = print fragmentation to screen;
0 = do not print fragmentation
@defragDelay time to wait between defrag commands;
gives the server some time to catch up
Called by: SQL Agent Job or DBA
Date Initials Description
----------------------------------------------------------------
2008-10-27 MFU Initial Release
2008-11-17 MFU Added page_count to log table
, added @printFragmentation option
********************************************************************
Exec dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1;
********************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
BEGIN
/* Declare our variables */
DECLARE @objectID INT
, @indexID INT
, @partitionCount BIGINT
, @schemaName NVARCHAR(130)
, @objectName NVARCHAR(130)
, @indexName NVARCHAR(130)
, @partitionNumber SMALLINT
, @partitions SMALLINT
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT;
/* Just a little validation... */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 10.0;
IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;
IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
SET @defragDelay = '00:00:05';
/* Determine which indexes to defrag using our
user-defined parameters */
SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus'
/* 0 = unprocessed, 1 = processed */
INTO #indexDefragList
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
WHERE avg_fragmentation_in_percent > @minFragmentation
And index_id > 0
OPTION (MaxDop 1);
/* Create a clustered index to boost performance a little */
CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(objectID, indexID, partitionNumber);
/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList
WHERE defragStatus = 0) > 0
BEGIN
/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID = objectID
, @fragmentation = fragmentation
, @indexID = indexID
, @partitionNumber = partitionNumber
, @pageCount = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;
/* Look up index information */
SELECT @objectName = QUOTENAME(o.name)
, @schemaName = QUOTENAME(s.name)
FROM sys.objects AS o
Inner Join sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.OBJECT_ID = @objectID;
SELECT @indexName = QUOTENAME(name)
FROM sys.indexes
WHERE OBJECT_ID = @objectID
And index_id = @indexID
And type > 0;
/* Determine if the index is partitioned */
SELECT @partitionCount = COUNT(*)
FROM sys.partitions
WHERE OBJECT_ID = @objectID
And index_id = @indexID;
/* Look for LOBs */
SELECT TOP 1
@containsLOB = column_id
FROM sys.columns WITH (NOLOCK)
WHERE
[OBJECT_ID] = @objectID
And (system_type_id In (34, 35, 99)
-- 34 = image, 35 = text, 99 = ntext
Or max_length = -1);
-- varbinary(max), varchar(max), nvarchar(max), xml
/* See if we should rebuild or reorganize; handle thusly */
IF @fragmentation < @rebuildThreshold And @partitionCount <= 1
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + N' ReOrganize';
IF @fragmentation >= @rebuildThreshold
And IsNull(@containsLOB, 0) = 0
-- Cannot rebuild if the table has one or more LOB
And @partitionCount <= 1
BEGIN
/* We should always rebuild online if possible
(SQL 2005 Enterprise) */
IF @onlineRebuild = 0
SET @rebuildCommand = N' Rebuild With
(Online = Off, MaxDop = 1)';
ELSE
SET @rebuildCommand = N' Rebuild With
(Online = On, MaxDop = 1)';
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + @rebuildCommand;
END;
/* If our index is partitioned, we should always reorganize */
IF @partitionCount > 1
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + N' ReOrganize'
+ N' Partition = '
+ CAST(@partitionNumber AS NVARCHAR(10));
-- no MaxDop needed, single threaded operation
/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN
/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE (@sqlCommand);
SET @dateTimeEnd = GETDATE();
/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@objectID
, @objectName
, @indexID
, @indexName
, @partitionNumber
, @fragmentation
, @pageCount
, @dateTimeStart
, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);
/* Just a little breather for the server */
WAITFOR Delay @defragDelay;
/* Print if specified to do so */
IF @printCommands = 1
PRINT N'Executed: ' + @sqlCommand;
END
ELSE
/* Looks like we're not executing, just print
the commands */
BEGIN
IF @printCommands = 1
PRINT @sqlCommand;
END
/* Update our index defrag list when we've
finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;
END
/* Do we want to output our fragmentation results? */
If @printFragmentation = 1
Select idl.objectID
, o.name As 'tableName'
, idl.indexID
, i.name As 'indexName'
, idl.fragmentation
, idl.page_count
From #indexDefragList As idl
Join sys.objects AS o
On idl.objectID = o.object_id
Join sys.indexes As i
On idl.objectID = i.object_id
And idl.indexID = i.index_id;
/* When everything is done, make sure to get rid of
our temp table */
DROP TABLE #indexDefragList;
SET NOCOUNT OFF;
RETURN 0
END
Go |
IF EXISTS(SELECT OBJECT_ID FROM sys.tables
WHERE [name] = N'dba_indexDefragLog')
BEGIN
DROP TABLE dbo.dba_indexDefragLog;
PRINT 'dba_indexDefragLog table dropped!';
END
CREATE TABLE dbo.dba_indexDefragLog
(
indexDefrag_id INT IDENTITY(1,1) NOT NULL
, objectID INT NOT NULL
, objectName NVARCHAR(130) NOT NULL
, indexID INT NOT NULL
, indexName NVARCHAR(130) NOT NULL
, partitionNumber smallint not null
, fragmentation FLOAT NOT NULL
, page_count INT NOT NULL
, dateTimeStart DATETIME NOT NULL
, durationSeconds INT NOT NULL
CONSTRAINT PK_indexDefragLog
PRIMARY KEY CLUSTERED (indexDefrag_id)
);
PRINT 'dba_indexDefragLog Table Created';
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'),
N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_indexDefrag_sp
As Print ''Hello World!''');
RAISERROR('Procedure dba_indexDefrag_sp created.'
, 10, 1);
END;
Go
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
ALTER PROCEDURE dbo.dba_indexDefrag_sp
/* Declare Parameters */
@minFragmentation FLOAT = 10.0
/* in percent, will not defrag if fragmentation
less than specified */
, @rebuildThreshold FLOAT = 30.0
/* in percent, greater than @rebuildThreshold
will result in rebuild instead of reorg */
, @onlineRebuild bit = 1
/* 1 = online rebuild; 0 = offline rebuild */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @tableName VARCHAR(4000) = Null
/* Option to specify a table name */
, @printCommands BIT = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation BIT = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay CHAR(8) = '00:00:05'
/* time to wait between defrag commands */
AS
/********************************************************************
Name: dba_indexDefrag_sp
Author: Michelle F. Ufford
Purpose: Defrags all indexes for the current database
Notes: This script was designed for SQL Server 2005
Enterprise Edition.
CAUTION: Monitor transaction log if executing for the first time!
@minFragmentation defaulted to 10%, will not defrag if
fragmentation if less than specified.
@rebuildThreshold defaulted to 30% as recommended by
Microsoft in BOL;
> than 30% will result in rebuild instead
@onlineRebuild 1 = online rebuild;
0 = offline rebuild
@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only
@tableName Specify if you only want to defrag indexes
for a specific table
@printCommands 1 = print commands to screen;
0 = do not print commands
@printFragmentation 1 = print fragmentation to screen;
0 = do not print fragmentation
@defragDelay time to wait between defrag commands;
gives the server some time to catch up
Called by: SQL Agent Job or DBA
Date Initials Description
----------------------------------------------------------------
2008-10-27 MFU Initial Release
2008-11-17 MFU Added page_count to log table
, added @printFragmentation option
********************************************************************
Exec dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1;
********************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
BEGIN
/* Declare our variables */
DECLARE @objectID INT
, @indexID INT
, @partitionCount BIGINT
, @schemaName NVARCHAR(130)
, @objectName NVARCHAR(130)
, @indexName NVARCHAR(130)
, @partitionNumber SMALLINT
, @partitions SMALLINT
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT;
/* Just a little validation... */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 10.0;
IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;
IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
SET @defragDelay = '00:00:05';
/* Determine which indexes to defrag using our
user-defined parameters */
SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus'
/* 0 = unprocessed, 1 = processed */
INTO #indexDefragList
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
WHERE avg_fragmentation_in_percent > @minFragmentation
And index_id > 0
OPTION (MaxDop 1);
/* Create a clustered index to boost performance a little */
CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(objectID, indexID, partitionNumber);
/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList
WHERE defragStatus = 0) > 0
BEGIN
/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID = objectID
, @fragmentation = fragmentation
, @indexID = indexID
, @partitionNumber = partitionNumber
, @pageCount = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;
/* Look up index information */
SELECT @objectName = QUOTENAME(o.name)
, @schemaName = QUOTENAME(s.name)
FROM sys.objects AS o
Inner Join sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.OBJECT_ID = @objectID;
SELECT @indexName = QUOTENAME(name)
FROM sys.indexes
WHERE OBJECT_ID = @objectID
And index_id = @indexID
And type > 0;
/* Determine if the index is partitioned */
SELECT @partitionCount = COUNT(*)
FROM sys.partitions
WHERE OBJECT_ID = @objectID
And index_id = @indexID;
/* Look for LOBs */
SELECT TOP 1
@containsLOB = column_id
FROM sys.columns WITH (NOLOCK)
WHERE
[OBJECT_ID] = @objectID
And (system_type_id In (34, 35, 99)
-- 34 = image, 35 = text, 99 = ntext
Or max_length = -1);
-- varbinary(max), varchar(max), nvarchar(max), xml
/* See if we should rebuild or reorganize; handle thusly */
IF @fragmentation < @rebuildThreshold And @partitionCount <= 1
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + N' ReOrganize';
IF @fragmentation >= @rebuildThreshold
And IsNull(@containsLOB, 0) = 0
-- Cannot rebuild if the table has one or more LOB
And @partitionCount <= 1
BEGIN
/* We should always rebuild online if possible
(SQL 2005 Enterprise) */
IF @onlineRebuild = 0
SET @rebuildCommand = N' Rebuild With
(Online = Off, MaxDop = 1)';
ELSE
SET @rebuildCommand = N' Rebuild With
(Online = On, MaxDop = 1)';
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + @rebuildCommand;
END;
/* If our index is partitioned, we should always reorganize */
IF @partitionCount > 1
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + N' ReOrganize'
+ N' Partition = '
+ CAST(@partitionNumber AS NVARCHAR(10));
-- no MaxDop needed, single threaded operation
/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN
/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE (@sqlCommand);
SET @dateTimeEnd = GETDATE();
/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@objectID
, @objectName
, @indexID
, @indexName
, @partitionNumber
, @fragmentation
, @pageCount
, @dateTimeStart
, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);
/* Just a little breather for the server */
WAITFOR Delay @defragDelay;
/* Print if specified to do so */
IF @printCommands = 1
PRINT N'Executed: ' + @sqlCommand;
END
ELSE
/* Looks like we're not executing, just print
the commands */
BEGIN
IF @printCommands = 1
PRINT @sqlCommand;
END
/* Update our index defrag list when we've
finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;
END
/* Do we want to output our fragmentation results? */
If @printFragmentation = 1
Select idl.objectID
, o.name As 'tableName'
, idl.indexID
, i.name As 'indexName'
, idl.fragmentation
, idl.page_count
From #indexDefragList As idl
Join sys.objects AS o
On idl.objectID = o.object_id
Join sys.indexes As i
On idl.objectID = i.object_id
And idl.indexID = i.index_id;
/* When everything is done, make sure to get rid of
our temp table */
DROP TABLE #indexDefragList;
SET NOCOUNT OFF;
RETURN 0
END
Go
Standard Version:
IF EXISTS(SELECT OBJECT_ID FROM sys.tables
WHERE [name] = N'dba_indexDefragLog')
BEGIN
DROP TABLE dbo.dba_indexDefragLog;
PRINT 'dba_indexDefragLog table dropped!';
END
CREATE TABLE dbo.dba_indexDefragLog
(
indexDefrag_id INT IDENTITY(1,1) NOT NULL
, objectID INT NOT NULL
, objectName NVARCHAR(130) NOT NULL
, indexID INT NOT NULL
, indexName NVARCHAR(130) NOT NULL
, fragmentation FLOAT NOT NULL
, page_count INT NOT NULL
, dateTimeStart DATETIME NOT NULL
, durationSeconds INT NOT NULL
CONSTRAINT PK_indexDefragLog
PRIMARY KEY CLUSTERED (indexDefrag_id)
);
PRINT 'dba_indexDefragLog Table Created';
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'),
N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp
As Print ''Hello World!''');
RAISERROR('Procedure dba_indexDefragStandard_sp created.'
, 10, 1);
END;
Go
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
ALTER PROCEDURE dbo.dba_indexDefragStandard_sp
/* Declare Parameters */
@minFragmentation FLOAT = 10.0
/* in percent, will not defrag if fragmentation
less than specified */
, @rebuildThreshold FLOAT = 30.0
/* in percent, greater than @rebuildThreshold
will result in rebuild instead of reorg */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @tableName VARCHAR(4000) = Null
/* Option to specify a table name */
, @printCommands BIT = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation BIT = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay CHAR(8) = '00:00:05'
/* time to wait between defrag commands */
AS
/********************************************************************
Name: dba_indexDefragStandard_sp
Author: Michelle F. Ufford
Purpose: Defrags all indexes for the current database
Notes: This script was designed for SQL Server 2005
Standard edition.
CAUTION: Monitor transaction log if executing for the first time!
@minFragmentation defaulted to 10%, will not defrag if
fragmentation if less than specified.
@rebuildThreshold defaulted to 30% as recommended by
Microsoft in BOL;
> than 30% will result in rebuild instead
@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only
@tableName Specify if you only want to defrag indexes
for a specific table
@printCommands 1 = print commands to screen;
0 = do not print commands
@printFragmentation 1 = print fragmentation to screen;
0 = do not print fragmentation
@defragDelay time to wait between defrag commands;
gives the server some time to catch up
Called by: SQL Agent Job or DBA
Date Initials Description
----------------------------------------------------------------
2008-10-27 MFU Initial Release
2008-11-17 MFU Added page_count to log table
, added @printFragmentation option
********************************************************************
Exec dbo.dba_indexDefragStandard_sp
@executeSQL = 1
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1;
********************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
BEGIN
/* Declare our variables */
DECLARE @objectID INT
, @indexID INT
, @schemaName NVARCHAR(130)
, @objectName NVARCHAR(130)
, @indexName NVARCHAR(130)
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT;
/* Just a little validation... */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 10.0;
IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;
IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
SET @defragDelay = '00:00:05';
/* Determine which indexes to defrag using our
user-defined parameters */
SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus'
/* 0 = unprocessed, 1 = processed */
INTO #indexDefragList
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
WHERE avg_fragmentation_in_percent > @minFragmentation
And index_id > 0
OPTION (MaxDop 1);
/* Create a clustered index to boost performance a little */
CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(objectID, indexID);
/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList
WHERE defragStatus = 0) > 0
BEGIN
/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID = objectID
, @fragmentation = fragmentation
, @indexID = indexID
, @pageCount = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;
/* Look up index information */
SELECT @objectName = QUOTENAME(o.name)
, @schemaName = QUOTENAME(s.name)
FROM sys.objects AS o
Inner Join sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.OBJECT_ID = @objectID;
SELECT @indexName = QUOTENAME(name)
FROM sys.indexes
WHERE OBJECT_ID = @objectID
And index_id = @indexID
And type > 0;
/* Look for LOBs */
SELECT TOP 1
@containsLOB = column_id
FROM sys.columns WITH (NOLOCK)
WHERE
[OBJECT_ID] = @objectID
And (system_type_id In (34, 35, 99)
-- 34 = image, 35 = text, 99 = ntext
Or max_length = -1);
-- varbinary(max), varchar(max), nvarchar(max), xml
/* See if we should rebuild or reorganize; handle thusly */
IF @fragmentation < @rebuildThreshold
Or IsNull(@containsLOB, 0) > 0
-- Cannot rebuild if the table has one or more LOB
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + N' ReOrganize;'
ELSE
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + ' Rebuild '
+ 'With (MaxDop = 1)'; -- minimize impact on server
/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN
/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE (@sqlCommand);
SET @dateTimeEnd = GETDATE();
/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
objectID
, objectName
, indexID
, indexName
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@objectID
, @objectName
, @indexID
, @indexName
, @fragmentation
, @pageCount
, @dateTimeStart
, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);
/* Just a little breather for the server */
WAITFOR Delay @defragDelay;
/* Print if specified to do so */
IF @printCommands = 1
PRINT N'Executed: ' + @sqlCommand;
END
ELSE
/* Looks like we're not executing, just print
the commands */
BEGIN
IF @printCommands = 1
PRINT @sqlCommand;
END
/* Update our index defrag list when we've
finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE objectID = @objectID
And indexID = @indexID;
END
/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
SELECT idl.objectID
, o.name As 'tableName'
, idl.indexID
, i.name As 'indexName'
, idl.fragmentation
, idl.page_count
FROM #indexDefragList AS idl
JOIN sys.objects AS o
ON idl.objectID = o.object_id
JOIN sys.indexes As i
ON idl.objectID = i.object_id
AND idl.indexID = i.index_id;
/* When everything is done, make sure to get rid of
our temp table */
DROP TABLE #indexDefragList;
SET NOCOUNT OFF;
RETURN 0
END
Go |
IF EXISTS(SELECT OBJECT_ID FROM sys.tables
WHERE [name] = N'dba_indexDefragLog')
BEGIN
DROP TABLE dbo.dba_indexDefragLog;
PRINT 'dba_indexDefragLog table dropped!';
END
CREATE TABLE dbo.dba_indexDefragLog
(
indexDefrag_id INT IDENTITY(1,1) NOT NULL
, objectID INT NOT NULL
, objectName NVARCHAR(130) NOT NULL
, indexID INT NOT NULL
, indexName NVARCHAR(130) NOT NULL
, fragmentation FLOAT NOT NULL
, page_count INT NOT NULL
, dateTimeStart DATETIME NOT NULL
, durationSeconds INT NOT NULL
CONSTRAINT PK_indexDefragLog
PRIMARY KEY CLUSTERED (indexDefrag_id)
);
PRINT 'dba_indexDefragLog Table Created';
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'),
N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp
As Print ''Hello World!''');
RAISERROR('Procedure dba_indexDefragStandard_sp created.'
, 10, 1);
END;
Go
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
ALTER PROCEDURE dbo.dba_indexDefragStandard_sp
/* Declare Parameters */
@minFragmentation FLOAT = 10.0
/* in percent, will not defrag if fragmentation
less than specified */
, @rebuildThreshold FLOAT = 30.0
/* in percent, greater than @rebuildThreshold
will result in rebuild instead of reorg */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @tableName VARCHAR(4000) = Null
/* Option to specify a table name */
, @printCommands BIT = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation BIT = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay CHAR(8) = '00:00:05'
/* time to wait between defrag commands */
AS
/********************************************************************
Name: dba_indexDefragStandard_sp
Author: Michelle F. Ufford
Purpose: Defrags all indexes for the current database
Notes: This script was designed for SQL Server 2005
Standard edition.
CAUTION: Monitor transaction log if executing for the first time!
@minFragmentation defaulted to 10%, will not defrag if
fragmentation if less than specified.
@rebuildThreshold defaulted to 30% as recommended by
Microsoft in BOL;
> than 30% will result in rebuild instead
@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only
@tableName Specify if you only want to defrag indexes
for a specific table
@printCommands 1 = print commands to screen;
0 = do not print commands
@printFragmentation 1 = print fragmentation to screen;
0 = do not print fragmentation
@defragDelay time to wait between defrag commands;
gives the server some time to catch up
Called by: SQL Agent Job or DBA
Date Initials Description
----------------------------------------------------------------
2008-10-27 MFU Initial Release
2008-11-17 MFU Added page_count to log table
, added @printFragmentation option
********************************************************************
Exec dbo.dba_indexDefragStandard_sp
@executeSQL = 1
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1;
********************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
BEGIN
/* Declare our variables */
DECLARE @objectID INT
, @indexID INT
, @schemaName NVARCHAR(130)
, @objectName NVARCHAR(130)
, @indexName NVARCHAR(130)
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT;
/* Just a little validation... */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 10.0;
IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;
IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
SET @defragDelay = '00:00:05';
/* Determine which indexes to defrag using our
user-defined parameters */
SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus'
/* 0 = unprocessed, 1 = processed */
INTO #indexDefragList
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
WHERE avg_fragmentation_in_percent > @minFragmentation
And index_id > 0
OPTION (MaxDop 1);
/* Create a clustered index to boost performance a little */
CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(objectID, indexID);
/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList
WHERE defragStatus = 0) > 0
BEGIN
/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID = objectID
, @fragmentation = fragmentation
, @indexID = indexID
, @pageCount = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;
/* Look up index information */
SELECT @objectName = QUOTENAME(o.name)
, @schemaName = QUOTENAME(s.name)
FROM sys.objects AS o
Inner Join sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.OBJECT_ID = @objectID;
SELECT @indexName = QUOTENAME(name)
FROM sys.indexes
WHERE OBJECT_ID = @objectID
And index_id = @indexID
And type > 0;
/* Look for LOBs */
SELECT TOP 1
@containsLOB = column_id
FROM sys.columns WITH (NOLOCK)
WHERE
[OBJECT_ID] = @objectID
And (system_type_id In (34, 35, 99)
-- 34 = image, 35 = text, 99 = ntext
Or max_length = -1);
-- varbinary(max), varchar(max), nvarchar(max), xml
/* See if we should rebuild or reorganize; handle thusly */
IF @fragmentation < @rebuildThreshold
Or IsNull(@containsLOB, 0) > 0
-- Cannot rebuild if the table has one or more LOB
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + N' ReOrganize;'
ELSE
SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
+ @schemaName + N'.' + @objectName + ' Rebuild '
+ 'With (MaxDop = 1)'; -- minimize impact on server
/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN
/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE (@sqlCommand);
SET @dateTimeEnd = GETDATE();
/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
objectID
, objectName
, indexID
, indexName
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@objectID
, @objectName
, @indexID
, @indexName
, @fragmentation
, @pageCount
, @dateTimeStart
, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);
/* Just a little breather for the server */
WAITFOR Delay @defragDelay;
/* Print if specified to do so */
IF @printCommands = 1
PRINT N'Executed: ' + @sqlCommand;
END
ELSE
/* Looks like we're not executing, just print
the commands */
BEGIN
IF @printCommands = 1
PRINT @sqlCommand;
END
/* Update our index defrag list when we've
finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE objectID = @objectID
And indexID = @indexID;
END
/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
SELECT idl.objectID
, o.name As 'tableName'
, idl.indexID
, i.name As 'indexName'
, idl.fragmentation
, idl.page_count
FROM #indexDefragList AS idl
JOIN sys.objects AS o
ON idl.objectID = o.object_id
JOIN sys.indexes As i
ON idl.objectID = i.object_id
AND idl.indexID = i.index_id;
/* When everything is done, make sure to get rid of
our temp table */
DROP TABLE #indexDefragList;
SET NOCOUNT OFF;
RETURN 0
END
Go
For those who are having troubles with this script…
1) “Not all of my indexes were defragged!” or “Nothing happened when I executed this script.”
This script will only defrag those indexes that surpass the specified threshold. If you’re not seeing your index in the output, try executing this:
Exec dbo.dba_indexDefrag_sp
@executeSQL = 0
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1; |
Exec dbo.dba_indexDefrag_sp
@executeSQL = 0
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1;
Check to see what your index’s fragmentation level is. Maybe it’s not as fragmented as you feared. 🙂
2) “My indexes are still fragmented after running this script.”
To quote The Powers That Be (aka Microsoft)…
“In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.” — Reorganizing and Rebuilding Indexes
3) “Can I use this in my production environment?”
That really depends on your environment. I’ve successfully used this in some very large production environments. However, I wouldn’t exactly recommend executing the script in the middle of a business day on a billion+ row, heavily fragmented, unpartitioned table, either. 🙂
If you’re not sure what the impact will be, execute the commands-only version of the script…
Exec dbo.dba_indexDefrag_sp
@executeSQL = 0
, @printCommands = 1
, @printFragmentation = 1; |
Exec dbo.dba_indexDefrag_sp
@executeSQL = 0
, @printCommands = 1
, @printFragmentation = 1;
… then execute the statements one at a time. Make sure you monitor tempdb and the transaction log to ensure you don’t have any space issues.
If you have any additional questions or suggestions for this script, leave me a comment and I’ll be happy to help. 🙂