Database Reference
In-Depth Information
Figure 17-5. Extended Events output showing an sp_statement_starting event causing recompilation
Note that after the statement recompilation, the stored procedure statement that caused the recompilation
is started again to execute with the new plan. You can capture the statement within the event, correlate the events
through sequence using the timestamps, or best of all, use the Causality Tracking on the extended events. Any of these
can be used to track down specifically which statement is causing the recompile.
Analyzing Causes of Recompilation
To improve performance, it is important that you analyze the causes of recompilation. Often, recompilation may
not be necessary, and you can avoid it to improve performance. For example, every time you go through a compile
or recompile process, you're using CPU for the optimizer to get its job done. You're also moving plans in and out
of memory as they go through the compile process. When a query recompiles, that query is blocked while the
recompile process runs, which means frequently called queries can become major bottlenecks if they also have
 
Search WWH ::




Custom Search