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

From the Extended Events output, you can see that the plan for the stored procedure is not found in the cache.
When the stored procedure is executed the first time, SQL Server looks in the procedure cache and fails to find any
cache entry for the procedure BasicSalesInfo , causing an sp_cache_miss event. On not finding a cached plan, SQL
Server makes arrangements to compile the stored procedure. Subsequently, SQL Server generates and saves the plan
and proceeds with the execution of the stored procedure. You can see this in the sp_cache_insert event. Figure 15-15
shows the details.
Figure 15-15. Details of the sp_cache_hit extended event
If this stored procedure is reexecuted to retrieve a result set for @Productld = 777 , then the existing plan is
reused, as shown in the sys.dm_exec_cached_plans output in Figure 15-16 .
Figure 15-16. sys.dm_exec_cached_plans output showing reuse of the stored procedure plan
EXEC dbo.BasicSalesInfo
@CustomerID = 29690,
@ProductID = 777;
You can also confirm the reuse of the execution plan from the Extended Events output, as shown in Figure 15-17 .
Figure 15-17. Profiler trace output showing reuse of the stored procedure plan