1

I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on same set of columns then remove all except one.

3

sp_BlitzIndex from Brent Ozar will help you find and identify those:

https://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/

After that You will have to check those indexes on your own and decide which of them are obsolete. This isn't a task I would try automating with any kind of script.

|improve this answer|||||
1

for exact duplicates I have been using the script below:

-- find duplicate indexes
-- http://www.sqlservercentral.com/Forums/Topic1359723-392-1.aspx
-- marcelo miorelli
-- 31 jan 2013

set transaction isolation level read uncommitted

;WITH IndexColumns AS (
        SELECT DISTINCT a.object_id, a.name, 
                        a.type_desc, b.column_id, 
                        TABLE_NAME=c.name, 
                        [COL NAME]=d.name, 
                        b.is_included_column
        FROM sys.indexes a

        INNER JOIN sys.index_columns b 
                ON a.object_id = b.object_id AND a.index_id = b.index_id

        INNER JOIN sys.tables c 
                ON b.object_id = c.object_id

        INNER JOIN sys.columns d 
                ON c.object_id = d.object_id 
               AND b.column_id = d.column_id 

        WHERE is_hypothetical = 0

        ),
    CombineCols AS (

        SELECT object_id, name, type_desc, 
               table_name
              ,columns=STUFF((
                SELECT ',' + [COL NAME]
                FROM IndexColumns b
                WHERE a.object_id = b.object_id 
                  AND a.name = b.name 
                  AND a.type_desc = b.type_desc 
                  AND a.TABLE_NAME = b.TABLE_NAME 
                  AND b.is_included_column = 0
                ORDER BY [COL NAME]
                FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
            ,include_columns=ISNULL(STUFF((
                SELECT ',' + [COL NAME]
                FROM IndexColumns b
                WHERE a.object_id = b.object_id AND 
                    a.name = b.name AND 
                    a.type_desc = b.type_desc AND
                    a.TABLE_NAME = b.TABLE_NAME AND b.is_included_column = 1
                ORDER BY [COL NAME]
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '')
        FROM IndexColumns a
        GROUP BY object_id, name, type_desc, table_name)
SELECT b.type_desc, a.table_name, a.columns, a.include_columns, b.name
FROM (
    SELECT table_name, columns, include_columns
    FROM CombineCols
    GROUP BY table_name, columns, include_columns
    HAVING COUNT(name) > 1) a
INNER JOIN CombineCols b 
    ON a.table_name = b.table_name AND
        a.columns = b.columns  AND
        a.include_columns = b.include_columns
ORDER BY a.table_name, a.columns
|improve this answer|||||
0

resolved my problem through a internet script and some self effort.

Using Below query, I stored all duplicate Indexes in #Duplicate_Indexes as it was about 1600.

        SET NOCOUNT ON;

        DECLARE  @First             [smallint]
                ,@Last              [smallint]
                ,@DBName            [nvarchar] (128)
                ,@SQLServer         [nvarchar] (128)
                ,@StringToExecuteP1 [nvarchar] (max)
                ,@StringToExecuteP2 [nvarchar] (max)
                ,@StringToExecuteP3 [nvarchar] (max)

        IF NOT EXISTS (SELECT *
                       FROM [tempdb].[sys].[objects]
                       WHERE [name] = 'DUPLICATE_INDEXES_INFO'
                        AND [type] IN (N'U'))
        BEGIN
            CREATE TABLE [tempdb].[dbo].[DUPLICATE_INDEXES_INFO]
                ([Server]               [nvarchar](128)
                ,[Database]             [nvarchar](128)
                ,[TableName]            [varchar](256)
                ,[IndexName]            [varchar](256)
                ,[IndexType]            [varchar](13)
                ,[KeyColumns]           [varchar](512)
                ,[NonKeyColumns]        [varchar](512)
                ,[KeyColumnsOrder]      [varchar](512)
                ,[NonKeyColumnsOrder]   [varchar](512)
                ,[IsUnique]             [char](1)
                ,[HasNonKeyColumns]     [char](1)
                ,[CheckDate]            [datetime])
        END
        ELSE
        BEGIN
            TRUNCATE TABLE [tempdb].[dbo].[DUPLICATE_INDEXES_INFO]
        END

        IF OBJECT_ID('Tempdb.dbo.#Indexes') IS NOT NULL
            DROP TABLE #Indexes
        CREATE TABLE #Indexes  
            ([RowNo] [smallint] IDENTITY(1, 1)
            ,[TableName] [varchar](256)
            ,[IndexName] [varchar](256)
            ,[IsUnique] [smallint]
            ,[IndexType] [varchar](13))

        IF OBJECT_ID('Tempdb.dbo.#AllIndexesInfo') IS NOT NULL
            DROP TABLE #AllIndexesInfo
        CREATE TABLE #AllIndexesInfo
            ([ObjectID]         [int] NOT NULL
            ,[TableName]        [nvarchar](128) NULL
            ,[IndexID]          [int] NOT NULL
            ,[IndexName]        [sysname] NULL
            ,[IndexType]        [varchar](13) NOT NULL
            ,[ColumnID]         [int] NOT NULL
            ,[ColumnName]       [sysname] NULL
            ,[IncludedColumns]  [bit] NULL
            ,[IsUnique]         [bit] NULL)

        IF OBJECT_ID('Tempdb.dbo.#AllIndexesDetailedInfo') IS NOT NULL
            DROP TABLE #AllIndexesDetailedInfo                  
        CREATE TABLE #AllIndexesDetailedInfo
            ([TableName]            [varchar](256)
            ,[IndexName]            [varchar](256)
            ,[IndexType]            [varchar](13)
            ,[KeyColumns]           [varchar](1024)
            ,[NonKeyColumns]        [varchar](1024)
            ,[KeyColumnsOrder]      [varchar](1024)
            ,[NonKeyColumnsOrder]   [varchar](1024)
            ,[IsUnique]             [char](1)
            ,[HasNonKeyColumns]     [char](1))

        DECLARE @DatabaseList TABLE ([RowNo]  [smallint] identity (1, 1)
                                    ,[DBName] [varchar](200))

        SELECT @SQLServer = CAST(SERVERPROPERTY('ServerName') AS [nvarchar](128))

        INSERT INTO @DatabaseList 
        SELECT [name] FROM [master].[sys].[databases] WITH (NOLOCK) 
        WHERE  [state_desc] = 'ONLINE' 
            AND [source_database_id] IS NULL
            AND [database_id] > 4

        SELECT @First = MIN([RowNo]) FROM @DatabaseList
        SELECT @Last = MAX([RowNo]) FROM @DatabaseList

        WHILE @First <= @Last
        BEGIN
            SELECT @DBName = [DBName]
            FROM @DatabaseList WHERE [RowNo] = @First

            SET @StringToExecuteP1 = 'USE [' + @DBName + '];
                INSERT INTO #AllIndexesInfo
                           ([ObjectID]
                           ,[TableName]
                           ,[IndexID]
                           ,[IndexName]
                           ,[IndexType]
                           ,[ColumnID]
                           ,[ColumnName]
                           ,[IncludedColumns]
                           ,[IsUnique])
                SELECT o.[object_id] AS [ObjectID]
                      ,OBJECT_NAME(o.[object_id]) AS [TableName]
                      ,i.[index_id] AS [IndexID]
                      ,i.[name] AS [IndexName]
                      ,CASE i.[type]
                        WHEN 0
                            THEN ''Heap''
                        WHEN 1
                            THEN ''Clustered''
                        WHEN 2
                            THEN ''Non-Clustered''
                        WHEN 3
                            THEN ''XML''
                        ELSE ''Unknown''
                        END AS [IndexType]
                    ,ic.[column_id] AS [ColumnID]
                    ,c.[name] AS [ColumnName]
                    ,ic.[is_included_column] [IncludedColumns]
                    ,i.[is_unique] AS [IsUnique]
                FROM [' + @DBName + '].[sys].[indexes] i
                INNER JOIN [' + @DBName + '].[sys].[objects] o 
                    ON i.[object_id] = o.[object_id]
                        AND o.[type] = ''U''
                        AND i.[index_id] > 0
                INNER JOIN [' + @DBName + '].[sys].[index_columns] ic 
                    ON i.[index_id] = ic.[index_id]
                        AND i.[object_id] = ic.[object_id]
                INNER JOIN [' + @DBName + '].[sys].[columns] c 
                    ON c.[column_id] = ic.[column_id]
                        AND c.[object_id] = ic.[object_id]

                INSERT INTO #Indexes
                SELECT DISTINCT [TableName]
                    ,[IndexName]
                    ,[IsUnique]
                    ,[IndexType]
                FROM #AllIndexesInfo'

            EXEC (@StringToExecuteP1)

            SET @StringToExecuteP2 = 'USE [' + @DBName + '];
                DECLARE @First              [smallint]
                       ,@Last               [smallint]
                       ,@IsUnique           [smallint]
                       ,@HasNonKeyCols      [char] (1)
                       ,@TableName          [varchar] (256)
                       ,@IndexName          [varchar] (256)
                       ,@IndexType          [varchar] (13)
                       ,@IndexColumns       [varchar] (1000)
                       ,@IncludedColumns    [varchar] (1000)
                       ,@IndexColsOrder     [varchar] (1000)
                       ,@IncludedColsOrder  [varchar] (1000)

                SELECT @First = MIN([RowNo])
                FROM #Indexes

                SELECT @Last = MAX([RowNo])
                FROM #Indexes

                WHILE @First <= @Last
                BEGIN
                    SET @IndexColumns = NULL
                    SET @IncludedColumns = NULL
                    SET @IncludedColsOrder = NULL
                    SET @IndexColsOrder = NULL

                    SELECT @TableName = [TableName]
                          ,@IndexName = [IndexName]
                          ,@IsUnique  = [IsUnique]
                          ,@IndexType = [IndexType]
                    FROM #Indexes
                    WHERE [RowNo] = @First

                    SELECT @IndexColumns = COALESCE(@IndexColumns + '', '', '''') 
                                                + [ColumnName]
                    FROM #AllIndexesInfo
                    WHERE [TableName] = @TableName
                        AND [IndexName] = @IndexName
                        AND [IncludedColumns] = 0
                    ORDER BY [IndexName]
                            ,[ColumnName]

                    SELECT @IncludedColumns = COALESCE(@IncludedColumns + '', '', '''') 
                                                + [ColumnName]
                    FROM #AllIndexesInfo
                    WHERE [TableName] = @TableName
                        AND [IndexName] = @IndexName
                        AND [IncludedColumns] = 1
                    ORDER BY [IndexName]
                            ,[ColumnName]

                    SELECT @IndexColsOrder = COALESCE(@IndexColsOrder + '', '', '''') 
                                                + [ColumnName]
                    FROM #AllIndexesInfo
                    WHERE [TableName] = @TableName
                        AND [IndexName] = @IndexName
                        AND [IncludedColumns] = 0

                    SELECT @IncludedColsOrder = COALESCE(@IncludedColsOrder + '', '', '''') 
                                                + [ColumnName]
                    FROM #AllIndexesInfo
                    WHERE [TableName] = @TableName
                        AND [IndexName] = @IndexName
                        AND [IncludedColumns] = 1

                    SET @HasNonKeyCols = ''N''

                    IF @IncludedColumns IS NOT NULL
                    BEGIN
                        SET @HasNonKeyCols = ''Y''
                    END

                    INSERT INTO #AllIndexesDetailedInfo (
                        [TableName]
                        ,[IndexName]
                        ,[IndexType]
                        ,[IsUnique]
                        ,[KeyColumns]
                        ,[KeyColumnsOrder]
                        ,[HasNonKeyColumns]
                        ,[NonKeyColumns]
                        ,[NonKeyColumnsOrder]
                        )
                    SELECT @TableName
                        ,@IndexName
                        ,@IndexType
                        ,CASE @IsUnique
                            WHEN 1
                                THEN ''Y''
                            WHEN 0
                                THEN ''N''
                            END
                        ,@IndexColumns
                        ,@IndexColsOrder
                        ,@HasNonKeyCols
                        ,@IncludedColumns
                        ,@IncludedColsOrder

                    SET @First = @First + 1
                END'

            EXEC (@StringToExecuteP2)

            SET @StringToExecuteP3 = 'USE [' + @DBName + '];
              INSERT INTO [tempdb].[dbo].[DUPLICATE_INDEXES_INFO]
                       ([Server]
                       ,[Database]
                       ,[TableName]
                       ,[IndexName]
                       ,[IndexType]
                       ,[KeyColumns]
                       ,[HasNonKeyColumns]
                       ,[NonKeyColumns]
                       ,[KeyColumnsOrder]
                       ,[NonKeyColumnsOrder]
                       ,[IsUnique]
                       ,[CheckDate])
                SELECT  ''' + @SQLServer + '''
                       ,''' + @DBName + '''
                       ,[TableName]
                       ,[IndexName]
                       ,[IndexType]
                       ,[KeyColumns]
                       ,[HasNonKeyColumns]
                       ,[NonKeyColumns]
                       ,[KeyColumnsOrder]
                       ,[NonKeyColumnsOrder]
                       ,[IsUnique]
                       ,CURRENT_TIMESTAMP
                FROM
                (
                  SELECT DISTINCT 
                     a1.[TableName]
                    ,a1.[IndexName]
                    ,a1.[IndexType]
                    ,a1.[KeyColumns]
                    ,a1.[HasNonKeyColumns]
                    ,a1.[NonKeyColumns]
                    ,a1.[KeyColumnsOrder]
                    ,a1.[NonKeyColumnsOrder]
                    ,a1.[IsUnique]
                  FROM #AllIndexesDetailedInfo a1
                  INNER JOIN #AllIndexesDetailedInfo a2 
                    ON a1.[TableName] = a2.TableName
                     AND a1.[IndexName] <> a2.[IndexName]
                     AND a1.[KeyColumns] = a2.[KeyColumns]
                     AND ISNULL(a1.[NonKeyColumns], '''') = ISNULL(a2.[NonKeyColumns], '''')
                  WHERE a1.[IndexType] <> ''XML''
                 ) a '

            EXEC (@StringToExecuteP3)

            TRUNCATE TABLE #Indexes
            TRUNCATE TABLE #AllIndexesInfo
            TRUNCATE TABLE #AllIndexesDetailedInfo

            SET @First = @First + 1 
        END

        SELECT [Server]
              ,[Database]
              ,[TableName]
              ,[IndexName]
              ,[IndexType]
              ,[KeyColumns]
              ,[NonKeyColumns]
              ,[KeyColumnsOrder]
              ,[NonKeyColumnsOrder]
              ,[IsUnique]
              ,[HasNonKeyColumns]
              ,[CheckDate] INTO #Duplicate_Indexes
        FROM [tempdb].[dbo].[DUPLICATE_INDEXES_INFO]

        SET NOCOUNT OFF;

Then Used bellow to select one Non Key Index and Created Script of that.

        ;WITH CTE
        AS
        (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 100)) ID,* FROM #Duplicate_Indexes
        )

        SELECT 'DROP INDEX '+TableName+'.'+IndexName,a.[Database] FROM
        CTE a INNER JOIN ( SELECT MAX(ID) ID FROM CTE GROUP BY [Server]
              ,[Database]
              ,[TableName]
              ,[IndexType]
              ,[KeyColumns]
              ,[NonKeyColumns]
              ,[KeyColumnsOrder]
              ,[NonKeyColumnsOrder]
              ,[IsUnique]
              ,[HasNonKeyColumns]
              ,[CheckDate]
              ) b on a.ID=b.ID AND a.IsUnique='N'

Then Execute the generated script.

I had to repeat it for 2 times, and it was done

Thanks

|improve this answer|||||
  • where from is your internet script? – Marcello Miorelli Jun 22 '16 at 11:09
  • it did not work for me - The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. – Marcello Miorelli Jun 22 '16 at 11:10
  • Tell me the line number where you faced the issue, it's executing fine on my environment. – user98113 Jun 22 '16 at 11:15
0

you can try the following script:

WITH IndexColumns AS
(
    SELECT I.object_id AS TableObjectId, OBJECT_SCHEMA_NAME(I.object_id) + '.' + OBJECT_NAME(I.object_id) AS TableName, I.index_id AS IndexId, I.name AS IndexName
        , (IndexUsage.user_seeks + IndexUsage.user_scans + IndexUsage.user_lookups) AS IndexUsage
        , IndexUsage.user_updates AS IndexUpdates

       , (SELECT CASE is_included_column WHEN 1 THEN NULL ELSE column_id END AS [data()]
        FROM sys.index_columns AS IndexColumns
        WHERE IndexColumns.object_id = I.object_id
          AND IndexColumns.index_id = I.index_id
        ORDER BY index_column_id, column_id
        FOR XML PATH('')
       ) AS ConcIndexColumnNrs

       ,(SELECT CASE is_included_column WHEN 1 THEN NULL ELSE COL_NAME(I.object_id, column_id) END AS [data()]
        FROM sys.index_columns AS IndexColumns
        WHERE IndexColumns.object_id = I.object_id
          AND IndexColumns.index_id = I.index_id
        ORDER BY index_column_id, column_id
        FOR XML PATH('')
       ) AS ConcIndexColumnNames

       ,(SELECT CASE is_included_column WHEN 1 THEN column_id ELSE NULL END AS [data()]
        FROM sys.index_columns AS IndexColumns
        WHERE IndexColumns.object_id = I.object_id
        AND IndexColumns.index_id = I.index_id
        ORDER BY column_id
        FOR XML PATH('')
       ) AS ConcIncludeColumnNrs

       ,(SELECT CASE is_included_column WHEN 1 THEN COL_NAME(I.object_id, column_id) ELSE NULL END AS [data()]
        FROM sys.index_columns AS IndexColumns
        WHERE IndexColumns.object_id = I.object_id
          AND IndexColumns.index_id = I.index_id
        ORDER BY column_id
        FOR XML PATH('')
       ) AS ConcIncludeColumnNames
    FROM sys.indexes AS I
       LEFT OUTER JOIN sys.dm_db_index_usage_stats AS IndexUsage
        ON IndexUsage.object_id = I.object_id
          AND IndexUsage.index_id = I.index_id
          AND IndexUsage.Database_id = db_id() 
)
SELECT
  C1.TableName
  , C1.IndexName AS 'Index1'
  , C2.IndexName AS 'Index2'
  , CASE WHEN (C1.ConcIndexColumnNrs = C2.ConcIndexColumnNrs) AND (C1.ConcIncludeColumnNrs = C2.ConcIncludeColumnNrs) THEN 'Exact duplicate'
        WHEN (C1.ConcIndexColumnNrs = C2.ConcIndexColumnNrs) THEN 'Different includes'
        ELSE 'Overlapping columns' END
--  , C1.ConcIndexColumnNrs
--  , C2.ConcIndexColumnNrs
  , C1.ConcIndexColumnNames as ConcIndexColumnNames1
  , C2.ConcIndexColumnNames as ConcIndexColumnNames2
--  , C1.ConcIncludeColumnNrs
--  , C2.ConcIncludeColumnNrs
  , C1.ConcIncludeColumnNames as ConcIncludeColumnNames1
  , C2.ConcIncludeColumnNames as ConcIncludeColumnNames2
  , C1.IndexUsage as IndexUsage1
  , C2.IndexUsage as IndexUsage2
  , C1.IndexUpdates as IndexUpdates1
  , C2.IndexUpdates as IndexUpdates2
  , 'DROP INDEX ' + C2.IndexName + ' ON ' + C2.TableName AS Drop2
  , 'DROP INDEX ' + C1.IndexName + ' ON ' + C1.TableName AS Drop1
FROM IndexColumns AS C1
  INNER JOIN IndexColumns AS C2 
    ON (C1.TableObjectId = C2.TableObjectId)
    AND (
         -- exact: show lower IndexId as 1
            (C1.IndexId < C2.IndexId
            AND C1.ConcIndexColumnNrs = C2.ConcIndexColumnNrs
            AND C1.ConcIncludeColumnNrs = C2.ConcIncludeColumnNrs)
         -- different includes: show longer include as 1
         OR (C1.ConcIndexColumnNrs = C2.ConcIndexColumnNrs
            AND LEN(C1.ConcIncludeColumnNrs) > LEN(C2.ConcIncludeColumnNrs))
         -- overlapping: show longer index as 1
         OR (C1.IndexId <> C2.IndexId
            AND C1.ConcIndexColumnNrs <> C2.ConcIndexColumnNrs
            AND C1.ConcIndexColumnNrs like C2.ConcIndexColumnNrs + ' %')
    )
ORDER BY C1.TableName, C1.ConcIndexColumnNrs
|improve this answer|||||
  • If you get the script from elsewhere, it is nice to give attribution. – LowlyDBA Dec 18 '19 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy