SP Recompiles

SQL Server performs recompiles as it executes stored procedures in order to optimize them and, to a certain extent, they are a normal part of every database’s ongoing operation. Recompiles are evaluated on a statement by statement basis as SQL Server executes a stored procedure, so the number of recompiles performed can easily, and unnecessarily, grow beyond the normal limit if attention is not paid to the way stored procedures are written.

When a stored procedure recompiles, it consumes significant system resources for the compilation process, and, if done excessively, can impact server performance. In SQL 7.0 and 2000, the entire stored procedure is recompiled, regardless of which part of it caused the recompile, so the larger the procedure is, the greater the impact of recompilation is. Furthermore, while recompiling, SQL Server places a compile lock on all the objects referenced by the stored procedure, and when there are excessive recompiles, the database might experience blocking. Notably, in SQL 2005, only the statement in question will be recompiled. This statement level recompile functionality will significantly improve the performance impact of recompilation.

Generally, if a stored procedure is recompiling every time it is executed, it should be rewritten to reduce the likelihood of it recompiling. In extreme cases, poor coding can result in a stored procedure recompiling multiple times within a single run.

The following cause a stored procedure to recompile:

  1. Dropping and recreating the stored procedure.
  2. Using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement.
  3. Running the sp_recompile system procedure against a table referenced by the stored procedure.
  4. The stored procedure execution plan is dropped from the cache. Infrequently used procedures are ‘aged’ by SQL Server and will be dropped from the cache if the cache memory is needed for other operations.
  5. All copies of the execution plan in the cache are in use.
  6. The procedure alternates between executing Data Definition Language (DDL) and Data Manipulation Language (DML) operations. When DDL operations (for example, CREATE statements) are interleaved with DML operations (for example, SELECT statement), the stored procedure will be recompiled every time it encounters a new DDL operation.
  7. Changing the schema of a referenced object (i.e. using an ALTER TABLE or CREATE INDEX). This applies to both permanent and temporary tables.
  8. When a stored procedure is compiled and optimized, it is done based on the statistics of the referenced tables at the time it is compiled. Each table in SQL Server (permanent or temporary) has a calculated recompilation threshold and if a large number of row modifications have been made and exceeds the threshold, then SQL Server will recompile the stored procedure to acquire the new statistics and optimize the procedure again. With respect to permanent tables, this is a normal part of database operations.
  9. The following SET options are ON by default in SQL Server, and changing the state of these options will cause the stored procedure to recompile:
    • SET ANSI_DEFAULTS
    • SET ANSI_NULLS
    • SET ANSI_PADDING
    • SET ANSI_WARNINGS
    • SET CONCAT_NULL_YIELDS_NULL

    While there are not good workarounds for the first four SET options, the last one can be avoided,by using the ISNULL function. Using the ISNULL function and setting any data that might contain a NULL to an empty string will accomplish the same functionality.

  10. The stored procedure performs certain operations on temporary tables such as:
    1. Declaration of temporary tables cause recompiles during the initial compilation. When a stored procedure is initially compiled, temporary tables do not exist so SQL Server recompiles after each temporary object is referenced for the first time. SQL Server caches and reuses this execution plan the next time the procedure is called and the recompile for this particular part of the stored procedure goes to zero. However, execution plans can be aged and dropped from the cache so periodically this may reoccur.
    2. Any DECLARE CURSOR statements whose SELECT statement references a temporary table causes a recompile.
    3. Any time a temporary table is created within a control-of-flow statement (for example, IF..ELSE or WHILE), a recompile occurs.
    4. Temporary tables have a global scope so they can be created in one stored procedure and then referenced in another. They can also be created using the EXECUTE() statement or with the sp_executesql() routine. However, if the temporary table has been created in a stored procedure (or EXECUTE() statement) other than the one currently referencing it, a recompile occurs every time the temporary table is referenced.
    5. Any statement containing the name of the temporary table that appears syntactically before the temporary table is created in the stored procedure causes a recompile.
    6. Any statements that contain the name of a temporary table which appear syntactically after a DROP TABLE against the temporary table causes the stored procedure to recompile.

The following practices should be followed to avoid and reduce the impact of recompiles:

  1. Put “dbo.” in front of every permanent object referenced in the stored procedure. While this does not prevent recompiles, it will minimize the impact of the recompile by stopping SQL Server from placing a COMPILE lock on the procedure while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
  2. Most recompile issues involve the use of temporary tables. As such, using table variables instead of temporary tables is the best way to avoid them. Table variables do not have recompilation threshold values, so recompilations do not occur because of changes in the number of rows.
  3. Place all of the temporary table creation statements together. As mentioned above, during the initial compilation of a stored procedure, SQL Server will recompile each time a temporary table is referenced for the first time throughout the code. By placing them all together, SQL Server will create execution plans for all of them at the same time in just one recompile.
  4. Make all schema changes (such as index creation) right after your create table statements and before you reference any of the temporary tables.
  5. Do not use a temporary table in the SELECT statement for a cursor. Furthermore, cursors should not be used at all.
  6. Do not create a temporary table within a control-of-flow statement (for example, IF..ELSE or WHILE).
  7. Do not create a temporary table in an EXECUTE statement or using the system procedure sp_executesql.
  8. Do not use a temporary table in a stored procedure other than the one the table was created in.
  9. Do not reference a temporary table before it is created.
  10. Do not reference a temporary table after it is dropped.
  11. Execute SQL statements that are causing recompilation with sp_executesql. Statements using this method are not compiled as part of the stored procedure plan, but have their own plan created. When the stored procedure encounters a statement using sp_executesql, it is free to use one of the statement plans or create a new plan for that statement. Using sp_executesql is preferred to using the EXECUTE because it allows parametrization of the query. This should only be used for specific SQL queries that have been determined to be causing excessive recompiles. Alternatively, a sub-procedure could also be used to execute specific statements that are causing recompilation. While the sub-procedure would be recompiled, the size and scope of the sub- procedure is much smaller and, as such, the impact greatly reduced. This mimics the statement level recompile functionality implemented in SQL Server 2005.
  12. Do not use query hints such as KEEPFIXEDPLAN. Query hints are only effective in very specific situations where the same dataset is being returned. Using query hints without an in-depth analysis of their effectiveness generally results in slower performance.