UPDATE: This script has been significantly updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.
Previously I posted that it’s important to keep your indexes in shape with weekly or (preferably) nightly defrags. Below is a script I use to execute nightly defrags in SQL 2005 Enterprise. I can’t claim complete credit… I believe this is a script I originally found on Microsoft and heavily modified to meet my needs.
You may want to modify the script if you’re doing much with XML or LOB’s. Also keep in mind that this is hitting the sys.dm_db_index_physical_stats table to view fragmentation information, which can be resource intensive.
If you’re not familiar with index defragmentation, check out “Alter Index” on Books Online.
If Not Exists(Select object_id From sys.tables Where [name] = N'dba_indexDefragLog') Begin 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 , dateTimeStart datetime not null , durationSeconds int not null Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id) ); Print 'dba_indexDefragLog Table Created'; End 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 */ , @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: CAUTION: Monitor transaction log when 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 @defragDelay time to wait between defrag commands; gives the server a little time to catch up Called by: SQL Agent Job or DBA Date Initials Description ------------------------------------------------------------------- 2008-10-27 MFU Initial Release *********************************************************************** Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @printCommands = 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 , @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 , 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 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 , dateTimeStart , durationSeconds ) Select @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @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 /* When everything is done, make sure to get rid of our temp table */ Drop Table #indexDefragList; Set NoCount Off; Return 0 End Go Set Quoted_Identifier Off Set ANSI_Nulls On Go |
20081117 UPDATE: Check out my Updated Index Defrag Script for SQL 2005 and 2008.
Great script — thanks for sharing!
You’re very welcome! 🙂
I’ve been looking at the script you’ve provided. Perhaps I’m just dim, or it’s the lack of sleep, but I cant figure out one little part…
…
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
….
What I don’t understand is why this piece is here? Create a stub, then alter it? Why not just create the procedure? If you’re worried about whether the procedure exists already, just do something like…
IF EXISTS (SELECT * FROM sys.procedures WHERE name = ‘p_A_Procedure’ )
DROP proc p_A_Procedure
GO
CREATE …..
Perhaps I missed something subtle in there. Please how me the error of my ways…
Honor Super Omnia-
Jason
Hi Jason,
Great question! The biggest reason is that I try to avoid executing “DROP PROCEDURE” statements in production. Some of my stored procedures are called 500,000 to 600,000 times per hour. With those kinds of volumes, even a sub-second DROP / CREATE process will result in a few dozen errors. Given my desire to avoid that, it simplifies the deployment process to always prefix my stored procedures with that little piece of code.
Some of my colleagues also use the CREATE / ALTER approach to simplify deployments using PowerShell. This can be really useful when you’re deploying hundreds of stored procedures.
That said, this obviously isn’t one of those high volume procs, so the code is only there because it’s part of my standard stored procedure template. It really can easily be replaced with a DROP / CREATE statement, if you prefer.
Thanks for the question!
Regards,
Michelle
Michelle … may I compliment you on producing such a professional script.
This is just to state a forlorn wish that maybe someday soon MS will make this whole ridiculous Create/Alter problem go away (pipe dream I know!)
Thank you, Roger! I have to agree with you, I’m eager for the day when MERGE-like functionality is also available for stored procedure creation. But I won’t hold my breath. 🙂
Hi Again. Say, is there any aspect of this that mandates Enterprise? I’ve tried running the script on my SQL Server 2005 Standard (sp2) database, but aside from creating the SP nothing appears to happen. No errors and I get “Command(s) completed successfully.”
Am I just being thick or am I running the wrong version?
Hi Chris! No, you’re not being thick. There are definitely components that will only work with Enterprise, such as the online and partitioning pieces. I don’t actually have SQL 2005 Standard installed, so I’ll have to install a copy and work on a Standard version of the defrag script this weekend. I’ll post the code when I’m done. 🙂
Regards,
Michelle
Add to my Bookmarks 😉
я люблю все ваши слова..
According to iGoogle, that means “I love all your words,” so thank you, Lena! 🙂
нам что-то похожее препод вчера в универе рассказывал, на самом деле тема интересная
I ran this script for a specific database. It completed successfully. Then I ran it in DETAILED mode (instead of LIMITED mode). What was strange was that many indexes still have 77% fragmentation or similar to what it was before. Why are the fragmentation numbers not decreasing?