Reburn Report

Technical Reports from the Front Lines of Software & Systems.

[
[
[

]
]
]

Is your SQL suffering from page fragmentation?

If so, follow these simple steps to fix the problem and improve performance!

There are several ways to check if your SQL Server pages are fragmented, but I like to use the following query to check if there are any pages in the current database context are more than 50% fragmented: 

SELECT
    DB_NAME(indexstats.database_id)
  , OBJECT_NAME(indexstats.object_id)
  , ind.name
  , indexstats.avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
    INNER JOIN sys.indexes ind
        ON ind.object_id = indexstats.object_id
           AND ind.index_id = indexstats.index_id
WHERE
    indexstats.avg_fragmentation_in_percent > 50 AND ind.name IS NOT NULL
ORDER BY
    indexstats.avg_fragmentation_in_percent DESC;

That gives me a good idea of whats going on and I can use it to communicate any areas of concern. Indexes need to be rebuilt or reorganized to fix the fragmentation so I have another script that will script for that:

DECLARE @DatabaseName NVARCHAR(MAX) = 'db-name'
DECLARE @IndexName NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @CurrentIndexName NVARCHAR(MAX)
DECLARE @CurrentTableName NVARCHAR(MAX)
DECLARE @CmdRebuidIndex NVARCHAR(MAX)

DECLARE @tempIndexTable TABLE
(
	RowID int not null primary key identity(1,1),	
	IndexName NVARCHAR(MAX),
	IndexType NVARCHAR(MAX),
	TableName NVARCHAR(MAX),
	AvgFragmentationInPercent FLOAT,
	ObjectTypeDescription NVARCHAR(MAX)		
)

INSERT INTO @tempIndexTable (IndexName, IndexType, TableName, AvgFragmentationInPercent, ObjectTypeDescription) (
	SELECT i.[name],
	s.[index_type_desc], --s.[index_type_desc]
	o.[name],
	s.[avg_fragmentation_in_percent],
	o.type_desc
	FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL) AS s
	INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
	INNER JOIN sys.objects AS o ON i.object_id = o.object_id
	WHERE (s.avg_fragmentation_in_percent > 1 AND s.[index_type_desc] IN ('CLUSTERED INDEX','NONCLUSTERED INDEX'))-- and (i.[Name] like '%IX%' OR i.[Name] like '%PK%'))	
)

PRINT 'Indexes to rebuild:'
SELECT * FROM @tempIndexTable;

RETURN; -- Comment this line when you want to rebuild/reorganize

DECLARE @totalCount INTEGER
SELECT @totalCount = count(1) FROM @tempIndexTable
DECLARE @counter INTEGER = 1

WHILE(@counter <= @totalCount)
BEGIN	

    SET @CurrentIndexName = (SELECT top 1 IndexName FROM @tempIndexTable WHERE RowID = @counter);
	SET @CurrentTableName = (SELECT top 1 TableName FROM @tempIndexTable WHERE RowID = @counter)
	
	PRINT 'Rebuild starting [' + @CurrentIndexName + 
	'] ON [dbo].[' + @CurrentTableName + '] at ' 
	+ convert(varchar, getdate(), 121)

	BEGIN TRY
		SET @CmdRebuidIndex = 'ALTER INDEX [' + @CurrentIndexName + '] ON [dbo].[' + @CurrentTableName + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
																										 -- REORGANIZE  WITH ( LOB_COMPACTION = ON )
																										 -- REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
			EXEC (@CmdRebuidIndex)
			PRINT 'Rebuild executed [' + @CurrentIndexName + '] ON [dbo].[' + @CurrentTableName + '] at ' + convert(varchar, getdate(), 121)
	END TRY
	BEGIN CATCH
		PRINT 'Failed to rebuild [' + @CurrentIndexName + '] ON [dbo].[' + @CurrentTableName + ']'
		PRINT ERROR_MESSAGE()
	END CATCH

	SET @counter += 1;
END

The script will run a dry by default to show which indexes will be impacted. To actually perform the defragmentation, find the return statement and comment it out. There are also a few commented lines near the @CmdRebuildIndex variable to choose between rebuild or reorganize. I need to clean this up for reuse but it works for now.

Leave a comment