Minimizing SQL Server Stored Procedure Recompiles
June 9, 2003
This article discusses the reasons that SQL Server 2000 decides to recompile a stored procedure and demonstrates techniques that can be used to minimize recompilation. If your system makes extensive use of stored procedures, minimizing recompilation can give you a nice boost in performance.
Last month I wrote about ways to monitor stored procedure recompilation in SQL Server 2000. That article concentrated on figuring out if recompiles are a problem and if so, where they were occurring. You can read the article at: http://www.databasejournal.com/article.php/2203601. As promised, this article shows how to change your stored procedure to cut the problem down to size. But you should recognize that there's a good reason for some of the recompiles and you shouldn't try to eliminate all of them.
We'll start with a short review of what constitutes a recompile, why you might want to minimize them, and how we measure them.
The first time that SQL Server is asked to execute a stored procedure, user-defined function, or trigger it has to convert the definition of the object into an execution plan. Execution plans are SQL Server's version of compiled code and I'll refer to the process of creating the plan as compilation.
SQL Server has what's called a cost based optimizer. That is it uses information about the values in the data columns to produce the best plan. Figuring out the best plan can consume a moderate amount of CPU resources and require that various database objects be locked while the recompile takes place. Frequent recompiles can be an important reason why a database is considered slow.
Plan creation, aka compilation, takes into account the estimated cost of executing each of the queries. The optimization algorithm takes into account:
SQL Server gets information about the data distribution from the statistics that it keeps for just this purpose. As the distribution of data changes, the optimal plan may also change. For example, the optimal plan for very small tables is to do a complete table scan, no matter what the query requests, because the scan requires the minimum amount of I/O. As the table grows, the optimal approach will change to one that uses an index. Depending of course, on which columns are indexed. The point is that the optimal plan changes as the data changes.
Execution plans aren't stored permanently. Instead, only the textual definition is kept permanently in syscomments. It holds the CREATE or ALTER statement that defined the object. As the code is compiled a copy of the execution plan is saved in the master.syscacheobjects table where it is used and becomes available for reuse.
Aggregate numbers of recompiles can be monitored using the Windows Performance Monitor. Discovering which statements are causing recompiles is best done with the SQL Profiler. Last month's article and the videos that accompany them show how to use both of these tools.
SQL Server usually uses the plan in the cache. However, sometimes, it can't and it must recompile. Sometimes it decides that a better plan might be found by creating a new plan. The next section summarizes the reasons and discusses what to do about them.