Index Defrag Script, v3.0
Filed under: Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
I’ve just completed the latest version of my index defrag script! Here’s a brief list of the updates:
- Fixed a bug with the LOB logic. In the previous version, after a LOB was encountered, all subsequent indexes would be reorganized.
- Added support for stat rebuilds after the index defrag is complete (@rebuildStats)
- Added an exclusion list table (dba_indexDefragExclusion) to support index scheduling
- Modified logging to show which defrags are “in progress”; added columns to dba_indexDefragLog
- Added support for the defrag of the model and msdb databases
- Added @scanMode as a configurable parameter
So what can this index defrag script do? Well, for starters, you can:
- Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
- Run this one script from a centralized database for all databases on a server
- Run this script for a specific database or table
- Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
- Defrag individual partitions
- Log its actions and the duration of the defrag
- Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
- Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
- Schedule specific indexes to only be defragged on weekends, or every other day
To use this last option, you need to add a record to the dba_indexDefragExclusion table. I think all of the columns are pretty self-explanatory except the [exclusionMask] column. The way this works is each day of the week is assigned a value:
1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday
Take a SUM of the values for the days that you want excluded. So if you want an index to only be defragged on weekends, you would add up Monday through Friday (2+4+8+16+32) and use a value of 62 for the exclusionMask column. For a little more information on how this works, check out my blog post on Bitwise Operations.
Please note: if you don’t insert any records into the dba_indexDefragExclusion table, by default all indexes will be defragged every run-time if they exceed the specified thresholds. This is normal behavior and may be perfectly fine in your environment. However, if the dba_indexDefragExclusion table does not exist, the script will fail.
I try to document each parameter within the code, so check the comments section in the script for a full list of parameters and what they do.
Special thanks to everyone who helped beta test this script! 
Without further ado, the script:
/* Drop Table Scripts: Drop Table dbo.dba_indexDefragLog; Drop Table dbo.dba_indexDefragExclusion; */ IF Not Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] In (N'dba_indexDefragLog', 'dba_indexDefragExclusion')) BEGIN CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) Not Null , databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , partitionNumber SMALLINT Not Null , fragmentation FLOAT Not Null , page_count INT Not Null , dateTimeStart DATETIME Not Null , dateTimeEnd DATETIME Null , durationSeconds INT Null CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; CREATE TABLE dbo.dba_indexDefragExclusion ( databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , exclusionMask INT Not Null /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */ CONSTRAINT PK_indexDefragExclusion PRIMARY KEY CLUSTERED (databaseID, objectID, indexID) ); PRINT 'dba_indexDefragExclusion Table Created'; END ELSE RAISERROR('One or more tables already exist. Please drop or rename before proceeding.', 16, 0); IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped'; END; Go CREATE PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 5.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 */ , @DATABASE VARCHAR(128) = Null /* Option to specify a database name; null will return all */ , @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @scanMode VARCHAR(10) = N'LIMITED' /* Options are LIMITED, SAMPLED, and DETAILED */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @maxDopRestriction TINYINT = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @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 */ , @debugMode BIT = 0 /* display some useful comments to help determine if/where issues occur */ , @rebuildStats BIT = 1 /* option to rebuild stats after completed index defrags */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for the current database Notes: CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @scanMode Specifies which scan mode to use to determine fragmentation levels. Options are: LIMITED - scans the parent level; quickest mode, recommended for most cases. SAMPLED - samples 1% of all data pages; if less than 10k pages, performs a DETAILED scan. DETAILED - scans all data pages. Use great care with this mode, as it can cause performance issues. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @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 a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments @rebuildStats Affects only statistics that need to be rebuilt 1 = rebuild stats 0 = do not rebuild stats Called by: SQL Agent Job or DBA Date Initials Version Description ---------------------------------------------------------------------------- 2007-12-18 MFU 1.0 Initial Release 2008-10-17 MFU 1.1 Added @defragDelay, CIX_temp_indexDefragList 2008-11-17 MFU 1.2 Added page_count to log table , added @printFragmentation option 2009-03-17 MFU 2.0 Provided support for centralized execution , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic 2009-06-18 MFU 3.0 Fixed bug in LOB logic, added @scanMode option , added support for stat rebuilds (@rebuildStats) , support model and msdb defrag , added columns to the dba_indexDefragLog table , modified logging to show "in progress" defrags , added defrag exclusion list (scheduling) ********************************************************************************* Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; BEGIN IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait; /* Declare our variables */ DECLARE @objectID INT , @databaseID INT , @databaseName NVARCHAR(128) , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(128) , @objectName NVARCHAR(128) , @indexName NVARCHAR(128) , @partitionNumber SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT , @editionCheck BIT , @debugMessage VARCHAR(128) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000) , @rebuildStatsID INT , @rebuildStatsSQL NVARCHAR(1000) , @indexDefrag_id INT; /* Create our temporary tables */ CREATE TABLE #indexDefragList ( databaseID INT , databaseName NVARCHAR(128) , objectID INT , indexID INT , partitionNumber SMALLINT , fragmentation FLOAT , page_count INT , defragStatus BIT , schemaName NVARCHAR(128) Null , objectName NVARCHAR(128) Null , indexName NVARCHAR(128) Null ); CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) , scanStatus BIT , statsStatus BIT ); CREATE TABLE #processor ( [INDEX] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait; /* 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'; IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED') SET @scanMode = 'LIMITED'; /* Make sure we're not exceeding the number of processors we have available */ INSERT INTO #processor EXECUTE XP_MSVER 'ProcessorCount'; IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor) SELECT @maxDopRestriction = Internal_Value FROM #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait; /* Retrieve the list of databases to investigate */ INSERT INTO #databaseList SELECT database_id , name , 0 -- not scanned yet for fragmentation , 0 -- statistics not yet updated FROM sys.databases WHERE name = IsNull(@DATABASE, name) And [name] Not In ('master', 'tempdb')-- exclude system databases And [STATE] = 0; -- state must be ONLINE IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait; /* Loop through our list of databases */ WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0 BEGIN SELECT TOP 1 @databaseID = databaseID FROM #databaseList WHERE scanStatus = 0; SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...'; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Determine which indexes to defrag using our user-defined parameters */ INSERT INTO #indexDefragList SELECT database_id AS databaseID , QUOTENAME(DB_NAME(database_id)) AS 'databaseName' , [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 */ , Null AS 'schemaName' , Null AS 'objectName' , Null AS 'indexName' FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) WHERE avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > 8 -- ignore objects with less than 1 extent And index_level = 0 -- leaf-level nodes only, supports @scanMode OPTION (MaxDop 2); /* Keep track of which databases have already been scanned */ UPDATE #databaseList SET scanStatus = 1 WHERE databaseID = @databaseID; END CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(databaseID, objectID, indexID, partitionNumber); /* Delete any indexes from our to-do that are also in our exclusion list for today */ DELETE idl FROM #indexDefragList AS idl Join dbo.dba_indexDefragExclusion AS ide ON idl.databaseID = ide.databaseID And idl.objectID = ide.objectID And idl.indexID = ide.indexID WHERE exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) > 0; SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM #indexDefragList; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait; /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait; /* Look up index information */ SELECT @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexDefragList As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And i.type > 0 And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE SP_EXECUTESQL @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM #indexDefragList WHERE objectID = @objectID And indexID = @indexID And databaseID = @databaseID; IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait; /* Determine if the index is partitioned */ SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT; IF @debugMode = 1 RAISERROR(' Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait; /* Determine if the table contains LOBs */ SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*) From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ IF @fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1 BEGIN SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); END; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 BEGIN /* Set online rebuild options; requires Enterprise Edition */ IF @onlineRebuild = 1 And @editionCheck = 1 SET @rebuildCommand = N' Rebuild With (Online = On'; ELSE SET @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ IF @maxDopRestriction IS Not Null And @editionCheck = 1 SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')'; ELSE SET @rebuildCommand = @rebuildCommand + N')'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN IF @debugMode = 1 RAISERROR(' Executing SQL statements...', 0, 42) WITH NoWait; /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart; SET @indexDefrag_id = SCOPE_IDENTITY(); /* Execute our defrag! */ EXECUTE SP_EXECUTESQL @sqlCommand; SET @dateTimeEnd = GETDATE(); /* Update our log with our completion time */ UPDATE dbo.dba_indexDefragLog SET dateTimeEnd = @dateTimeEnd , durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd) WHERE indexDefrag_id = @indexDefrag_id; /* 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 printing the commands */ BEGIN IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait; IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!'); END IF @debugMode = 1 RAISERROR(' Updating our index defrag status...', 0, 42) WITH NoWait; /* Update our index defrag list so we know we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count FROM #indexDefragList; END; /* Do we want to rebuild stats? */ IF @rebuildStats = 1 BEGIN WHILE Exists(SELECT TOP 1 * FROM #databaseList WHERE statsStatus = 0) BEGIN /* Build our SQL statement to update stats */ SELECT TOP 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' + 'Execute sp_updatestats;' , @rebuildStatsID = databaseID FROM #databaseList WHERE statsStatus = 0; SET @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Execute our stats update! */ EXECUTE SP_EXECUTESQL @rebuildStatsSQL; /* Keep track of which databases have been updated */ UPDATE #databaseList SET statsStatus = 1 WHERE databaseID = @rebuildStatsID; END; END; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; DROP TABLE #databaseList; DROP TABLE #processor; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait; SET NOCOUNT OFF; RETURN 0 END Go SET Quoted_Identifier OFF SET ANSI_Nulls ON Go
Comments
64 Comments on Index Defrag Script, v3.0
-
Michael Swart on
Wed, 24th Jun 2009 6:31 am
-
Michelle Ufford on
Wed, 24th Jun 2009 8:17 am
-
Vic Prahu on
Wed, 24th Jun 2009 2:01 pm
-
Aaron Bertrand on
Wed, 24th Jun 2009 4:36 pm
-
Microsoft and DiscountASP.NET news on
Wed, 24th Jun 2009 7:14 pm
-
Internet Marketing Email » Blog Archive » Index Defrag Script, v3.0 : SQL Fool on
Thu, 25th Jun 2009 5:35 am
-
Luke on
Thu, 25th Jun 2009 5:36 am
-
John Sansom on
Thu, 25th Jun 2009 7:37 am
-
SDC on
Thu, 25th Jun 2009 8:55 am
-
SQL Server Index Defrag Script: Update | John Sansom - SQL Server DBA in the UK on
Thu, 25th Jun 2009 9:46 am
-
Jordan Schwartz on
Tue, 30th Jun 2009 11:47 am
-
Tittan on
Wed, 1st Jul 2009 2:41 am
-
Robert Hillen on
Mon, 6th Jul 2009 12:49 am
-
Derick on
Tue, 7th Jul 2009 3:37 am
-
SuperCoolMoss on
Tue, 7th Jul 2009 3:05 pm
-
Index Defrag Script Update : SQL Fool on
Thu, 9th Jul 2009 9:45 am
-
chris crowe on
Fri, 10th Jul 2009 12:33 am
-
5 tâches courantes d’administration de SQL Server 2005 | Admin Réseau - Blog on
Fri, 10th Jul 2009 8:36 am
-
Michelle Ufford on
Sat, 11th Jul 2009 11:46 am
-
Pinal Dave on
Sun, 12th Jul 2009 8:01 pm
-
Jason Callas on
Mon, 13th Jul 2009 1:24 pm
-
Jason Callas on
Mon, 13th Jul 2009 1:26 pm
-
Roy on
Tue, 14th Jul 2009 10:29 am
-
Ashish on
Tue, 14th Jul 2009 11:32 am
-
Joshua Jin on
Wed, 22nd Jul 2009 12:47 pm
-
Michelle Ufford on
Wed, 22nd Jul 2009 4:43 pm
-
Rebecca on
Tue, 4th Aug 2009 4:14 pm
-
Sven Moderow on
Thu, 6th Aug 2009 8:05 am
-
gmamata on
Tue, 11th Aug 2009 4:44 pm
-
gmamata on
Tue, 25th Aug 2009 10:14 am
-
Sergio Pacheco on
Wed, 9th Sep 2009 3:56 pm
-
Rick on
Wed, 9th Sep 2009 4:04 pm
-
Michelle Ufford on
Thu, 10th Sep 2009 6:48 am
-
Michelle Ufford on
Thu, 10th Sep 2009 6:49 am
-
mstjean on
Thu, 10th Sep 2009 10:30 am
-
Michelle Ufford on
Fri, 11th Sep 2009 11:13 am
-
Rick on
Mon, 14th Sep 2009 1:33 pm
-
Mike F on
Wed, 16th Sep 2009 12:36 pm
-
Michelle Ufford on
Thu, 1st Oct 2009 5:58 am
-
Michelle Ufford on
Thu, 1st Oct 2009 6:00 am
-
Vincent on
Wed, 7th Oct 2009 12:21 pm
-
Michelle Ufford on
Wed, 7th Oct 2009 12:46 pm
-
Vincent on
Wed, 7th Oct 2009 2:17 pm
-
Vincent on
Wed, 7th Oct 2009 2:21 pm
-
Karl F on
Tue, 13th Oct 2009 4:04 pm
-
Bill on
Wed, 14th Oct 2009 11:39 am
-
links for 2009-10-20 - sashidhar.com on
Tue, 20th Oct 2009 2:10 am
-
Jason Callas on
Tue, 20th Oct 2009 6:57 pm
-
Bill on
Wed, 21st Oct 2009 9:27 am
-
Aq on
Fri, 23rd Oct 2009 7:57 am
-
lamingus on
Fri, 30th Oct 2009 9:20 am
-
Michelle Ufford on
Fri, 30th Oct 2009 6:20 pm
-
SQL 2008 Express: SQL Agent « My life as a DBA. on
Mon, 2nd Nov 2009 1:49 pm
-
Madhavarao on
Tue, 3rd Nov 2009 12:37 am
-
lamingus on
Tue, 3rd Nov 2009 12:59 pm
-
Matt on
Thu, 5th Nov 2009 9:47 am
-
Matt on
Thu, 5th Nov 2009 10:06 am
-
Martin on
Fri, 6th Nov 2009 6:40 am
-
SQL 2008 Express: SQL Agent | Darryl Marshall on
Sun, 8th Nov 2009 1:46 pm
-
Michelle Ufford on
Sun, 8th Nov 2009 2:35 pm
-
Michelle Ufford on
Sun, 8th Nov 2009 2:38 pm
-
Martin on
Mon, 9th Nov 2009 2:12 am
-
Jugal Shah on
Wed, 18th Nov 2009 4:30 am
-
Olek on
Sat, 21st Nov 2009 1:16 pm
Hi Michelle, Great script and thanks for sharing. I had built a version myself but I find that on VLDBs, dm_db_index_physical_stats is very resource intensive (and hence it takes a while), even with LIMITED scan mode.
It’s even a bit much on SLDBs (Somewhat large databases).
What’s your experience with this function? Have you noticed anything yourself?
Hi Michael. I actually run this index defrag on a 1.4TB database every night. I run it with a MAXDOP restriction of 2, and a @defragDelay of 1 minute. It takes about 4 hours for the DMF to finish running, and then about 30 minutes to an hour for the defrags to complete. So yes, it is resource intensive, but I have never run into any locking or blocking problems. I did run into a couple of replication delays when I tried to defrag the current partitions, so I added this into my script (line 325):
And partition_number <> $partition.myPartitionFunction(GetDate()) — don’t defrag current partition
That seemed to help with the replication problems. Also, the reason I added the scheduling was to move some of the nonpartitioned indexes on billion-row tables to a weekend-only defrag schedule. This has seemed to help in the amount of time that it takes to complete during the weekdays.
I hope that helps answer your question!
Hi Michelle, once again great updates on this new version. I was using he previous IndexDefrag script version wiht minor modifications, one of them was to skip certain tables in 200+ databases, seems like you have covered that in this update.
One minor finding, when i execute this Sproc with @executesql = 0, and @printCommands = 1 , @printFragmentation = 1, i noticed that the IndexName in results for certain tables is repeated! But the PrintCommands has the correct value for IndexName. I hope this makes sense, if not contact me and I will try to elaorate!
Index maintenance : these are not your father’s maintenance plans!…
UPDATE 2009-06-24 I have blogged about both of them before ( here and here ), but I can never say enough…
SQL Server Index Defrag Script…
Hiya. . . Some of you may have already seen this, but just in case you haven’t; Some excellent SQL Server…
[...] Michelle Ufford put an intriguing blog post on Index Defrag Script, v3.0 : SQL FoolHere’s a quick excerpt… varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(’ Building our SQL statements. … 0, 42) WITH NoWait; /* Update our index defrag list so we know we’ve finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And … [...]
hi, thank you for the sript, but is there a downloadable version of the script or do i have copy the script from the post? thanks.
Hi Michelle,
This looks to be an excellent update to an already brilliant database maintenance script. I for one certainly appreciate your efforts and want to say a big thank you to you.
I’m looking forward to testing this out and I think the inclusion of statistics management will be a great feature.
Thanks for the contribution to the community! I’m going to put this to work.
[...] it out here: Index Defrag Script, V3.0 Share and [...]
Just a thought to get this to run faster on larger databases would be to spawn several instances of this script and have them pull out n number of tables at a time. This way you could have lets say 5 instances working on 50 tables each. That should help reduce the time to complete.
I just found this script and I must say, its very thorough. I had been running a similar script that set threshold levels for rebuild or reorg but I never took it this far. I am definitely going to use this script. I did however use the “multi-instance” method in my script which sped up the process quite a bit..
Hi Michelle..
I have a problem to compiling this excellent script.
Server: Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 263
Incorrect syntax near ‘OBJECT_ID’.
I run this on (SQL 2005, SP3):
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Hi Michelle,
Thanks for this script, this is just what I was looking for.
I have just one issue. When I run this SP I got an error, incorrect syntax near ‘(’. It seems it does not work when the database is in compatible mode 80 (SQL Server 2000).
I solved it by replacing the object_id(@tablename) parameter in the dm_db_index_physical_stats line with @tab_id. Then insert two line before this command:
declare @tab_id int
set @tab_id=object_id(@tablename)
Hi Michelle. Thanks for your script. However, I found that the “Rebuild Stat” part always rebuild the first database only. It seems that the script missed a loop to run through all the database in the database list.
Besides, I found some databases are skipped during the defragmentation. Even though there are indexes with over threshold, they haven’t been defraged.
Pls help. Thank you.
Hello Michelle,
Thanks for the new version. Does the update stats section only run for one database?
SCM
[...] only complete for one database. Whoopsies! I’ve fixed the bug and updated the script in my previous post, so if you’re using my defrag script, please update it. Thanks to Derick and SuperCoolMoss [...]
smal bug on line 562
SELECT TOP 1 @rebuildStatsSQL = ‘Use ‘ + databaseName + ‘; ‘ +
This should be
SELECT TOP 1 @rebuildStatsSQL = ‘Use [' + databaseName + ']; ‘ +
I have a number of web sites which have a database name like “blog.crowe.co.nz”
This little fix sorts it out.
Thanks for the script - much appreciated - I am trying (real hard) to learn more about SQL performance and I am planning on reading your blog often.
ps: I am coming to SQL Pass Summit 2009 in Seattle (all the way from New Zealand)
chris
[...] mon cas je fais tourner un job le weekend qui exécute la procédure dba_IndexDefrag_sp de Michelle Ufford. Cette procédure permet de vérifier tous les index de toutes les bases [...]
Thanks, Chris! I’ve never used a period in a database name before.
Very cool news about the Summit. Looking forward to meeting you there.
Very nice Michelle,
Again, it is wonderful script and I have just updated all my production server with this script.
I am eager to meet you at PASS!
Kind Regards,
Pinal
The square brackets around the USE command also fixes the situation when hyphens are in the database name. I use “-dev” and “-da” on my development servers.
Make that “-qa”…
I found a small bug for /* Look up index information */ SELECT @updateSQL
should add the below in the where clause
And idl.indexID = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘
after
And i.type > 0
Thanks Michelle for sharing your script !
I just have to make two small changes to work it on case sensitive server.
It would be nice for the community if you include those changes in the script.
1) Replace capital case “XP_MSVER “ to small case “xp_msver”
2) Replace capital case “SP_EXECUTESQL” to small case “sp_executesql”
Ashish
In your script logic, you have “If our index is partitioned, we should always reorganize”, can you expain why? Thanks! (Joshua)
@Joshua Hi Joshua. Good question! The reason is that, if you attempt to rebuild a single partition *online*, it will fail. You can rebuild a single partition offline, but that’s never been acceptable in my environments. Alternatively, you can rebuild all partitions online, but again, that negates much of the benefit of partitioning, since most partitions should (hopefully) not need to be rebuilt.
Excellent, Michelle. I have been needing to smarten up my defragger for ages. Stumbled upon this today… the varied parms are perfect, and it works very well on all of my db’s…. VLDB and otherwise. I have already tested it and deployed.
Thanks very much.
Will all fulltext indexes also be defraged? (like ALTER FULLTEXT CATALOG catalog_name REORGANIZE)?
Hi Michelle Ufford,
Can we use this script to defrag SHARE POINT databases in SQL Server 2005?
Hi Michelle,
I’m planning to use this Script to defrag Share point databases. Can we use this script to defrag SHARE POINT databases in SQL Server 2005?
Thanks Michelle,
I needed to move away from the defrag maintenance plan (created huge log files to ship) to a more analytical script. Yours is awesome but I don’t see any logic for sorting in the TempDB. Wouldn’t this minimize the i/o on the mdb’s partition when performing the rebuild online? Knowing that rebuilding the index will be logged, does sorting it in TempDB minimize the extent of the log growth?
getting a couple of errors
1. When executing script get error
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table ‘SP_EXECUTESQL’. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
2. exec dbo.dba_indexDefrag_sp
Could not find stored procedure ‘XP_MSVER’.
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table ‘SP_EXECUTESQL’. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
@Sergio I’ll be adding options for sorting in TempDB in future versions. There’s several benefits in using Sort_In_TempDB = On, including shorter defrag durations, less impact on user transactions, and less logging (assuming TempDB is using simple recovery and your database is using full).
@Rick what version of SQL Server are you using, including service pack?
Nice script… but I’m gonna hafta stare at it for a while.
I didn’t see it–but it may be there– do you have a cutoff to prevent rebuild/reorg if pagecount is below some threshold? I (somewhat arbitrarily) set minimum pagecount to 10 via a parm to prevent R/R. Recently I saw a post from Gila where she indicated it’s not worth it to R/R unless pagecount > IIRC 1000 pages (the one link I *didn’t* save)
I see you are ordering the rebuild/reorg so the most fragmented get done first; I’ve done a variant of that where I order by (pagecount * fragmentation) desc. If I am limited for time, I’d rather rebuild/reorg a table consisting of 100000 pages at 30% fragmentation than a table with 10 pages at 90% fragmentation.
How about a parm to set “max minutes to run”; set @startdt at the top, and before any major operation confirm “@maxmins=0 or getdate() <= dateadd(minute,@maxmins,@startdt)” … granted this won’t be exact; you’d just prevent starting a new operation after @maxmins minutes. If you have a small window of time per day to do maintenance you could schedule this every day and take care of the worst offenders without worrying about it running wild.
@mstjean Thank you for your suggestions. Yes, I do use a minimum page count of 8 (1 extent)… I haven’t yet explored limited it to page counts that high (i.e. 1000), mainly because those defrag so quickly anyway. I’m also considering a max time restriction for the next version of the script, too.
Michelle,
We are using SQL Server 2005 SP3 Enterprise Editon.
thx,
Rick
I’ve been using this script for a couple of months and thoroughly love it. But (there’s always a “but”, right?), I have encountered some situations where the entire job bombs because the current index being rebuilt is a deadlock victim. Have you given any thought to adding a TRY/CATCH block around the execution piece, perhaps logging the error for the current index, then continuing on with the job normally? Or is it your intent that this situation is handled differently?
Thanks!
-Mike F
Hi Mike,
Thanks for the feedback! That’s a good idea and something I’ll consider for future versions.
Regards,
Michelle
Hi Rick,
Is your server case-sensitive? You could possibly have to make those commands lowercase.
Regards,
Michelle
Great script Michelle, but I’m wondering why you don’t check sys.dm_db_index_usage_stats prior to rebuilding. Any index that is not used should not show up in that DMV, and there is no reason to rebuild indexes that are unused, right?
Vince
@Vincent thanks for your comment. Good point! Yes, the logic of identifying which indexes to rebuild is something I’m looking at in the upcoming version.
Great, I added it using a simple inner join to sys.dm_db_index_usage_stats.
I’ll keep an eye out for how you add that logig.
I’m currently [trying to] add logic to check the version using SELECT CONVERT(int, SERVERPROPERTY(’EngineEdition’)) … 3 being Enterprise ed. This way, the person running or automating the proc won’t have to know the edition - the proc will know and choose to use online or not appropriately.
Never mind, I see you have edition logic. Feel free to delete my pollution. 
Excellent! Thank you for sharing!
SharePoint 2007 SP2 added a proc that rebuilds with fill factor 80 that seems to be the current general starting point for SharePoint - but the SPS proc didn’t include any logic based on current fragmentation. So now I know why our TRN log backups seem larger than they should be…
Now that target fill factor is set your script should be able to perform ongoing maintenance. Testing in Dev now.
Like ‘mstjean’ I’ve always noticed that indices with low page counts naturally can have high fragmentation % values even when fully optimzed. I am wondering what logic might keep these from unnecessary rebuilding - even though they’re small and impact is low. I’m sure someone else out there has input on this one.
Also noticed the SharePoint proc_DefragmentIndices doesn’t seem to sort in tempDB (yet) :).
Looking forward to future revisions!
-Karl
Great script. I modified slightly to use Sort_In_TempDB = On. One issue I do encounter from time to time is a failure when rebuilding clustered indexes on views. For some strange reason, it behaves as if the SET QUOTED_IDENTIFIER ON was never set. The failure message looks like this:
ALTER INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods
When I print the list of commands and do not execute, sure enough I find an indexed view in the list. When I copy that command to a query session and run it, it works fine, and then running the sp as a whole after that will work. However, whenever an index on a view needs to be rebuilt, the procedure will fail when it gets to that statement. Strange behaviour, especially since we can clearly see Quoted_Identifier is properly set at the beginning of the procedure.
Anyone else out there encounter this?
Cheers,
Bill
[...] Index Defrag Script, v3.0 : SQL Fool (tags: sql sqlserver optimization index defrag performance) You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. [...]
Great tool…have 1 question and 1 feature request
1) Can I use the exclude table to exclude an entire database or table? A quick read through the code seems that I need to specify actual indexes.
2) Detect if a database is in read-only mode and automatically skip it.
As you can see, my feature request is related to my exclude question.
Thanks.
Ok, I figured out why this proc was failing on Indexed Views. Despite having SET QUOTED_IDENTIFIERS ON inside the proc, it is ignored in favor of the session option when the proc is created which is saved in the database. For some reason, when I ran the script on my servers, the session option was SET QUOTED_IDENTIFIERS OFF. Once I recreated explicitly setting SET QUOTED_IDENTIFIERS ON before the procedure was created, everything works fine.
newbe 
what does this error mean?
Msg 208, Level 16, State 1, Line 5
Invalid object name ’sys.tables’.
Msg 170, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 263
Line 263: Incorrect syntax near ‘OBJECT_ID’.
Wanting to run your script however have same error as Newbe
Msg 208, Level 16, State 1, Line 5
Invalid object name ’sys.tables’.
Msg 170, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 263
Line 263: Incorrect syntax near ‘OBJECT_ID’.
@Aq and @lamingus - what version of SQL Server are you using? What compatibility level are you using? Those error messages sound suspiciously like SQL Server 2000. This script is only intended for SQL Server 2005 and higher.
[...] out your own backup and defrag scripts. SQLfool (Twitter) has a great script that checks for defragmentation in indexes. Over at SQLDBATips you can get another useful script that helps with the backup [...]
Thanks for this Useful script .. which is really handy, helpful for dumb fellows like me
Using SQL 2005 as far as I know; Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘dba_indexDefrag_sp’.
All I really need is to run a script to rebuild all indexes for a single data base. Our support staff has customer data set up on SQL for testing purpose. We basically need to verify that rebuilding the indexes will correct the performance problems a few customers are reporting.
Although this script looks quite eloquent we need a simpler down and dirty tool that as I say only runs for the intended database and rebuilds all indexes in the selected database. The one we are working with right now has quite a lot of 60% to 99 % fragmentation.
I am testing this in a dev environment using and Adventureworks db. I have a query that tells me which databases / tables need to be defagged. For example, it states that AdventureWorksLT is at 99%.
When I run your script agains the AWLT db, and then re-run my query that returns defrag statistics, shouldn’t the number be lower than 99? I guess I’m having trouble understanding how I know your script worked.
Any help is much appreciated.
Thanks,
Matt
Oops I meant defragged on my comment above. Didn’t mean to offend anyone.
Hi Michelle,
Thanks for your script, it’s exactly what I was looking for.
Although I’m not an expert in SQL scripting I can interpret most of it.
But wherever I run it, either 2005/2009 SE/EE it drops after:
[Msg 50000, Level 16, State 0, Line 50
One or more tables already exist. Please drop or rename before proceeding.
Procedure dba_indexDefrag_sp dropped]
The indexdefraglog table just shows ‘Null’ in all rows. I’m using the default for the variables.
What am I doing wrong.
cheers
Martin
[...] out your own backup and defrag scripts. SQLfool (Twitter) has a great script that checks for defragmentation in indexes. Over at SQLDBATips you can get another useful script that helps with the backup [...]
@Matt the script will only defrag any index with 8 or more pages, which follows Microsoft’s best practices recommendation. I don’t have the AdventureWorksLT database handy, but I would guess that most of those indexes have less than 8 pages, which is probably why you’re not seeing any change after running the defrag script.
@Martin You’re getting those errors because the tables already exist, which means some version of the script has already been executed once. In my script, I do not automatically drop the tables, just to err on the safe side (i.e. you want historical defrag information). To update to the latest version, just comment out the section that creates the tables that already exist (probably just dba_indexDefragLog, since dba_indexDefragExclusion is new in this version). After that, you can call the script with “Execute dba_indexDefrag_sp;” (uses all default parameters).
Hi Michelle,
Thanks for the answer Michelle.
I’m running the script against several heavily fragmented databases. I took a a script from Brent Ozars Blog and got that result.
Now I Imagined that the tables could be the cause so I deleted them but still the result is the same. I realized later be running the newly created stored procedure I could defragment the indexes.
Isn’t it the intend of the scipt to run it right away or do you have to run the SP separately?
Thanks
Great Work buddy
I suggest script should skip disabled indexes.
Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog. 
-
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
-
Around the Web
-
SQLRockstar Blog Ranking
