We'll make much use of the pubs and Northwind databases for our examples in this book. Here's a tip for restoring them to their default condition. First, login as SA in Query Analyzer. Then, go to your SQL Server installation directory and browse to the INSTALL subdirectory under it. From this directory, run either the INSTPUBS.SQL or INSTNWND.SQL script in Query Analyzer to refresh the pubs or Northwind database, respectively. If your server is only in integrated mode, run the scripts, and then run the following script in the Query Analyzer:
USE dbname GO EXEC sp_changedbowner 'domain\userID' GO EXEC sp_changedbowner sa GO
will be your domain
In this chapter, we covered several issues concerning stored procedures and have set the scene for the forthcoming chapters of this book. We have
Don't just forget your stored procedures once they are created, just as you will never forget your indexes. When a new index is added to a table, or any
Finally, don't make stored procedures what they are not. Don't replace SQL Server functionality such as CHECK constraints with a stored procedure unless the CHECK constraint doesn't offer enough functionality
We shall return to these ideas in more detail as we progress though the book.
In this chapter, we will be discussing optimizing SQL Server's stored procedures. Here, our focus will not be on optimizing the SQL statements
In this chapter, we will also discuss error handling, signaling error conditions, and debugging 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
Let's now take a look at these general optimization techniques.
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,
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
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.
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.
option forces the recompilation of a stored procedure's
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
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
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
If this were the case one would have to question the suitability of the stored procedure for the
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.