Today I ran into an issue with my automated index maintenance. It was trying to rebuild an index that I had recently removed. This prompted me to add a job to update the list of indexes that I have i my database. This job would do three tasks. The first would be to add any new indexes that I had created. It will look at the indexes i am currently maintaining (by referencing dbo.IndexMaintenance) and compare that to a list of indexes from a set of the currently existing indexes in my database. It will add a record into dbo.IndexMaintenance for each new index. After this, It will disable the maintenance for any removed indexes, as well as re-enable the maintenance on any indexes that are currently present, yet have been previously disabled. Please check it out and comment with any suggestions or thoughts.


CREATE PROC dbo.Job_UpdateIndexMaintenance
AS

-- same script we used to populate the IndexMaintenance table to begin with.
-- We will use this to snapshot what we should be maintaining now
SELECT DISTINCT schema_name(st.schema_id) SchemaName, -- schema name
object_name(si.object_id) [TableName], --Table Name
si.name IndexName, -- Index Name
0 OptionStatisticsNorecompute, -- Statistics Norecompute
1 OptionAllowRowLocks, -- Allow Row Locks
1 OptionAllowPageLocks, -- Allow Page Locks
0 OptionSortInTempDB, -- Sort In TempDB
CASE sum(CASE WHEN sc.system_type_id = 35 THEN 1 -- text
WHEN sc.system_type_id = 99 THEN 1 -- ntext
WHEN sc.system_type_id = 34 THEN 1 -- image
WHEN sc.system_type_id = 241 THEN 1 -- xml
WHEN sc.system_type_id = 165 and sc.max_length = -1 THEN 1 -- varbinary
WHEN sc.system_type_id = 167 and sc.max_length = -1 THEN 1 -- varchar
WHEN sc.system_type_id = 231 and sc.max_length = -1 THEN 1 -- nvarchar
ELSE 0
END) WHEN 0 THEN 1 else 0 end OptionOnlineRebuild, -- Online Rebuilds
1 KeepMaintained, -- Keep Maintained
CASE si.fill_factor WHEN 0 THEN 100 ELSE si.fill_factor END Fill_Factor-- Fill Factor
into #IndexMaintenance
FROM sys.indexes si
JOIN sys.tables st
ON st.object_id = si.object_id
JOIN sys.columns sc
ON si.object_id = sc.object_id
WHERE si.type_desc <> 'HEAP' -- we do not want heap data
GROUP BY st.schema_id, si.name,si.object_id,si.fill_factor

GO
-- Locate new indexes and insert them
INSERT INTO [IndexMaintenance].[dbo].[IndexMaintenance]
([SchemaName]
,[TableName]
,[IndexName]
,OptionStatisticsNorecompute
,[OptionAllowRowLocks]
,[OptionAllowPageLocks]
,OptionSortInTempdb
,[OptionOnlineRebuild]
,[KeepMaintained]
,[Fill_Factor])
SELECT im.SchemaName
,im.TableName
,im.IndexName
,im.OptionStatisticsNorecompute
,im.OptionAllowRowLocks
,im.OptionAllowPageLocks
,im.OptionSortInTempdb
,im.OptionOnlineRebuild
,im.KeepMaintained
,im.Fill_Factor
FROM #IndexMaintenance im
WHERE NOT EXISTS (SELECT 1
FROM dbo.IndexMaintenance im2
WHERE im2.SchemaName= im.SchemaName
AND im2.TableName = im.TableName
AND im2.IndexName = im.IndexName)

--Disable Old Indexes
UPDATE im
SET KeepMaintained = 0
FROM dbo.IndexMaintenance im
WHERE NOT EXISTS (SELECT 1
FROM #IndexMaintenance im2
WHERE im2.SchemaName= im.SchemaName
AND im2.TableName = im.TableName
AND im2.IndexName = im.IndexName)
AND im.KeepMaintained = 1

--Enable Old Indexes
UPDATE im
SET KeepMaintained = 1
FROM dbo.IndexMaintenance im
WHERE EXISTS (SELECT 1
FROM #IndexMaintenance im2
WHERE im2.SchemaName= im.SchemaName
AND im2.TableName = im.TableName
AND im2.IndexName = im.IndexName)
AND im.KeepMaintained = 0