October 30, 2015 at 7:13 am
Hi,
First post.
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Hope this makes sense.
February 9, 2016 at 8:35 am
Read a couple of posts that helped to answer my questions.
1. https://blogs.msdn.microsoft.com/sqlcat/2010/06/09/identifying-query-compileruntime-parameter-values-using-xml-showplan-output/ by Peter Scharlock answered question 1.
<ParameterList>
<ColumnReference Column="@P5" ParameterCompiledValue="’1753-01-01 00:00:00.000’" ParameterRuntimeValue="’2010-04-01 11:01:05.000’" />
As you might imagine, the data above relates to the parameter [Column="@P5"] used to compile a given query execution plan...
2. http://www.sommarskog.se/query-plan-mysteries.html, on execution plans by Erland Sommarskog and it answered question 2.
There are other events that do not cause the entire procedure plan to be evicted from the cache, but which trigger recompilation of one or more individual statements in the procedure. The recompilation occurs the next time the statement is executed.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply