Database Reference
In-Depth Information
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID = 29690
AND sod.ProductID = 711;
The execution plan generated for this ad hoc query is based on the exact text of the query, which includes
comments, case, trailing spaces, and hard returns. You'll have to use the exact text to pull the information out of
sys.dm_exec_cached_plans
.
SELECT c.usecounts
,c.cacheobjtype
,c.objtype
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
WHERE t.text = '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 = 29690
AND sod.ProductID = 711;';
Figure
15-1
shows the output of
sys.dm_exec_cached_plans
.
Figure 15-1.
sys.dm_exec_cached_plans output
You can see from Figure
15-1
that a compiled plan is generated and saved in the procedure cache for the
preceding ad hoc query. To find the specific query, I used the query itself in the
WHERE
clause. You can see that this
plan has been used once up until now (
usecounts = 1
). If this ad hoc query is reexecuted, SQL Server reuses the
existing executable plan from the procedure cache, as shown in Figure
15-2
.
Figure 15-2.
Reusing the executable plan from the procedure cache
In Figure
15-2
, you can see that the
usecounts
value for the preceding query's executable plan has increased
to 2, confirming that the existing plan for this query has been reused. If this query is executed repeatedly, the
existing plan will be reused every time.