It’s been quite some time since my last index defrag script update. A big part of the reason for that is because I wanted to implement many of the suggestions I’ve received, but I just haven’t had the time. I still have those changes planned, but I’m not sure quite when I’ll get to it. Rather than continue to wait for a major release, I’m releasing a small update to my defrag that will take care of the most common complaints I receive.
Change Log:
- Bug fix for databases containing spaces or special characters
- Support for case-sensitive databases
- Re-executable CREATE script (for those who want to re-run the whole script)
- Comma-delimited list of databases is now supported for the @database parameter
Feature List:
- Defrag a single database, a list of databases, or all databases (@database)
- Time Limitations: stop defragging after the specified amount of time has elapsed (@timeLimit). Please note, it will not kill a defrag that is currently in process, even if it exceeds the threshold.
- Optional stop-and-resume functionality: pick up where your defrag last left off without having to rescan sys.dm_db_index_physical_stats. (@forceRescan)
- Defrag scheduling: choose which days to defrag certain indexes, or exclude certain indexes altogether, by using the dbo.dba_indexDefragExclusion table.
- Defrag priority: choose whether to defrag indexes in ascending or descending order by range_scan_count (default), fragmentation, or page_count.
- Current partition exclusion: choose whether or not to exclude the right-most populated partition from the defrag process, common for sliding-window tables (@excludeMaxPartition)
- Commands-only mode: Choose to just log the current defrag status and print the defrag commands, rather than executing them, by using @executeSQL.
- … and tons more! Please read the parameter list and notes section for details of all the options available.
FAQ:
I often receive the same questions about this script, so allow me to answer them here:
“I keep running the script, but my index is still fragmented. Why?”
This is most likely a very small index. Here’s what Microsoft has to say:
“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. For more information about mixed extents, see Understanding Pages and Extents.”
“What database should I create it in?” or “Can I create this in the MASTER database?”
It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.
“Can I run this againt a SharePoint database?”
Yes, you can.
“What are the minimum requirements to run this script?” or “Will this run on SQL Server 2000 instances?”
You need to be on SQL Server 2005 SP2 or higher.
Special thanks to Richard Yanger for his assistance with beta testing. 
You can download a text file of this script here: dba_indexDefrag_sp_v41
/*** Scroll down to the see important notes, disclaimers, and licensing information ***/ /* Let's create our parsing function... */ IF EXISTS ( SELECT [object_id] FROM sys.objects WHERE name = 'dba_parseString_udf' ) DROP FUNCTION dbo.dba_parseString_udf; GO CREATE FUNCTION dbo.dba_parseString_udf ( @stringToParse VARCHAR(8000) , @delimiter CHAR(1) ) RETURNS @parsedString TABLE (stringValue VARCHAR(128)) AS /********************************************************************************* Name: dba_parseString_udf Author: Michelle Ufford, http://sqlfool.com Purpose: This function parses string input using a variable delimiter. Notes: Two common delimiter values are space (' ') and comma (',') Date Initials Description ---------------------------------------------------------------------------- 2011-05-20 MFU Initial Release ********************************************************************************* Usage: SELECT * FROM dba_parseString_udf(<string>, <delimiter>); Test Cases: 1. multiple strings separated by space SELECT * FROM dbo.dba_parseString_udf(' aaa bbb ccc ', ' '); 2. multiple strings separated by comma SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ','); *********************************************************************************/ BEGIN /* Declare variables */ DECLARE @trimmedString VARCHAR(8000); /* We need to trim our string input in case the user entered extra spaces */ SET @trimmedString = LTRIM(RTRIM(@stringToParse)); /* Let's create a recursive CTE to break down our string for us */ WITH parseCTE (StartPos, EndPos) AS ( SELECT 1 AS StartPos , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos UNION ALL SELECT EndPos + 1 AS StartPos , CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos FROM parseCTE WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0 ) /* Let's take the results and stick it in a table */ INSERT INTO @parsedString SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos) FROM parseCTE WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0 OPTION (MaxRecursion 8000); RETURN; END GO /* First, we need to take care of schema updates, in case you have a legacy version of the script installed */ DECLARE @indexDefragLog_rename VARCHAR(128) , @indexDefragExclusion_rename VARCHAR(128) , @indexDefragStatus_rename VARCHAR(128); SELECT @indexDefragLog_rename = 'dba_indexDefragLog_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112) , @indexDefragExclusion_rename = 'dba_indexDefragExclusion_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112); IF EXISTS ( SELECT [object_id] FROM sys.indexes WHERE name = 'PK_indexDefragLog' ) EXECUTE sp_rename dba_indexDefragLog, @indexDefragLog_rename; IF EXISTS ( SELECT [object_id] FROM sys.indexes WHERE name = 'PK_indexDefragExclusion' ) EXECUTE sp_rename dba_indexDefragExclusion, @indexDefragExclusion_rename; IF NOT EXISTS ( SELECT [object_id] FROM sys.indexes WHERE name = 'PK_indexDefragLog_v40' ) 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 , sqlStatement VARCHAR(4000) NULL , errorMessage VARCHAR(1000) NULL CONSTRAINT PK_indexDefragLog_v40 PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; END IF NOT EXISTS ( SELECT [object_id] FROM sys.indexes WHERE name = 'PK_indexDefragExclusion_v40' ) BEGIN 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_v40 PRIMARY KEY CLUSTERED (databaseID, objectID, indexID) ); PRINT 'dba_indexDefragExclusion Table Created'; END IF NOT EXISTS ( SELECT [object_id] FROM sys.indexes WHERE name = 'PK_indexDefragStatus_v40' ) BEGIN CREATE TABLE dbo.dba_indexDefragStatus ( databaseID INT NOT NULL , databaseName NVARCHAR(128) NOT NULL , objectID INT NOT NULL , indexID INT NOT NULL , partitionNumber SMALLINT NOT NULL , fragmentation FLOAT NOT NULL , page_count INT NOT NULL , range_scan_count BIGINT NOT NULL , schemaName NVARCHAR(128) NULL , objectName NVARCHAR(128) NULL , indexName NVARCHAR(128) NULL , scanDate DATETIME NOT NULL , defragDate DATETIME NULL , printStatus BIT DEFAULT (0) NOT NULL , exclusionMask INT DEFAULT (0) NOT NULL CONSTRAINT PK_indexDefragStatus_v40 PRIMARY KEY CLUSTERED (databaseID, objectID, indexID, partitionNumber) ); PRINT 'dba_indexDefragStatus Table Created'; END; 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 = 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 */ , @defragOrderColumn NVARCHAR(20) = 'range_scan_count' /* Valid options are: range_scan_count, fragmentation, page_count */ , @defragSortOrder NVARCHAR(4) = 'DESC' /* Valid options are: ASC, DESC */ , @timeLimit INT = 720 /* defaulted to 12 hours */ /* Optional time limitation; expressed in minutes */ , @database VARCHAR(128) = NULL /* Option to specify one or more database names, separated by commas; NULL will return all */ , @tableName VARCHAR(4000) = NULL -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @forceRescan BIT = 0 /* Whether or not to force a rescan of indexes; 1 = force, 0 = use existing scan, if available */ , @scanMode VARCHAR(10) = N'LIMITED' /* Options are LIMITED, SAMPLED, and DETAILED */ , @minPageCount INT = 8 /* MS recommends > 1 extent (8 pages) */ , @maxPageCount INT = NULL /* NULL = no limit */ , @excludeMaxPartition BIT = 0 /* 1 = exclude right-most populated partition; 0 = do not exclude; see notes for caveats */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @sortInTempDB BIT = 1 /* 1 = perform sort operation in TempDB; 0 = perform sort operation in the index's database */ , @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 */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags one or more indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING. DO NOT RUN UNATTENDED ON LARGE DATABASES DURING BUSINESS HOURS. @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 @defragOrderColumn Defines how to prioritize the order of defrags. Only used if @executeSQL = 1. Valid options are: range_scan_count = count of range and table scans on the index; in general, this is what benefits the most FROM defragmentation fragmentation = amount of fragmentation in the index; the higher the number, the worse it is page_count = number of pages in the index; affects how long it takes to defrag an index @defragSortOrder The sort order of the ORDER BY clause. Valid options are ASC (ascending) or DESC (descending). @timeLimit Optional, limits how much time can be spent performing index defrags; expressed in minutes. NOTE: The time limit is checked BEFORE an index defrag is begun, thus a long index defrag can exceed the time limitation. @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. @forceRescan Whether or not to force a rescan of indexes. If set to 0, a rescan will not occur until all indexes have been defragged. This can span multiple executions. 1 = force a rescan 0 = use previous scan, if there are indexes left to defrag @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. @minPageCount Specifies how many pages must exist in an index in order to be considered for a defrag. Defaulted to 8 pages, AS Microsoft recommends only defragging indexes with more than 1 extent (8 pages). NOTE: The @minPageCount will restrict the indexes that are stored in dba_indexDefragStatus table. @maxPageCount Specifies the maximum number of pages that can exist in an index and still be considered for a defrag. Useful for scheduling small indexes during business hours and large indexes for non-business hours. NOTE: The @maxPageCount will restrict the indexes that are defragged during the current operation; it will not prevent indexes FROM being stored in the dba_indexDefragStatus table. This way, a single scan can support multiple page count thresholds. @excludeMaxPartition If an index is partitioned, this option specifies whether to exclude the right-most populated partition. Typically, this is the partition that is currently being written to in a sliding-window scenario. Enabling this feature may reduce contention. This may not be applicable in other types of partitioning scenarios. Non-partitioned indexes are unaffected by this option. 1 = exclude right-most populated partition 0 = do not exclude @onlineRebuild 1 = online rebuild; 0 = offline rebuild @sortInTempDB Specifies whether to defrag the index in TEMPDB or in the database the index belongs to. Enabling this option may result in faster defrags and prevent database file size inflation. 1 = perform sort operation in TempDB 0 = perform sort operation in the index's database @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 Called by: SQL Agent Job or DBA ---------------------------------------------------------------------------- DISCLAIMER: This code and information are provided "AS IS" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties or merchantability and/or fitness for a particular purpose. ---------------------------------------------------------------------------- LICENSE: This index defrag script is free to download and use for personal, educational, and internal corporate purposes, provided that this header is preserved. Redistribution or sale of this index defrag script, in whole or in part, is prohibited without the author's express written consent. ---------------------------------------------------------------------------- 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) 2009-08-28 MFU 3.1 Fixed read_only bug for database lists 2010-04-20 MFU 4.0 Added time limit option , added static table with rescan logic , added parameters for page count & SORT_IN_TEMPDB , added try/catch logic and additional debug options , added options for defrag prioritization , fixed bug for indexes with allow_page_lock = off , added option to exclude right-most partition , removed @rebuildStats option , refer to http://sqlfool.com for full release notes 2011-04-28 MFU 4.1 Bug fixes for databases requiring [] , cleaned up the create table section , updated syntax for case-sensitive databases , comma-delimited list for @database now supported ********************************************************************************* Example of how to call this script: EXECUTE dbo.dba_indexDefrag_sp @executeSQL = 1 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1 , @forceRescan = 1 , @maxDopRestriction = 1 , @minPageCount = 8 , @maxPageCount = NULL , @minFragmentation = 1 , @rebuildThreshold = 30 , @defragDelay = '00:00:05' , @defragOrderColumn = 'page_count' , @defragSortOrder = 'DESC' , @excludeMaxPartition = 1 , @timeLimit = NULL , @database = 'sandbox,sandbox_caseSensitive'; *********************************************************************************/ SET NOCOUNT ON; SET XACT_ABORT ON; SET QUOTED_IDENTIFIER ON; BEGIN BEGIN TRY /* Just a little validation... */ IF @minFragmentation IS NULL OR @minFragmentation NOT BETWEEN 0.00 AND 100.0 SET @minFragmentation = 10.0; IF @rebuildThreshold IS NULL OR @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 @defragOrderColumn IS NULL OR @defragOrderColumn NOT IN ('range_scan_count', 'fragmentation', 'page_count') SET @defragOrderColumn = 'range_scan_count'; IF @defragSortOrder IS NULL OR @defragSortOrder NOT IN ('ASC', 'DESC') SET @defragSortOrder = 'DESC'; IF @scanMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED') SET @scanMode = 'LIMITED'; IF @debugMode IS NULL SET @debugMode = 0; IF @forceRescan IS NULL SET @forceRescan = 0; IF @sortInTempDB IS NULL SET @sortInTempDB = 1; 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 NVARCHAR(4000) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000) , @indexDefrag_id INT , @startdatetime DATETIME , @enddatetime DATETIME , @getIndexSQL NVARCHAR(4000) , @getIndexSQL_Param NVARCHAR(4000) , @allowPageLockSQL NVARCHAR(4000) , @allowPageLockSQL_Param NVARCHAR(4000) , @allowPageLocks INT , @excludeMaxPartitionSQL NVARCHAR(4000); /* Initialize our variables */ SELECT @startdatetime = GETDATE() , @enddatetime = DATEADD(minute, @timeLimit, GETDATE()); /* Create our temporary tables */ CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) , scanStatus BIT ); CREATE TABLE #processor ( [index] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); CREATE TABLE #maxPartitionList ( databaseID INT , objectID INT , indexID INT , maxPartition INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NOWAIT; /* 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 /* Output the parameters we're working with */ IF @debugMode = 1 BEGIN SELECT @debugMessage = 'Your SELECTed parameters are... Defrag indexes WITH fragmentation greater than ' + CAST(@minFragmentation AS VARCHAR(10)) + '; REBUILD indexes WITH fragmentation greater than ' + CAST(@rebuildThreshold AS VARCHAR(10)) + '; You' + CASE WHEN @executeSQL = 1 THEN ' DO' ELSE ' DO NOT' END + ' want the commands to be executed automatically; You want to defrag indexes in ' + @defragSortOrder + ' order of the ' + UPPER(@defragOrderColumn) + ' value; You have' + CASE WHEN @timeLimit IS NULL THEN ' NOT specified a time limit;' ELSE ' specified a time limit of ' + CAST(@timeLimit AS VARCHAR(10)) END + ' minutes; ' + CASE WHEN @database IS NULL THEN 'ALL databases' ELSE 'The ' + @database + ' database(s)' END + ' will be defragged; ' + CASE WHEN @tableName IS NULL THEN 'ALL tables' ELSE 'The ' + @tableName + ' TABLE' END + ' will be defragged; We' + CASE WHEN EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL) AND @forceRescan <> 1 THEN ' WILL NOT' ELSE ' WILL' END + ' be rescanning indexes; The scan will be performed in ' + @scanMode + ' mode; You want to limit defrags to indexes with' + CASE WHEN @maxPageCount IS NULL THEN ' more than ' + CAST(@minPageCount AS VARCHAR(10)) ELSE ' BETWEEN ' + CAST(@minPageCount AS VARCHAR(10)) + ' AND ' + CAST(@maxPageCount AS VARCHAR(10)) END + ' pages; Indexes will be defragged' + CASE WHEN @editionCheck = 0 OR @onlineRebuild = 0 THEN ' OFFLINE;' ELSE ' ONLINE;' END + ' Indexes will be sorted in' + CASE WHEN @sortInTempDB = 0 THEN ' the DATABASE' ELSE ' TEMPDB;' END + ' Defrag operations will utilize ' + CASE WHEN @editionCheck = 0 OR @maxDopRestriction IS NULL THEN 'system defaults for processors;' ELSE CAST(@maxDopRestriction AS VARCHAR(2)) + ' processors;' END + ' You' + CASE WHEN @printCommands = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to PRINT the ALTER INDEX commands; You' + CASE WHEN @printFragmentation = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to OUTPUT fragmentation levels; You want to wait ' + @defragDelay + ' (hh:mm:ss) BETWEEN defragging indexes; You want to run in' + CASE WHEN @debugMode = 1 THEN ' DEBUG' ELSE ' SILENT' END + ' mode.'; RAISERROR(@debugMessage, 0, 42) WITH NOWAIT; END; IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NOWAIT; /* Retrieve the list of databases to investigate */ /* If @database is NULL, it means we want to defrag *all* databases */ IF @database IS NULL BEGIN INSERT INTO #databaseList SELECT database_id , name , 0 -- not scanned yet for fragmentation FROM sys.databases WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases AND [state] = 0 -- state must be ONLINE AND is_read_only = 0; -- cannot be read_only END; ELSE /* Otherwise, we're going to just defrag our list of databases */ BEGIN INSERT INTO #databaseList SELECT database_id , name , 0 -- not scanned yet for fragmentation FROM sys.databases AS d JOIN dbo.dba_parseString_udf(@database, ',') AS x ON d.name = x.stringValue WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases AND [state] = 0 -- state must be ONLINE AND is_read_only = 0; -- cannot be read_only END; /* Check to see IF we have indexes in need of defrag; otherwise, re-scan the database(s) */ IF NOT EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL) OR @forceRescan = 1 BEGIN /* Truncate our list of indexes to prepare for a new scan */ TRUNCATE TABLE dbo.dba_indexDefragStatus; 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 dbo.dba_indexDefragStatus ( databaseID , databaseName , objectID , indexID , partitionNumber , fragmentation , page_count , range_scan_count , scanDate ) SELECT ps.database_id AS 'databaseID' , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName' , ps.[object_id] AS 'objectID' , ps.index_id AS 'indexID' , ps.partition_number AS 'partitionNumber' , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation' , SUM(ps.page_count) AS 'page_count' , os.range_scan_count , GETDATE() AS 'scanDate' FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os ON ps.database_id = os.database_id AND ps.[object_id] = os.[object_id] AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number WHERE avg_fragmentation_in_percent >= @minFragmentation AND ps.index_id > 0 -- ignore heaps AND ps.page_count > @minPageCount AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode GROUP BY ps.database_id , QUOTENAME(DB_NAME(ps.database_id)) , ps.[object_id] , ps.index_id , ps.partition_number , os.range_scan_count OPTION (MAXDOP 2); /* Do we want to exclude right-most populated partition of our partitioned indexes? */ IF @excludeMaxPartition = 1 BEGIN SET @excludeMaxPartitionSQL = ' SELECT ' + CAST(@databaseID AS VARCHAR(10)) + ' AS [databaseID] , [object_id] , index_id , MAX(partition_number) AS [maxPartition] FROM [' + DB_NAME(@databaseID) + '].sys.partitions WHERE partition_number > 1 AND [rows] > 0 GROUP BY object_id , index_id;'; INSERT INTO #maxPartitionList EXECUTE sp_executesql @excludeMaxPartitionSQL; END; /* Keep track of which databases have already been scanned */ UPDATE #databaseList SET scanStatus = 1 WHERE databaseID = @databaseID; END /* We don't want to defrag the right-most populated partition, so delete any records for partitioned indexes where partition = MAX(partition) */ IF @excludeMaxPartition = 1 BEGIN DELETE ids FROM dbo.dba_indexDefragStatus AS ids JOIN #maxPartitionList AS mpl ON ids.databaseID = mpl.databaseID AND ids.objectID = mpl.objectID AND ids.indexID = mpl.indexID AND ids.partitionNumber = mpl.maxPartition; END; /* Update our exclusion mask for any index that has a restriction ON the days it can be defragged */ UPDATE ids SET ids.exclusionMask = ide.exclusionMask FROM dbo.dba_indexDefragStatus AS ids JOIN dbo.dba_indexDefragExclusion AS ide ON ids.databaseID = ide.databaseID AND ids.objectID = ide.objectID AND ids.indexID = ide.indexID; END SELECT @debugMessage = 'Looping through our list... there are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL AND page_count BETWEEN @minPageCount AND ISNULL(@maxPageCount, page_count); IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NOWAIT; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM dbo.dba_indexDefragStatus WHERE ( (@executeSQL = 1 AND defragDate IS NULL) OR (@executeSQL = 0 AND defragDate IS NULL AND printStatus = 0) ) AND exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0 AND page_count BETWEEN @minPageCount AND ISNULL(@maxPageCount, page_count)) > 0 BEGIN /* Check to see IF we need to exit our loop because of our time limit */ IF ISNULL(@enddatetime, GETDATE()) < GETDATE() BEGIN RAISERROR('Our time limit has been exceeded!', 11, 42) WITH NOWAIT; END; IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NOWAIT; /* Grab the index with the highest priority, based on the values submitted; Look at the exclusion mask to ensure it can be defragged today */ SET @getIndexSQL = N' SELECT TOP 1 @objectID_Out = objectID , @indexID_Out = indexID , @databaseID_Out = databaseID , @databaseName_Out = databaseName , @fragmentation_Out = fragmentation , @partitionNumber_Out = partitionNumber , @pageCount_Out = page_count FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL ' + CASE WHEN @executeSQL = 0 THEN 'AND printStatus = 0' ELSE '' END + ' AND exclusionMask & Power(2, DatePart(weekday, GETDATE())-1) = 0 AND page_count BETWEEN @p_minPageCount AND ISNULL(@p_maxPageCount, page_count) ORDER BY + ' + @defragOrderColumn + ' ' + @defragSortOrder; SET @getIndexSQL_Param = N'@objectID_Out INT OUTPUT , @indexID_Out INT OUTPUT , @databaseID_Out INT OUTPUT , @databaseName_Out NVARCHAR(128) OUTPUT , @fragmentation_Out INT OUTPUT , @partitionNumber_Out INT OUTPUT , @pageCount_Out INT OUTPUT , @p_minPageCount INT , @p_maxPageCount INT'; EXECUTE sp_executesql @getIndexSQL , @getIndexSQL_Param , @p_minPageCount = @minPageCount , @p_maxPageCount = @maxPageCount , @objectID_Out = @objectID OUTPUT , @indexID_Out = @indexID OUTPUT , @databaseID_Out = @databaseID OUTPUT , @databaseName_Out = @databaseName OUTPUT , @fragmentation_Out = @fragmentation OUTPUT , @partitionNumber_Out = @partitionNumber OUTPUT , @pageCount_Out = @pageCount OUTPUT; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NOWAIT; /* Look up index information */ SELECT @updateSQL = N'UPDATE ids SET schemaName = QUOTENAME(s.name) , objectName = QUOTENAME(o.name) , indexName = QUOTENAME(i.name) FROM dbo.dba_indexDefragStatus AS ids INNER JOIN ' + @databaseName + '.sys.objects AS o ON ids.objectID = o.[object_id] INNER JOIN ' + @databaseName + '.sys.indexes AS i ON o.[object_id] = i.[object_id] AND ids.indexID = i.index_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 ids.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE sp_executesql @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM dbo.dba_indexDefragStatus 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 are 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(' Checking for indexes that do NOT allow page locks...', 0, 42) WITH NOWAIT; /* Determine if page locks are allowed; for those indexes, we need to always REBUILD */ SELECT @allowPageLockSQL = 'SELECT @allowPageLocks_OUT = COUNT(*) FROM ' + @databaseName + '.sys.indexes WHERE object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' AND index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' AND Allow_Page_Locks = 0;' , @allowPageLockSQL_Param = '@allowPageLocks_OUT INT OUTPUT'; EXECUTE sp_executesql @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks 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) AND @allowPageLocks = 0 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, or if the index does not allow page locks, REBUILD it */ ELSE IF (@fragmentation >= @rebuildThreshold OR @allowPageLocks <> 0) 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 sort operation preferences */ IF @sortInTempDB = 1 SET @rebuildCommand = @rebuildCommand + N', SORT_IN_TEMPDB = ON'; ELSE SET @rebuildCommand = @rebuildCommand + N', SORT_IN_TEMPDB = 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 ELSE /* Print an error message if any indexes happen to not meet the criteria above */ IF @printCommands = 1 OR @debugMode = 1 RAISERROR('We are unable to defrag this index.', 0, 42) WITH NOWAIT; /* Are we executing the SQL? IF so, do it */ IF @executeSQL = 1 BEGIN SET @debugMessage = 'Executing: ' + @sqlCommand; /* Print the commands we're executing if specified to do so */ IF @printCommands = 1 OR @debugMode = 1 RAISERROR(@debugMessage, 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 , sqlStatement ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @datetimestart , @sqlCommand; SET @indexDefrag_id = SCOPE_IDENTITY(); /* Wrap our execution attempt in a TRY/CATCH and log any errors that occur */ BEGIN TRY /* 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; END TRY BEGIN CATCH /* Update our log with our error message */ UPDATE dbo.dba_indexDefragLog SET dateTimeEnd = GETDATE() , durationSeconds = -1 , errorMessage = ERROR_MESSAGE() WHERE indexDefrag_id = @indexDefrag_id; IF @debugMode = 1 RAISERROR(' An error has occurred executing this command! Please review the dba_indexDefragLog table for details.' , 0, 42) WITH NOWAIT; END CATCH /* Just a little breather for the server */ WAITFOR DELAY @defragDelay; UPDATE dbo.dba_indexDefragStatus SET defragDate = GETDATE() , printStatus = 1 WHERE databaseID = @databaseID AND objectID = @objectID AND indexID = @indexID AND partitionNumber = @partitionNumber; 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 OR @debugMode = 1 PRINT ISNULL(@sqlCommand, 'error!'); UPDATE dbo.dba_indexDefragStatus SET printStatus = 1 WHERE databaseID = @databaseID AND objectID = @objectID AND indexID = @indexID AND partitionNumber = @partitionNumber; END END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying a summary of our action...', 0, 42) WITH NOWAIT; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , range_scan_count FROM dbo.dba_indexDefragStatus WHERE defragDate >= @startdatetime ORDER BY defragDate; END; END TRY BEGIN CATCH SET @debugMessage = ERROR_MESSAGE() + ' (Line Number: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ')'; PRINT @debugMessage; END CATCH; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #databaseList; DROP TABLE #processor; DROP TABLE #maxPartitionList; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NOWAIT; SET NOCOUNT OFF; RETURN 0; END |
Pingback: Recommended reading for anyone dealing with MSSQL | Clarion Edge
Pingback: Log Buffer #225, A Carnival of the Vanities for DBAs | The Pythian Blog
Hi Michelle,
Thanks for making this available. Just an exceedingly minor point
FROM [' + DB_NAME(@databaseID) + '].sys.partitions
would be better off as
FROM ‘ + QUOTENAME(DB_NAME(@databaseID)) + ‘.sys.partitions
to deal correctly with any database names containing the “]” character
… And on a CS collation DATETIMEStart needs to be dateTimeStart
Super script.
Have found some bugs,
1) @fragmentation_Out is set as INT where @fragmentation, and the table columns are floats.
2) If you run the sp with @executeSQL = 0 and @database = NULL, it will populate the Status table with all the indexes to process. However if you then run the sp again with @executeSQL = 1 and specify a value for @database, all the rows in the status table get processed, regardless of the database.
Pingback: Index Defrag Script, v4.0 « SQL Fool
Pingback: Index Defrag Script, v3.0 « SQL Fool
Pingback: Index Defrag Script « SQL Fool
Pingback: Index Defrag Script « SQL Fool
This is by far the best script I’ve found for db maintnence, although have noted a slight bug in our environment (probably the way the applications work more than anything).
We run it nightly for 30 minutes at a time with a force rescan on weekends. During the week, if an object is dropped / index recreated after the initial scan, the job fails in the next run.
I’ve amended it on our environment to remove the object from dba_indexDefragStatus if it comes back without an object / index name after the /* Look up index information */.
Just thought to let you know.
The script was excellent, it would be great if you can add the code for HTML report upon the completion. so that we can see the fragmentation level of pre index and post indexing.
Awesome script. I have been using it in our production systems for over a year now. Other than what phil mentione above, the @database variable is a bit too short for instances with a lot of databases. I increased it to VARCHAR(4000). Just ran into this on a new instance we are taking over.
What is the format for the days exclusion to put into the dba_indexDefragExclusion table? I want to exclude an entire dbatabase (SharePoint_Config) since Mircosoft recommends not doing this outside of SharePoint.
UPDATE ids
SET ids.exclusionMask = ide.exclusionMask
FROM dbo.dba_indexDefragStatus AS ids
JOIN dbo.dba_indexDefragExclusion AS ide
ON ids.databaseID = ide.databaseID
AND ids.objectID = ide.objectID
AND ids.indexID = ide.indexID;
This is a great procedure and has greatly simplified my life.
Hey Michelle,
I’ve got a little question about the script:
We are running this script daily, the job starts with:
EXEC master.dbo.dba_indexDefrag_sp
@timeLimit = 120,
@maxPageCount = 3000000
There’s no @forceRescan parameter given (so standard = 0, no rescan)
Now all indexes will be defragmented (except those > 3000000 page count).
We have 1 big index, this one stays fragmented (stays in dbo.dba_indexDefragStatus as not defragmented, while other indexes are defragmented now).
In this case, nothing will happen anymore with your script…
Because the script will check if there were still indexes to defrag (the 1 big index, but with those parameters, the defragmentation will not start for this 1), he will find one and not rescan the databases… while all other indexes are defragmented (done) in the dbo.dba_indexDefragStatus.
Ok I could use the @forceRescan parameter, but I don’t want to rescan the whole instance each day (timeloss and performance loss, I’ve only a 2hours maintenance window available)
Wouldn’t it be better to add the parameters in the check to see IF we have indexes in need of defrag?
Love this routine! Thanks! I’m replacing my current script with this one across all our servers. Would love it even more if the log contained the post-defrag fragmentation level too
Sounds like a great script. Just a little problem on installation, I get the following error…
Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
This is an excellent tool. Thanks!
How can I just log the fragmentation with out defraging or rebuilding. I want to track the fragmentation over time for baselining.
Is there any specific reason to use OPTION (MAXDOP 2); on dmv query?
Is there any specific reason to use OPTION (MAXDOP 2); on dmv query? I am having 8 CPUs
@David, guessing a combination of @executeSQL = 0 & @forceRescan = 1, will do the capturing part, but you’d have to move the results from dbo.indexDefragStatus into another table to store it.
First of all – great script!
Second, I found an error where an object name (index name) is the full 128 characters. When using the QuoteName() function, the storage in the tables and the variables in the script need to be upped to 130 characters to allow for the storage of the ‘[]‘ characters.
Why is my posts removed?
when compiling the procedure, i get this msg
Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 463
Incorrect syntax near ‘OBJECT_ID’
thoughts?
A question: why did you remove the @rebuildStats-option?
After doing a defrag, it is better the stats are recalculated.
Especially using your tools on a regular basis: indexes will probably never become much fragmented to trigger a rebuild. So it will always do a defrag never updating the statistics and I do NOT want them to be triggered at random.
Hi Michelle, I have a recommendation for the next release.
In this version, the table dba_indexDefragStatus is truncated in case of a @ForceRescan. This means it is deleting all outstanding re-index actions from all databases. Since I run this script at various times for various databases, it is currently also deleting remaining index actions for other databases. My suggestion is to change the “truncate table” statement to “delete from … where databasename = @databasename”
Correction: the delete statement should be: “databasename in (select name from #databaselist)”
Hi Michelle,
Great script!
It would be great though, if you could add the option to choose between ONLINE REORGANIZE of individual index partitions (like it does now) and ONLINE REBUILD of the complete partitioned index.
We can’t afford rebuilding partitions offline, and an online reorganize does not reduce fragmentation levels enough.
Another idea: if would be nice to be able to exclude certain databases (without having to add each individual table of that database into the dba_indexDefragExclusion table.
Hi Michelle,
once again: thank you for this great solution.
I think the idea to be able to exclude certain databases ( as Rob mentioned ) would be a nice feature.
Maybe the same variable @database but when you wish to exclude a database you have to type ‘-YourDatabaseName’.
Michelle,
Great script!
One thing I ran across.
If looking to just print the commands (@executeSQL = 0) with print fragmentation results on (@printFragmentation = 1) the results at the end of executing the procedure do not display as the select statement is based off of defragDate which is NULL in table dba_indexDefragStatus because I did not execute.
I feel some might want to execute the procedure to receive the commands only but to also view the results of the current fragmentation without executing.
Just an FYI for possible improvements.
Thanks again for the script!
Bug when selecting a tablename?
It seems that a single table defrag is not working if the tablename is not in the database where this SP is installed. This because the OBJECT_ID(@tablename) is always returning NULL. As a result the complete database is processed, taking a very long time. To fix this, the object_ID() function must use the 3-part name convention)
Hi. first off, many thanks for sharing this excellent script. I’ve noticed some weird behaviour today though.
I copied this last week and have shared it with my team and we’ve all used it successfully. However, when I opened it today to execute it on a different server I’m getting the error “Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464. Incorrect syntax near ‘OBJECT_ID’.” which is around this line…
” , GETDATE() AS ‘scanDate’
FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps
JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os …”
I thought someone must have edited the script by mistake so I re-downloaded and the same thing happened. I then thought it must be SSMS playing up, so tried SSMS from a different machine to a different server and same thing. I copied the offending select part of the Insert statement to a new window, declared the variables used within the select and the query will parse. Any ideas?
Using:-
Microsoft SQL Server Management Studio 10.50.1617.0
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3623
Operating System 5.1.2600
Pingback: Update Statistics fails with Error Number:–1073548784 "Could not allocate space for object 'dbo.SORT” - REPLTalk covers Using and Tuning SQL Replication - Site Home - MSDN Blogs
We needed this for fast changing tables with several thousand pages. We have to apply online defrag due to the characteristics of data
This is a great script! Thanks!
I’m not seeing any options to “prune” the “dba_indexDefragLog” table. Does this just continue to grow?
Thanks!!
I think I found the solution for the below error
“Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464. Incorrect syntax near ‘OBJECT_ID’.”
All you just need to do is to change the database Compatibility level to either “SQL Server 2005 (90)” or “SQL Server 2008 (100)”
Hello,
Thanks for sharing your great backup script. Is there any way we could get logic added to rebuild statistics if an index is reorganized?
Thanks again!
Hey Michelle and everyone…
I made some additions to this script to provide some of the functionality asked for in the replies (those I thought would be good) and some that I felt would make this a fully rounded procedure. I am willing to share what I have done and was wondering if I could get this to Michelle for her to look over.
I will admit I am not the expert at T-SQL so I most likely perfomed some things incorrectly (though they all seem to work in my environment.) I would love it if someone (like an expert) could really look over my code and let me know if the see some common issues.
Thanks
Thanks for the awesome script!
Wonderful script!
However, would it be possible to exclude certain indexes from being rebuilt/re-organized altogether. In other words what value do I assign in ExclusionMask in the dba_indexDefragExclusion table to exclude the index for all days?
For some reason my previous comment did not get published….
My question is…how can we exclude some indexes from being rebuilt/reorganized at all? In other words what should I set in the exclusion mask to ignore rebuilding the index on all days?
Thanks in advance. Other than that, this script is superb
Cheers!
This is an awesome script.. i loved testing this, changing it as per my own need and the parameters thought are absolutely inline and captures most of the information…
I have one question and don’t know how to go about doing this, please do let me know if their is a way out or you can help me out getting the code done.
The customer of mine wants me to segregate indexes based on the operation used:
1) They want to run Rebuild index online for some of the tables and we need to to automate that without doing any manual work, similarly reorg.
2) Also, they want to segregate the indexes which needs rebuilding of indexes offline and they wont run as part of the script provided, but rather it has to pick the details and dump into an exclusion table and later the commands should be picked up and executed (by a job or in any automated way possible), also they wish to exclude all those indexes which they know they can’t do much about (they keep getting defragged, small tables).
3) Keeping all this in mind, i looked at the script its very close to what we require except the above 2 points..
Rebuilding indexes offline needs change request here at my clients place and hence possibly keeping them in some store and then executing it when the maintenance window is enabled. (no manual work, only automation required)
4) Also, i wish to know if i wish to complete the defrag in 4 hours, i specify the time in the parameter, question is what if it doesn’t complete within the time range specified, i know there is a parameter named forcescan, but do you think its a good way to go for stopping the indexing at a specific time frame? what will be the state of the database? please help me with these questions would be a lot of help.
You can also mail me at: shanku02@yahoo.co.in
Thanks and Regards,
Tara
Michelle, thanks for making this script available. It’s great.
How would you recommend redirecting the ‘debugMode = 1′ and @debugMessage messages to a log file?
Thank you.
One more request… Could you add logic involving the use of the index exlusion table? Right now you can only define one day to exclude an index from being defragmented. It would be nice to have an option to exclude all days or multiple days instead of just one day per index.
Thanks!
@Jason: The logic you’ve requested should already be in there. The exclusion mask for days is just that a bit flag mask for the days that should be excluded. The values are listed in the script and just need to be added together to come up with the integer equivalent of the bits required. So for Monday and Sunday exclusion you would enter 3 which sets the bit mask as 0000011. An exclusion of all days would look like this in binary 1111111 or 127 as an integer value.
Hi Michelle,
First, thank you for this great script, it’s exactly what I searched.
Just one question: would it be possible to add a function to send a mail with the result, a kind of report?
Thanks.
Thanks for taking the time to build this script and sharing.
Thanks for the great script.
It would be great if you can provide similar script for backups & recovery!!!!
Thanks for the updated script, Michelle. In comparing it to the 2008 version I’ve been using, one thing I notice is the removal of DBCC SHRINKFILE. I know db shrinking is a point of contention among most db developers, so I supposed I’m not surprised to see it gone. I’m just wondering what your specific reasons are for removing it, and perhaps for including it in the first place.
Pingback: RedGate SQL Index Manager BETA « Tradney's Blog
What is the use of creating the function dba_parseString_udf?
should we add one more parameter, so if there is LOB data then first we need to reorganize index but after that it still more fragmented then do rebuild on that ?
Hi ,
Great Script…
I have few query on this script…
In this script ,
its checking if @allowPageLocks =0 (count for index which have Allow_Page_Locks=0) and if object have 1 or more LOB Objects then although index fragmentation value exceeds the rebuidthreshold value , it dont make rebuid , but make reorganization on it.
I am a little confused on it, and need to know , what is reason that we should do rebuid on index if we found ,1 or more LOB objects and Allow_Page_Locks=0
Pingback: SQL Server Tools | Jeremy Reid's Website
Fantastic script with superb flexibility. Thanks very much.
Pingback: Selectively Updating Statistics
Great tool.
Is there a way to compare Fragmentation Levels side-by-side Before and After the running this SP?
Pingback: SharePoint SQL Server Performance Tuning Roundup | SharePoint 2010 Performance Blog
Pingback: SharePoint SQL Server Performance Tuning Roundup | SharePoint 2010 Performance Blog
Pingback: SQL Server isn’t always perfect - SQL Server with Mr. Denny
Pingback: Statistics and Recompilations, Part II | Erin Stellato | Erin Stellato
@Patrice
Old comment/question but could be of help for someone else:
Change
ON d.name = x.stringValue
To
ON d.name COLLATE DATABASE_DEFAULT = x.stringValue COLLATE DATABASE_DEFAULT
I run the script an d got the error:
Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1255_CI_AS” and “Hebrew_CI_AS” in the equal to operation.
Well done. Pretty great tool that I had ever try to maintanence on indexes.
Maybe If you should modify a little bit change.
You are checking the Online rebuild eligibility on this part of your code (1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer) ;
IF (SELECT ServerProperty(‘EditionID’)) IN (1804890536, 610778273, -2117995310)
But you didn’t add a SQL Server 2008 R2 Datacenter Edition ID into your code.
On the next version don’t forget to add -978676123 = Datacenter ID in to your code. Because, you can use Online rebuild processing with Datacenter Edition too.
Kind Regards,
Yigit.
when i run the script to create it first time i get:
Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464
Incorrect syntax near ‘OBJECT_ID’.
Phil,
For your OBJECT_ID error, check to make sure that the compatibility level of the database that you are creating the stored procedure in is 90 or higher. If the database is in compatibility mode 80, it will generate the OBJECT_ID error you are experiencing.
Tim
Hello there,
Great script, I have ran this script on one of our servers and the transcactional log backup is huge and makes are log shipping to break do you know a way to fix this or maybe what should we look at?
Much appreciate your help
Great script! Only change I made to it was to include a @FillFactor variable that let me specify the fill factor to use when rebuilding.
Pingback: One Hour Server
When I run your script, I get this message.
Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
Cannot resolve the collation conflict between “Latin1_General_CI_AS_KS_WS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Will you please help?
I’m sorry for this being so silly, but is there a trick to actually making this work?
The tables are created and I exec the sp, but nothing happens. All the tables are still empty and it seems nothing changes.
I’ve tried looking back at previous posts of your different versions, but can’t seem to find any kind of trick to actually working this for those of us sql newbies.
Hi Rob,
Try running this. It should return some messages to you. Do you see anything?
EXECUTE dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1
, @forceRescan = 1
, @maxDopRestriction = 1
, @minPageCount = 8
, @maxPageCount = NULL
, @minFragmentation = 1
, @rebuildThreshold = 30
, @defragDelay = ’00:00:05′
, @defragOrderColumn = ‘page_count’
, @defragSortOrder = ‘DESC’
, @excludeMaxPartition = 1
, @timeLimit = NULL;
David, thanks for letting me know about that. I’ll check into the collation issue while I’m working on the next version.
@Paul – Thanks for the suggestion!
@Foustrouka – Try breaking it up into chunks, i.e. run with @minPageCount = 8 and @maxPageCount = 1000, then @minPageCount = 1000 and @maxPageCount = 10000, etc. Also, if you have really large tables (i.e. >100GB for a single table), you may want to consider partitioning them. If you can’t partition them, you’ll want to add them to the exclusion list and defrag those manually.
Michelle – it actually ended up running – I just didn’t know it would take a long time to fill the tables.
Hi Michelle,
I found your script while looking for tools to help get our SQL server under control. I haven’t had a chance to try it (although I think we were running an earlier version on our previous server), but I wanted to point out a problem for some of us. The section that inserts into dbo.dba_indexDefragLog lists column DATETIMEStart, but the create statement names the column dateTimeStart. In our case we have case-sensitivity on, which caused the script to fail until I tweaked the insert statement.
Thanks for a great script. When I wrapped it in a job I found it was failing in my SharePoint environment. It turns out that a handful of tables have ALLOW_PAGE_LOCKS set to false which means the index cannot be rebuilt so I added a loop to delete the indexes from the to do list when that condition occurs.
I’ve modified the script quite a bit so I don’t think my “patch” is compatible with your latest version, but the field to check is (dbname).sys.indexes.allow_page_lock which must be true (1) for rebuilding to take place.
Michelle-
After my last comment I was successfully able to incorporate my patch for checking the ALLOW_PAGE_LOCKS option into version 4.1 of your script and deploy it to a couple of servers. Please contact me if you would like the code for your next version, this is a great tool and I’d love to be able to contribute to its development.
Pingback: A hole in SQL maintenance « nujakcities
Pingback: Database Maintenance for Microsoft SharePoint 2010 Products | SharePoint 2010 Performance Blog
Great script, good work Michelle, thank you!
First of all I greatly appreciate the hard work that was done on this script. We do not have any DBA so I am the one in charge of our DB and we have a huge Fragmentation level 99% on most of our database tables.
I am still struggling to get this going, to your post to rob about
EXECUTE dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1
, @forceRescan = 1
, @maxDopRestriction = 1
, @minPageCount = 8
, @maxPageCount = NULL
, @minFragmentation = 1
, @rebuildThreshold = 30
, @defragDelay = ’00:00:05′
, @defragOrderColumn = ‘page_count’
, @defragSortOrder = ‘DESC’
, @excludeMaxPartition = 1
, @timeLimit = NULL;
Those settings above is it something that I need to add into the script to be functional or is there already a spot to fill in all that information?
Any help would be appreciated.
Thanks
Disregard, I figured it out finally
Hi
I just found your script, and it look promising, i just have one feature request, is it possible to add the ability to switch a database to SIMPLE logging before doing any Rebuild/Reorganize, and then back to FULL logging afterward?
The reason for this is that, a few of our databases are quite large, and the space on the drive where they are are limited, so if the Databases are in FULL logging mode when running, it will just fill the drive, which would be bad.
hi dear
Can u give us any solution which will work in sql azure
Thanks
Hi Michelle. This is one great script! I had created a simpler one, but will change to yours asap. I hope you don’t mind if I republish this too, of course making a link and giving proper credit to you. Also if I do any improvement I will let you know.
thanks!
Pingback: Sharing knowledge: Index Defrag Script | The Lonely DBA
Pingback: Database Maintenance Strategies for Dynamics AX | MSDN Blogs
Ah. The reason for the error of Object_ID is in the compatibility mode of the database. It has to be over 90
Very good script and thanks for all your work. Could this be run from a central location against a number of servers also what is the @database parameter for multiple databases; having @database=NULL runs the script against all databases including system.
Thanks again,
Firstly, thank you for this amazing work!
I am having a problem , even after running this
EXECUTE dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1
, @forceRescan = 1
, @maxDopRestriction = 1
, @minPageCount = 1
, @maxPageCount = NULL
, @minFragmentation = 1
, @rebuildThreshold = 30
, @onlineRebuild = 1
, @sortInTempDB = 1
, @defragDelay = ’00:00:05′
, @defragOrderColumn = ‘page_count’
, @defragSortOrder = ‘DESC’
, @excludeMaxPartition = 1
, @timeLimit = NULL
, @database = ‘EWS_LIVE,RCC_LIVE,RSI_Live,B1if’;
I still end up with over 20-70% fragmentation in objects with over 20 page, even ones with 200 pages are still reporting fragmentation.
If the database collation is case sensitive then you will get an invalid column error on the insert into “DATETIMEStart”. Easy fix just change it to “dateTimeStart” and you can run the create store procedure.
Regards,
Serg
Pingback: Utility Database | FradenSQL
I’m running SQL 2005 Standard. I have some very large tables that need to be defragged but I want to keep the indexes online during the rebuild. Is there another way to accomplish this without upgrading to Enterprise? Thank you for your script and help.
Michelle, This new version is a thing of beauty. I’ve tried creating several, starting from scratch and other peoples suggestions. The simplicity and effectiveness of version 4.1 made it a no-brainer to just plug into my management db on each instance. The force rescan and exclusion by date options are particularly clever. Bravo!
Kudos to you! I have been using this script in our environment for a few years now and I just wanted to thank you for your efforts on this as it’s saved my TONS of time. Our databases purr like a kitten thanks to you.
Michelle,
I have added a tweak that I thought I should mention. We have a couple of archive tables that I was excluding based on Max page count. This put me into a loop where the status table would not truncate, and I had force rescan set to 0 so a rescan would never happen, and the last couple of indexes would never be rebuilt. I added a line to the where clause to force a truncate when all indexes within the page count limits had been exhausted:
IF NOT EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL AND page_count BETWEEN @minPageCount AND ISNULL(@maxPageCount, page_count) )
OR @forceRescan = 1
Hello,
Great script! Worked well in 2008R2 SP2 but when I tried it in 2012 RTM, got the message:
“The user does not have permission to perform this action. (Line Number: 442)”
even if i ran it as a sysadmin server role member…
Thanks,
Chris
Hello,
The problem was not with SQL 2012 but with databases member of a Always On availabily group. I modified the code as below:
/* Retrieve the list of databases to investigate */
/* If @database is NULL, it means we want to defrag *all* databases */
IF @database IS NULL
BEGIN
IF @@VERSION LIKE ‘%2012%’
EXEC (‘INSERT INTO #databaseList
SELECT database_id
, name
, 0 — not scanned yet for fragmentation
FROM master.sys.databases
WHERE [name] NOT IN (”master”, ”tempdb”)– exclude system databases
AND [state] = 0 — state must be ONLINE
AND is_read_only = 0
AND replica_id IS NULL
UNION ALL
SELECT DISTINCT database_id
, name
, 0
FROM [sys].[dm_hadr_name_id_map] A,
[sys].[dm_hadr_availability_group_states] B,
[sys].[dm_hadr_database_replica_states] C,
[sys].[sysdatabases] D
WHERE A.ag_id=B.group_id
AND B.group_id=C.group_id
AND C.database_id=D.dbid
AND B.primary_replica=@@servername — if we are on the active node
AND B.primary_recovery_health=1′)
ELSE
INSERT INTO #databaseList
SELECT database_id
, name
, 0 — not scanned yet for fragmentation
FROM sys.databases
WHERE [name] NOT IN (‘master’, ‘tempdb’)– exclude system databases
AND [state] = 0 — state must be ONLINE
AND is_read_only = 0; — cannot be read_only
END;
Pingback: 1 dia do evento 24h PASS « Sergio C. Fonseca Weblog
I have some indexes with page compression enabled. Will running this script over those indexes remove the compression?
hi Michelle,
thanks for the script. that fixed my problem , which i was facing with our script .
but , a question, there were couple of tables which were left fragmented , and they were not even small, most of them were above 1000 pages. what could be the reason for this ??
Pingback: The Default Fillfactor for an Index « Voice of the DBA
Hi Michelle – thanks for sharing all your awesome scripts!
I previously used this script and everything worked 100%!
But now recently when I tried to run the procedure again, I get the following error (I also recreated it to ensure no changes from your version cause the problem):
Cannot insert the value NULL into column ‘objectName’, table ‘TestDb.dbo.dba_indexDefragLog’; column does not allow nulls. INSERT fails. (Line Number: 732)
The only thing that I can think of is that the object ID inserted from sys.dm_db_index_physical_stats does not exist in (@databaseName).sys.objects?
Not long ago one of the guys dropped all of the tables in our master db – and it took quite some time for our ‘DBA’ to get the master db up and running again using some sort of backup. I’m not sure if this may be the reason for the problem? And how to fix this? Or maybe it’s something totally different.
I don’t see fillfactor(@fillfactor) option during the index rebuild. Am i missing or it not there in the code? Can someone please suggest me on this?
Pingback: Top 10 Free SQL Server Tools of 2013 - Work Smarter This Year
Hi Michelle,
Do you already have the new script for SQL 2008 version?
I tried it on our 2008 and it has an error:
Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 277
Incorrect syntax near ‘OBJECT_ID’.
Thank you in advance.
Red
Holy wow this is awesome! Thank you so much for sharing this!!
Pingback: Ten Free SQL Server Tools that you Need to Know About | sql Hammer
Pingback: SQL Server
Hello,
I am not sure if your script does this, but, shouldn’t the clustered indexes be defragmented first?
Regards.
Hi,
the sp is amazing. It consider almost everything in rebuilding an index. But I have a suggestion. If there is a lob object then you reorganize index. but you can rebuild index as offline. maybe you can add a parameter whether user wants to offline rebuild for only lob object indexes and online rebuild for other tables.
I have used this tremendous tool while at two separate employers. This has been invaluable and the logging is tremendous. I built out a job to simply grab fragmentation levels at various points of the day and then log the results to a new table that is not truncated. What I am being asked to do now is a monthly validation that checks the current fragmentation level and compares that to the dba_indexDefragLog, to validate that any index shown to be fragmented over 30% at a point-in-time at month-end, has been maintained in the last 30 days. I want to automate this on a monthly basis and think I have properly assessed the situation and needs, but don’t necessarily want to re-invent the wheel. Any ideas how to accomplish this or if anyone has had a similar request?
Pingback: Easy automation of SQL Server database maintenance - Paul S. Randal
Hi Michelle,
Thanks again for doing this for the community! I noticed that ONLINE rebuilds are failing in my case. We’re running SQL Server 2012 SP1 CU3 BTW.
The Enterprise Edition check needs to have 1872460670 added.
http://msdn.microsoft.com/en-us/library/ms174396.aspx
Thanks!
Pingback: SQL Server 2012 defrag job runs interactively, but not as a job – why? | Question and Answer
Found small problem in my case:
Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 431
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
and fixed it this way:
--> ALKR
/*OLD CODE
ON d.name = x.stringValue
*/
ON d.name = (x.stringValue COLLATE Latin1_General_100_CI_AS)
--< ALKR
When I run your defrag script, I get the following error collation conflict error:Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.