Understanding Stored Procedure Optimization

When working with any computer program, the way it performs is very important. This is even more important when you start working with database procedures that can be executed many times by many users at one time. In this section, we will look at how to make procedures run better, faster, and more efficiently. Along the way, we will see what makes a good stored procedure and what makes a bad one. Learning how to design and code a stored procedure that performs well will allow your application to execute that much faster.

Because many different users execute stored procedures many times a day, the performance of the stored procedures is very important. It is easy to see that if a stored procedure is written poorly and slows the processing of the server, by multiplying the effect over a long period of time, your users might become very frustrated waiting for an answer from the server. That is why learning how to optimize their performance is so critical. In this section, we will look at some methods of understanding and optimizing procedure performance.

Using Good Procedure Performance Practices

To understand how to write a good stored procedure, we must first understand how a procedure is processed and executed by the server. In the lesson on Day 9, "Indexes and Performance," you were introduced to performance and some of the tools available to you to see what is happening when a query is executed. Stored procedures are also subject to the same steps that were described on Day 9, but they don't occur all at the same time. Knowing when each of these steps is performed will help you in understanding how to make the stored procedure better. To review these steps, here is a summary of what they are.

• Parse—Verify the syntax and check for valid object names.
  
  
• Optimize—Decide on the most efficient execution path.
  
  
• Compile—Create the executable SQL code.
  
  
• Execute—Execute the SQL script or batch.
  
  

These steps are taken whenever a batch, script, trigger, stored procedure, or single SQL statement is executed. Now, let's review these steps and change the definition to reflect when they are performed.

• Parse—The stored procedure's syntax and object names are checked at creation time.
  
  
• Optimize—Whenever a new procedure plan is needed by the server, the procedure is optimized using the current parameters.
  
  
• Compile—Immediately after the stored procedure is optimized, the server will compile it, but only when a new plan is required.
  
  
• Execute—Every time a procedure is executed.
  
  

When is a stored procedure actually optimized by the server? When you execute a procedure, the server generates an optimization plan for the SQL script (batch) that is calling the stored procedure, but not for the procedure itself. Optimization of the procedure is not done until the procedure itself is executed. To help demonstrate where a stored procedure is actually optimized, we will build a small SQL script (batch) that includes two distinct steps. In the example in Listing 16.1, you will see that the first step is to create the stored procedure and the second step consists of two code blocks.

Code Listing 16.1. Test SQL Script to Work with Stored Procedure

 1:use Northwind
 2:Drop procedure demo_performance
 3:go
 4:create procedure Demo_Performance (@ordID int)
 5:as
 6:select count(*) as sp_order_count
 7:from orders
 8:where orderid = @ordID
 9:return 0
10:go
11:--This will not be executed
12:if 1 = 2
13:begin
14:print 'executing the procedure'
15:print ''
16:exec demo_performance 10260
17:print ''
18:select count(*) as Order_count_1 from orders
19:end
20:--This will be executed
21:if 1 = 1
22:begin
23:print 'executing without the procedure'
24:print ''
25:select count(*) as Order_count_2 from orders
26:end
27:go
						

Before executing this SQL batch, turn on the Show Execution Plan option in Query Analyzer. After you have done this, execute the script. The output you receive will include the actual output as shown and an execution plan as shown in Figure 16.1.

executing without the procedure

Results:

Order_count
-----------
830

(1 row(s) affected)

From this example, you can see that the creation of the stored procedure does not generate an execution plan. Only when the procedure is actually executed is a plan generated for it. In addition, you can see that the server did not optimize the procedure when it optimized the batch in which it was executed. The CREATE PROCEDURE statement is not optimized as a part of an execution batch.

Notice that the optimizer did generate an execution plan for the SELECT statement in the first BEGIN block. Only the SELECT statement in the BEGIN block that was actually executed generated an execution plan.

Now, change the IF 1=2 statement to IF 1=1 to allow the first BEGIN block to be executed. After executing the batch, the output displayed is

executing the procedure

Results:

sp_order_count
--------------
1

(1 row(s) affected)


Order_count_1
-------------
830

(1 row(s) affected)

executing without the procedure

Order_count_2
-------------
830


(1 row(s) affected)

When you look at the execution plan that was generated as shown in Figure 16.2, you will see that three separate execution plans are generated by the server: one for each SELECT statement in the BEGIN blocks and one for the SELECT statement in the stored procedure.

The most important point to this example is that the stored procedure is not optimized until it is actually executed inside of a batch.

Every time a stored procedure is executed, the server looks first in the procedure cache to see whether there is already a compiled plan for the procedure. A plan would already exist in the procedure cache if the stored procedure had been executed at least once while SQL Server has been running. If that plan still exists, the server uses it and entirely skips the optimization step for the procedure.

Figure 16.3 shows the basic logic used by SQL Server for determining how a stored procedure will be processed. First, the server will check the procedure cache for an available plan. If one is found, the server will pass the parameter values to the procedure and then execute it. If a plan is not found, the server will substitute the parameter values into each statement in the procedure and will generate a plan, compile that plan, pass the parameters, and finally execute the procedure.

Understanding the Compilation Process

Because the process of optimizing a stored procedure takes time and work on the server, it is a good thing that stored procedures can reuse the optimization plans that are already in the procedure cache. If the optimization plan for a procedure never changes, it is wasteful to recompile that procedure every time it is executed. However, there are stored procedures that need to be reoptimized each time that they are executed. Look at the following example of a stored procedure:

select sum(unitprice * quantity)
from [order details]
where orderid = @ordID

Depending on the value of @ordID, this SQL query could find all the rows in the table (*) or very few (orderid = 10260). When the server generates a plan for this query, it might select a table scan or one of the available indexes for use. After the plan is selected, it will remain in cache until either the server is shut down, or the server needs the space the plan takes up for another procedure being executed.

The problem is this: The first time you execute a procedure with a query containing a WHERE clause that returns a variable number of rows depending on the value passed, the value is substituted into the SELECT statement and it is then optimized for that parameter. If you execute the procedure at a later time and the optimization plan is still in the procedure cache, you will get the previous optimization plan, even if it is not correct for the new value being passed.

Using the RECOMPILE Option in Stored Procedures

One way to avoid the problem described in the previous section is use the WITH RECOMPILE option with the stored procedure. By specifying this option, you can have the server generate a new optimization plan before the procedure is executed.

You can add this option in either the CREATE PROCEDURE statement or in the EXEC PROCEDURE statement. Where you place this option affects how the stored procedure is processed and executed.

Using the WITH RECOMPILE in the CREATE PROCEDURE Statement

When you use the WITH RECOMPILE option in the CREATE PROCEDURE statement, the execution plan will not be saved in the procedure cache. This option will instruct the server to recompile the stored procedure every time it is executed. This is very similar to the way that a standard query is processed, and it can be useful if parameters being passed to the stored procedures make the default execution plan run poorly. By recompiling every time, the procedure can be optimized for the new parameters. Of course, if the procedure is recompiled every time it is executed, there will be many plans in memory, which fills up the procedure cache and could actually impact performance. The following example shows a stored procedure with the WITH RECOMPILE option. Notice that it is placed between the parameter list and the AS keyword.

CREATE PROCEDURE sp_demo_proc (@ordID int)
WITH RECOMPILE
AS SELECT * FROM Orders
Where OrderId = @ordID
							

Using the WITH RECOMPILE in the EXEC PROCEDURE Statement

Instead of specifying the WITH RECOMPILE option in the CREATE PROCEDURE statement, you can use it in the EXEC PROCEDURE statement. Using it in the EXEC PROCEDURE statement causes the stored procedure to be recompiled in that single execution, and the new plan is stored in the procedure cache for later EXEC PROCEDURE commands. Here is an example of using the WITH RECOMPILE option in an EXEC PROCEDURE statement:

Exec sp_demo_proc 10260
With Recompile

The downside of this option is that it does not solve the original problem. Any plans generated by using the WITH RECOMPILE are left in the procedure cache to be used by other users, again causing possibly unanticipated results.

Forcing All Stored Procedures to Be Recompiled

You can tell the server to recompile all stored procedures and triggers associated with a particular table the next time they are executed. You do this by executing the system-stored procedure SP_RECOMPILE as shown:

EXEC sp_recompile Orders

This command is also used to optimize existing plans when new indexes are added to a table.

Automatically Running Your Stored Procedures at SQL Startup

You can have stored procedures automatically executed when SQL Server is started. You can execute as many stored procedures as you want, but each individual stored procedure executed will use up a user connection.

The execution of these stored procedures will begin after the last database has been recovered at startup.

To tell the server which stored procedures to execute at startup, you use the system stored procedure SP_PROCOPTION. The syntax of this procedure is

Exec sp_procoption [@ProcName = ] <procedure name>
    ,[@OptionName = ] <option>
    ,[@OptionValue = ] <value>

Using this stored procedure is actually quite easy. 'Startup'is the default option (it is the only one) and the value parameter can be either 'on' or 'off', with 'on' being the default. So, if I needed the SP_PROCOPTION procedure to execute at startup, I would execute the following statement:

Exec sp_procoption sp_demo_proc