Reindex with Before and After Comparison

A maintenance workhorse: this stored procedure takes a snapshot of index fragmentation (via the GetIndexes procedure), defrags or rebuilds every index below 95% scan density, takes a second snapshot, and reports before/after densities with the time each operation took. Set @Online = 1 for online defragmentation (the default) or 0 for offline full reindex; pass @DB to limit it to one database.

Example: EXEC dbo.Reindex -- whole server, online defrag\r\nEXEC dbo.Reindex @DB = 'MyDatabase', @Online = 0 -- one database, offline rebuild

ALTER PROCEDURE [dbo].[Reindex]
(
	@DB VarChar(255) = '',
	@Online Bit = 1 --1: Online Defrag / 0: Offline full Reindex.
) AS
BEGIN
	SET NOCOUNT ON;
	
	EXEC GetIndexes @DatabaseName=@DB, @NoReturn=1

	SELECT
		*,
		0 AS TimeTaken
	INTO
		#BeforeSnapshot
	FROM
		##ServerIndexes AS SI
	WHERE
		IsNull(SI.IndexName, '') <> ''
		AND SI.ScanDensity < 95

	DECLARE @ServerName		VarChar(255)
	DECLARE @DatabaseName	VarChar(255)
	DECLARE @ObjectName		VarChar(255)
	DECLARE @IndexName		VarChar(255)
	DECLARE @ScanDensity	Decimal(5, 2)
	DECLARE @RebuildSQL		VarChar(8000)
	DECLARE @DefragSQL		VarChar(8000)
	DECLARE @BeforeTime		DateTime

	DECLARE DefragItems CURSOR FAST_FORWARD FOR
		SELECT
			tBS.DatabaseName,
			tBS.ObjectName,
			tBS.IndexName,
			tBS.ScanDensity,
			tBS.RebuildSQL,
			tBS.DefragSQL
		FROM
			#BeforeSnapshot AS tBS
		ORDER BY
			tBS.DatabaseName,
			tBS.ObjectName,
			tBS.IndexName

	OPEN DefragItems

	FETCH FROM DefragItems INTO @DatabaseName, @ObjectName, @IndexName, @ScanDensity, @RebuildSQL, @DefragSQL

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		--------------------------------------------------------------------------------------------------------

		SET @BeforeTime = GetDate()

		IF @Online = 1
		BEGIN--IF
			IF RTrim(LTrim(@IndexName)) <> ''
			BEGIN--IF
				PRINT('Defrag: ' + @DatabaseName + '.' + @ObjectName + '[' + @IndexName + ']: ' + Convert(Varchar, @ScanDensity) + '%')
				EXEC(@DefragSQL)
			END--IF
		END ELSE BEGIN --ELSE
			PRINT('Reindex: ' + @DatabaseName + '.' + @ObjectName + '[' + @IndexName + ']: ' + Convert(Varchar, @ScanDensity) + '%')
			EXEC(@RebuildSQL)
		END--IF

		UPDATE
			#BeforeSnapshot
		SET
			TimeTaken = DateDiff(ms, @BeforeTime, GetDate())
		WHERE
			DatabaseName = @DatabaseName
			AND ObjectName = @ObjectName
			AND IndexName = @IndexName

		--------------------------------------------------------------------------------------------------------
		FETCH FROM DefragItems INTO @DatabaseName, @ObjectName, @IndexName, @ScanDensity, @RebuildSQL, @DefragSQL
	END

	CLOSE DefragItems DEALLOCATE DefragItems

	EXEC GetIndexes @DatabaseName=@DB, @NoReturn=1

	SELECT
		*
	INTO
		#AfterSnapshot
	FROM
		##ServerIndexes AS SI
	WHERE
		IsNull(SI.IndexName, '') <> ''

	SELECT
		A.DatabaseName,
		A.ObjectName,
		A.IndexName,
		Convert(VarChar, B.ScanDensity) + '%' AS BeforeScanDensity,
		Convert(VarChar, A.ScanDensity) + '%' AS AfterScanDensity,
		(A.ScanDensity - B.ScanDensity) AS ScanDensityChange,
		Convert(VarChar, B.TimeTaken) + 'ms' AS TimeTaken
	FROM
		#AfterSnapshot AS A
	INNER JOIN #BeforeSnapshot AS B
		ON B.DatabaseName = A.DatabaseName
		AND B.ObjectName = A.ObjectName
		AND B.IndexName = A.IndexName
	ORDER BY
		A.DatabaseName,
		A.ObjectName,
		A.IndexName

	DROP TABLE #BeforeSnapshot
	DROP TABLE #AfterSnapshot
END
GO