Database Reference
In-Depth Information
AS
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;
To retrieve a result set for
soh.Customerld = 29690
and
sod.ProductId=711
, you can execute the stored
procedure like this:
EXEC dbo.BasicSalesInfo
@CustomerID = 29690,
@ProductID = 711;
Figure
15-13
shows the output of
sys.dm_exec_cached_plans
.
Figure 15-13.
sys.dm_exec_cached_plans output showing stored procedure plan caching
From Figure
15-13
, you can see that a compiled plan of type
Proc
is generated and cached for the stored
procedure. The
usecounts
value of the executable plan is 1 since the stored procedure is executed only once.
Figure
15-14
shows the Extended Events output for this stored procedure execution.
Figure 15-14.
Extended Events output showing that the stored procedure plan isn't easily found in the cache
