How to find the missing indices and duplicate indices

BKAN 41 Reputation points
2022-09-27T12:53:29.093+00:00

Hi All,
I am new to the forum and please excuse if I am posting in the wrong forums.
When i worked on-premise databases I used the following queries to find the missing and duplicate indices,, I was using the below queries, but when I run it in Azure SQL Databases I am getting the below error
Msg 104385, Level 16, State 1, Line 1
Catalog view 'dm_db_missing_index_details' is not supported in this version.
Please advice. When I run them against the Master database I do not get any rows returned, but when run against the regular databases I get this error.

SELECT db.[name] AS [DatabaseName]  
    ,id.[object_id] AS [ObjectID]  
       ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]  
    ,id.[statement] AS [FullyQualifiedObjectName]  
    ,id.[equality_columns] AS [EqualityColumns]  
    ,id.[inequality_columns] AS [InEqualityColumns]  
    ,id.[included_columns] AS [IncludedColumns]  
    ,gs.[unique_compiles] AS [UniqueCompiles]  
    ,gs.[user_seeks] AS [UserSeeks]  
    ,gs.[user_scans] AS [UserScans]  
    ,gs.[last_user_seek] AS [LastUserSeekTime]  
    ,gs.[last_user_scan] AS [LastUserScanTime]  
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.  
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.  
    ,gs.[system_seeks] AS [SystemSeeks]  
    ,gs.[system_scans] AS [SystemScans]  
    ,gs.[last_system_seek] AS [LastSystemSeekTime]  
    ,gs.[last_system_scan] AS [LastSystemScanTime]  
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]  
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.  
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]  
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE  
        WHEN id.[equality_columns] IS NOT NULL  
            AND id.[inequality_columns] IS NOT NULL  
            THEN '_'  
        ELSE ''  
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE  
        WHEN id.[equality_columns] IS NOT NULL  
            AND id.[inequality_columns] IS NOT NULL  
            THEN ','  
        ELSE ''  
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]  
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]  
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)  
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]  
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]  
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]  
WHERE  db.[database_id] = DB_ID()  
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'  
ORDER BY ObjectName, [IndexAdvantage] DESC  
   
   
TSQL TO GET LIST OF DUPLICATE INDICES  
select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from  
   (select distinct object_name(i.object_id) tablename,i.name indexname,  
             (select distinct stuff((select ', ' + c.name  
                                       from sys.index_columns ic1 inner join  
                                            sys.columns c on ic1.object_id=c.object_id and  
                                                             ic1.column_id=c.column_id  
                                      where ic1.index_id = ic.index_id and  
                                            ic1.object_id=i.object_id and  
                                            ic1.index_id=i.index_id  
                                      order by index_column_id FOR XML PATH('')),1,2,'')  
                from sys.index_columns ic  
               where object_id=i.object_id and index_id=i.index_id) as columnlist  
       from sys.indexes i inner join  
            sys.index_columns ic on i.object_id=ic.object_id and  
                                    i.index_id=ic.index_id inner join  
            sys.objects o on i.object_id=o.object_id  
      where o.is_ms_shipped=0) t1 inner join  
   (select distinct object_name(i.object_id) tablename,i.name indexname,  
             (select distinct stuff((select ', ' + c.name  
                                       from sys.index_columns ic1 inner join  
                                            sys.columns c on ic1.object_id=c.object_id and  
                                                             ic1.column_id=c.column_id  
                                      where ic1.index_id = ic.index_id and  
                                            ic1.object_id=i.object_id and  
                                            ic1.index_id=i.index_id  
                                      order by index_column_id FOR XML PATH('')),1,2,'')  
                from sys.index_columns ic  
               where object_id=i.object_id and index_id=i.index_id) as columnlist  
       from sys.indexes i inner join  
            sys.index_columns ic on i.object_id=ic.object_id and  
                                    i.index_id=ic.index_id inner join  
            sys.objects o on i.object_id=o.object_id  
 where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and  
       substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and  
       (t1.columnlist<>t2.columnlist or  
         (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))  
  

Thanks

Azure SQL Database
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Alberto Morillo 34,461 Reputation points MVP
    2022-09-27T13:54:13.553+00:00

    The following query will provide you missing indexes on Azure SQL Database. Do not run the query on the master, but on each user database. The following scripts do not apply on Azure Synapse.

    SELECT CONVERT (varchar, getdate(), 126) AS runtime,  
           mig.index_group_handle,  
           mid.index_handle,  
           CONVERT (decimal (28,1),  
            migs.avg_total_user_cost *  
            migs.avg_user_impact *  
            (migs.user_seeks + migs.user_scans))  
            AS improvement_measure,  
           'CREATE INDEX missing_index_' +  
           CONVERT (varchar, mig.index_group_handle) +  
           '_' +  
           CONVERT (varchar, mid.index_handle) +  
           ' ON ' +  
           mid.statement +  
           ' (' + ISNULL (mid.equality_columns,'') +  
           CASE WHEN mid.equality_columns IS NOT NULL  
                AND mid.inequality_columns IS NOT NULL  
            THEN ','  
            ELSE ''  
            END + ISNULL (mid.inequality_columns, '') +  
            ')' +  
            ISNULL (' INCLUDE (' + mid.included_columns + ')',  
                    '') AS create_index_statement,  
            migs.*,  
        mid.database_id, mid.[object_id]  
        FROM sys.dm_db_missing_index_groups mig  
        INNER JOIN sys.dm_db_missing_index_group_stats migs  
            ON migs.group_handle = mig.index_group_handle  
        INNER JOIN sys.dm_db_missing_index_details mid  
            ON mig.index_handle = mid.index_handle  
        WHERE CONVERT (decimal (28,1),  
                       migs.avg_total_user_cost *  
                   migs.avg_user_impact *  
                  (migs.user_seeks + migs.user_scans)) > 10  
        ORDER BY migs.avg_total_user_cost *  
                 migs.avg_user_impact *  
             (migs.user_seeks + migs.user_scans) DESC  
    

    To find duplicated indexes, please execute the following query:

    ;WITH CTE_INDEX_DATA AS (  
           SELECT  
                  SCHEMA_DATA.name AS schema_name,  
                  TABLE_DATA.name AS table_name,  
                  INDEX_DATA.name AS index_name,  
                  STUFF((SELECT  ', ' + COLUMN_DATA_KEY_COLS.name + ' ' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END -- Include column order (ASC / DESC)  
      
                                      FROM    sys.tables AS T  
                                                    INNER JOIN sys.indexes INDEX_DATA_KEY_COLS  
                                                    ON T.object_id = INDEX_DATA_KEY_COLS.object_id  
                                                    INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS  
                                                    ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id  
                                                    AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id  
                                                    INNER JOIN sys.columns COLUMN_DATA_KEY_COLS  
                                                    ON T.object_id = COLUMN_DATA_KEY_COLS.object_id  
                                                    AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id  
                                      WHERE   INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id  
                                                    AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id  
                                                    AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0  
                                      ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal  
                                      FOR XML PATH('')), 1, 2, '') AS key_column_list ,  
              STUFF(( SELECT  ', ' + COLUMN_DATA_INC_COLS.name  
                                      FROM    sys.tables AS T  
                                                    INNER JOIN sys.indexes INDEX_DATA_INC_COLS  
                                                    ON T.object_id = INDEX_DATA_INC_COLS.object_id  
                                                    INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS  
                                                    ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id  
                                                    AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id  
                                                    INNER JOIN sys.columns COLUMN_DATA_INC_COLS  
                                                    ON T.object_id = COLUMN_DATA_INC_COLS.object_id  
                                                    AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id  
                                      WHERE   INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id  
                                                    AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id  
                                                    AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1  
                                      ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal  
                                      FOR XML PATH('')), 1, 2, '') AS include_column_list,  
           INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)  
           FROM sys.indexes INDEX_DATA  
           INNER JOIN sys.tables TABLE_DATA  
           ON TABLE_DATA.object_id = INDEX_DATA.object_id  
           INNER JOIN sys.schemas SCHEMA_DATA  
           ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id  
           WHERE TABLE_DATA.is_ms_shipped = 0  
           AND INDEX_DATA.type_desc IN ('NONCLUSTERED', 'CLUSTERED')  
    )  
    SELECT  
           *  
    FROM CTE_INDEX_DATA DUPE1  
    WHERE EXISTS  
    (SELECT * FROM CTE_INDEX_DATA DUPE2  
     WHERE DUPE1.schema_name = DUPE2.schema_name  
     AND DUPE1.table_name = DUPE2.table_name  
     AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))  
     AND DUPE1.index_name <> DUPE2.index_name)  
    
    2 people found this answer helpful.
    0 comments No comments

  2. BKAN 41 Reputation points
    2022-09-27T15:17:23.61+00:00

    @Alberto Morillo thanks
    I still get the same error message
    Catalog view 'dm_db_missing_index_details' is not supported in this version.

    1 person found this answer helpful.

  3. BKAN 41 Reputation points
    2022-09-27T15:46:53.457+00:00

    @Alberto Morillo yes I did run against the USER database and not the Master on the missing indices SQL, I get the below error
    Catalog view 'dm_db_missing_index_details' is not supported in this version.
    And for Duplicate one's I get the error
    Msg 103010, Level 16, State 1, Line 1
    Parse error at line: 21, column: 36: Incorrect syntax near 'FOR'.

    I think it synapse database , does that make a difference , I see it under the
    Home>Azure Synapse Analytics>market-synapse>marketpool
    does this make a difference?

    Thanks

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.