Find the Missing Indexes in SQL Server Database

Missing Index Analysis in SQL Server

I am not here to describe what is index and what is its impact on SQL Server. 

There will be missing indexes in any SQL Server database if proper indexes are not created or any application related queries are deployed in the system.

Below Query helps in finding the missing indexes of a particular database.

Use [DBNAME]
GO
SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,Ind.[name] AS IndexName,SUBSTRING(
( SELECT ', ' + AC.name FROM sys.[tables] AS T INNER JOIN sys.[indexes] I 
ON T.[object_id] = I.[object_id] 
INNER JOIN sys.[index_columns] IC  ON I.[object_id] = IC.[object_id] AND 
 I.[index_id] = IC.[index_id] INNER JOIN sys.[all_columns] AC ON 
T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] WHERE 
Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND 
IC.is_included_column = 0
ORDER BY IC.key_ordinal 
FOR  XML PATH('')), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name FROM sys.[tables] AS T INNER JOIN sys.[indexes] I 
ON T.[object_id] = I.[object_id]  INNER JOIN sys.[index_columns] IC  
ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC 
ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id 
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR  XML PATH('')), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id] 
INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
ORDER BY TableName
GO


Above query will give the proposed indexes on the database to improve the performance. 

Confused how to put the indexes in create statement syntax. Here is the simplest way to create all the missing indexes in one shot.

Below Query will help in finding the missing indexes and create the syntax as well.

--Create Missing Indexes---
-----------------------------------
SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
    ,db.[database_id] AS [DatabaseID]
    ,db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,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]
    ,gs.[avg_user_impact] AS [AvgUserImpact]
    ,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]
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IXNC_' + 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 id.[database_id] = 6 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

Being a DBA, my suggestion is to go through the proposed indexes and analyze whether it will seriously help or not. More indexes may kill the performance some times. Pay double attention when doing this activity.

Get back to me in case of the script doesn't work or if needed help :)