Posted By

g8rpal on 10/05/10


Tagged

index suggest


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

bobbym245
Tyster


Suggest Indexes to Add


 / Published in: SQL
 

  1. DECLARE @IndexFilegroupName VARCHAR(200)
  2. SET @IndexFilegroupName = 'PRIMARY'
  3.  
  4. SELECT
  5. avg_total_user_cost,
  6. avg_user_impact,
  7. --mid.database_id ,
  8. --mid.object_id ,
  9. mid.equality_columns ,
  10. mid.inequality_columns ,
  11. mid.included_columns ,
  12. mid.statement AS [TABLE],
  13. user_scans,
  14. user_seeks,
  15. last_user_seek,
  16. last_user_scan,
  17. 'CREATE NONCLUSTERED INDEX ix_' + OBJECT_NAME(mid.object_id) + '_'
  18. + ISNULL(REPLACE(REPLACE(REPLACE(equality_columns,'[',''),']',''),',',''),'') + ISNULL(REPLACE(REPLACE(REPLACE(inequality_columns,'[',''),']',''),',',''),'') + CASE WHEN included_columns IS NOT NULL THEN '_inc' ELSE '' END
  19. + ' ON ' + mid.statement
  20. + ' (' + REPLACE(ISNULL(equality_columns,'') + ISNULL(inequality_columns,''),'][','],[') + ')'
  21. + CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' ELSE '' END
  22. + ' ON [' + @IndexFilegroupName + '];'
  23. AS statement
  24. FROM sys.dm_db_missing_index_group_stats AS migs
  25. INNER JOIN sys.dm_db_missing_index_groups AS mig
  26. ON (migs.group_handle = mig.index_group_handle)
  27. INNER JOIN sys.dm_db_missing_index_details AS mid
  28. ON (mig.index_handle = mid.index_handle)
  29. ORDER BY 1 DESC

Report this snippet  

You need to login to post a comment.