looking for missing indexes in the cached execution plans
You can try to execute below script and find for missing indexes in cached execution plans from here
SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU
, total_elapsed_time/execution_count AS AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC
It has sort of worked for me in sql server 2008 and 2012, but still you will need to check if that works the way you looking in sql server 2014.
Also,
In addition to above i would suggest you read Finding what queries in the plan cache use a specific index , a great script from Jonathan, which really helps in understanding the usage of those indexes getting used for the current execution plans in cache.
If I'm looking for missing indexes by query in a batch (rather than by the whole batch itself), I prefer to use sys.dm_exec_text_query_plan() rather than sys.dm_exec_query_plan. This takes the statement_start_offset and statement_end_offset to return the plan for an actual query, rather than the batch (e.g. Stored Procedure or Function) as a whole.
CREATE TABLE #query_cache
(
PlanHandle VARBINARY(64),
DatabaseName VARCHAR(255),
SchemaName VARCHAR(50),
ObjectName VARCHAR(50),
ExecutionCount BIGINT,
StatementText NVARCHAR(MAX),
StatementStart BIGINT,
StatementEnd BIGINT,
QueryPlan XML
);
Firstly, build your universe of queries in your cache, along with their plan_handle, statement_start_offset and statement_end_offset`. This takes everything that has executed within the last day.
SELECT
deqs.plan_handle AS PlanHandle
,DB_NAME(CAST(depa.value AS SMALLINT)) AS DatabaseName
,OBJECT_SCHEMA_NAME(dest.objectid, CAST(depa.value AS INT)) AS SchemaName
,OBJECT_NAME(dest.objectid, CAST(depa.value AS INT)) AS ObjectName
,SUM(deqs.execution_count) AS ExecutionCount
,Q.statementtext
,MAX(statement_start_offset) statement_start_offset
,MAX(statement_end_offset) statement_end_offset
INTO #query_cache
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS depa
CROSS APPLY (VALUES (SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END - deqs.statement_start_offset)/2)+1))) AS Q(statementtext)
WHERE deqs.last_execution_time > DATEADD(DAY, -1, GETDATE())
AND depa.attribute = 'dbid'
GROUP BY
dest.text, Q.statementtext, deqs.plan_handle, dest.objectid, depa.value;
Unfortunately, as dm_exec_text_query_plan returns the plan as an NVARCHAR(MAX), we have an intermediate step here to run a TRY_CAST() to XML (as you're on 2012+). Apparently there are occasions where the NVARCHAR will not cast to XML, so if you're automating this in a Production environment, it's better to avoid breaking something.
This bit is a little slow...
UPDATE qc
SET QueryPlan = TRY_CAST(detqp.query_plan AS XML)
FROM #query_cache AS qc
CROSS APPLY sys.dm_exec_text_query_plan(PlanHandle, qc.StatementStart, qc.StatementEnd) AS detqp
WHERE qc.StatementText IS NOT NULL;
You can then use your cache to get a number of different metrics, such as Convert Warnings, Missing Indexes and Key Lookups.
WITH
XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
DatabaseName
,SchemaName
,ObjectName
,ExecutionCount
,StatementText
,qc.QueryPlan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/*/@StatementType)[1]', 'varchar(50)') StatementType
,qc.QueryPlan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/*/@StatementOptmEarlyAbortReason)[1]', 'varchar(50)') StatementOptmEarlyAbortReason
,qc.QueryPlan.value('count(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/Warnings/PlanAffectingConvert)', 'int') ConvertWarnings
,qc.QueryPlan.value('count(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/Warnings/NoJoinPredicate)', 'int') NoJoinPredicateWarnings
,qc.QueryPlan.value('count(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex)', 'int') MissingIndexes
,qc.QueryPlan.value('count(.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]])', 'int') KeyLookups
FROM #query_cache AS qc;
Possible improvements for this:
Filter out specific databases like
masterormsdb.Automate the gathering process to collect on a regular basis
Filter to only return the top x queries by IO/CPU/Duration
I had a similar need recently.
The answer by KASQLDBA just pulls out the first missing index from the plan. There could be multiple.
To that end I ended up using the following (I wanted the info at stored procedure level - use sys.dm_exec_query_stats instead of sys.dm_exec_procedure_stats if this is not desired)
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT [Procedure] = quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id)),
Impact = MissingIndexGroup.n.value('@Impact', 'float'),
[Database] = MissingIndex.n.value('@Database', 'nvarchar(130)'),
[Schema] = MissingIndex.n.value('@Schema', 'nvarchar(130)'),
[Table] = MissingIndex.n.value('@Table', 'nvarchar(130)'),
EqualityColumns = SUBSTRING(eqColumns.list,2,8000),
InEqualityColumns = SUBSTRING(ineqColumns.list,2,8000),
IncludedColumns = SUBSTRING(incColumns.list,2,8000),
qp.query_plan
FROM sys.dm_exec_procedure_stats ps
cross apply sys.dm_exec_query_plan(ps.plan_handle) qp
cross apply qp.query_plan.nodes('//MissingIndexes/MissingIndexGroup') MissingIndexGroup(n)
cross apply MissingIndexGroup.n.nodes('MissingIndex') MissingIndex(n)
cross apply (SELECT ',' + n.n.value('@Name','nvarchar(130)') FROM MissingIndex.n.nodes('./ColumnGroup[@Usage="EQUALITY"]/Column') n(n) FOR XML PATH('')) eqColumns(list)
cross apply (SELECT ',' + n.n.value('@Name','nvarchar(130)') FROM MissingIndex.n.nodes('./ColumnGroup[@Usage="INEQUALITY"]/Column') n(n) FOR XML PATH('')) ineqColumns(list)
cross apply (SELECT ',' + n.n.value('@Name','nvarchar(130)') FROM MissingIndex.n.nodes('./ColumnGroup[@Usage="INCLUDE"]/Column') n(n) FOR XML PATH('')) incColumns(list)
where ps.database_id = db_id()