Chapter 2: Optimizing and Debugging

In this chapter, we will be discussing optimizing SQL Server's stored procedures. Here, our focus will not be on optimizing the SQL statements themselves, but instead, how SQL statements can be optimized within the context of a stored procedure.

In this chapter, we will also discuss error handling, signaling error conditions, and debugging stored procedures.

Optimizing Stored Procedures

The performance of a stored procedure is largely dependent on the nature of the T-SQL code within it; however, there are a few things you can do to improve the overall performance of the stored procedure. As we mentioned, in this section, we won't specifically look at optimizing T-SQL code; instead, we will look at the general optimization techniques that we can apply to any stored procedure.

Let's now take a look at these general optimization techniques.

SET NOCOUNT ON

SET NOCOUNT ON is probably the most important optimization setting for stored procedures. By using it, we can greatly reduce the time and network bandwidth used for executing stored procedures, especially the complex ones.

When a T-SQL DML statement (SELECT, INSERT, UPDATE, or DELETE) is executed, statistics relating to the number of rows affected by those statements are passed back to the client for interpretation. For example, suppose we execute the following statement in Query Analyzer:

     SELECT *     FROM dbo.Customers     WHERE CustomerID LIKE 'AN%' 

After execution, we will receive a rowset, and the statistic about the number of rows affected, such as:

     (2 row(s) affected) 

This is not particularly bothersome, and at times, can be even useful when executing a single command in an adhoc fashion. Now, consider the following example of a stored procedure:

     CREATE PROCEDURE TestDataGenerator     AS       DECLARE @count INT       IF OBJECT_ID('dbo.TestDataTable') IS NULL       BEGIN         CREATE TABLE dbo.TestDataTable         (           IDCOL INT PRIMARY KEY,           VarCharCol VARCHAR(255)         )       END       SELECT @count=1       WHILE @count<100       BEGIN         INSERT TestDataTable(IDCOL, VarCharCol)         SELECT @count,           REPLICATE(CHAR((@count%26)+65),@count%255)         SELECT @count=@count+1       END 

This procedure creates a test table (if it doesn't already exist) and adds 100 rows to the test table; perhaps, not the most practical of stored procedures, but it demonstrates the point. Now execute this stored procedure by using the following command:

     EXEC TestDataGenerator 

We don't receive any rowsets back, as this is not the purpose of this particular procedure. However, we will receive 100 messages, 1 message for every INSERT operation, telling us that a row has been inserted:

     (1 row(s) affected)     (1 row(s) affected)     (1 row(s) affected)     ... 

Each one of these messages consumes network bandwidth, which is simply a waste. Imagine the impact if we were inserting, for example, 1000 rows. A far more efficient approach would be to use the SET NOCOUNT command to disable the sending of these messages within the scope of the stored procedure. For example:

     ALTER PROCEDURE TestDataGenerator     AS     SET NOCOUNT ON       DECLARE @count INT     ... 

Now, if we execute the stored procedure, the 100 rows are still inserted, as before; however, no unnecessary messages are sent to our client application for each INSERT, thereby reducing network usage.

Important

NOCOUNT is limited to the scope of the procedure where it is set. This means that when the procedure where NOCOUNT ON is SET has completed execution NOCOUNT will return to its default value of OFF. Therefore, you need to set this explicitly within every procedure.

RECOMPILE

The RECOMPILE option forces the recompilation of a stored procedure's execution plan every time it is run. An execution plan can be thought of as a series of steps that SQL Server must follow internally to achieve the outcome that you have requested. It has the following syntax:

     CREATE PROCEDURE procedure_name     WITH RECOMPILE     ... 

One of the benefits of using stored procedures is that the execution plan is compiled the first time the procedure is run, and from then on, the cached plan is used, which saves precious processing time. Depending on the complexity of the stored procedure, the query optimizer (the part of SQL Server that determines the execution plan) may consider many different plans before selecting the most appropriate one. At times, this consideration can be time-consuming, sometimes as long or longer than the actual execution of the stored procedure itself. Therefore, it is usually desirable to have SQL Server determine an execution plan for a stored procedure on its first execution, and then reuse that execution plan for subsequent executions.

So, when should we use the option of forcing the generation of a new execution plan, every time? This is appropriate only when every execution of the stored procedure differs in dynamics (the number of rows affected, indexes used, and so on) so substantially that the previous execution plan is not suitable. Causing the execution plan to recompile every time would ensure that the most appropriate plan is used. But this carries a real performance cost, as depending on the amount of code and complexity of code within the stored procedure, generating the execution plan for a stored procedure can increase the execution time of the stored procedure significantly (sometimes more than 100%).

If this were the case one would have to question the suitability of the stored procedure for the tasks that it has been assigned to do. To improve performance, in this situation, it may be more suitable to create multiple stored procedures that match all the dynamics of the various execution scenarios and call the appropriate procedure from within our client application. This would allow each procedure to retain its execution plan, thereby reducing execution time, as the procedure plan would not need to be generated each time it was run. It would still result in optimal performance of each execution, by allowing the user to select the most appropriate procedure (and therefore the most appropriate plan) for the type of execution requested.

Rather than embedding the RECOMPILE option into the stored procedure we can specify this as part of the execution statement, for example:

     EXEC procedure_name parameters WITH RECOMPILE 

While this has the same result as embedding the RECOMPILE option within the stored procedure, using this method gives us the ability to selectively choose when the execution plan is recompiled, rather than just recompiling by default on every execution.