Viewing the SQL Server Procedure Cache with DMVs

In the previous blog, we took a quick look at query plan caching using the classic but still serviceable syscacheobjects view. A serious journey of exploration into the SQL procedure cache must, however, begin with the dynamic management view sys.dm_exec_cached_plans. While this view does provide us with some interesting information in its own right, the most critical piece of information is the plan handle.

SELECT *

FROM sys.dm_exec_cached_plans cp

SELECT cp.plan_handle

, cp.size_in_bytes

, cp.cacheobjtype

, cp.objtype

FROM sys.dm_exec_cached_plans cp

Result from sys.dm_exec_cached_plans view

Note that sys.dm_exec_cached_plans do not include database id.

The plan handle for a query is our entry into other functions which provide detailed performance information. Even by itself, though, this view allows you to observe how much memory is being taken up by cached plans.

SELECT objtype

, SUM(size_in_bytes) AS size_in_bytes

FROM sys.dm_exec_cached_plans

GROUP BY objtype

The Plan Handle

Once we have obtained a plan handle or a set of plan handles, we can get additional information about the cached plans by providing the handle as input to a dynamic management function. Of course, this means that we must use CROSS APPLY in our query, since we will be joining a view to one or more functions, rather than joining two or more views.

Getting the SQL Command Text

The columns returned by the function sys.dm_exec_sql_text include “text”, the actual SQL command text.

SELECT cp.plan_handle

, cp.size_in_bytes

, cp.cacheobjtype

, cp.objtype

, txt.*

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) txt

sys.dm_exec_sql_text returns the database id among other columns, so we can filter for a specific database if we wish.

Examining the Cached Plan Itself

Using sys.dm_exec_query_plan you can obtain an execution plan for anything in the cache. The function returns an XML plan. The XML appears in the results as a column of blue text; clicking on the XML text in the column launches a window with a graphical view just as with query plans generated on-the-spot when running a query in the Management Studio.

SELECT txt.text

, qp.query_plan

, cp.size_in_bytes

, cp.cacheobjtype

, cp.objtype

, cp.plan_handle

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) txt

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

Query Execution Stats

sys.dm_exec_query_stats provides straightforward summary statistics on the execution of queries. Execution times are presented differently for memory-optimized tables, but that need not concern us here. Times presented in microseconds are accurate only to milliseconds. Here is a simple example reporting the execution count for query plans in the cache:

Obtaining Information about Query Plan Attributes

sys.dm_exec_plan_attributes requires a little special handling. Each attribute of a particular plan has its own separate line in the function output table, making it impossible to, say, filter by database id. It is therefore common to use sys.dm_exec_plan_attributes in conjunction with the PIVOT operator to turn row data representing different attributes into more easily managed separate columns.

The “raw” query without using PIVOT:

SELECT cp.plan_handle, epa.attribute, epa.value

FROM sys.dm_exec_cached_plans cp

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa

WHERE cacheobjtype = ‘Compiled Plan’

While the PIVOT operator can sometimes be challenging, it is quite straightforward when used for the sys.dm_exec_plan_attributes function. The name of the attribute you want goes in the IN list, and the value column is provided as the argument to any aggregate function other than COUNT in the PIVOT expression. The example shown below creates a column for the dbid attribute value.

SELECT plan_handle, pvt.dbid, pvt.sql_handle

FROM (

SELECT plan_handle, epa.attribute, epa.value

FROM sys.dm_exec_cached_plans

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa

WHERE cacheobjtype = ‘Compiled Plan’) AS ecpa

PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN (“dbid”, “sql_handle”)) AS pvt

Some plan attributes, like the database id, dbid, are self-explanatory. Others, however, require special techniques. For example, SQL Server must track many session level parameters that are set when a query executes, including core ANSI standard parameters such as ARITHABORT and CONCAT_NULL_YIELDS_NULL. In my opinion, there is rarely a good reason to deviate from the standard settings, but such deviations can and do occur.

SELECT plan_handle, pvt.set_options, pvt.sql_handle

FROM (

SELECT plan_handle, epa.attribute, epa.value

FROM sys.dm_exec_cached_plans

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa

WHERE cacheobjtype = ‘Compiled Plan’) AS ecpa

PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN (“set_options”, “sql_handle”)) AS pvt

The dynamic management function returns values for many SET options, but it returns them in one single numeric value. Whether an individual session parameter is ON or OFF is indicated by setting the corresponding bit in this single value to one. This is easy to test with bitwise logic. For example, the ARITHABORT corresponds to the 13th bit, i.e. 4096. For reasons known only in the rainy pacific northwest, the datatype of the set_options attribute is an sql_variant and must be cast as an integer.

SELECT plan_handle

, pvt.set_options

, IIF(CAST(pvt.set_options AS INT) & 4096 > 0, ‘ON’,‘OFF’) AS ARITHABORT

, pvt.sql_handle

FROM (

SELECT plan_handle, epa.attribute, epa.value

FROM sys.dm_exec_cached_plans

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa

WHERE cacheobjtype = ‘Compiled Plan’) AS ecpa

PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN (“set_options”, “sql_handle”)) AS pvt

Quick Review:

Below is a list of views and functions that provide information about cached plans.

sys.dm_exec_cached_plans

A view which provides the plan handle necessary for accessing other dynamic management functions

sys.dm_exec_plan_attributes

Provides details information about the attributes and execution environment for plans

sys.dm_exec_query_plan

Provides the XML plan which can be graphically viewed in the Management Studio

sys.dm_exec_query_stats

A view introduced in 2012 for many key performance stats for queries, including execution count

sys.dm_exec_sql_text

Provides the SQL text of the query plan and can be used to filter for specific queries

Conclusion

Once we have mastered some less-common query structures, execution-related dynamic management views and functions provide a convenient and informative method for examining the properties of cached query plans. The DMVs provide substantially more information than the classic compatibility views. One of the most important advantages of this technique is that it permits us to explore the plans cached for queries that we ourselves have not run in the Management Studio. Indeed, we shall use this capability to examine some of the characteristics of queries generated automatically by client programs using .NET LINQ.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.