Query Recompilation in SQL Server 2000
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Collapse the table of content
Expand the table of content

Query Recompilation in SQL Server 2000

SQL Server 2000

Thomas Davidson
Microsoft Corporation

May 2002

Applies to:
     Microsoft® SQL Server™ 2000

Summary: How to understand and consider the implications of query recompilation during the application development cycle. (7 printed pages)


Architecture: Recompilation and Stored Procedures
Tracking Instances of Recompilation
Factors Affecting Recompilation
Recommendations for Best Use of Recompilation


In Microsoft® SQL Server™ 2000, the query optimizer selects the best strategy to access data quickly and efficiently. This process may be repeated, or "recompiled," and is one of many optimization strategies you can use in SQL Server 2000 to take advantage of better querying plans as they become available. Recompilation yields superior performance in some situations, although excessive recompilation can be detrimental. This article will discuss some of these costs and benefits, and will draw on contrasts between different versions of SQL Server to provide further context for understanding recompilation.

Architecture: Recompilation and Stored Procedures

To understand the ramifications of recompilation as it affects performance, some context in SQL Server architecture is necessary. Of particular note is the difference between versions of SQL Server. The architecture of SQL Server 2000 and SQL Server version 7.0 differs fundamentally from that of SQL Server version 6.5. The query optimizer in SQL Server 6.5 is not as sophisticated, and therefore with fewer optimization strategies to choose from, it is often faster at selecting an execution plan. Although recompilations can occur in SQL Server 6.5, they are less likely.

Recompilation affects the entire stored procedure query plan. As compared to SQL Server 6.5, the more frequent and longer recompilation of SQL Server 2000 may appear disadvantageous in some cases. The SQL Server 2000 query optimizer performs "constant folding": that is, it evaluates constant expressions at compile time to save doing them every time the plan was used at run time. SQL Server 6.5 does not perform constant folding.

One consequence of this difference is that in SQL Server 6.5, changing the set options (which affect the result of expression evaluation) does not affect the compile plan. In contrast, SQL Server 2000 may require a recompilation depending on the set option used.

Note   You might not think evaluating 2*2 in WHERE a > 2*2 and a < 1*3 saves very much, but it does allow for further optimizations: one query simplification leads to another. In the above case, we see that a>4 and a<3 is impossible, and thus we can eliminate the part of the query below that filter.

Further, when recompiles do occur in SQL Server 6.5, they are not shared. Two users executing the same procedure have separate versions of the execution plan. Additionally, the execution plans for these two users may employ different access strategies depending on the effect of the parameters supplied to the stored procedure.

SQL Server 6.5 stored procedures are reusable but not reentrant. That is, only one user at a time can use any given stored procedure query plan. In high-use environments, users of SQL Server 6.5 will have multiple independent query plans for the same procedure.

In SQL Server 2000 stored procedure architecture, the query plan is divided into two structures, a compiled plan and an executable plan:

  • Compiled plan

    The bulk of the execution plan is a reentrant, read-only data structure used by any number of users. This is called the compiled or query plan. Reentrance implies all users share the compiled plan. No user context information (such as data variable values) is stored in the compiled plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel plan covers all parallel executions, regardless of their degree of parallelism.

  • Executable plan

    Each user currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is called the executable plan or execution context. The execution context data structures are reusable but not reentrant. Each user has a copy of this data structure. If a user executes a query and one of the structures is not in use, it is reinitialized with the context for the new user.

During peak periods of use, the recompilation of the single compiled plan architecture can result in serialized lockout behavior during recompilation of the query plan. In other words, when recompilation occurs, a compile lock is placed on the shared compiled plan, suspending all concurrent executions until the completion of recompilation. In such cases, excessive recompilation can be undesirable.

Tracking Instances of Recompilation

SQL Profiler can be used to track the instances of stored procedure recompilation. Furthermore, in SQL Server 2000 Service Pack 2, you can find the reason for recompilation in the EventSubClass data column. To track instances of recompilation:

  1. On the SQL Profiler menu, click new trace and connect to the appropriate SQL Server.
  2. On the Trace Properties dialog box, click the Events tab, and then expand the Stored Procedure event class.
  3. Select SP:recompile, SP:Starting, SP:Completed, SP:StmtStarting, and SP:StmtCompleted (to see the stored procedure statement causing the recompilation).
  4. On the Data Columns tab, add EventSubClass. EventSubClass provides a numeric value that describes the reason for the recompile. These values are as follows:
    Schema, bindings, or permissions changed between compile or execute.1
    Statistics changed (rowmodctr in sysindexes).2
    Recompile DNR (Deferred Name Resolution). Object did not exist at compile time. Recheck at run time.3
    Set options changed.4
    Temp table schema, bindings, or permissions changed.5
    Remote rowset schema, bindings, or permissions changed. 6

You can use trace flag 205 to report when a statistics-dependent stored procedure is being recompiled as a result of Autostat. You can also use the SQL Profiler to identify when UPDATE STATISTICS statements are being run. To do this, perform the following steps:

  1. On the SQL Profiler menu, click new trace and connect to the appropriate SQL Server.
  2. Go to the Events tab, and then expand the Objects event class.
  3. Select Autostats. You may also want to select the Object:opened event as well as the Stored Procedure event class (SP:Starting, SP:Completed, SP:StmtStarting, and SP:StmtCompleted) to see the stored procedure statement causing the Autostats to fire.
    Note   If many statistics are being updated by Autostat, a great number of messages can be written to the error log. Experiment extensively with these trace flags before using them on any production or otherwise critical server.

Factors Affecting Recompilation

Recompilation occurs in the following situations:

  • Schema changes: For example, adding or dropping indexes (after population of tables including temporary tables).
  • Rows changed threshold: There are thresholds on the number of row changes that cause stored procedure recompilation, depending on the type of object as follows:
    Table typeEmpty conditionThreshold when emptyThreshold when not empty
    Permanent< 500 Rows# of Changes >= 500# of Changes >= 500 + (20 percent of Cardinality)
    Temporary< 6 Rows# of Changes >= 6# of Changes >= 500 + (20 percent of Cardinality)
    Table VariableNo thresholdsNo thresholdsNo thresholds

    The above thresholds, recorded by Profiler under the SP:Recompile event, govern the recompilation of permanent and temporary tables. The data column EventSubClass provides the reason for recompilation. When a threshold is crossed, EventSubClass will = 2, indicating that statistics changed. Row changes are recorded in the rowmodctr column of the sysindexes table.

  • Use of certain SET options in stored procedures can cause recompilation.
    1. Generally, those that affect query behavior or result sets such as:








    2. Other SET options include:






  • The following SET options do not trigger recompilation of stored procedures:
    • NOEXEC

Recommendations for Best Use of Recompilation

Keep these best practices in mind as you analyze your use of recompilation:

  • Use SQL Profiler to determine the frequency of recompilation. Make sure you can identify the statement causing the recompilation. For details, see Tracking Instances of Recompilation earlier in this article.
  • SET options should be established at the connection level. Using SET options to govern the behavior of individual stored procedures can result in more recompilations in SQL Server 2000. To minimize this particular case, establish SET options at connection time, and ensure that they stay in effect for the duration of the connection.
  • If a stored procedure creates a temporary table, all data definition language such as create table and create index should be at the beginning of the stored procedure.
  • Since table variables are not subject to rows-changed thresholds, they can be used in lieu of temporary tables as follows:
    DECLARE   @TableVar1 TABLE
       (a int primary key,    -- note: indexes (constraints) can be used
         b char(10))
    ------------ insert 1000 rows into @TableVar1
     declare @i int
     select @i = 1
     while @i < 1000
       insert into @TableVar1
       select @i, 'T1 ' + convert(char(10),@i*2)
       select @i = @i + 1
  • For temporary tables, the option keep plan can be used on any SELECT statement to eliminate the 6-row threshold. The first recompile would be at the 500-row threshold.
  • Another option, keepfixed plan, can be used on a SELECT statement to prevent any recompilation based upon Query Processor (QP) threshold crossing (only dependency tracking will cause recompiles).
  • Large stored procedures take longer to compile than small ones. Large stored procedures can be problematic, as recompilation is done at the stored procedure level, not the statement level. Use sp_executesql to avoid recompiling the entire stored procedure.
  • Sp_execute executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. sp_executesql can be used to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution. Plan reuse in such cases is fine provided that:
    • The different parameter values do not change the size of the result set.
    • The same parameter variables are provided each time.

    For example, if you initially search for values based on first name only, an index on the first_name column would be beneficial. If, however, you next want to search on last name, you would not want to reuse the plan based on first name. sp_executesql would not be beneficial in this case.

  • As a last resort, identify the statement causing the recompilation, and move the statement to its own stored procedure to minimize recompilation work.
  • Given the single compiled plan architecture of SQL Server 2000, and serialization of recompiling query plans, some users have chosen to use temporary stored procedures. While temporary stored procedures will not avoid recompilation, they will minimize the serialization impact of recompiling a single query plan. Each execution will have its own temporary stored procedure query plan.
  • Turning off AutoStats for a given object means the rows modification threshold will not trigger recompilation automatically. The stored procedure sp_autostats can be used to govern autostats behavior and to avoid threshold recompilation.

In summary, the sophisticated query optimizer in SQL Server 2000 provides many new data access strategies. An important goal of recompilation is to take advantage of better data access plans when they are available. In certain cases, however, the cost of recompilation outweighs the benefits, and can negatively impact performance. These situations should be tracked and minimized.


© 2016 Microsoft