Rebuild indexes based on fragmentation – sp_RebuildIndex

In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure is based on sys.dm_db_index_physical_stats and resides in master database. This procedure automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%.

Code for the procedure is as follow:

USE [master]
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_RebuildIndex] @FragmentationLevel [int] = 30
	,@SortInTempDB [varchar] (8) = 'OFF'
	,@Online [varchar] (8) = 'OFF'
	,@LOBCompaction [varchar] (8) = 'ON'
	,@ScanMode [varchar] (16) = 'DETAILED'
	,@MinRowsInTable [int] = 1000
	--Stored Procedure: [dbo].[sp_RebuildIndex]
	--Author: Basit A. Farooq
	--Version #: v1.0.0
	--********************
	-- Purpose/Comments
	--********************
	--Stored procedure to rebuild the indexes of database.
	--Indexes will be rebuild based on fragmentation criteria and scan mode.
AS
BEGIN
	SET NOCOUNT ON;

	--***********
	--Parameters
	--***********
	--@FragmentationLevel [int]
	--Specifies index fragementation level search criteria.
	--@SortInTempDB [varchar](8)
	--Specifies whether to store the sort results in tempdb. The default is OFF.
	--@LOBCompaction [varchar](8)
	--Specifies that all pages that contain large object (LOB) data are compacted.
	--The default is ON
	--@Online [varchar](8)
	--Specifies whether underlying tables and associated indexes are available for queries and
	--data modification during the index operation. The default is OFF.
	--@ScanMode [varchar](16) Is the name of the mode.
	--@ScanMode specifies the scan level that is used to obtain statistics.
	--Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED.
	--The default (NULL) is LIMITED.
	--The DETAILED mode scans all pages and returns all statistics.
	--The SAMPLED mode returns statistics based on a 1 percent sample of all the pages
	--in the index or heap. The modes are progressivelyslower from LIMITED to DETAILED,
	--because more work is performed in each mode.
	--@MinRowsInTable [int]
	--Specifies minimum rows in table for search criteria.
	--********
	--Usage
	--********
	--EXEC [<Database_Name>].[dbo].[sp_RebuildIndex]
	--EXEC [<Database_Name>].[dbo].[sp_RebuildIndex] 30, 'OFF', 'OFF', 'ON', 'LIMITED', 10
	DECLARE @StatementID [int]
		,@SQLStatement [varchar] (max)
		,@ErrorText [varchar] (256)
		,@SQLProcedureName [varchar] (64)

	SET @SQLProcedureName = 'sRebuildIndex'

	BEGIN TRY
		--Validating store procedure parameters
		SET @ErrorText = 'Invalid argument was supplied for @FragementationLevel parameter.' + ' Valid inputs for this parameter should be in the between 1 and 100'

		IF @FragmentationLevel NOT BETWEEN 1
				AND 100
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@FragmentationLevel
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @SortInTempDB parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @SortInTempDB != 'ON'
			AND @SortInTempDB != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@SortInTempDB
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @LOBCompaction parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @LOBCompaction != 'ON'
			AND @LOBCompaction != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@LOBCompaction
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @Online parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @Online != 'ON'
			AND @Online != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@Online
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @ScanMode parameter.' + ' Valid inputs for this parameter are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED.'

		IF @ScanMode != 'LIMITED'
			AND @ScanMode != 'NULL'
			AND @ScanMode != 'SAMPLED'
			AND @ScanMode != 'DETAILED'
			AND @ScanMode != 'DEFAULT'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@ScanMode
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @MinRowsInTable parameter.' + ' Valid inputs for this parameter should be in the between 1 and 1000000000000000'

		IF @MinRowsInTable NOT BETWEEN 1
				AND 1000000000000000
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@MinRowsInTable
					)
		END

		--Creating temporary tables.
		IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL
			DROP TABLE #SQLStatementsStore

		CREATE TABLE #SQLStatementsStore (
			[SQLStatementID] [int] IDENTITY(1, 1)
			,[SQLstatement] [varchar](1024)
			)

		IF OBJECT_ID('Tempdb.dbo.#PartitionTables ') IS NOT NULL
			DROP TABLE #PartitionTables

		CREATE TABLE #PartitionTables (
			[ObjectId] [int]
			,[SchemaId] [int]
			,[TableName] [varchar](512)
			)

		IF OBJECT_ID('Tempdb.dbo.#Tables') IS NOT NULL
			DROP TABLE #Tables

		CREATE TABLE #Tables (
			[ObjectId] [int]
			,[SchemaId] [int]
			,[TableName] [varchar](512)
			)

		IF OBJECT_ID('Tempdb.dbo.#Index_Physical_Stats') IS NOT NULL
			DROP TABLE #Index_Physical_Stats

		CREATE TABLE #Index_Physical_Stats (
			[ObjectId] [int] NOT NULL
			,[TableName] [varchar](100) NOT NULL
			,[IndexId] [int] NOT NULL
			,[IndexName] [varchar](500) NOT NULL
			,[IndexPartitionNumber] [int] NOT NULL
			,[IndexType] [varchar](500) NOT NULL
			,[PadIndex] [varchar](256) NOT NULL
			,[IndexFillFactor] [int] NOT NULL
			,[StatisticsNoRecompute] [varchar](256) NOT NULL
			,[IgnoreDuplicateKey] [varchar](256) NOT NULL
			,[AllowRowLocks] [varchar](256) NOT NULL
			,[AllowPageLocks] [varchar](256) NOT NULL
			,[AvgFragmentationInPercent] [float] NOT NULL
			,[CountRows] [int] NOT NULL
			)

		INSERT INTO #PartitionTables (
			[ObjectId]
			,[SchemaId]
			,[TableName]
			)
		SELECT DISTINCT t.[object_id]
			,t.[schema_id]
			,t.[name]
		FROM [sys].[partitions] p
		INNER JOIN [sys].[tables] t ON t.[object_id] = p.[object_id]
			AND p.[partition_number] > 1

		INSERT INTO #Tables (
			[ObjectId]
			,[SchemaId]
			,[TableName]
			)
		SELECT t.[object_id]
			,t.[schema_id]
			,t.[name]
		FROM [sys].[tables] t
		WHERE t.[name] COLLATE Latin1_General_CI_AS NOT IN (
				SELECT [TableName]
				FROM #PartitionTables
				)

		INSERT INTO #Index_Physical_Stats (
			[ObjectId]
			,[TableName]
			,[IndexId]
			,[IndexName]
			,[IndexPartitionNumber]
			,[IndexType]
			,[PadIndex]
			,[IndexFillFactor]
			,[StatisticsNoRecompute]
			,[IgnoreDuplicateKey]
			,[AllowRowLocks]
			,[AllowPageLocks]
			,[AvgFragmentationInPercent]
			,[CountRows]
			)
		SELECT dm.[object_id]
			,DB_NAME(DB_ID()) + '.' + s.[name] + '.' + o.[name]
			,dm.[index_id]
			,i.[name]
			,dm.[partition_number]
			,dm.[index_type_desc]
			,[pad_index] = CASE i.[is_padded]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,i.[fill_factor]
			,[statistics_norecompute] = CASE st.[no_recompute]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[ignore_dup_key] = CASE i.[ignore_dup_key]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[allow_row_locks] = CASE i.[allow_row_locks]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[allow_page_locks] = CASE i.[allow_page_locks]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,dm.[avg_fragmentation_in_percent]
			,p.[rows]
		FROM [sys].[objects] o
		INNER JOIN [sys].[indexes] i ON o.[object_id] = i.[object_id]
			AND i.NAME <> 'NULL'
		INNER JOIN [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, @ScanMode) dm ON i.[object_id] = dm.[object_id]
			AND i.[index_id] = dm.[index_id]
			AND dm.[avg_fragmentation_in_percent] >= 5
		INNER JOIN [sys].[schemas] s ON o.[schema_id] = s.[schema_id]
		INNER JOIN [sys].[stats] st ON i.[name] COLLATE Latin1_General_CI_AS = st.[name] COLLATE Latin1_General_CI_AS
			AND o.[object_id] = st.[object_id]
			AND o.[type] COLLATE Latin1_General_CI_AS = 'U'
		INNER JOIN [sys].[partitions] p ON i.[index_id] = p.[index_id]
			AND o.[object_id] = p.[object_id]
			AND p.[rows] > @MinRowsInTable

		--Changing the fillfactor to 100 where fillfactor is 0
		UPDATE #Index_Physical_Stats
		SET IndexFillFactor = 100
		WHERE IndexFillFactor = 0

		-- Building quries to re-build partiton tables indexes partitions with
		-- fragmentation over 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + pt.[TableName] + ' ' + 'REBUILD PARTITION = ' + CONVERT([varchar](10), ips.[IndexPartitionNumber])
		FROM #PartitionTables pt
		INNER JOIN [sys].[indexes] i ON pt.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON pt.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] > @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON pt.[SchemaId] = s.[schema_id]

		-- Building quries to re-build non-partiton tables indexes with
		-- fragmentation over 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + t.[TableName] + ' ' + 'REBUILD WITH (' + ' PAD_INDEX = ' + CONVERT([varchar](8), ips.[PadIndex]) + ', FILLFACTOR = ' + CONVERT([varchar](8), ips.[IndexFillFactor]) + ', SORT_IN_TEMPDB = ' + CONVERT([varchar](8), @SortInTempDB) + ', STATISTICS_NORECOMPUTE = ' + CONVERT([varchar](8), ips.[StatisticsNoRecompute]) + ', ONLINE = ' + CONVERT([varchar](8), @Online) + ', ALLOW_ROW_LOCKS = ' + CONVERT([varchar](8), ips.[AllowRowLocks]) + ', ALLOW_PAGE_LOCKS = ' + CONVERT([varchar](8), ips.[AllowPageLocks]) + ' )'
		FROM #Tables t
		INNER JOIN [sys].[indexes] i ON t.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON t.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] > @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON t.[SchemaId] = s.[schema_id]

		-- Building quries to re-organise partiton tables indexes partitions with
		-- fragmentation between 5 and 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + pt.[TableName] + ' ' + 'REORGANIZE PARTITION = ' + CONVERT([varchar](8), ips.[IndexPartitionNumber])
		FROM #PartitionTables pt
		INNER JOIN [sys].[indexes] i ON pt.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON pt.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] BETWEEN 5
				AND @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON pt.[SchemaId] = s.[schema_id]

		-- Building quries to re-build non-partiton tables indexes with
		-- fragmentation between 5 and 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + t.[TableName] + ' ' + 'REORGANIZE WITH ( LOB_COMPACTION = ' + @LOBCompaction + ' )' AS 'query'
		FROM #Tables t
		INNER JOIN [sys].[indexes] i ON t.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON t.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] BETWEEN 5
				AND @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON t.[SchemaId] = s.[schema_id]

		SELECT @StatementID = MIN(SQLStatementID)
		FROM #SQLStatementsStore

		WHILE @StatementID IS NOT NULL
		BEGIN
			SELECT @SQLStatement = SQLstatement
			FROM #SQLStatementsStore
			WHERE SQLStatementID = @StatementID

			PRINT 'Executing...[' + @SQLStatement + '].'

			EXEC (@SQLStatement)

			DELETE
			FROM #SQLStatementsStore
			WHERE SQLStatementID = @StatementID

			SELECT @StatementID = MIN(SQLStatementID)
			FROM #SQLStatementsStore
		END

		SELECT [ObjectId]
			,[TableName]
			,[IndexId]
			,[IndexName]
			,[IndexPartitionNumber]
			,[IndexType]
			,[PadIndex]
			,[IndexFillFactor]
			,[StatisticsNoRecompute]
			,[IgnoreDuplicateKey]
			,[AllowRowLocks]
			,[AllowPageLocks]
			,[AvgFragmentationInPercent]
			,[CountRows]
		FROM #Index_Physical_Stats

		IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL
			DROP TABLE #SQLStatementsStore

		IF OBJECT_ID('Tempdb.dbo.#PartitionTables ') IS NOT NULL
			DROP TABLE #PartitionTables

		IF OBJECT_ID('Tempdb.dbo.#Tables') IS NOT NULL
			DROP TABLE #Tables

		IF OBJECT_ID('Tempdb.dbo.#Index_Physical_Stats') IS NOT NULL
			DROP TABLE #Index_Physical_Stats
	END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER()
			,ERROR_SEVERITY()
			,ERROR_STATE()
			,ISNULL(ERROR_PROCEDURE(), @SQLProcedureName)
			,ERROR_LINE()
			,ERROR_MESSAGE()
	END CATCH
END
GO

Mark this procedure as system object by executing the following command so that this procedure can be accessed from any database:

USE [master]
GO

EXEC sys.sp_MS_marksystemobject sp_RebuildIndex;
GO

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know, if you find a bug or want me add any other feature.

7 thoughts on “Rebuild indexes based on fragmentation – sp_RebuildIndex”

  1. I get the following error when I run th Stored Procedure:

    1018 15 1 sRebuildIndex 1 Incorrect syntax near ‘index’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Like

    Reply

  6. it can be very usefull for me but i have an error message says that can’t cast an partition number cause of the non partitioned index

    Can someone help me plz?

    Like

    Reply

Leave a comment