The query optimizer is one of the most complex parts of the SQL Server engine, and it gets more complex with every release—and even with every service pack. The optimizer's job is to determine the optimum internal plan for carrying out a T-SQL query that has been submitted for execution, either ad hoc through an application or as part of a stored procedure. The optimizer has to make decisions such as
- which indexes (if any) SQL Server should use to access each table
- which index (if any) SQL Server should use to perform any ORDER BY operations
- in what order SQL Server should access the tables in a multitable query
- which JOIN algorithm SQL Server should use for each pair of tables
- how SQL Server should perform the GROUP BY and DISTINCT operations
Because Microsoft's SQL Server developers want the query optimizer to find a way to execute every query quickly, they're continually adding more methods for accessing your data and more algorithms to use in determining which method is best. Although evaluating all possible methods and finding the best one is time-intensive, the time is usually well spent. However, you don't want the optimizer to look for a new plan if a useful one already exists in SQL Server's plan cache. Let's explore how you can determine when and why the optimizer looks for a new query plan for a stored procedure, then discuss how you can avoid having SQL Server perform unnecessary work.
Recompilation and Optimization
Most SQL Server documentation uses the term "compiling" to refer to the optimizer's process of determining a suitable execution plan. But when SQL Server compiles a stored procedure, it does more than optimize all the procedure's data-manipulation language (DML) statements (SELECT, INSERT, UPDATE, and DELETE). It also translates into executable form the non-DML statements: declarations, conditional statements, calls to procedures or functions, and so on. SQL Server always compiles stored procedures as a unit; during compilation, the query optimizer finds a plan for every DML statement in the procedure. Whenever a recompilation occurs, SQL Server reoptimizes every statement, whether it needs to or not. It's the cost of the optimization phase that I'm concerned with in this article.
Reducing unnecessary recompiles is one of the best ways to improve a procedure or application's performance. However, you don't want to avoid all recompilations. In some situations, recompilation and reoptimization are desirable. In a moment, I'll look at the most common reasons SQL Server automatically recompiles a procedure, and you'll see that these recompilations can be useful at times. You can use several methods to explicitly force SQL Server to recompile a procedure, but determining when and why to force recompilation is a topic for another article. In this column, I deal only with automatic recompilation.
How can you tell when the optimizer has recompiled a procedure? The easiest, most straightforward method is to use SQL Server Profiler to capture the SP:Recompile event, which occurs whenever a stored procedure recompiles during execution. If you have SQL Server 2000 Service Pack 2 (SP2), Profiler can give you information about why the procedure was recompiled. SP2 makes information available in the EventSubClass column when Profiler reports the SP:Recompile event. This column contains a value between 1 and 6 that indicates why SQL Server recompiled the procedure. Table 1, page 22, lists the meanings of the different values as documented in the Microsoft article "HOW TO: Identify the Cause of Recompilation in an SP:Recompile Event" (Q308737, http://support.microsoft.com).
Besides SP:Recompile, you need only three events to set up a trace to capture all procedure recompiles and their causes: SP:Starting, SP:StmtStarting, and SP:Completed. The SP:StmtStarting event tells you which statement inside the procedure caused the recompile. The SP:Recompile event occurs after the procedure starts and right before the statement that caused the recompile.