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



No comments:

Post a Comment