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.
 
Search WWH ::




Custom Search