Query Recompilation

Benefits and Drawbacks of Recompilation

The recompilation of queries can be both beneficial and harmful. Sometimes, it may be beneficial to consider a new processing strategy for a query instead of reusing the existing plan, especially if the data distribution in the table (or the corresponding statistics) has changed or new indexes are added to the table.

Recompiles in SQL Server 2014 are at the statement level. This increases the overall number of recompiles that can occur within a procedure, but it reduces the effects and overhead of recompiles in general. Statement-level recompiles reduce overhead because they recompile only an individual statement rather than all the statements within a procedure

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.

Analyzing Causes of Recompilation

Statement recompilation occurs for the following reasons:

  • The schema of regular tables, temporary tables, or views referred to in the stored procedure statement have changed. Schema changes include changes to the metadata of the table or the indexes on the table.
  • Bindings (such as defaults) to the columns of regular or temporary tables have changed.
  • Statistics on the table indexes or columns have changed, either automatically or manually.
  • An object did not exist when the stored procedure was compiled, but it was created during execution. This is called deferred object resolution, which is the cause of the preceding recompilation.
  • SET options have changed.
  • The execution plan was aged and deallocated.
  • An explicit call was made to the sp_recompile system stored procedure.
  • There was an explicit use of the RECOMPILE hint.

Recompiling explicitly :

EXEC dbo.CustomerList
    @CustomerId = 1
    WITH RECOMPILE;

Avoiding Recompilations

You can avoid it by following these implementation practices:

  • Don’t interleave DDL and DML statements.
  • Avoid recompilation caused by statistics changes.
  • Use the KEEPFIXED PLAN option.
  • Disable the auto update statistics feature on the table.
  • Use table variables.
  • Avoid changing SET options within the stored procedure.
  • Use the OPTIMIZE FOR query hint.
  • Use plan guides.