Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cleveland DBA

Colleen Morrow is a Database Engineer for a software company in Westlake, Ohio. She has worked in the IT industry for 15+ years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

RYO Maintenance Plan – Index Maintenance

It’s been a couple of weeks since my last post, but I swear I have a good excuse. I’ve been busily working on a separate writing project, but I’m back now and ready to continue rolling my own maintenance plan. To recap, so far I’ve covered backups, backup cleanup, and updating statistics. Today I’m going to tackle index maintenance.

I know I mentioned Ola Hallengren’s solution when I started writing this series. I want to mention it again since it’s especially pertinent for the topic of index maintenance. Ola has put a lot of work into making a very robust solution, and I would recommend it to anyone. The solution I’m about to offer isn’t nearly as robust. So why aren’t I using Ola’s solution, rather than writing my own? Two reasons. First, if you’ll recall, Ola’s backup procedure didn’t meet my requirement of handling multiple backup locations, which started me down this road to begin with. Second, I’m a bit OCD when it comes to things like this. It would drive me nuts to have half my maintenance one way and half another. So now that I’ve started, I have to finish. And, well, my index maintenance routine doesn’t need to be that robust. So maybe that’s three reasons.

In fact, my solution is pretty basic. As with the rest of my procedures, I wanted to be able to run this procedure against one or more databases. And my only other requirement was the ability to specify whether or not index rebuilds should be performed online whenever possible. I use the 30% fragmentation threshold for determining whether to rebuild or reorganize, so I didn’t need to add that as a parameter. But you could certainly make that a parameter if your environment dictates it.

Online index operations

When index operations, like rebuilds, are performed offline, SQL Server obtains exclusive locks on source data and related indexes. Users are prevented from modifying, even querying underlying data. With online operations, access to the source table and indexes continues undisturbed. This ability is critical to systems that must be available 24 hours a day. It does come with a cost, however. Typically, online index operations will take longer to complete than offline operations. This performance hit is especially pronounced in busy systems. While SQL Server is rebuilding the index, it’s actually maintaining two copies of that index, which means twice as much work to handle insert, update, or delete operations. So, even though you can perform index maintenance while users are accessing the system, it’s still recommended that you pick a time when there will be less activity going on.

Finding fragmented indexes

To determine how fragmented an index is, we use the sys.dm_db_index_physical_stats DMV. This particular function returns information on the size and fragmentation of the specified index. Depending on the parameters passed in, you can also see information like record counts, record size, forwarded record count, compressed page count, etc. For our purposes, we’ll use the LIMITED mode, which will show us the percent fragmentation. We join this function with sys.objects, sys.indexes and sys.schemas and insert everything we need into a temporary work table. Note that we’re only concerned with indexes more than five percent fragmented, and with more than 1000 pages.

			INSERT INTO #work_to_do
			SELECT
				' + cast(@dbid as varchar(2000)) + ' as dbid,
				''' + @dbname + ''',
				sch.name,
				s.object_id AS objectid,
				o.name,
				s.index_id AS indexid,
				i.name,
				i.type,
				s.partition_number AS partitionnum,
				s.avg_fragmentation_in_percent AS frag,
				i.fill_factor
			FROM sys.dm_db_index_physical_stats (' + cast(@dbid as varchar(2000)) + ', NULL, NULL , NULL, ''LIMITED'') s
			join sys.objects o (NOLOCK) on o.object_id = s.object_id
			join sys.indexes i (NOLOCK) on i.object_id = s.object_id and i.index_id = s.index_id
			JOIN sys.schemas sch (NOLOCK) ON sch.schema_id = o.schema_id
			WHERE s.avg_fragmentation_in_percent > 5.0 AND s.index_id > 0
			and s.page_count > 1000;  

Once we’ve compiled a list of indexes to rebuild/reorg, we can start stepping through them and performing the operation. Now, you may have noticed that I said “whenever possible” earlier, with respect to online operations. That’s because online operations are not allowed on indexes containing XML, image, text, or ntext data. Nor are they allowed on varchar(max), nvarchar(max) or varbinary(max) datatypes. And, because clustered indexes contain all the table columns, online operations are not allowed on clustered indexes if the underlying table contains any of these datatypes.

To handle this possibility, I decided to use a temporary table called #online_op, containing a single value, is_online, which is seeded with a “ON” value. Once I’m in my loop building my ALTER INDEX statements, I need to determine whether or not the current index can be rebuilt online or not. If we’re executing this procedure with the @online parameter set to ‘N’, obviously our decision is pretty simple, so we update the #online_op table to ‘OFF’.

 IF @online = 'N'
						UPDATE #online_op set is_online = 'OFF'
					ELSE 

Otherwise, we start off optimistic and set is_online to ‘YES’ and then start checking our datatypes. If the index is clustered, we know that none of the columns in the table can contain the “forbidden” datatypes or varchar|nvarchar|varbinary(max) columns.

 -- Start by assuming an opline operation
						UPDATE #online_op set is_online = 'ON'

						--Clustered check all columns for forbidden datatypes
						IF @indextype = 1
						BEGIN
							SET @sqlstmt =
							'UPDATE #online_op SET is_online = ''OFF'' WHERE EXISTS(SELECT 1 FROM '+quotename(@dbname, '[')+'.sys.columns c
							JOIN '+quotename(@dbname, '[')+'.sys.types t ON c.system_type_id = t.user_type_id OR (c.user_type_id = t.user_type_id AND t.is_assembly_type = 1)
							WHERE c.[object_id] = '+ cast(@objectid as varchar(50)) +'
								AND (t.name IN(''xml'',''image'',''text'',''ntext'')
								OR (t.name IN(''varchar'',''nvarchar'',''varbinary'') AND (c.max_length = t.max_length or c.max_length = -1))
								OR (t.is_assembly_type = 1 AND c.max_length = -1)))'
							EXEC (@sqlstmt)
						END 

If the column is non-clustered, we can confine our check to the indexed columns only.

 --NonClustered check just the index columns for forbidden datatypes
						IF @indextype = 2
						BEGIN
							SET @sqlstmt =
							'UPDATE #online_op SET is_online = ''OFF'' WHERE EXISTS(SELECT 1 FROM '+quotename(@dbname, '[')+'.sys.indexes i
							join '+quotename(@dbname, '[')+'.sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
							join '+quotename(@dbname, '[')+'.sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
							join '+quotename(@dbname, '[')+'.sys.types t on c.user_type_id = t.user_type_id
							where (
									t.name in (''text'', ''ntext'', ''image'', ''xml'')
									or (t.name in (''varchar'', ''nvarchar'', ''varbinary'') and c.max_length = t.max_length or c.max_length = -1)
									or (t.is_assembly_type = 1 AND c.max_length = -1)
								  )
							and i.object_id = '+ cast(@objectid as varchar(50)) +'
							and i.index_id = '+ cast(@indexid as varchar(50)) +')'
							EXEC(@sqlstmt)
						END 

After all of that, if the index operation can be performed online, the value in #online_op should be "ON", otherwise it will be "OFF" and this value is used when building the ALTER INDEX statement. And that's as complicated as it gets in this procedure. The rest of the code is very straightforward, so I'm not going to step through it.

The procedure

 USE [master]
GO
/****** Object:  StoredProcedure [maint].[sp_index_maint]    Script Date: 02/27/2012 08:22:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [maint].[sp_index_maint] (@dbs VARCHAR(128) = '%', @online char(1) = 'Y')
AS
BEGIN
	DECLARE

	  @dbid int
	, @dbname varchar(128)
	, @objectid int
	, @indexid int
	, @partitioncount bigint
	, @schemaname sysname
	, @objectname sysname
	, @indexname sysname
	, @indextype tinyint
	, @partitionnum bigint
	, @partitions bigint
	, @frag float
	, @fillfactor int
	, @sqlstmt varchar(max)

	SET NOCOUNT ON

	BEGIN TRY

		IF @online NOT IN ('Y', 'N')
			RAISERROR (
				 N'Invalid value for parameter @online.  Specify Y to perform online index operations where possible, N for offline operations.'
				,16
				,1
				);

		IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL
			DROP TABLE #work_to_do

		CREATE TABLE [#work_to_do](
			[dbid] [int] NULL,
			[dbname] [varchar](250),
			[schemaname] [varchar](250),
			[objectid] [int] NULL,
			[objectname] [varchar](250),
			[indexid] [int] NULL,
			[indexname] [varchar](250),
			[indextype] [tinyint],
			[partitionnum] [int] NULL,
			[frag] [float] NULL,
			[fillfactor] [int]
		)

		IF OBJECT_ID('tempdb..#online_op') IS NOT NULL
			DROP TABLE #online_op

		CREATE TABLE [#online_op] (
			is_online varchar(3)
		)
		INSERT INTO #online_op VALUES ('ON');

		DECLARE dbnames CURSOR FOR
			SELECT d.database_id, d.name from sys.databases d
			WHERE d.name <> 'tempdb'
				AND d.state = 0
				AND d.name LIKE @dbs

		OPEN dbnames
		FETCH NEXT FROM dbnames INTO @dbid, @dbname

		WHILE @@FETCH_STATUS = 0
		BEGIN

			PRINT convert(varchar, getdate(),120)+'  Beginning index maintenance for database: '+@dbname

			SET @sqlstmt =  'USE ' + quotename(@dbname,'[') +';
			INSERT INTO #work_to_do
			SELECT
				' + cast(@dbid as varchar(2000)) + ' as dbid,
				''' + @dbname + ''',
				sch.name,
				s.object_id AS objectid,
				o.name,
				s.index_id AS indexid,
				i.name,
				i.type,
				s.partition_number AS partitionnum,
				s.avg_fragmentation_in_percent AS frag,
				i.fill_factor
			FROM sys.dm_db_index_physical_stats (' + cast(@dbid as varchar(2000)) + ', NULL, NULL , NULL, ''LIMITED'') s
			join sys.objects o (NOLOCK) on o.object_id = s.object_id
			join sys.indexes i (NOLOCK) on i.object_id = s.object_id and i.index_id = s.index_id
			JOIN sys.schemas sch (NOLOCK) ON sch.schema_id = o.schema_id
			WHERE s.avg_fragmentation_in_percent > 5.0 AND s.index_id > 0
			and s.page_count > 1000;
			'

			EXEC (@sqlstmt);

			DECLARE partitions CURSOR FOR
			SELECT * FROM #work_to_do order by frag desc  --let's start with the worst ones first

			-- Open the cursor.
			OPEN partitions;
			FETCH NEXT FROM partitions
			   INTO @dbid, @dbname, @schemaname, @objectid, @objectname, @indexid, @indexname, @indextype, @partitionnum, @frag, @fillfactor;

			WHILE @@FETCH_STATUS = 0
				BEGIN;

					-- if we're opting to perform offline operations there's no point checking for "forbidden" datatypes
					IF @online = 'N'
						UPDATE #online_op set is_online = 'OFF'
					ELSE
					BEGIN
						-- Start by assuming an opline operation
						UPDATE #online_op set is_online = 'ON'

						--Clustered check all columns for forbidden datatypes
						IF @indextype = 1
						BEGIN
							SET @sqlstmt =
							'UPDATE #online_op SET is_online = ''OFF'' WHERE EXISTS(SELECT 1 FROM '+quotename(@dbname, '[')+'.sys.columns c
							JOIN '+quotename(@dbname, '[')+'.sys.types t ON c.system_type_id = t.user_type_id OR (c.user_type_id = t.user_type_id AND t.is_assembly_type = 1)
							WHERE c.[object_id] = '+ cast(@objectid as varchar(50)) +'
								AND (t.name IN(''xml'',''image'',''text'',''ntext'')
								OR (t.name IN(''varchar'',''nvarchar'',''varbinary'') AND (c.max_length = t.max_length or c.max_length = -1))
								OR (t.is_assembly_type = 1 AND c.max_length = -1)))'
							EXEC (@sqlstmt)
						END

						--NonClustered check just the index columns for forbidden datatypes
						IF @indextype = 2
						BEGIN
							SET @sqlstmt =
							'UPDATE #online_op SET is_online = ''OFF'' WHERE EXISTS(SELECT 1 FROM '+quotename(@dbname, '[')+'.sys.indexes i
							join '+quotename(@dbname, '[')+'.sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
							join '+quotename(@dbname, '[')+'.sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
							join '+quotename(@dbname, '[')+'.sys.types t on c.user_type_id = t.user_type_id
							where (
									t.name in (''text'', ''ntext'', ''image'', ''xml'')
									or (t.name in (''varchar'', ''nvarchar'', ''varbinary'') and c.max_length = t.max_length or c.max_length = -1)
									or (t.is_assembly_type = 1 AND c.max_length = -1)
								  )
							and i.object_id = '+ cast(@objectid as varchar(50)) +'
							and i.index_id = '+ cast(@indexid as varchar(50)) +')'
							EXEC(@sqlstmt)
						END
					END

					SELECT @partitioncount = count (*)
					FROM sys.partitions (NOLOCK)
					WHERE object_id = @objectid AND index_id = @indexid;

					IF @fillfactor = 0
						SET @fillfactor = 90

					-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
					IF @frag < 30.0
					BEGIN

						SET @sqlstmt = 'ALTER INDEX ' + quotename(@indexname, '[') + ' ON '
							+quotename(@dbname,'[')+'.'+ quotename(@schemaname, '[') + '.' + quotename(@objectname, '[') + ' REORGANIZE; ';

						IF @partitioncount > 1
							SET @sqlstmt = @sqlstmt + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

						-- No stats on XML or spatial data
						IF @indextype in (1,2)
							SET @sqlstmt = @sqlstmt +' UPDATE STATISTICS ' +quotename(@dbname,'[')+'.'+ quotename(@schemaname, '[') + '.' + quotename(@objectname, '[')
								+ ' (' +quotename(@indexname, '[') + ') WITH FULLSCAN '

						SET @sqlstmt = @sqlstmt + ';'

					END

					IF @frag >= 30.0
					BEGIN

						SET @sqlstmt = 'ALTER INDEX ' + quotename(@indexname, '[') +' ON '
							+quotename(@dbname,'[')+'.'+ quotename(@schemaname, '[') + '.' + quotename(@objectname, '[') + ' REBUILD WITH (FILLFACTOR = '+ cast(@fillfactor as varchar)

						SELECT @sqlstmt = @sqlstmt + ', ONLINE='+ ISNULL(is_online, 'YES') from #online_op

						SET @sqlstmt = @sqlstmt+') ';

						IF @partitioncount > 1
							SET @sqlstmt = @sqlstmt + ' PARTITION=' + CONVERT (CHAR, @partitionnum)

						SET @sqlstmt = @sqlstmt + '; '

					END

					--PRINT @sqlstmt;
					EXEC (@sqlstmt);

					FETCH NEXT FROM partitions
					   INTO @dbid, @dbname, @schemaname, @objectid, @objectname, @indexid, @indexname, @indextype, @partitionnum, @frag, @fillfactor;

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

			TRUNCATE TABLE #work_to_do

			FETCH NEXT FROM dbnames INTO @dbid, @dbname

		END
		CLOSE dbnames
		DEALLOCATE dbnames

		drop table #work_to_do
		drop table #online_op

		PRINT convert(varchar, getdate(),120)+'  Complete'

	END TRY
	BEGIN CATCH

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();

		-- Use RAISERROR inside the CATCH block to return
		-- error information about the original error that
		-- caused execution to jump to the CATCH block.
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );

	END CATCH

END  

Still to come… integrity checks! Ooh! Aah!

Comments

Leave a comment on the original post [colleenmorrow.com, opens in a new window]

Loading comments...