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: https://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.
