5

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
WHERE 1=1
  --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

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.

| improve this answer | |
  • 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 – Marcello Miorelli Oct 9 '15 at 17:57
  • adding top 20 does not help – Marcello Miorelli Oct 9 '15 at 18:10
3

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

| improve this answer | |
2

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()
| improve this answer | |
  • 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. – Marcello Miorelli Oct 18 '19 at 13:32
  • 1
    @MarcelloMiorelli - fair point. the Question is tagged 2014. Updated – Martin Smith Oct 18 '19 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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