Yet another way to defrag indexes

,

Lovingly modified from example D in the world famous dm_db_index_physical_stats books online entry.

By putting the script in an query that can be executed, it lets me pass database names to it, without having to create the proc in each DB.  Feel free to play with the defaults, but these seem to work for us.  Because these are very critical online systems, I have decided to keep the ONLINE=ON setting by default.  But because there are tables that may have column types that can't be built online, such as text or XML, then a TRY/CATCH block switches it to OFF.

CREATE PROCEDURE [dbo].[usp_defrag_indexes]
	@dbname sysname, @fillfactor int = 80, @fragthreshold int = 30
AS
	declare @query nvarchar(max)
	if (DB_ID(@dbname)) is NULL
	begin
		print 'Invalid database name.'
		return 0
	end
	if @dbname = 'tempdb'
	begin
		print 'TEMPDB cannot be indexed reliably.'
		return 0
	end
	if (select COUNT(*) from master.dbo.sysdatabases where name = @dbname and cmptlevel < 90) > 0
	begin
		print 'Only SQL 2005 databases and up allowed.'
		return 0
	end
	

	print '----- Beginning defragging of: ' + quotename(@dbname) + ' at '
		+ CONVERT(varchar(19), getdate(), 121);
	if (@fillfactor < 0 or @fillfactor > 100)		-- defaults to common 80% fillfactor
		set @fillfactor = 80
	if (@fragthreshold < 0 or @fragthreshold > 100)	-- prevents silliness
		set @fragthreshold = 30
	set @query =
	'USE ' + QUOTENAME(@dbname) + ';
	SET NOCOUNT ON;
	DECLARE @objectid int;
	DECLARE @indexid int;
	DECLARE @partitioncount bigint;
	DECLARE @schemaname nvarchar(130); 
	DECLARE @objectname nvarchar(130); 
	DECLARE @indexname nvarchar(130); 
	DECLARE @partitionnum bigint;
	DECLARE @partitions bigint;
	DECLARE @frag float;
	DECLARE @command nvarchar(4000); 
	DECLARE @lock_esc int;
	
	-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
	-- and convert object and index IDs to names.

	SELECT
		A.object_id AS objectid,
		A.index_id AS indexid,
		A.partition_number AS partitionnum,
		A.avg_fragmentation_in_percent AS frag,
		B.lock_escalation AS lock_escalation
	INTO #work_to_do
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') as A
		inner join sys.tables as B on B.object_id = A.object_id
	WHERE A.avg_fragmentation_in_percent >= 10.0 AND A.index_id > 0 and A.page_count > 500

	-- Declare the cursor for the list of partitions to be processed.
	DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

	-- Open the cursor.
	OPEN partitions;

	-- Loop through the partitions.
	WHILE (1=1)
		BEGIN;
			FETCH NEXT
			   FROM partitions
			   INTO @objectid, @indexid, @partitionnum, @frag, @lock_esc;
			IF @@FETCH_STATUS < 0 BREAK;
			SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
			FROM sys.objects AS o
			JOIN sys.schemas as s ON s.schema_id = o.schema_id
			WHERE o.object_id = @objectid;
			SELECT @indexname = QUOTENAME(name)
			FROM sys.indexes
			WHERE  object_id = @objectid AND index_id = @indexid;
			SELECT @partitioncount = count (*)
			FROM sys.partitions
			WHERE object_id = @objectid AND index_id = @indexid;

			IF @partitioncount > 1 and @frag < ' +cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REORGANIZE PARTITION='' + CAST(@partitionnum AS nvarchar(10));
			ELSE
			IF @partitioncount > 1 and @frag >= ' + cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REBUILD PARTITION='' + CAST(@partitionnum AS nvarchar(10));
			ELSE 
			IF @frag < ' +cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REORGANIZE'';
			ELSE				
			IF @frag >= ' +cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REBUILD WITH (ONLINE=ON, FILLFACTOR = ' + cast(@fillfactor as varchar(3)) + ')'';
				
			IF CAST(SERVERPROPERTY(''Edition'') AS VARCHAR(MAX)) LIKE ''%Standard%''
				SET @command = REPLACE(@command, ''ONLINE=ON'', ''ONLINE=OFF'')

			IF @lock_esc <> 0 -- if lock escalation is disabled, a reorganize is not possible, so this fixes it 
				SET @command = REPLACE(@command, ''REORGANIZE'', ''REBUILD'');
				
			BEGIN TRY
				EXEC (@command);
				PRINT N''Executed: '' + @command;
			END TRY
			BEGIN CATCH -- if the rebuild fails, generally because ONLINE=ON was a problem
				IF @command like ''%line[_]item %''
					SET @command = REPLACE(@command, ''REORGANIZE'', ''REBUILD'')
				SET @command = REPLACE(@command, ''ONLINE=ON'', ''ONLINE=OFF'')
				exec (@command)
				print N''Executed (CATCH with ONLINE = OFF): '' + @command
			END CATCH
		END

	-- Close and deallocate the cursor.
	CLOSE partitions;
	DEALLOCATE partitions;

	-- Drop the temporary table.
	DROP TABLE #work_to_do;'
	-- print (@query)
	exec (@query)

	print '----- Ending defragging of: ' + quotename(@dbname) + ' at '
		+ CONVERT(varchar(19), getdate(), 121);
	print '';
GO

Rate

4 (4)

Share

Share

Rate

4 (4)