Using SQL Server Stored Procedures to Dynamically Drop and Recreate Indexes

Article
07/30/2016

Recently, I've been working on a project that of necessity involves periodically updating data in some reasonably large tables that exist in an Operational Data Store (ODS). This particular ODS is used for both reporting via SQL Server Reporting Services and staging data for use in a SQL Server Analysis Services database. Since a number of the tables in the ODS are used for reporting purposes, it's not entirely surprising that the report designers have created a few indexes to help report performance. I don't have a problem with indexes, but as any experienced DBA is well aware, the more and larger the indexes the greater the impact on the performance of inserts and updates.

The magnitude of the performance impact was really brought home when a simple update on a 12 million row table that normally completed in roughly three minutes had to be killed at the two hour mark. On further investigation, it was found that over 30 indexes had been added to the table in question. So to address the immediate problem and allow the update to complete in a reasonable time period, DROP INDEX and CREATE INDEX commands were scripted out and added to a stored procedure which would first drop the indexes then run the update statement and finally recreate the indexes. That worked well for a couple of days and then performance again began to degrade. When this episode of performance degradation was investigated, it was found that of the indexes that had been scripted out and added to the stored procedure only one remained and several additional indexes had been created.

Not wishing to revise a rather lengthy stored proc on an almost daily basis, after a brief bit of research, I found a blog posting by Percy Reyes entitled Script out all SQL Server Indexes in a Database using T-SQL. That was great, but only covered NONCLUSTERED indexes and since we were seeing both CLUSTERED and NONCLUSTERED indexes, it would need a bit of revision. Coincidentally, at about the same time there was some serious talk of adding COLUMNSTORE indexes on one or two of these tables, which would essentially cause any update statement to fail. The possibility of having to contend with COLUMNSTORE indexes in addition to CLUSTERED and NONCLUSTERED indexes would necessitate a reasonably significant revision to the T-SQL presented in Percy's blog, especially since it would be necessary to dynamically drop and then recreate indexes. With those bits of information, it was time to formulate a plan, which would mean accomplishing the following:

Capturing and storing the names of tables, their associated indexes and the definitions of those indexes
Dropping the indexes after the definitions had been safely stored
Recreating the indexes from stored definitions using correct syntax

A relatively simple three step task, the first of which was to create a stored proc that would capture the names of the tables, associated indexes and the definitions of those indexes. That lead to creation of the following SQL Server stored procedure:

CREATE PROCEDURE [dbo].[sp_GetIndexDefinitions]
as

IF NOT EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='WORK')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA WORK'
END

IF OBJECT_ID('[WORK].[IDXDEF]','U') IS NOT NULL DROP TABLE [WORK].[IDXDEF]

CREATE TABLE WORK.IDXDEF (SchemaName NVARCHAR(100), TableName NVARCHAR(256), IndexName NVARCHAR(256), IndexDef NVARCHAR(max))

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(100)
DECLARE @is_disabled VARCHAR(100)
DECLARE @IndexOptions VARCHAR(max)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(max)
DECLARE @TSQLScripDisableIndex VARCHAR(max)

DECLARE CursorIndex CURSOR FOR
SELECT schema_name(st.schema_id) [schema_name], st.name, si.name,
CASE WHEN si.is_unique = 1 THEN 'UNIQUE ' ELSE '' END
, si.type_desc,
CASE WHEN si.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END
+ CASE WHEN si.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
+ CASE WHEN si.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
+ CASE WHEN INDEXPROPERTY(st.object_id, si.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
+ CASE WHEN si.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END
+ 'SORT_IN_TEMPDB = OFF'
+ CASE WHEN si.fill_factor>0 THEN ', FILLFACTOR =' + cast(si.fill_factor as VARCHAR(3)) ELSE '' END AS IndexOptions
,si.is_disabled , FILEGROUP_NAME(si.data_space_id) FileGroupName
FROM sys.tables st
INNER JOIN sys.indexes si on st.object_id=si.object_id
WHERE si.type>0 and si.is_primary_key=0 and si.is_unique_constraINT=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
and st.is_ms_shipped=0 and st.name<>'sysdiagrams'
ORDER BY schema_name(st.schema_id), st.name, si.name

open CursorIndex
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName

WHILE (@@fetch_status=0)
BEGIN
DECLARE @IndexColumns VARCHAR(max)
DECLARE @IncludedColumns VARCHAR(max)

SET @IndexColumns=''
SET @IncludedColumns=''

DECLARE CursorIndexColumn CURSOR FOR
SELECT col.name, sic.is_descending_key, sic.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes si on tb.object_id=si.object_id
INNER JOIN sys.index_columns sic on si.object_id=sic.object_id and si.index_id= sic.index_id
INNER JOIN sys.columns col on sic.object_id =col.object_id and sic.column_id=col.column_id
WHERE si.type>0 and (si.is_primary_key=0 or si.is_unique_constraINT=0)
and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and si.name=@IndexName
ORDER BY sic.index_column_id

OPEN CursorIndexColumn
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn

WHILE (@@fetch_status=0)
BEGIN
IF @IsIncludedColumn=0
SET @IndexColumns=@IndexColumns + @ColumnName + CASE WHEN @IsDescendingKey=1 THEN ' DESC, ' ELSE ' ASC, ' END
ELSE
SET @IncludedColumns=@IncludedColumns + @ColumnName +', '

FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
END

CLOSE CursorIndexColumn
DEALLOCATE CursorIndexColumn
SET @IndexColumns = substring(@IndexColumns, 0, len(ltrim(rtrim(@IndexColumns))))
SET @IncludedColumns = CASE WHEN len(@IncludedColumns) >0 THEN substring(@IncludedColumns, 0, len(@IncludedColumns)) ELSE '' END

SET @TSQLScripCreationIndex =''
SET @TSQLScripDisableIndex =''
SET @TSQLScripCreationIndex='CREATE '+ @is_unique + @IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' +QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+
CASE WHEN @IndexTypeDesc = 'NONCLUSTERED COLUMNSTORE' THEN ' ('+@IncludedColumns+') '
WHEN @IndexTypeDesc = 'CLUSTERED COLUMNSTORE' THEN ' '
ELSE ' ('+@IndexColumns+') '
END +
CASE WHEN @IndexTypeDesc = 'NONCLUSTERED COLUMNSTORE' and len(@IncludedColumns)>0 THEN ''
when @IndexTypeDesc = 'CLUSTERED COLUMNSTORE' THEN ''
ELSE
CASE WHEN LEN(@IncludedColumns)>0 THEN CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' ELSE '' END
END +
CASE WHEN @IndexTypeDesc not like ('%COLUMNSTORE%') THEN CHAR(13) + 'WITH (' + @IndexOptions + ') ' + ' ON ' + QUOTENAME(@FileGroupName) ELSE '' END + ';'

INSERT INTO [WORK].[IDXDEF] (Schemaname,TableName,IndexName,IndexDef) values (@SchemaName, @TableName, @IndexName, @TSQLScripCreationIndex)

FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName

END
CLOSE CursorIndex
DEALLOCATE CursorIndex

When that tested out, it was time for the next step of the task and simply dynamically dropping the indexes. But I wanted to ensure that when the indexes were dropped, that the index definitions would be safely stored (I'm like most other DBAs and sort of enjoy being employed). That resulted in creation of the following stored proc:

CREATE PROCEDURE [dbo].[sp_DropIndexes] as

EXEC sp_GetIndexDefinitions

DECLARE @DropIndex NVARCHAR(max)
DECLARE @SchemaName NVARCHAR(256)
DECLARE @TableName NVARCHAR(256)
DECLARE @IndexName NVARCHAR(256)
DECLARE CursorIDXDrop CURSOR FOR
SELECT DISTINCT ss.name AS schemaname, st.name AS tblname, si.name AS indexnname
FROM sys.tables st
INNER JOIN sys.schemas ss ON st.schema_id=ss.schema_id
INNER JOIN sys.indexes si ON st.object_id=si.object_id
WHERE si.type<>0 AND st.is_ms_shipped=0 AND st.name<>'sysdiagrams' AND
(is_primary_key=0 AND is_unique_constraint=0)

OPEN CursorIDXDrop
FETCH NEXT FROM CursorIDXDrop INTO @SchemaName, @TableName, @IndexName
WHILE @@FETCH_STATUS=0
BEGIN
SET @DropIndex= 'DROP INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
EXEC sp_executesql @DropIndex
FETCH NEXT FROM CursorIDXDrop INTO @SchemaName, @TableName, @IndexName
END
CLOSE CursorIDXDrop
DEALLOCATE CursorIDXDrop

After that worked, with the index definitions safely stored so that I could manually re-create them if necessary, it was time to move on to the third step of dynamically re-creating the indexes. That resulted in creation of the following stored proc:

CREATE PROCEDURE [dbo].[sp_RebuildIndexes]
as

IF OBJECT_ID('[WORK].[IDXDEF]','U') IS NOT NULL
BEGIN

DECLARE @Command nvarchar(max)
DECLARE @IndexCmd nvarchar(max)
DECLARE @NumRows int
DECLARE CursorIndexes CURSOR for
SELECT DISTINCT CAST(IndexDef as nvarchar(max)) as IndexDef from work.IDXDEF
SET @NumRows=0
OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes into @Command
WHILE @@FETCH_STATUS=0
BEGIN
EXEC sp_executesql @Command
FETCH NEXT FROM CursorIndexes into @Command
SET @NumRows=@NumRows+1
END;
CLOSE CursorIndexes
DEALLOCATE CursorIndexes
--PRINT LTRIM(RTRIM(CAST(@NumRows as varchar(10)))) + ' Indexes Recreated from stored definitions'
END

By having my update script call the sp_DropIndexes before the update and then call the sp_RebuildIndexes, it was very easy to drop the indexes, run updates and then re-create the indexes in a reasonable time period without the necessity of having to continually revise code.

Using SQL Server Stored Procedures to Dynamically Drop and Recreate Indexes

Additional resources