Query Plan's Compile Time Parameter Values

Most of you would know how to get the query plan from the cache via "sys.dm_exec_cached_plans" or "sys.dm_exec_query_stats" DMV or via a "sys.dm_exec_requests" with the plan_handle. But one interesting thing which many not have noticed is the ability to check the "compile time" parameter values of the query or stored procedure via the query plan.

 What is compile time parameter value?

Let’s say we have the following stored procedure

       EXEC usp_get_income @start_income, @end_income;

@start_income, @end_income are the parameters. For the first call, the query optimizer generates the query plans using the parameter values passed initially. For the subsequent calls, the query plan is reused and it is based on the values passed for the first call. Till the plan moves out of the cache or till it is recompiled, the query plan generated via the initial call is reused. The parameter values based on which the query plan is generated is referred as the compile time values

Consider the following example

EXEC usp_get_income 1000, 2000;

Open up another query window and pull out the plan of the

call "EXEC usp_get_income 1000, 2000" using the following

query

select cp.objtype, cp.size_in_bytes,

       cp.cacheobjtype,

       cp.usecounts,

       st.text,

       qp.query_plan,cp.plan_handle

from sys.dm_exec_cached_plans cp

outer apply sys.dm_exec_sql_text(cp.plan_handle) st

outer apply sys.dm_exec_query_plan(cp.plan_handle) qp

where st.text like '%usp_get_income%'

and st.text not like '%dm_exec_cached_plans%';

Use count value 1 indicates it is the first time execution. Click

on the value in the query plan column

Right click on the right most operator and click properties as

shown below.

Expand parameter list section and observe the "parameter

compile value" section as shown below.

"parameter compile value" indicates the inputs based on

which the plan was prepared and shows 1000,2000 as values.

Re run the query with different values

EXEC usp_get_income 5000, 6000;

Check the query's plan reuse count via

"sys.dm_exec_cached_plans" script provided earlier. Check

the compile time value again. It indicates old values

1000,2000 which were used to generate the plan initially at

first compilation.

The compile time parameter values of a

poor performing query can be extremely useful in

troubleshooting query regressions / parameter sniffing

issues. More about the same in subsequent posts.

Tweet