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!