Performance Tuning

SQL Server: Query to Find Duplicate Indexes.

SQL Server allows you to create many non-clustered indexes (999 non-clustered indexes in SQL Server 2008 or later versions), so technically you can create more than one non-clustered index on the same columns(s) with a different name. Recently I was evaluating one of database environment where we have hundreds of databases, this server houses databases for one of the major litigation product, so technically all the databases has same architecture but different set of data. I was evaluating our environment for the regular evaluation of indexes and its utilization and did reanalyses that databases has few duplicate indexes. Actually admin section of the application provides the ability to create indexes (may be for temporary use) from the UI and it was created by application users unintentionally without understating the impact of having these indexes on the major tables with millions of records.

I also noticed the overlapping index and I'll discuss about that in my next post.

Below script will return you database wise list of duplicate indexes on your server. If you want to use this specific database, please add condition to filter database name in the query with SYS.DATABASES. This script is the based on the script that I posted here on one of my previous post. Using this script you can identify the duplicate indexes and drop those, I hope it will be helpful. 

USE master

GO

DECLARE

       @DBName VARCHAR(100),

       @DBCount INT,

       @IndexCount INT,

       @SQL VARCHAR(8000) = '',

       @Columns varchar(2000) = '',

       @IncludedColumns varchar(2000) = '',

       @IndexName VARCHAR(1000) = ''

DECLARE @tblDBList TABLE (DBName VARCHAR(100))

DECLARE @tblIndexList TABLE (IndexName VARCHAR(1000))

CREATE TABLE #tblIndexListSingleDB (DataBaseName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(100), TableName VARCHAR(100), IndexType VARCHAR(100) , IsIncluded BIT)

CREATE TABLE #tblIndexListAllDB (RecordID INT Identity(1,1), DataBaseName VARCHAR(100), TableName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(1000), IncludedColumns VARCHAR(2000), IndexType VARCHAR(100))

 INSERT INTO @tblDBList SELECT name FROM SYS.DATABASES

                                                      --optional condition if you wish to use to this query on limited database.

                                                      -- WHERE Name Like '%database name%'

                                                      -- WHERE database_id BETWEEN 5 AND 20

 SELECT @DBCount = COUNT(*) FROM @tblDBList

 WHILE @DBCount > 0

 BEGIN

   SELECT TOP 1 @DBName = DBName FROM @tblDBList

   SET @SQL = @SQL + ' INSERT INTO #tblIndexListSingleDB (DataBaseName, IndexName, ColumnName, TableName, IndexType, IsIncluded) ' + CHAr(13)

   SET @SQL = @SQL + ' SELECT ' + '''' + @DBName + '''' + ' AS DataBaseName, IND.NAME AS IndexName, COL.NAME AS ColumnName, T.Name AS TableName, IND.TYPE_DESC AS IndexType , IC.IS_INCLUDED_COLUMN AS IsIncluded ' + CHAR(13)

   SET @SQL = @SQL + ' FROM ' + @DBName + '.SYS.INDEXES IND ' + CHAR(13)

   SET @SQL = @SQL + ' INNER JOIN ' + @DBName + '.SYS.INDEX_COLUMNS IC ON IND.OBJECT_ID = IC.OBJECT_ID AND IND.INDEX_ID = IC.INDEX_ID ' + CHAR(13)

   SET @SQL = @SQL + ' INNER JOIN ' + @DBName + '.SYS.COLUMNS COL ON IC.OBJECT_ID = COL.OBJECT_ID AND IC.COLUMN_ID = COL.COLUMN_ID ' + CHAR(13)

   SET @SQL = @SQL + ' INNER JOIN ' + @DBName + '.SYS.TABLES T ON IND.OBJECT_ID = T.OBJECT_ID ' + CHAR(13)

   SET @SQL = @SQL + ' ORDER BY T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID ' + CHAR(13)

   

   EXEC(@SQL)

   PRINT (@SQL)

  

   INSERT INTO @tblIndexList SELECT IndexName FROM #tblIndexListSingleDB GROUP BY IndexName

  

   SELECT @IndexCount = COUNT(*) FROM @tblIndexList

   WHILE @IndexCount > 0

   BEGIN

    SELECT TOP 1 @IndexName = IndexName FROM @tblIndexList

   

    SELECT @Columns = @Columns + ColumnName + ', '

            FROM #tblIndexListSingleDB

                  WHERE IndexName = @IndexName AND IsIncluded != 1 ORDER BY ColumnName

    IF LEN(@Columns) > 1

     SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

    SELECT @IncludedColumns = @IncludedColumns + ColumnName + ', '

            FROM #tblIndexListSingleDB

                  WHERE IndexName = @IndexName AND IsIncluded = 1

                         ORDER BY ColumnName

    IF LEN(@IncludedColumns) > 1

     SET @IncludedColumns = LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1)

   

    INSERT INTO #tblIndexListAllDB (DataBaseName, TableName, IndexName, ColumnName, IncludedColumns, IndexType)

    SELECT DataBaseName, TableName, IndexName, @Columns, @IncludedColumns, IndexType

     FROM  #tblIndexListSingleDB

      WHERE IndexName = @IndexName

       GROUP BY DataBaseName, TableName, IndexName, IndexType

    DELETE FROM @tblIndexList WHERE IndexName = @IndexName

    SELECT @IndexCount = COUNT(*) FROM @tblIndexList

    SET @Columns = ''

    SET @IncludedColumns = ''

   END

  

  

   DELETE FROM @tblDBList WHERE DBName =  @DBName

   SELECT @DBCount = COUNT(*) FROM @tblDBList

   SET @SQL = ''

   DELETE FROM #tblIndexListSingleDB

  

 END

 SELECT T1.DataBaseName, T1.TableName, T1.IndexName, T1.ColumnName, T1.IncludedColumns

      FROM #tblIndexListAllDB T1

      JOIN (SELECT DataBaseName, TableName, ColumnName, IncludedColumns FROM #tblIndexListAllDB GROUP BY DataBaseName, TableName, ColumnName, IncludedColumns HAVING COUNT(*) > 1) T2

            ON T1.DataBaseName = T2.DataBaseName AND T1.TableName = T2.TableName AND T1.ColumnName = T2.ColumnName AND T1.IncludedColumns = T2.IncludedColumns

 --SELECT * FROM #tblIndexListAllDB

 DROP TABLE #tblIndexListSingleDB

 DROP TABLE #tblIndexListAllDB