I have the following query that shows me what is cached in sql server:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
  --AND OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
  AND query_plan IS NOT NULL
ORDER BY ExecutionCount DESC

enter image description here

Is there a way I could query the queryPlan field looking for missing indexes?

3 Answers 3


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

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.


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.

  • I have some servers, involved in replication (subscribers) that when I run this script it takes so long... I have always had to stop the query. I check there is nothing blocking anything... is this a known fact? or it is just me? talking about sql 2014 here, over 100 databases, not sure memory and disks but I could check next week Oct 9, 2015 at 17:57
  • adding top 20 does not help Oct 9, 2015 at 18:10

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.

   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
  ,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'       
      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...

  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.

XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    ,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


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)

    (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),
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()
  • I loved it, just some of us will be in SQL Server 2016 for still a long time, and STRING_AGG is not available there. Oct 18, 2019 at 13:32
  • 1
    @MarcelloMiorelli - fair point. the Question is tagged 2014. Updated Oct 18, 2019 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.