#MSSQL #Research Missing Indexes

missing_indexes.sql

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (
  migs.user_seeks + migs.user_scans) AS
  improvement_measure,
  LEFT(PARSENAME(mid.statement, 1), 32) as object,
  'CREATE INDEX [XK'
  + LEFT(PARSENAME(mid.statement, 1), 32) 
  + MAX(CASE 
    WHEN ind.name LIKE '%[0-9]%' THEN 
	   substring(ind.name, PatIndex('%[0-9]%', ind.name), len(ind.name))
    ELSE '01'
   END) + ']'
  + ' ON ' + mid.statement + ' ('
  + ISNULL(mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS
      NOT NULL AND
      mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END
  + ISNULL(mid.inequality_columns, '') + ')'
  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
  + CASE
	   WHEN MIN(ind.fill_factor) IS NOT NULL THEN
		  ' WITH (FILLFACTOR = ' + CAST(MIN(ind.fill_factor) as varchar(3)) + ')'
	   ELSE ''
    END AS
  create_index_statement,
  migs.user_seeks,
  migs.avg_user_impact
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
  ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
  ON mig.index_handle = mid.index_handle
LEFT JOIN sys.indexes ind
  ON mid.object_id = ind.object_id
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (
migs.user_seeks + migs.user_scans) > 10
AND mid.statement IS NOT NULL
--AND LEFT(PARSENAME(mid.statement, 1), 32) NOT LIKE '%Usage%'
--AND LEFT(PARSENAME(mid.statement, 1), 32) NOT LIKE '%Cdr%'
AND migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (
migs.user_seeks + migs.user_scans) >= 30000
GROUP BY mid.statement, migs.avg_total_user_cost, migs.avg_user_impact, migs.user_seeks, migs.user_scans, mid.equality_columns, mid.inequality_columns, mid.included_columns
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (
migs.user_seeks + migs.user_scans) DESC