Defragging SQL Indexes

Similar to hard drives, each time data in a SQL index is added, deleted, or updated, the index has the potential to become fragmented. The ultimate result is that SQL queries will take a less than optimal time to execute. Just as defragging a hard drive can improve performance, defragging SQL Server indexes can improve performance significantly.

Analyzing Index Fragmentation

Defragging SQL table indexes comes at a cost. Before we hit go scheduling index maintenance scripts, its best to find out if your indexes are defragmented (and to what level). Fortunately, SQL Server provides us with a relatively easy way to determine the fragmentation level of its indexes.

The T-SQL script below will output each indexes, its table, and its fragmentation percent. Obviously, a lower fragmentation percent is preferred. Less than obvious, is that a index of zero means there are currently no indexes associated with the specified table (not always a bad thing, but something you may want to look at).

Determine Index Fragmentation

SELECT
	object_name(ps.object_id) as TableName,
	ps.index_id as IndexId,
	i.name as IndexName,
	ps.index_type_desc as IndexType,
	ps.avg_fragmentation_in_percent as FragPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
	INNER JOIN sys.indexes AS i ON ps.OBJECT_ID = i.OBJECT_ID AND ps.index_id = i.index_id
WHERE
	ps.database_id = DB_ID() -- current database only

Defragging Database Indexes

If after analyzing your indexes, it makes sense to (or you just want to) defrag your table indexes, simply use one of the scripts below to rebuild or reorganize the desired index. Select which script to run based on the percent fragmentation of your index. Although there is no 100% defined rule, it is generally believed that it is more efficient to rebuild an index (as opposed to simply reorganize it) once fragmentation exceeds 30%. Thus, if your index is fragmented above 30%, rebuild the index. Otherwise, a index reorganization might be faster.

Rebuild an Index

ALTER INDEX [IndexName] ON [TableName] REBUILD WITH(ONLINE = ON)

*WITH(ONLINE = ON) is only supported by enterprise level editions of SQL Server. In order to run on all other editions, simply remove WITH(ONLINE = ON) or use WITH(ONLINE = OFF)

Re-organize an Index

ALTER INDEX [IndexName] ON [TableName] REORGANIZE

Automatically Defragging All Indexes for a Database

So the next logical step in looking at index fragmentation and manually generating SQL to defrag indexes is to automate the process. For those interested, the SQL script below will to just that. However, the script below comes with a little bit of common sense warnings:

Automatically Defrag All Indexes for Current Database

DECLARE @IndexStats AS TABLE  (
			ObjectId int, 
			TableName nvarchar(max), 
			IndexId int, 
			IndexName nvarchar(max), 
			IndexType nvarchar(max), 
			PreAvgPercentFrag float, 
			PostAvgPercentFrag float)

-- Get index stats before optimization
INSERT INTO @IndexStats (ObjectId, TableName, IndexId, IndexName, IndexType, PreAvgPercentFrag)
SELECT
	ps.object_id,
	object_name(ps.object_id) as TableName,
	ps.index_id,
	i.name,
	ps.index_type_desc,
	ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
	INNER JOIN sys.indexes AS i ON ps.OBJECT_ID = i.OBJECT_ID AND ps.index_id = i.index_id
WHERE
	ps.database_id = DB_ID() -- current database only
	
-- Loop through indexes and 
DECLARE @tableName as nvarchar(max)
DECLARE @indexName as nvarchar(max)
DECLARE @avgPercFrag as float
DECLARE @dynSql as nvarchar(max)

-- Get Cursor to iterate through stats table, defragging table indexes
DECLARE IndexStatsCursor CURSOR FAST_FORWARD 
	FOR SELECT TableName, IndexName, PreAvgPercentFrag FROM @IndexStats WHERE PreAvgPercentFrag > 0

OPEN IndexStatsCursor
FETCH IndexStatsCursor INTO @tableName, @indexName, @avgPercFrag

WHILE @@Fetch_Status = 0
BEGIN
	FETCH IndexStatsCursor INTO @tableName, @indexName, @avgPercFrag
	SET @dynSql = 'ALTER INDEX ' + @indexName + ' ON ' + @tableName
	
	-- Full Rebuild or Re-Organize based on current fragmentation
	IF @avgPercFrag > 30
		SET @dynSql = @dynSql + ' REBUILD WITH(ONLINE = ON)'
	ELSE
		SET @dynSql = @dynSql + ' REORGANIZE'
	
	-- Perform Rebuild/ReOrganize
	EXEC (@dynSql)
END

CLOSE IndexStatsCursor
DEALLOCATE IndexStatsCursor

-- Get index stats after optimization
UPDATE @IndexStats
SET PostAvgPercentFrag = ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps
WHERE 
	ObjectId = ps.object_id
	AND ps.index_type_desc = IndexType
	AND ps.index_id = IndexId

-- Display Pre and Post Stats
SELECT 
	ObjectId,
	TableName, 
	IndexId, 
	IndexName,
	IndexType, 
	PreAvgPercentFrag, 
	PostAvgPercentFrag
FROM @IndexStats
ORDER BY
	TableName,
	IndexId

*WITH(ONLINE = ON) is only supported by enterprise level editions of SQL Server. In order to run on all other editions, simply remove WITH(ONLINE = ON) or use WITH(ONLINE = OFF)