During server performance health check, we saw that on few databases some indexes are missing, its common for DBAs when they are dealing with more than 100 DBs on more than 1 production server. Being a part of DBA activities, to improve server performance we needed to review, compare and synchronize the indexes of databases.
Below are the steps we followed to synchronize indexes of same structure databases.
Step 1. Check Indexes count for all databases
Execute GetServerDBsIndexCount
Step 2. Remove Indexes from imperfect databases
Execute RemoveIndexes 'ImperfectDB'
Step 3. Recreate Indexes from base database to imperfect databases
Execute CreateIndexesFromMasterDB@MasterDB='SQLCat', @ImperfectDB ='ImperfectDB'
Step 4. Re-check Indexes count for all databases
Execute GetServerDBsIndexCount
Step 5. Find missing NonClustered, Clustered and Unique constraint Indexes
Execute FindAllMissingIndexes @MasterDB='SQLCat', @ImperfectDB ='ImperfectDB'
If database are on different servers you just need to copy indexes details onto the other server and go through the similar steps.
1. Get Indexes Count of SQL Server Databases
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/* Script By: Amna Asif
Purpose: Get Indexes Total Count on Databases
*/
GO
CREATE PROCEDURE GetServerDBsIndexCount
AS
BEGIN
DECLARE @DatbaseName VARCHAR(200)
CREATE TABLE #IndexTable
(
DatabaseName VARCHAR(200),
IndexesCount INT
)
DECLARE DatabaseList CURSOR
FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND database_id > 4
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DatbaseName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('
INSERT INTO #IndexTable(DatabaseName,IndexesCount)
SELECT '''+@DatbaseName+''',COUNT(i.name)
FROM [' + @DatbaseName+ '].sys.indexes i
INNER JOIN [' + @DatbaseName+ '].sys.tables t ON i.object_id = t.object_id
INNER JOIN [' + @DatbaseName+ '].sys.schemas sc ON sc.schema_id=t.schema_id
WHERE t.type_desc = ''USER_TABLE''
AND i.type_desc <> ''Heap''
AND i.is_disabled = 0
AND i.is_Primary_Key=0
')
FETCH NEXT FROM DatabaseList INTO @DatbaseName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
SELECT *
FROM #IndexTable
ORDER BY IndexesCount ASC, DatabaseName
DROP TABLE #IndexTable
END
2. Remove All Indexes from a SQL Server Database
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*
Script By: Amna Asif
Purpose: Remove All Indexes from a Database
*/
CREATE PROCEDURE RemoveIndexes
(
@ImperfectDatabase VARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #DBIndexesList
(
TableName VARCHAR(1000),
TableID BIGINT,
IndexName VARCHAR(1000),
IndexID BIGINT,
SchemaName VARCHAR(1000),
IndexType VARCHAR(1000)
)
---------------------------------------------------
EXEC('
INSERT INTO #DBIndexesList
SELECT OBJECT_NAME( i.[object_id], DB_ID('''+@ImperfectDatabase+''') ) TableName,
i.[object_id] ObjectID,
i.NAME IndexNAme,
i.index_id IndexID,
SC.name SchemaName,
CASE WHEN i.is_disabled = 1 THEN ''Disabled''
WHEN i.is_primary_key = 1 AND i.type_desc = ''CLUSTERED'' THEN ''PrimaryClustered''
WHEN i.is_primary_key = 1 AND i.type_desc = ''NONCLUSTERED'' THEN ''PrimaryNonClustered''
WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc = ''CLUSTERED'' THEN ''UniqueClusteredIndex''
WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc = ''NONCLUSTERED'' THEN ''UniqueNonClusteredIndex''
WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc = ''CLUSTERED'' THEN ''UniqueConstraintClustered''
WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc = ''NONCLUSTERED'' THEN ''UniqueConstraintNonClustered''
ELSE CASE WHEN i.type_desc = ''CLUSTERED'' THEN ''Clustered''
WHEN i.type_desc = ''NONCLUSTERED'' THEN ''NonClustered''
ELSE '''' END
END
FROM ['+@ImperfectDatabase+'].sys.indexes i
LEFT JOIN ['+@ImperfectDatabase+'].information_schema.table_constraints tc ON i.NAME = tc.constraint_name
AND OBJECT_NAME(i.object_id, DB_ID('''+@ImperfectDatabase+''')) = tc.table_name
INNER JOIN ['+@ImperfectDatabase+'].SYS.tables T ON i.[object_id] = T.[object_id]
INNER JOIN ['+@ImperfectDatabase+'].SYS.SCHEMAS SC ON SC.[SCHEMA_ID] = T.[SCHEMA_ID]
WHERE t.type_desc = ''USER_TABLE''
AND i.type_desc <> ''Heap''
AND i.is_disabled = 0
AND i.is_Primary_Key=0
--AND is_unique = 1 AND is_unique_constraint =0
')
---------------------------------------------------
DECLARE @idxTableName SYSNAME
, @idxTableID INT
, @idxname SYSNAME
, @idxid INT
, @indextype VARCHAR(200)
, @SqlStatement VARCHAR(4000)
, @SchemaName VARCHAR(200)
DECLARE curidx CURSOR
FOR
SELECT TableName, TableID, IndexName, IndexID, SchemaName, IndexType
FROM #DBIndexesList
ORDER BY IndexType, TableName, IndexName
OPEN curidx
FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName ,@indextype
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @indextype IN ('UniqueConstraintClustered','UniqueConstraintNonClustered'))
BEGIN
SET @SqlStatement = ' IF EXISTS (SELECT * FROM '
+ @ImperfectDatabase + '.sys.indexes
WHERE object_id = OBJECT_ID('''+@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+''')
AND name = '''+@idxname+''' )'
+ CHAR(10)
+' ALTER TABLE '+@ImperfectDatabase+'.[dbo].['+@idxTableName+'] DROP CONSTRAINT ['+@idxname+']'
+ CHAR(10)
+ CHAR(10)
END
ELSE
BEGIN
SET @SqlStatement = ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].SYS.INDEXES WHERE name = ''' + @idxname
+ ''' AND OBJECT_ID = OBJECT_ID('''+@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+''')) '
+ CHAR(10)
+ 'DROP INDEX [' + @idxname + '] ON [' + @ImperfectDatabase + '].'
+ @SchemaName + '.[' + @idxTableName + '] '
+ CHAR(10)
+ CHAR(10)
END
PRINT ( '-----------------Table=['+@idxTableName+'] -- Index=[' + @idxname+']-----------------' )
PRINT ( @SqlStatement )
FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName,@indextype
END
CLOSE curidx
DEALLOCATE curidx
DROP TABLE #DBIndexesList
END
3. Re-Create Indexes on a Database From a Master\Base Database
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*
Script By: This script is only modified by Aasim Abdullah\ Amna Asif
Purpose: Re-Create Indexes on Database From a Base Database
*/
CREATE PROCEDURE CreateIndexesFromMasterDB
(
@MasterDatabase VARCHAR(200),
@ImperfectDatabase VARCHAR(200)
)
AS
BEGIN
CREATE TABLE #DBIndexesList
(
TableName VARCHAR(1000),
TableID BIGINT,
IndexName VARCHAR(1000),
IndexID BIGINT,
SchemaName VARCHAR(1000),
IndexType VARCHAR(1000)
)
---------------------------------------------------
EXEC('INSERT INTO #DBIndexesList
SELECT OBJECT_NAME( i.[object_id], DB_ID('''+@MasterDatabase+''') ) TableName,
i.[object_id] ObjectID,
i.NAME IndexNAme,
i.index_id IndexID,
SC.name SchemaName,
CASE WHEN i.is_disabled = 1 THEN ''Disabled''
WHEN i.is_primary_key = 1 AND i.type_desc = ''CLUSTERED'' THEN ''PrimaryClustered''
WHEN i.is_primary_key = 1 AND i.type_desc = ''NONCLUSTERED'' THEN ''PrimaryNonClustered''
WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc =''CLUSTERED'' THEN ''UniqueClusteredIndex''
WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc =''NONCLUSTERED'' THEN ''UniqueNonClusteredIndex''
WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc =''CLUSTERED'' THEN ''UniqueConstraintClustered''
WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc =''NONCLUSTERED'' THEN ''UniqueConstraintNonClustered''
ELSE CASE WHEN i.type_desc = ''CLUSTERED'' THEN ''Clustered''
WHEN i.type_desc = ''NONCLUSTERED'' THEN ''NonClustered''
ELSE '''' END
END
FROM ['+@MasterDatabase+'].sys.indexes i
LEFT JOIN ['+@MasterDatabase+'].information_schema.table_constraints tc ON i.NAME = tc.constraint_name
AND OBJECT_NAME(i.object_id, DB_ID('''+@MasterDatabase+''')) = tc.table_name
INNER JOIN ['+@MasterDatabase+'].SYS.tables T ON i.[object_id] = T.[object_id]
INNER JOIN ['+@MasterDatabase+'].SYS.SCHEMAS SC ON SC.[SCHEMA_ID] = T.[SCHEMA_ID]
WHERE t.type_desc = ''USER_TABLE''
AND i.type_desc <> ''Heap''
AND i.is_disabled = 0
AND i.is_Primary_Key=0
')
---------------------------------------------------
DECLARE @idxTableName SYSNAME
, @idxTableID INT
, @idxname SYSNAME
, @idxid INT
, @indextype VARCHAR(200)
, @colCount INT
, @IxColumn SYSNAME
, @ColumnIDInTable INT
, @ColumnIDInIndex INT
, @IsIncludedColumn INT
, @sIncludeCols VARCHAR(4000)
, @sIndexCols VARCHAR(4000)
, @SqlStatement VARCHAR(4000)
, @rowcnt INT
, @sParamSQL VARCHAR(4000)
, @location SYSNAME
, @fillfactor INT
, @SchemaName VARCHAR(200)
-- Get all the index info
DECLARE curidx CURSOR ---- Add sys. with databse name
FOR
SELECT TableName, TableID, IndexName, IndexID, SchemaName, IndexType
FROM #DBIndexesList
ORDER BY IndexType, TableName, IndexName
OPEN curidx
FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName ,@indextype
--loop
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( @indextype IN ('UniqueConstraintClustered','UniqueConstraintNonClustered'))
BEGIN
SET @SqlStatement = ' IF EXISTS (SELECT * FROM '+ @ImperfectDatabase
+ '.sys.indexes WHERE object_id = OBJECT_ID('''+@ImperfectDatabase+'.'
+ @SchemaName+'.'+@idxTableName+''') AND name = '''+@idxname+''' )'
+ CHAR(10)
+' ALTER TABLE '+@ImperfectDatabase+'.[dbo].['+@idxTableName+'] DROP CONSTRAINT ['+@idxname+']'
+ CHAR(10)
+ ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].sys.objects WHERE OBJECT_ID=OBJECT_ID('''
+ @ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ + ''') )'
+ CHAR(10)
+ ' BEGIN IF NOT EXISTS (SELECT 1 FROM [' + @ImperfectDatabase
+ '].SYS.INDEXES WHERE name = ''' + @idxname
+ ''' AND OBJECT_ID=OBJECT_ID(''' + +@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ + ''') )'
+ CHAR(10)
END
ELSE
BEGIN
SET @SqlStatement = ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].SYS.INDEXES WHERE name = ''' + @idxname
+ ''' AND OBJECT_ID = OBJECT_ID('''+@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+''')) '
+ CHAR(10)
+ 'DROP INDEX [' + @idxname + '] ON [' + @ImperfectDatabase + '].'
+ @SchemaName + '.[' + @idxTableName + '] '
+ CHAR(10)
+ ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase
+ '].sys.objects WHERE OBJECT_ID=OBJECT_ID(''' +@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ ''') )'
+ CHAR(10)
+ ' BEGIN IF NOT EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].SYS.INDEXES WHERE name = ''' + @idxname
+ ''' AND OBJECT_ID=OBJECT_ID(''' + +@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ + ''') )'
+ CHAR(10)
END
SET @SqlStatement = @SqlStatement + 'BEGIN' + CHAR(10)
SELECT @SqlStatement= @SqlStatement+ CASE @indextype
WHEN 'CLUSTERED'
THEN ' CREATE CLUSTERED '+ 'INDEX [' + @idxname + '] ON ['
+ @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']'
WHEN 'UniqueClusteredIndex'
THEN ' CREATE UNIQUE CLUSTERED '+ 'INDEX [' + @idxname + '] ON ['
+ @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']'
WHEN 'UniqueConstraintClustered'
THEN ' ALTER TABLE [' + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + '] '
+'ADD CONSTRAINT ['+@idxname+'] UNIQUE CLUSTERED '
WHEN 'NonClustered'
THEN ' CREATE NONCLUSTERED '+ 'INDEX [' + @idxname + '] ON ['
+ @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']'
WHEN 'UniqueNonClusteredIndex'
THEN ' CREATE UNIQUE NONCLUSTERED '+ 'INDEX [' + @idxname + '] ON ['
+ @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']'
WHEN 'UniqueConstraintNonClustered'
THEN ' ALTER TABLE [' + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + '] '
+'ADD CONSTRAINT ['+@idxname+'] UNIQUE NONCLUSTERED '
ELSE ' CREATE NONCLUSTERED '+ 'INDEX [' + @idxname + '] ON ['
+ @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']'
END
SET @SqlStatement = @SqlStatement + CHAR(10) + '('
SET @SqlStatement = @SqlStatement + CHAR(10)
SET @colCount = 0
SELECT @fillfactor = fill_factor
FROM sys.indexes
WHERE name = @idxname
IF ISNULL(@fillfactor, 0) = 0 SET @fillfactor = 90
-- Get the number of cols in the index
SELECT @colCount = COUNT(*)
FROM sys.index_columns ic
INNER JOIN sys.columns sc ON ic.object_id = sc.object_id
AND ic.column_id = sc.column_id
WHERE ic.object_id = @idxtableid
AND index_id = @idxid
AND ic.is_included_column = 0
-- Get the file group info
SELECT @location = f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.object_id = @idxTableID
AND i.index_id = @idxid
-- Get all columns of the index
DECLARE curidxcolumn CURSOR
FOR
SELECT sc.column_id AS columnidintable ,
sc.NAME ,
ic.index_column_id columnidinindex ,
ic.is_included_column AS isincludedcolumn
FROM sys.index_columns ic
INNER JOIN sys.columns sc ON ic.object_id = sc.object_id
AND ic.column_id = sc.column_id
WHERE ic.object_id = @idxTableID
AND index_id = @idxid
ORDER BY ic.index_column_id
SET @sIncludeCols = ''
SET @sIndexCols = ''
SET @rowcnt = 0
OPEN curidxColumn
FETCH NEXT FROM curidxColumn INTO @ColumnIDInTable, @IxColumn, @ColumnIDInIndex, @IsIncludedColumn
--loop
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @IsIncludedColumn = 0
BEGIN
SET @rowcnt = @rowcnt + 1
SET @sIndexCols = CHAR(9) + @sIndexCols + '['+ @IxColumn + ']'
-- Check the sort order of the index cols
IF ( INDEXKEY_PROPERTY(@idxTableID, @idxid, @ColumnIDInIndex, 'IsDescending') ) = 0
SET @sIndexCols = @sIndexCols + ' ASC '
ELSE
SET @sIndexCols = @sIndexCols + ' DESC '
IF @rowcnt < @colCount
SET @sIndexCols = @sIndexCols + ', '
END
ELSE
BEGIN
-- Check for any include columns
IF LEN(@sIncludeCols) > 0
SET @sIncludeCols = @sIncludeCols + ','
SET @sIncludeCols = @sIncludeCols + '['
+ @IxColumn + ']'
END
FETCH NEXT FROM curidxColumn INTO @ColumnIDInTable,
@IxColumn, @ColumnIDInIndex, @IsIncludedColumn
END
CLOSE curidxColumn
DEALLOCATE curidxColumn
--append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @SqlStatement + @sIndexCols + CHAR(10)+ ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) ' + CHAR(10)+ CHAR(10)+ CHAR(10)
ELSE
SET @sIndexCols = @SqlStatement + @sIndexCols + CHAR(10) + ') ' + CHAR(10)+ CHAR(10)+ CHAR(10)
-- Build the options
SET @sParamSQL = ' WITH (FILLFACTOR = ' + CAST(ISNULL(@fillfactor, 90) AS VARCHAR(3)) + ', '
IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1 )
SET @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, '
IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1 )
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '
IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1 )
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = on, '
IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1 )
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF '
SET @sParamSQL = CASE @indextype WHEN 'UniqueConstraintClustered' THEN @sParamSQL+' )'
WHEN 'UniqueConstraintNonClustered' THEN @sParamSQL+' )'
ELSE @sParamSQL + ', DROP_EXISTING = off ) ' END
SET @SqlStatement = @sIndexCols + CHAR(10) + @sParamSQL + ' ON [' + @location + ']' + CHAR(10) + 'END END' + CHAR(10)
PRINT ( '-----------------Table=['+@idxTableName+'] -- Index=[' + @idxname+']-----------------' )
PRINT ( @SqlStatement )
FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName,@indextype
END
CLOSE curidx
DEALLOCATE curidx
DROP TABLE #DBIndexesList
END
4. Find missing Indexes by comparing indexes of Mater and Slave Database.
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*
Script By: Amna Asif
Purpose: Find missing Indexes
*/
GO
CREATE PROCEDURE FindAllMissingIndexes(@MasterDB varchar(200),@ImperfectDB varchar(200) )
AS
EXEC('
SELECT t.name ObjectName, i.name IndexName
FROM '+@MasterDB+'.sys.indexes i
INNER JOIN '+@MasterDB +'.sys.tables t ON i.object_id = t.object_id
INNER JOIN '+@MasterDB +'.sys.schemas sc ON sc.schema_id = t.schema_id
WHERE t.type_desc = ''USER_TABLE''
AND i.is_primary_key = 0
AND i.is_disabled = 0
EXCEPT
SELECT t.name ObjectName, i.name IndexName
FROM '+@ImperfectDB+'.sys.indexes i
INNER JOIN '+@ImperfectDB+'.sys.tables t ON i.object_id = t.object_id
INNER JOIN '+@ImperfectDB+'.sys.schemas sc ON sc.schema_id = t.schema_id
WHERE t.type_desc = ''USER_TABLE''
AND i.is_primary_key = 0
AND i.is_disabled = 0
')
