Problem Statement: Script to find the missing indexes using 3 different ways.
Solution: 1) Find missing indexes using DMVs
--Find top ten missing indexes using following DMV SELECT TOP 10 ( user_seeks ) * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage],--%advantage in the query migs.last_user_seek, --Date and time of last seek caused by user queries that the recommended index in the group could have been used for. mid.[statement] AS [Database.Schema.Table], 'Create index idx_' + Parsename( mid.[statement], 1) + '_' + Cast(row_number() OVER(ORDER BY (user_seeks) * avg_total_user_cost * ( avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) + ' on ' + Parsename( mid.[statement], 1) + '(' ++ equality_columns + ')' + CASE WHEN mid.included_columns IS NOT NULL THEN ' include ('+ mid.included_columns+')' ELSE '' END create_index, 'drop index idx_' + Parsename( mid.[statement], 1) + '_' + Cast(row_number() OVER(ORDER BY (user_seeks) * avg_total_user_cost * ( avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) + ' on ' + Parsename( mid.[statement], 1) drop_index, mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, -- Average cost of the user queries that could be reduced by the index in the group. migs.avg_user_impact --Average percentage benefit that user queries could experience, 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 WHERE mid.database_id = Db_id() -- Remove this to see for entire instance ORDER BY index_advantage DESC OPTION (RECOMPILE);
2) Find missing indexes on FK
SELECT fk.NAME AS CONSTRAINT_NAME,
s.NAME AS SCHEMA_NAME,
o.NAME AS TABLE_NAME,
fkc_c.NAME AS CONSTRAINT_COLUMN_NAME
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS fkc_c
ON fkc.parent_object_id = fkc_c.object_id
AND fkc.parent_column_id = fkc_c.column_id
LEFT JOIN sys.index_columns ic
JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
ON fkc.parent_object_id = ic.object_id
AND fkc.parent_column_id = ic.column_id
JOIN sys.objects AS o
ON o.object_id = fk.parent_object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE c.NAME IS NULL
3) Find missing indexes using stored query plans.
SELECT MissingIndexNode.value('(MissingIndexGroup/@Impact)[1]', 'float') AS impact, Object_name(sub.objectid, sub.dbid) AS calling_object_name, MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Table)[1]', 'VARCHAR(128)') AS table_name, Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') FROM MissingIndexNode.nodes ( 'MissingIndexGroup/MissingIndex/ ColumnGroup[@Usage=''EQUALITY'']/Column' ) AS t ( c ) FOR XML PATH('')), 1, 1, '') AS equality_columns, Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') FROM MissingIndexNode.nodes ( 'MissingIndexGroup/MissingIndex/ ColumnGroup[@Usage=''INEQUALITY'']/Column' ) AS t ( c ) FOR XML PATH('')), 1, 1, '') AS inequality_columns, Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') FROM MissingIndexNode.nodes ( 'MissingIndexGroup/MissingIndex/ ColumnGroup[@Usage=''INCLUDE'']/Column' ) AS t ( c ) FOR XML PATH('')), 1, 1, '') AS include_columns, sub.usecounts AS qp_usecounts, sub.refcounts AS qp_refcounts, qs.execution_count AS qs_execution_count, qs.last_execution_time AS qs_last_exec_time, qs.total_logical_reads AS qs_total_logical_reads, qs.total_elapsed_time AS qs_total_elapsed_time, qs.total_physical_reads AS qs_total_physical_reads, qs.total_worker_time AS qs_total_worker_time, StmtPlanStub.value('(StmtSimple/@StatementText)[1]', 'varchar(8000)') AS statement_text FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY qs.plan_handle ORDER BY qs.statement_start_offset ) AS StatementID, qs.* FROM sys.dm_exec_query_stats qs) AS qs JOIN (SELECT x.query('../../..') AS StmtPlanStub , x.query('.') AS MissingIndexNode, x.value('(../../../@StatementId)[1]', 'int') AS StatementID, cp.*, qp.* FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp CROSS APPLY qp.query_plan.nodes ( '/ShowPlanXML/BatchSequence/ Batch/Statements/StmtSimple/ QueryPlan/MissingIndexes/ MissingIndexGroup' ) mi ( x )) AS sub ON qs.plan_handle = sub.plan_handle AND qs.StatementID = sub.StatementID
