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

Stored Procedures
Using stored procedures is a standard technique for improving the effectiveness of plan caching. When the stored
procedure is compiled at execution time (this is different for native compiled procedures, which are covered in
Chapter 22), a plan is generated for each of the SQL statements within the stored procedure. The execution plan
generated for the stored procedure can be reused whenever the stored procedure is reexecuted with different
parameter values.
In addition to checking sys.dm_exec_cached_plans , you can track the execution plan caching for stored
procedures using the Extended Events tool. Extended Events provides the events listed in Table 15-2 to track the plan
caching for stored procedures.

Table 15-2. Events to Analyze Plan Caching for the Stored Procedures Event Class

Event Description

sp_cache_hit The plan is found in the cache.

sp_cache_miss The plan is not found in the cache.

sp_cache_insert

The event fires when a plan is added to cache.

sp_cache_remove The event fires when a plan gets removed from cache.

To track the stored procedure plan caching using trace events, you can use these events along with the other
stored procedure events and data columns shown in Table 15-3 .

Table 15-3. Data Columns to Analyze Plan Caching for Stored Procedures Event Class

Event Data Column

SP:CacheHit EventClass

SP:CacheMiss TextData

SP:Completed LoginName

SP:ExecContextHit SPID

SP:Starting StartTime

SP:StmtCompleted

To understand how stored procedures can improve plan caching, reexamine the procedure created earlier called
BasicSalesInfo . The procedure is repeated here for clarity:

IF (SELECT OBJECT_ID('BasicSalesInfo')
) IS NOT NULL
DROP PROC dbo.BasicSalesInfo ;
GO
CREATE PROC dbo.BasicSalesInfo
@ProductID INT,
@CustomerID INT