Work With Execution Plans in Cache

Find query plan for a query

Use this query if you like find info about cached execution plans. It display the query text as well as the showplanxml that can bee viewed as a grafical execution plan in management studio.


SELECT
    c.usecounts
    ,c.cacheobjtype
    ,c.objtype
    ,t.text
    ,x.query_plan
    ,qs.creation_time
    ,qs.last_execution_time
    ,qs.max_elapsed_time
    ,qs.max_logical_reads
    ,qs.total_elapsed_time/qs.execution_count AS [Average_elapsed_time]
FROM
    sys.dm_exec_cached_plans c
    JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = c.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
    CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) x
WHERE
    t.text LIKE '%' -- Put the query text you are serching for here.
    --and c.objtype = 'Prepared'
OPTION(MAXDOP 1, RECOMPILE);

How many times are the plan used

Sometimes it is useful to se how many times a query plan is used. For instance if you consider to use the “Optimize for Ad Hoc workload” setting. This query gives you information about how many stored plans that are used only ones.

SELECT
    c.objtype AS [CacheType]
    , COUNT_BIG(*) AS [Total Plans]
    , SUM(CAST(c.size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
    , SUM(CAST(c.usecounts AS BIGINT)) AS [Total Use Count]
    , AVG(CAST(c.usecounts AS BIGINT)) AS [Average Use Count]
    , SUM(CAST((CASE WHEN c.usecounts = 1 THEN c.size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
    , SUM(CASE WHEN c.usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM
	sys.dm_exec_cached_plans c
GROUP BY c.objtype
ORDER BY [Total MBs - USE Count 1] DESC

Index usage

This is a useful script when you have thoughts about what queries is using a specific index.

DECLARE @IndexName AS NVARCHAR(128)
SET @IndexName = '[My_Index]'; -- Searched index name quoted

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
  c.usecounts
  ,c.cacheobjtype
  ,c.objtype
  ,t.text                                     AS query_text
  ,x.query_plan
  ,qs.creation_time
  ,qs.last_execution_time
  ,qs.max_elapsed_time
  ,qs.max_logical_reads
  ,qs.total_elapsed_time / qs.execution_count AS [Average_elapsed_time]
FROM
  sys.dm_exec_cached_plans AS c
  JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = c.plan_handle
  CROSS APPLY sys.Dm_exec_query_plan(c.plan_handle) AS x
  CROSS APPLY x.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements') AS batch(stmt)
  CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
  CROSS APPLY sys.Dm_exec_sql_text(c.plan_handle) t
OPTION(MAXDOP 1, RECOMPILE);