Defragmenting Indexes

First published on TECHNET on Aug 31, 2010

Last update to script 1Nov2011.

Maintaining indexes is a primary duty of Database Administrators, because appropriate indexes are critical to maintaining high performance in any relational database, and proper maintenance is required to avoid excessive fragmentation. In case that's not clear enough, let me emphasize: index fragmentation can dramatically degrade performance.

While the Maintenance Plan Wizard in SQL Server Management Studio (SSMS) is very helpful for a number of things, and it includes reorganize and rebuild index tasks, those tasks should be created with great caution. Neither of these tasks will intelligently decide which indexes need to be defragmented, it will simply defragment all indexes in all the databases you specify. Indexes with less than 10% average fragmentation should usually be left alone, indexes with fragmentation between 10% and 30% should be reorganized, and indexes with fragmentation above 30% should be rebuilt. And for novice DBAs just getting into index maintenance, you don't need to reorganize an index if it's going to be rebuilt, because rebuilding recreates the index completely.

What I often do to maintain indexes on systems where the databases are less than 100GB or so is to create a SQL Server Agent job that will examine every index and conditionally skip, reorganize, or rebuild them, then I use the Maintenance Plan Wizard to create a SQL Agent job to run that job on a regular basis. You could schedule the job directly in SQL Server Agent, but I like to have it in my maintenance plans with the backups, integrity checking, statistics updates, and cleanup tasks. I've attached the script I usually use to this post, and its code uses nested loops to cycle through all databases except for those in an exclusion list, then loops through all tables, and then loops through all indexes with more than 10% fragmentation. For each index fragmented greater than 10%, it reorganizes the index if it's fragmented between 10-30%, and rebuilds it if the fragmentation is over 30%, using a 90% fill-factor. This applies to both clustered and non-clustered indexes. You may change the assigned values of the variables to change the fill-factor or action thresholds.

LOGICAL vs. PHYSICAL FRAGMENTATION

One thing that seems to confuse a lot of folks is the difference between logical and physical fragmentation, so here's a very short explanation: A database table can have one or more indexes, all of which exist inside database files (such as master.mdf). Indexes can be logically fragmented inside a database file, and a database file or log file can be physically fragmented. Indexes become fragmented by some INSERT/UPDATE/DELETE operations, and database files and log files become physically fragmented each time they increase in size. Defragmenting indexes does NOT defragment database or log files, and defragmenting a database or log file does NOT defragment any indexes inside the database file. Defragmenting indexes should be done on a regular basis, while physical fragmentation can and should be avoided completely. If a database or log file is already fragmented, it should be defragmented, and then kept from becoming fragmented.

The script I've attached to this post only defragments indexes, not database or log files.

You avoid database and log file fragmentation by creating the initial files large enough to hold 1-2 years worth of data without having to grow in size. An easy way to estimate if a database or log file is fragmented is to open the database properties in SSMS, click on the Files page, and look at the settings. If file growth is allowed and set to grow 1MB at a time (the default) and the Initial Size is a large number (e.g. 2912) that's not a round number (e.g. 3000), then it probably grew to it's present size one megabyte at a time, and may be extremely fragmented. You could get a big disk I/O boost by physically defragmenting it. A more precise method is to use contig.exe, a free download from Microsoft ( http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx ). For example,
C:\> contig -a "E:\Data\master.mdf"
will tell you the total number of fragments for the master.mdf file. One 'fragment' means it's not fragmented, and the larger the number, the worse your disk I/O performance will be because of fragmentation. Once a database file is defragmented, keep it defragmented by making it large enough so that it won't have to autogrow, and set up an alert so that you get notified when it gets to 80% of it's file capacity, so you can make plans to grow it in one large chunk during a low-useage time period.

If there's enough free space on the disk, contig.exe can also be used to defragment a single file just be dropping the "-a" option, and can defragment mutliple files by using wildcards (e.g. *.mdf). Contig can defragment SQL Server database and log files while the databases are online, but there is some performance cost (whether or not the performance hit will be a problem depends on the particular system). Two additional methods to defragment physical database and log files are backup/restore or detach-move-attach, and both require down-time. The detach-move-attach method requires the files to be moved to another hard drive to accomplish the defragmentation, although they can be moved back to the original drive before reattaching.

TESTING FRAGMENTED INDEXES

Want to create an index that you know is logically fragmented for testing purposes? Here's some code to do exactly that:

USE master
GO

CREATE DATABASE TestFrag
GO

USE TestFrag
GO

CREATE TABLE dbo.Table1(
Cola int NOT NULL,
CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Cola ASC)) ON [PRIMARY]
GO

DECLARE @a int
SET @a = 1
WHILE @a < 100000
BEGIN
IF @a % 2 = 0
INSERT Table1 VALUES(@a)
SET @a = @a + 1
END

SET @a = 1
WHILE @a < 100000
BEGIN
IF @a % 2 <> 0
INSERT Table1 VALUES(@a)
SET @a = @a + 1
END
GO


SELECT index_name = b.name, a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Table1'), NULL, NULL, NULL ) AS a
INNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

UPDATES to Script:
16Sep2010: Revised to handle object names with hyphens, spaces, etc.
11Jul2011: Added code to update statistics with FULLSCAN after reorganizing an index. Changed database STATIC cursor to LOCAL FAST_FORWARD READ_ONLY and table and index cursors from STATIC to FAST_FORWARD READ_ONLY.
27Sep2011: 1. Revised to loop through clustered indexes first since that will automatically result in the table's non-clustered indexes being rebuilt, then it loops through non-clustered indexes to cover the ones that weren't just rebuilt from the first loop. 2. Revised to allow online rebuilds for enterprise edition of SQL Server. Implemented with a switch, with a default of online rebuilds. 3. Revised to skip indexes below a minimum number of data pages. 4. Revised to skip reorg commands on indexes with disabled page-level locking. 5. Revised to skip indexes that are disabled.
1Nov2011: Added print-only option. Suggested by Casey Loranger: Added code to optionally switch recovery model to bulk-insert and back to full.

Defrag_Indexes_v5.sql