One of many actually cool issues that SQL Server does is sustain with index statistics behind the scenes. It is aware of how an index has been used, how usually it’s used, or whether it is even getting used in any respect. However SQL Server doesn’t simply monitor the indexes that you simply presently have, it even retains up with statistics for indexes that it needs you had!
What are the Missing Indexes?
When SQL Server is processing a question, it’s going to generally make a suggestion for an index that it believes will assist that question run quicker. These are often known as missing Indexes, or as I wish to check with them, missing Index Solutions. The rationale that I stress “solutions” is that whereas SQL Server is excellent at figuring out when an index is required, it generally isn’t so good at recommending what that index ought to be.
What Is Unsuitable With The Missing Index Suggestions?
When SQL Server recommends a missing index, it does so based mostly on the execution of that question at that particular cut-off date. It doesn’t consider some other elements reminiscent of present server load, or any current indexes, so you must take into account these index suggestions as simply that, suggestions. Don’t simply blindly create no matter indexes SQL Server is recommending.
How Do I Discover Missing Index Suggestions?
The under question will present missing index solutions for the required database. It pulls info from the sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_details DMVs. You too can slender it right down to a specified desk by un-commenting the AND assertion and specifying the desk title. This script additionally generates a CREATE INDEX script for every report, so as to take a greater take a look at the index and resolve if you wish to use it. I don’t keep in mind the place I initially bought this script, I believe it could be based mostly off of Glenn Berry’s Diagnostic Script.
USE YourDatabase GO 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);
It’s not unusual to see many related suggestions, often with the identical columns that fluctuate barely so as or between an embrace assertion. This goes again to what I mentioned earlier about SQL Server solely contemplating indexes for a question at that particular cut-off date. Seeing a number of suggestions for a similar index that solely fluctuate barely is an effective indicator that a number of queries may gain advantage from an index.
It’s essential to say that these missing Index statistics get reset each time SQL Server is restarted, so if you happen to’ve lately rebooted the server chances are you’ll not have many index stats amassed but.
Additionally, if you happen to’re involved in code that can Persist And Aggregate Index Statistics in your server, check out my SQL Server Metrics Pack venture that’s obtainable on GitHub.