March 7, 2016 at 1:22 pm
SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC OPTION (RECOMPILE);
The following query for missing index gives this output.
index_advantage
37.99
equality_columns
[User_ID]
inequality_columns
NULL
included_columns
NULL
user_seeks
1378
avg_user_impact
55.37
Table Name
Employee
Which index should I add?
Thanks!
March 7, 2016 at 2:52 pm
It's saying to create an index on the "equality_column", however you have to test and also take into consideration any indexes that already exist (I.E. index consolidation).
March 7, 2016 at 3:40 pm
Thanks, what is inequality columns.
March 7, 2016 at 4:44 pm
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply