Index plays an important role in performance tuning. A proper index can improve the performance and a bad index can hamper the performance.
What Are Missing Indexes?
SQL Server keeps index statistics behind the scenes. It knows how an index has been used, how often it is used, or if it is even being used at all. But SQL Server doesn’t just monitor the indexes that we currently have on a database, it even keeps up with statistics for indexes that it wishes we had!
When SQL Server is processing a query, it makes a suggestion for an index that it believes will help that query run faster. These are known as Missing Index Suggestions.
Guidance and Recommendations
When SQL Server recommends a missing index, it does so based on the execution of that query at that specific point in time. It doesn’t take into consideration any other factors such as current server load, or any existing indexes, so you should consider these index recommendations as just that, recommendations. This is just for guidance. Please note that you should not create all the missing indexes this script suggests.
- Do not just blindly create whatever indexes SQL Server is recommending without any prior analysis.
- You should not create more than 10 indexes per table.
- The script is a good starting point. You should pay attention to Average User Impact and Average Total User Cost when you are going to create an index.
Script
The below query will show missing index suggestions for the specified database. It pulls information from the [sys].[dm_db_missing_index_groups] [sys].[dm_db_missing_index_group_stats] and [sys].[dm_db_missing_index_details] DMVs.
You can also narrow it down to a specified table by un-commenting the WHERE clause statement and specifying the table name. This script also generates a CREATE INDEX script for each record, so that you can take a better look at the index and decide if you want to use it.
Here is the script, which we can use to identify missing indexes on a database.
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
OPTION (RECOMPILE);
Note: – It’s important to mention that these Missing Index statistics get reset every time SQL Server is restarted, so if you’ve recently rebooted the server you may not have many index stats accumulated yet.
References
Welcome to our SQL World! 