Missing indexes

[Almeonamy]

Hello. Can you describe how the "missing index" feature works? How it finds candidates for index building?

[sergiisyrovatchenko]

Hi. I am using an standard functionality: sys.dm_db_missing_index_group_stats and show only indexes which applied by (i.UserImpact * i.TotalReads) / MAX(i.UserImpact * i.TotalReads) OVER() condition

Try to use this code:

IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
    DROP TABLE #Indexes

SELECT ObjectID     = d.[object_id]
     , UserImpact   = gs.[avg_user_impact]
     , TotalReads   = gs.[user_seeks] + gs.[user_scans]
     , TotalSeeks   = gs.[user_seeks]
     , TotalScans   = gs.[user_scans]
     , LastUsage    = ISNULL(gs.[last_user_scan], gs.[last_user_seek])
     , IndexColumns =
                CASE
                    WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL
                        THEN d.[equality_columns] + ', ' + d.[inequality_columns]
                    WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL
                        THEN d.[equality_columns]
                    ELSE d.[inequality_columns]
                END
     , IncludedColumns = d.[included_columns]
INTO #Indexes
FROM sys.dm_db_missing_index_groups g WITH(NOLOCK)
JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle]
JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle]
WHERE d.[database_id] = DB_ID()

SELECT i.ObjectID
     , ObjectName    = o.[name]
     , SchemaName    = s.[name]
     , Fragmentation = CAST(100. * (i.UserImpact * i.TotalReads) / MAX(i.UserImpact * i.TotalReads) OVER() AS FLOAT)
     , i.TotalReads
     , i.TotalSeeks
     , i.TotalScans
     , i.LastUsage
     , i.IndexColumns
     , i.IncludedColumns
FROM #Indexes i
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID
JOIN sys.schemas s WITH(NOLOCK) ON o.[schema_id] = s.[schema_id]