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 master or msdb.
Automate the gathering process to collect on a regular basis
Filter to only return the top x queries by IO/CPU/Duration
