The Lazy DBA

Index fragmentation is a lot like cholesterol. The bad kind, not the good kind. It builds up slowly. Some deletes occur, leaving empty space in a data page here. Inserts occur, but the target page is packed, so a page split occurs so the record can be inserted in the correct order, yet the other page is now mostly empty. Updates are a double whammy. Over time, your index pages continue to be less and less full, meaning you have to perform that many more reads per query.

Just like cholesterol, it’s not perceptible. Sure, if you compared yourself now to 10 years ago, you’d be able to instantly recognize that you feel tired all the time, or occasionally dizzy spells or have blurred vision. Then all of a sudden — HEART ATTACK!

Ok, so maybe it’s not a heart attack. However your phone is ringing, and your phone never rings. Maybe some queries for your application start timing out, or your users are complaining that Application-X is slow. However the point remains that the performance degradation over time is imperceptible unless you’ve set something up to monitor index fragmentation, and you’re not aware of what was going on until you have a serious problem, and it’s your problem.

Doc, What Should I Do?

Well, your indexes are fragmented, so you should… defragment them? See, software is great because unlike high cholesterol, we can just make the problem go away as if we were exercising and eating right for the past decade or so.

For dealing with index fragmentation, we have three possible options:

1. Drop the indexes and recreate them.
2. Rebuild the index.
3. Reorganize the index.

The first option is great, except indexes that are dropped aren’t available for use.  However it does ensure that not only will our data pages be as full as possible (or as full as our FILLFACTOR dictates), but they have the best chance of being contiguous, avoiding those pesky disk-head movements.  Additionally, DROP INDEX and CREATE INDEX are atomic, so this means the table isn’t available for use either while locks are held, not just the index.  It also means that to have all these actions be atomic in aggregate, they’ll need to be wrapped in a TRANSACTION.  These are also logged operations, which means your transaction log with nom-nom-nom on the hunks of spinning rust in your server.  It’s great when we have large windows of downtime, but that’s it.

The second option is to do an ALTER INDEX … REBUILD.  This dynamically rebuilds the index, and means we don’t have to worry about PRIMARY KEY or UNIQUE constraints as we would with dropping and recreating the indexes.  The operation is atomic, so we don’t have to concern ourselves with wrapping it in a transaction either.  Granted, like dropping and recreating the index, locks are created as well — a shared lock for non-clustered indexes, and an exclusive lock for clustered indexes.  Again, this is also a logged operation.  Enterprise Edition users have the luxury of being able to perform this operation and keep the table online.

Last, we have the lowly ALTER INDEX … REORGANIZE.  This defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes.  It also compacts pages, and removes any empty pages that are created as a result.

Now, standard medical procedure Microsoft best practices recommend the following as a baseline:

• If fragmentation is below 5 percent, don’t bother.
• If fragmentation is between 5 and 30 percent, defragment.
• If fragmentation is greater than 30 percent, rebuild.

Your mileage may vary depending on your application.  How?  We’ll save that for another day.  Right now, I’d prefer to prescribe some medication so we can get some immediate results and we can look at the index fragmentation in more detail later.

Use a Gun.  It That Doesn’t Work, Use More Gun

Right now we’re looking for a general cure so we can act quickly.  Well, step right up, because I have the magic stored procedure that’ll automagically rebuild or reorganize your indexes, online if supported, put hair on your chest and win you the girl of your dreams!

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexMaintenance90]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[IndexMaintenance90]
GO

CREATE PROCEDURE [dbo].[IndexMaintenance90]
	@DefragThreshold							TINYINT = 5
,	@RebuildThreshold							TINYINT = 30
AS
BEGIN
	SET NOCOUNT ON;

	-- Version check.
	IF CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 2) AS FLOAT) < 9
		BEGIN
			PRINT 'Versions of SQL Server prior to SQL Server 2005 are not supported.';
			RETURN 0;
		END

	DECLARE @Indexes							TABLE
	(
		[IndexID]								INTEGER	IDENTITY(1, 1)	NOT NULL	PRIMARY KEY CLUSTERED
	,	[SchemaName]							SYSNAME					NOT NULL
	,	[ObjectName]							SYSNAME					NOT NULL
	,	[IndexName]								SYSNAME					NOT NULL
	,	[IndexType]								TINYINT					NOT NULL
	,	[FragmentationPercent]					FLOAT					NOT NULL
	);

	DECLARE @SQLString							NVARCHAR(2048);

	DECLARE	@SchemaName							SYSNAME;
	DECLARE	@ObjectName							SYSNAME;
	DECLARE	@IndexName							SYSNAME;
	DECLARE	@IndexType							TINYINT;
	DECLARE	@FragmentationPercent				FLOAT;
	DECLARE @i									INTEGER;

	INSERT INTO
		@Indexes
	SELECT
		[s].[name]								AS SchemaName
	,	[o].[name]								AS ObjectName
	,	[i].[name]								AS IndexName
	,	[i].[type]								AS IndexType
	,	[ips].[avg_fragmentation_in_percent]	AS FragmentationPercent
	FROM
		[sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'LIMITED')	ips
	INNER JOIN
		[sys].[objects]	o
		ON
			[ips].[object_id] = [o].[object_id]
	INNER JOIN
		[sys].[schemas]	s
		ON
			[o].[schema_id] = [s].[schema_id]
	INNER JOIN
		[sys].[indexes]	i
		ON
			[ips].[object_id] = [i].[object_id]
		AND	[ips].[index_id] = [i].[index_id]
	WHERE
		[i].[type] > 0
	AND	[i].[is_disabled] = 0
	AND [ips].fragment_count IS NOT NULL
	AND	[ips].[avg_fragmentation_in_percent] > ISNULL(@DefragThreshold, 0);

	SELECT @i = MIN([i].[IndexID]) FROM @Indexes i;

	SELECT
		@SchemaName				= [i].[SchemaName]
	,	@ObjectName				= [i].[ObjectName]
	,	@IndexName				= [i].[IndexName]
	,	@IndexType				= [i].[IndexType]
	,	@FragmentationPercent	= [i].[FragmentationPercent]
	FROM
		@Indexes	i
	WHERE
		[i].[IndexID] = @i;

	WHILE @@ROWCOUNT > 0
	BEGIN
		SET @SQLString = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName);

		IF @FragmentationPercent >= ISNULL(@RebuildThreshold, 0)
			BEGIN
				-- Enterprise Editions support online rebuilding.  However, XML indexes can't be rebuilt online.
				IF (SERVERPROPERTY('EngineEdition') = 3) AND @IndexType <> 3
					SET @SQLString = @SQLString + 'REBUILD WITH (ONLINE = ON);'
				ELSE
					SET @SQLString = @SQLString + 'REBUILD;'
			END
		ELSE
			BEGIN
				SET @SQLString = @SQLString + ' REORGANIZE;';
				-- XML Indexes do not have statistics to update.
				IF @IndexType <> 3
					SET @SQLString = @SQLString + CHAR(10) + 'UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ' ' + QUOTENAME(@IndexName) + ';';
			END	

		PRINT @SQLString;
		EXECUTE sp_executesql @SQLString;

		SELECT @i = MIN([i].[IndexID]) FROM @Indexes i WHERE [i].[IndexID] > @i;

		SELECT
			@SchemaName				= [i].[SchemaName]
		,	@ObjectName				= [i].[ObjectName]
		,	@IndexName				= [i].[IndexName]
		,	@IndexType				= [i].[IndexType]
		,	@FragmentationPercent	= [i].[FragmentationPercent]
		FROM
			@Indexes	i
		WHERE
			[i].[IndexID] = @i;
	END

	PRINT CHAR(10) + 'Maintenance complete.';
	RETURN 0;
END
GO

IndexMaintenance90
  [@DefragThreshhold = ] defrag_threshold,
  [@RebuildThreshhold = ] rebuild_threshold

[@DefragThreshold = ] defrag_threshold
TINYINT.  The minimum percentage of fragmentation in which an index should be defragmented, with a default of 5.

[@RebuildThreshold = ] rebuild_threshold
TINYINT.  The minimum percentage of fragmentation in which the index will be rebuilt instead of defragmented, with a default of 30.

This mamma jamma will determine the level of fragmentation for all indexes in the current database, and rebuild, defrag or do nothing as appropriate, keeping your maintenance windows to a minimum.  If your server is running the Enterprise Edition of the engine, it’ll perform an online rebuild.  Indexes that are reorganized also have their statistics updated.  Currently this doesn’t support partitioned indexes, but it shall in a future version.

Just create it and go — either fire it off manually, or create a SQL Agent Job to run this as part of your regular maintenance.

Download: (SQL Server 2005/2008) IndexMaintenance90.sql