Query Recompilation in 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)

Contents

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

Introduction

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 behavīor 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 theEventSubClassdata column. To track instances of recompilation:

1. On the SQL Profiler menu, clicknew traceand connect to the appropriate SQL Server.
2. On theTrace Propertiesdialog box, click theEventstab, and then expand theStored Procedureevent class.
3. SelectSP:recompile,SP:Starting,SP:Completed,SP:StmtStarting, andSP:StmtCompleted(to see the stored procedure statement causing the recompilation).
4. On theData Columnstab, addEventSubClass.EventSubClassprovides 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
   Temptable 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, clicknew traceand connect to the appropriate SQL Server.
2. Go to theEventstab, and then expand theObjectsevent class.
3. SelectAutostats. You may also want to select theObject:openedevent as well as theStored Procedureevent class (SP:Starting,SP:Completed,SP:StmtStarting, andSP: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 type	Empty condition	Threshold when empty	Threshold 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 Variable	No thresholds	No thresholds	No thresholds

  The above thresholds, recorded by Profiler under theSP:Recompileevent, 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 behavīor or result sets such as:

       ANSI_DEFAULTS

       ANSI_NULL_DFLT_OFF

       ANSI_NULL_DFLT_ON

       ANSI_NULLS

       ANSI_PADDING

       CONCAT_NULL_YIELDS_NULL

       FORCEPLAN

  2. Other SET options include:

       ANSI_WARNINGS

       ARITHABORT

       LANGUAGE

       NUMERIC_ROUNDABORT

       QUOTED_IDENTIFIER

• The following SET options do not trigger recompilation of stored procedures:
  • ARITHIGNORE
  • CURSOR_CLOSE_ON_COMMIT
  • DEADLOCK_PRIORITY
  • FMTONLY
  • IDENTITY_INSERT
  • IMPLICIT_TRANSACTIONS
  • LOCK_TIMEOUT
  • NOCOUNT
  • NOEXEC
  • PARSEONLY
  • QUERY_GOVERNOR_COST_LIMIT
  • ROWCOUNT
  • STATISTICS IO
  • STATISTICS TIME
  • TRANSACTION ISOLATION LEVEL
  • XACT_ABORT

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, seeTracking Instances of Recompilationearlier in this article.
• SET options should be established at the connection level. Using SET options to govern the behavīor 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
  begin
  insert into @TableVar1
  select @i, 'T1 ' + convert(char(10),@i*2)
  select @i = @i + 1
  end
  

• For temporary tables, the optionkeep plancan be used on anySELECTstatement to eliminate the 6-row threshold. The first recompile would be at the 500-row threshold.
• Another option,keepfixed plan, can be used on aSELECTstatement 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. Usesp_executesqlto avoid recompiling the entire stored procedure.
• Sp_executeexecutes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically.sp_executesqlcan 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 thefirst_namecolumn 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_executesqlwould 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 proceduresp_autostatscan be used to govern autostats behavīor 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.

References

• Microsoft Knowledge Base (KB) articles:

   INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work(Q195565)

   INF: Troubleshooting Stored Procedure Recompilation(Q243586)

   INF: How to Identify the Cause of Recompilation in a SP:Recompile Event(Q308737)

• SET Options and Recompilation(SQL Server Magazine, February 2001)