Identifying Stored Procedure Recompilation Problems in SQL Server 2000
May 12, 2003
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.