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.
IE 7
Firefox 2.0