Index Defrag Script, v4.1
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, 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, 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