Query Recompilation - SQL Server Query Performance Tuning - page 316

Identifying the Statement Causing Recompilation
SQL Server can recompile individual statements within a procedure or the entire procedure. Thus, to find the cause of
recompilation, it's important to identify the SQL statement that can't reuse the existing plan.
You can use extended events to track statement recompilation. You can also use the same events to identify the
stored procedure statement that caused the recompilation. Table 17-1 shows the relevant events you can use.
Table 17-1. Events to Analyze Query Recompilation
Events
sql_batch_completed or module_end
sql_statement_recompile
sql_batch_starting or module_start
sp_statement_completed or sql_statement_completed (Optional)
sp_statement_starting or sql_statement_starting (Optional)
Consider the following simple stored procedure:
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc;
GO
CREATE PROC dbo.TestProc
AS
CREATE TABLE #TempTable (C1 INT);
INSERT INTO #TempTable
(C1)
VALUES (42);
-- data change causes recompile
GO
On executing this stored procedure the first time, you get the Extended Events output shown in Figure 17-4 .
EXEC dbo.TestProc;