Around 1980 when relational databases and I were
both young, I was told that the time to parse the
SQL statement and create the search plan took about
50 percent of the time required to execute a SQL
query. Most of this was consumed by CPU time for
parsing and plan creation. The other 50 percent was
devoted to executing the query. Then as now,
execution time was primarily consumed by I/O for
reading the pages required to satisfy the query.
With computer CPUs being about 30,000 times faster
today than they were in 1980 you'd think that
parsing and plan creation would no longer involve
significant effort. But they do.
That is not to say that the time spent in
compilation and plan creation isn't worth the
effort. It is. SQL Server 2000 uses a far more
sophisticated algorithm for creating plans then
databases did in the past. Its algorithm is cost
based instead of schema based. That means it uses
knowledge about the actual values in the columns
being searched to pick the plan that will most
likely result in the fewest I/O operations. The
fewer the I/O operations, the faster the query runs.
In SQL Server, the knowledge about columns is stored
as column statistics.
When SQL Server executes a stored procedure for
the first time, it creates an execution plan. The
plan is a set of instructions on how to execute the
query and the process of creating the plan is
referred to as compilation. Plans are stored in the
procedure cache, which is in the dynamic table
master..syscacheobjects. The next time the stored
procedure is used, the plan can be retrieved from
cache and it doesn't have to be re-created. That is,
unless there is a reason that the existing plan was
removed from the cache or that the plan can no
longer be used. In these cases, the plan must be
recompiled.
At times SQL Server can spend excessive time in
recompiling stored procedures. Because stored
procedures have to be locked during recompilation,
excessive recompilation can slow a system down out
of proportion to the amount of CPU consumed. It has
become a problem in many systems.
This article discusses the reasons that SQL
Server 2000 decides to recompile a stored procedure
and demonstrates the tools and techniques that you
can use to monitor recompilation. In another article
next month, I'll discuss what can be done to
minimize recompilation. If your system makes
extensive use of stored procedures, minimizing
recompilation can give you an important boost in
performance.
