Query Recompilation - SQL Server Query Performance Tuning - page 317

Figure 17-4. Extended Events output showing an sql_statement_recompile event from recompilation

In Figure 17-4 , you can see that you have a recompilation event ( sql_statement_recompile ), indicating that the
stored procedure went through recompilation. When a stored procedure is executed for the first time, SQL Server
compiles the stored procedure and generates an execution plan, as explained in the previous chapter. By the way, you
might see other statements if you're using Extended Events to follow along. Just filter or group by your database ID to
make it easier to see the events you're interested in. It's always a good idea to put filters on your Extended Events sessions.
Since execution plans are maintained in volatile memory only, they get dropped when SQL Server is restarted.
On the next execution of the stored procedure, after the server restart, SQL Server once again compiles the stored
procedure and generates the execution plan. These compilations aren't treated as a stored procedure recompilation
since a plan didn't exist in the cache for reuse. An sql_statement_recompile event indicates that a plan was already
there but couldn't be reused.

■ i discuss the significance of the recompile_cause data column later in the “analyzing Causes of
recompilation” section.

Note

To see which statement caused the recompile, look at the statement column within the sql_statement_recompile
event. It shows specifically the statement being recompiled. You can also identify the stored procedure statement
causing the recompilation by using any of the various statement starting events in combination with a recompile
event. If you enable Causality Tracking as part of the Extended Events session, you'll get an identifier for the start of
an event and then sequence numbers of other events that are part of the same chain. Figure 17-5 shows
attach_activity_id.seq for the sp_statement_starting event immediately before the sql_statement_recompile
event. If you look at the recompile event, it follows as the next in sequence.