Database Reference
In-Depth Information
Figure 15-18.
sys.dm_exec_cached_plans output showing a parameterized plan generated using sp_executesql
In Figure
15-18
, you can see that the plan is generated for the parameterized part of the query submitted through
sp_executesql
. Since the plan is not tied to the variable part of the query, the existing execution plan can be reused if
this query is resubmitted with a different value for one of the parameters (
d.ProductID=777
), as follows:
EXEC sp_executesql @query,@paramlist,@CustomerID = 29690,@ProductID = 777;
Figure
15-19
shows the output of
sys.dm_exec_cached_plans
.
Figure 15-19.
sys.dm_exec_cached_plans output showing reuse of the parameterized plan generated using sp_executesql
From Figure
15-19
, you can see that the existing plan is reused (
usecounts
is 2 on the plan on line 2) when the
query is resubmitted with a different variable value. If this query is resubmitted many times with different values for
the variable part, the existing execution plan can be reused without regenerating new execution plans.
The query for which the plan is created (the text column) matches the exact textual string of the parameterized
query submitted through
sp_executesql
. Therefore, if the same query is submitted from different parts of the
application, ensure that the same textual string is used in all places. For example, if the same query is resubmitted
with a minor modification in the query string (say in lowercase instead of uppercase letters), then the existing plan is
not reused, and instead a new plan is created, as shown in the
sys. dm_exec_cached_plans
output in Figure
15-20
.
SET @query = N'SELECT soh.SalesOrderNumber ,soh.OrderDate ,sod.OrderQty ,sod.LineTotal FROM
Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.
SalesOrderID where soh.CustomerID = @CustomerID AND sod.ProductID = @ProductID' ;
Figure 15-20.
sys.dm_exec_cached_plans output showing sensitivity of the plan generated using sp_executesql
Another way to see that there are two different plans created in cache is to use additional dynamic management
objects to see the properties of the plans in cache.
SELECT decp.usecounts,
decp.cacheobjtype,
decp.objtype,
dest.text,
deqs.creation_time,
deqs.execution_count,
