Improve performance through caching
In SQL Server 7.0, reusing compiled plans for stored procedures can make a dramatic difference in overall system performance. (For more information on plan reuse, see "SQL Server 7.0 Plan Caching," September 1999.) The greater complexity of the Query Optimizer saves parse-and-compile time (which includes query optimization). But reuse of compiled plans isn't always the most important reason for using stored procedures. With earlier SQL Server releases, the savings aren't great, and SQL Server 7.0 includes other methods of saving and reusing query plans. So why should you use stored procedures?
Stored procedures let you develop SQL code in programmable units, which anyone working on the application can use. Stored procedures allow controlled access to data, so a user can have permission to run a procedure without having ad hoc access to the object that the stored procedure references. From a performance standpoint, the savings in network traffic of not needing to send the entire block of code from client to server can be an important reason for using stored procedures. So let's look closer at stored procedure plan caching and how best to use it.
Stored Procedure Caching vs. Plan Caching
SQL Server uses several mechanisms to save and reuse plans for ad hoc or autoparameterizable queries. Most of these techniques deal with optimizing a SQL statement. With stored procedure plan caching, you're dealing with multiple plans—one plan for each statement in the procedure. Generally, all statements in a procedure are compiled at the same time, so you can consider the procedure's plan to be the combination of the plans for all the statements.
Procedures can be parameterized, and in general, the plan for a procedure is based on the arguments that are passed the first time the procedure is called. Regardless of the value or values passed in, subsequent executions of the same procedure use the same plan, which isn't always a good idea. For example, suppose you have a stored procedure that computes information about customers' purchasing habits by country. If the procedure accepts a parameter for the country, it might contain a statement such as
SELECT country(*) FROM sales
WHERE country = @country
Suppose you have a nonclustered index on the country column. If, the first time the procedure is called, the country you supply as the parameter occurs only a few times in the table, the optimizer might use the index. (Nonclustered indexes are typically a good choice only when the rows that need to be accessed form a small percentage of the table.) After that, the saved plan for this stored procedure always tells SQL Server to use the nonclustered index on the country column. If someone executes the procedure with a country value that occurs in many rows, the original plan is still used, even though it's not an optimal plan for this second case. The mechanisms that SQL Server uses to autoparameterize queries that aren't stored procedures are very conservative, and SQL Server doesn't autoparameterize any query in which a parameter change could lead to a different plan. However, when writing stored procedures, you can control when a stored procedure is recompiled.
The mechanisms for recompiling procedures are well-described in the online documentation, so I won't go into detail here. If you check the syntax for the CREATE PROCEDURE statement, you'll see that you can add the option WITH RECOMPILE to the procedure definition. This option causes SQL Server to compile and optimize the procedure every time it runs. If you know that different parameters might require vastly different plans, consider this option. Also, you can recompile one execution of a procedure by using the WITH RECOMPILE option with the EXECUTE statement. Any time a procedure is executed with this option, SQL Server doesn't check for any existing plans in cache, and the new plan developed for this single execution is saved in cache.
The option of recompiling a stored procedure is different from the new SQL Server 7.0 capability of automatically updating statistics. (See "SQL Server Statistics: A Useful Query Optimizer Tool," August 1999, for details on automatic updating of statistics.) Automatic updating of statistics doesn't mean that plans automatically recompile. The statistics update only if SQL Server recompiles and reoptimizes a plan or if the query optimizer detects that the statistics are out of date. However, to take advantage of updated statistics after SQL Server saves a procedure plan, you need to specify one of the recompile options to invoke the optimizer.
Another feature of stored procedures in SQL Server 7.0 sometimes can cause a recompilation. If you build a temporary table, then create an index on that table, and later in the same procedure add data to the table, your stored procedure might be compiled several times. If SQL Server tries to compile the entire plan at once before executing the statements in the procedure that populate the table, the optimizer has no way to know what the data will be when you execute the procedure. In fact, if a procedure contains the creation of a table, SQL Server compiles it only up to the first reference to the table. At execution time, it executes the plan up to the first reference to the table and recompiles from that point. As you add more data to the table, the procedure might be recompiled again. If SQL Server detects that a table was empty when the procedure was first compiled (e.g., if you'd just created the table), the query processor sets a low recompilation threshold for the table. At present, this threshold is five data-modification statements (INSERT, UPDATE, or DELETE). So every time five or more changes are made to the newly created table, the procedure recompiles.