2.0 Sql_Handle and Plan_Handle Explained

Article
01/09/2007

2.1 What is a Plan_Handle

Cached compiled plans are stored in the SQLCP or the OBJCP cache stores. A cached compiled plan can be retrieved from either of these cache stores using the plan_handle of the compiled plan. The plan_handle is a hash value derived from the compiled plan of the entire batch. It is therefore guaranteed to be unique for every compiled plan and can serve as an identifier for compiled plans. It is important to note a couple of points here:

1. Since the compiled plan is generated for the entire batch, the plan_handle too is an identifier of the compiled plan for the entire batch. Individual statements in the batch have query plans associated with them and the compiled plan can be thought of as a container for all these query plans. When the compiled plan for a batch is removed from the cache, the query plans for the queries/statements in the batch are also deleted. The query plan for individual statements in the batch can be retrieved using the plan_handle and statement offsets as demonstrated later in this section.

2. The plan_handle for a compiled plan remains the same even if one or more statements in the batch get recompiled.

2.2 What is a Sql_Handle

While the compiled plans are stored in the two cache stores described above, the actual sql text of the batch is stored in the SQL Manager Cache (SQLMGR). Like a compiled plan, the sql text is stored per batch, including the comments, in its entirety. The sql text cached in the SQLMGR cache can be retrieved using the sql_handle. The sql_handle contains the MD5 hash of the entire batch text and is guaranteed to be unique for every batch. Therefore the sql_handle serves as an identifier for the batch text in the SQLMGR cache.

There are some distinctive aspects of the SQLMGR cache that sets it apart from other caches:

1. While the SQLMGR cache does contribute to system memory pressure, it is not subject to the common cache store entries eviction algorithms. In other words the SQLMGR cache is not built on the same framework as the other CP cache stores.

2. There is only one global SQLMGR cache but there are as many top level memory objects (to allocate entries in this cache) as there are schedulers on the machine. This results in better data locality.

3. Memory is allocated for the SQLMGR cache from the buffer pool. The query below can be used to find the size of the SQLMGR cache:

select sum(bytes_used) as total_bytes_used

from sys.dm_os_memory_objects where type = 'MEMOBJ_SQLMGR'

go

In SQL Server 2005, there is no mechanism to view the contents of the SQLMGR cache.

2.3 Sql_Handle: Plan_Handle :: 1: N

While the sql_handle for every batch text is unique, the plan_handle need not be unique. This results in a 1:N relation between sql_handles and plan_handles. Such a condition occurs when the cache key for the compiled plans is different. This may occur due to change in set options between two executions of the same batch.

As an example consider the batch below: the only difference between 2 consecutive executions of this batch is the set options i.e. set quoted_identifier is on/off.

set quoted_identifier off

---this is an example for sql_handles, plan_handles interaction

if exists(select col1 from t1 where col2 > 5) declare @x int;

set quoted_identifier on

---this is an example for sql_handles, plan_handles interaction

if exists(select col1 from t1 where col2 > 5) declare @x int;

Use the following query to look to get the sql_handle, plan_handle and the sql text of the batch:

select st.text, qs. sql_handle, qs.plan_handle

from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st

There will be 2 entries with the same text and sql_handle, but different plan handles as below:

Text

Sql_handle

Plan_handle

---this is an example for sql_handles, plan_handles interaction if exists(select col1 from t1 where col2 > 5) declare @x int;

0x020000003

46A991F313A

868044F8006

18391CA450F

24C927

0x060001003

46A991FC844

65060000000

00000000000

000000

---this is an example for sql_handles, plan_handles interaction if exists(select col1 from t1 where col2 > 5) declare @x int;

0x020000003

46A991F313A

868044F8006

18391CA450F

24C927

0x060001003

46A991FC8C4

42060000000

00000000000

000000

What this effectively means is that we have two plans corresponding to the same batch, and it under scores the importance of making sure that the plan cache affecting set options are the same when the same queries are executed repeatedly for plan re-use. A common pitfall that most don’t realize is that different clients may have different default values for the set options. For example, OSQL (ODBC Driver) sets quoted_identifier off for every connection, while SQL Server Management Studio (ADO.NET) sets quoted_identifier on. Executing the same queries from these two clients will result in multiple plans (as described in the example above).

We have a strict 1:N relationship of sql_handles and plan_handles. The life time of entries in the SQLMGR cache is tied to the association of the sql_handle to at least one plan_handle. Plans (and therefore plan_handles) may be removed from the CP cache stores either due to memory pressure or because the object associated with the object cached plan is dropped. When the last plan_handle associated with a sql_handle is destroyed, the entry in the SQLMGR cache is also deleted.

2.4 Plan_Handle, Sql_Handle and Plan Cache DMV’s

In the following section we will demonstrate how the sql_handle and plan_handle can be used to retrieve useful information from the plan cache related DMVs and diagnose performance problems.

2.4.1 Sys.dm_exec_cached_plans

The plan_handle can be obtained from sys.dm_exec_cached_plans DMV which has one row per cached query plan. In addition to the plan_handle this DMV has other useful information regarding the cached query plan like:

Number of times the query plan has been used (usecounts)
Number of bytes consumed by this cache object (size_in_bytes)
Type of the cache object i.e. if it’s a compiled plan, or a parse tree or an xproc (cacheobjtype)
Memory address of the cache object (memory_object_address). The memory_object_adress of the cache object can used to get the memory breakdown of the cache object as demonstrated in the previous section.

Note that this DMV does not have the batch text associated with a compiled plan. The batch text can be retrieved by passing the plan_handle retrieved from sys.dm_exec_cached_plans to sys.dm_exec_sql_text as a parameter. The query below gives us the plan_handle, usecounts, size_in_bytes of the compiled plan, cacheobjtype, the compiled plan memory object address and the entire batch text:

select st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, cp.memory_object_address

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

order by cp.usecounts desc

If you order the result set by size_in_bytes and observe that the cache has just a few large plans with high usecounts, then this may be due to the following reasons:

a) The batch may contain a large number of statements.

b) The query plan for one of the queries in the multi-statement batch is very large.

c) The compiled plan may have a large number of concurrent users increasing the cacheable execution time memory.

d) The plan may be currently executing and the query plan for the currently executing statement in the batch my require a large amount of memory to execute. This exeuction memory is allocated from the cache stores, and is partially returned to the buffer pool after query execution. Therefore the plan would have a siginificantly larger size during execution than when its not being executed.

Now use the procedure PlanBreakdown and function ComplPlanDetails as described in this posting to get size and composition of the compiled plan memory object. The PlanDetails table contains one row per memory object in each plan. The PlanSummary table gives the mxc_count, xstmt_count, cstmt_count and cursor_count. The checklist below details how to interpret the data in the tables PlanDetails and PlanSummary:

a) If a plan has a small xstmt_count and large aggregate size, then examine the PlanDetails table to see if one XStmt in particular is consuming more memory than the others or if the memory is evenly distributed.

b) An mxc_count of greater than one indicates concurrent use of the plan.

c) The xstmt_count in the total will give some indication on the usage pattern of the plan. If the xstmt_count is low, it indicates many statements being executed concurrently versus a high xstmt_count which indicates one statement being executed by a large number of users.

d) An xstmt_count greater than the cstmt_count indicates concurrent use of some statements within the plan.

e) A large cursor_count may indicate a leaked cursor (opening cursors but failign to close them).

f) If a plan with a large number of CStmts is causing performance degradation, it should be examined to see if they can be split into multiple batches or if they can be rewriten using stored procedures.

2.4.2 Sys.dm_exec_sql_text

Sys.dm_exec_sql_text takes either a sql_handle or a plan_handle as a parameter and returns sql text associated with the cached plan. In other words the parameter to this DMF can be any of the following: sql_handle for adhoc sql, sql_handle for object, sql_handle for an XP, plan_handle for TSQL module (triggers, functions, procedures), or plan_handle to a CLR compiled plan. The DMF resolves the supplied handle appropriately, and if the supplied handle was for a compiled plan or prochdr then sensitive information like passwords are blocked in the returned sql text. The DMF text column supplies the entire batch text for dynamic sql and for modules like triggers, procedures, functions it gives the module definition.

Sys.dm_exec_sql_text is useful in quickly identifying identical batches that may have different compiled plans due to several factors like set options differences (as demonstrated above section).

2.4.3 Sys.dm_exec_cached_plan_dependent_objects

The plan_handle of a compiled plan can also be passed to sys.dm_exec_cached_plan_dependent_objects. This DMF returns a table with one row for every dependant object of the compiled plan i.e. it returns one row for each MXC and cursor. If the supplied plan_handle is not for a compiled plan then the DMF returns NULL. The query below gets the dependant objects for all compiled plans, their usecounts and their memory_object_address:

select st.text, cp.plan_handle, do.usecounts, do.cacheobjtype,

do.memory_object_address

from sys.dm_exec_cached_plans cp

cross apply

sys.dm_exec_sql_text(cp.plan_handle) st

cross apply

sys.dm_exec_cached_plan_dependent_objects(cp.plan_handle) do

Consider the example below where the stored procedure is executed simultaneously from 10 connections:

create procedure p1

begin

declare @x int;

set @x = 0;

while (@x < 10000000)

set @x = @x + 1;

select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 here t1.col1 = 50000;

end

Now query sys.dm_exec_cached_plans to retrieve the usecounts, memory_object_address and plan_handle information for the compiled plan:

Text

Plan_handle

Memory_

Object_

Address

Usecounts

create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end

0x050001003D

299E5FB8A193

070000000000

000000000000

0x0793A0C0

Now pass these plan_handle and memory_object_address to the function CompPlanDetails (described in a previous posting).

select plan_handle, memory_object_address, parent_address, type, name

from dbo.CompPlanDetails

(0x0793A0C0, 0x050001003D299E5FB8A19307000000000000000000000000)

Plan_Handle	Memory_ object_ address	Parent_ address	Type
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x0793AD58	0x0793A0C0	MEMOBJ_ XSTMT
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x0793A538	0x0793A0C0	MEMOBJ_ COMPILE_ ADHOC
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x0793A948	0x0793A0C0	MEMOBJ_ PLAN SKELETON
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x0793AB50	0x0793A0C0	MEMOBJ_ STATEMENT
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x07BBA040	NULL	MEMOBJ_ QUERY EXECCNTXT FORSE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x06EB0028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x06F94028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x0627A028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x07A9A028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x07B66028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x0793A0C0	NULL	MEMOBJ_ COMPILE_ ADHOC
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x06FB6028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x072E2028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x07BFA028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x07B20028	NULL	MEMOBJ_ EXECUTE
0x050001003 D299E5FB8A1 93070000000 00000000000 000000	0x07464028	NULL	MEMOBJ_ EXECUTE

There are 10 MXC’s corresponding to the simultaneous executions of the stored procedure. Now let us look at the dependant objects DMV for the compiled plan with plan handle 0x050001003D299E5FB8A19307000000000000000000000000:

select st.text, do.usecounts, do.cacheobjtype,

do.memory_object_address

from sys.dm_exec_cached_plans cp

cross apply

sys.dm_exec_sql_text(cp.plan_handle) st

cross apply

sys.dm_exec_cached_plan_dependent_objects(cp.plan_handle) do

where cp.plan_handle = 0x050001003D299E5FB8A19307000000000000000000000000

Text	Usecounts	Cacheobjtype	Memory_ object_ address
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x06FB6028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x06EB0028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x06F94028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x072E2028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x0627A028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x07BFA028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x07B20028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x07A9A028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x07464028
create procedure p1 as begin declare @x int; set @x = 0; while (@x < 10000000) set @x = @x + 1; select t1.col2, t2.col2 from t1 join t2 on t1.col1 = t2.col1 where t1.col1 = 50000; end	1	Executable Plan	0x07B66028

Notice that this DMV has the usecounts of the MXC. It also gives the memory_object_address of the MXC. Now upon re-execution of the stored procedure from one connection, the compiled plan has a usecount of 11 while one of the MXC’s is re-used incrementing its usecount to 2.

MXC’s may be deleted under memory pressure and re-created on subsequent execution of the same compiled plan. Therefore the sum of the usecounts of the MXCs as reported in the dependant objects DMV need not always equal the usecounts of the compiled plan. Infact it is strictly less than or equal to the usecounts of the compiled plan.

Note that there is a one to one mapping between the dependant objects listed here and in sys.dm_os_memory_objects based on memory_object_address. As demonstrated in the previous section, sys.dm_os_memory_objects provides information on the memory objects allocated including their memory object address, parent address of the memory object if any, pages allocated, page size etc.

2.4.5 Sys.dm_exec_requests

Sys.dm_exec_requests returns execution and resource consumption information for currently executing requests in the server. This DMV also provides the sql_handle, plan_handle and statement offsets for each query. It can therefore be used to identify the long running queries among the requests currently executing in the server using the query below:

select top 10 substring(st.text, (er.statement_start_offset/2) + 1,

((case statement_end_offset

when -1

then datalength(st.text)

else

er.statement_end_offset

end

- er.statement_start_offset)/2) + 1) as statement_text

, *

from sys.dm_exec_requests er

cross apply sys.dm_exec_sql_text(er.sql_handle) st

order by total_elapsed_time desc

BOL provides information on the columns returned by this DMV. It also lists the relationship cardinalities between sys.dm_exec_requests and the other plan cache related DMVs.

2.4.6 Sys.dm_exec_query_stats

The compiled plan is generated for the entire batch, while query plans are for individual statements in the batch. When a multi-statement batch is executing on the server, in order to identify the slow running queries, we will need to drill down further and look at the query plans for the individual statements in the batch. This is where sys.dm_exec_query_stats is extremely useful. Sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans. This DMV contains a row per query/statement within the cached plan. It is important to note that when the compiled plan is removed from cache, the rows corresponding to query plans for statements in that batch are also deleted from this DMV. The DMV provides both the sql_handle and the plan_handle, so we can join with sys.dm_exec_sql_text and sys.dm_exec_cached_plans to retrieve the batch text and the compiled plan for the batch. It also provides statement offsets, both start and end off sets of the query in the batch. Note that these offsets start at zero and an end offset of -1 indicates end of the batch. The statement offsets can be used in combination with the sql_handle passed to sys.dm_exec_sql_text to extract the query text from the entire batch text as demonstrated below. The query below selects the top 10 queries with highest CPU time and is useful in identifying expensive queries that were executed on the server.

select top 10 substring(st.text, (qs.statement_start_offset/2) + 1,

((case statement_end_offset

when -1

then datalength(st.text)

else

qs.statement_end_offset

end

- qs.statement_start_offset)/2) + 1) as statement_text,

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) st

order by total_elapsed_time/execution_count desc

Sys.dm_exec_query_stats returns a wealth of information regarding the per-statement IO, CPU and duration statistics. BOL has information on columns returned by this DMV and how to interpret the data in each of these columns. Note that the DMV is updated after the query execution is completed, so it might beneficial to query this DMV a couple of times to get updated information if there is a workload currently executing on the server.

2.0 Sql_Handle and Plan_Handle Explained

Additional resources