Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 9, 2003

Minimizing SQL Server Stored Procedure Recompiles

By Andrew Novick

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.

Recompilation Review

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:

  • The columns and base tables to be accessed
  • The joins, grouping, WHERE clause, and ORDER BY clause.
  • The available indexes on the tables
  • The distribution of data in columns that are referenced in the query

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM