Execution Plan Cache Behavior - SQL Server Query Performance Tuning - page 282

Since the plan generated for the preceding query includes the filter criterion value, the reusability of the plan is
limited to the use of the same filter criterion value. Reexecute the query, but change son.CustomerlD to 29500 .
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 = 29500
AND sod.ProductID = 711;
The existing plan can't be reused, and if the sys.dm_exec_cached_plans is rerun as is, you'll see that the
execution count hasn't increased (Figure 15-3 ).
Figure 15-3. sys.dm_exec_cached_plans shows that the existing plan is not reused
Instead, I'll adjust the query against sys.dm_exec_cached_plans .
SELECT c.usecounts,
c.cacheobjtype,
c.objtype,
t.text,
c.plan_handle
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
WHERE t.text LIKE 'SELECT soh.SalesOrderNumber,
soh.OrderDate,
sod.OrderQty,
sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID%';
You can see the output from this query in Figure 15-4 .
Figure 15-4. sys.dm_exec_cached_plans showing that the existing plan can't be reused