Posted By

tvanzele on 04/12/10


Tagged

sql DMV


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

g8rpal
Tyster
gdelgado


Display Missing Indexes of the Current DB ordered by Index Advantage


 / Published in: SQL
 

  1. -- Missing Indexes current database by Index Advantage
  2. SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
  3. migs.last_user_seek, mid.[statement] AS [DATABASE.Schema.TABLE],
  4. mid.equality_columns, mid.inequality_columns, mid.included_columns,
  5. migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
  6. FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
  7. INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
  8. ON migs.group_handle = mig.index_group_handle
  9. INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
  10. ON mig.index_handle = mid.index_handle
  11. WHERE mid.database_id = DB_ID()
  12. ORDER BY index_advantage DESC;

Report this snippet  

You need to login to post a comment.