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.
