Optimizing Stored Procedures | ppk on JavaScript, 1/e

Stored procedures are the optimal method for compiling a query to SQL Server. You can pass stored procedures parameters to make the procedure conform to your specifications. In SQL Server 2000, stored procedure performance has been slightly improved because SQL Server now caches these parameters. On some applications, such as a SAP SD workload, this could mean a 6 percent improvement in stored procedure performance over SQL Server 7.0.

Determining Parameters for Stored Procedures

To determine the parameters a stored procedure accepts, you can run sp_help as shown here:

sp_help <stored procedure name>

For example, to determine details about the CustOrderHist stored procedure in the Northwind database, use the following syntax:

sp_help CustOrderHist

This outputs valuable metadata about your stored procedure, and the parameters it's expecting.

Note

You must be connected to the database that has the stored procedure you'd like to investigate before you can run the sp_help stored procedure. In other words, you can't use the following syntax: sp_help Northwind.dbo.CustOrderHist.

Tip

If you want help on an individual system stored procedure, you can highlight the stored procedure name in Query Analyzer and use the SHIFT-F1 key combination to access help on the procedure.

Troubleshooting Cached Stored Procedures

In some rare instances you may find that SQL Server caches the wrong stored procedures. For example, you make a change to a stored procedure and find that SQL Server still uses the old cached version. This means that SQL Server may be using its procedure cache too aggressively in order to improve performance. Run a test to see how long it takes a stored procedure to execute in a noncached state.

You don't want to have to restart your SQL Server to flush the cache. Instead, fix the problem by using DBCC FREEPROCCACHE. This DBCC command flushes your procedure cache and outputs the following message:

DBCC execution completed. If DBCC printed error messages,  contact your system administrator.

Along these same lines, you can run the DBCC FLUSHPROCINDB command to force SQL Server to re-create the stored procedures in the database. The command uses the database ID rather than the database name. To determine the database ID quickly, use the db_id() function as shown here:

DECLARE @databaseid int SET @databaseid = DB_ID('Northwind') DBCC FLUSHPROCINDB (@databaseid)

If you're trying to benchmark a database and don't want SQL Server to cache data, you can use a similar DBCC command. By issuing the following command, SQL Server will flush its data cache.

DBCC DROPCLEANBUFFERS

Tuning Stored Procedure Performance

Stored procedures make a lot of sense when programming an application. When you execute a stored procedure from your application, you don't have to pass the entire query. Instead, you only have to pass SQL Server the stored procedure name, along with any parameters it may need. This saves network traffic in addition to optimizing performance because of SQL Server's caching abilities.

It also takes quite a bit more effort to change code, versus changing backend stored procedures. To change hard-coded queries in your application, you have to recompile and redeliver the application. If you change a stored procedure, you only need to recompile the stored procedure. Here are a few performance tips to make your stored procedures run more efficiently:

Use output statements instead of returning the entire resultsets. Output statements perform much more efficiently.
Include the SET NOCOUNT ON statement at the top of your stored procedure. This reduces network traffic as well as avoiding potential problems. The statement forces SQL Server to omit returning the amount of rows that were returned with the query. This count is considered a resultset that is returned to the client. Along these same lines, disable trace flag 3640, which can slow down performance as well.
Keep parameters compact. Only use a large parameter if you absolutely need it.
Never prefix your stored procedures with sp_, which is reserved for the system stored procedures. Although SQL Server allows you to compile stored procedures with this name, it is not recommended as it may impact execution time.

Building Dynamic Stored Procedures

One of the questions I'm often asked by developers is how to create commands in a stored procedure on the fly. For example, you may have an application that needs to sort dynamically on any column, based on user input. Let's take that scenario and create a small stored procedure to perform that type of function against the Customers table in the Northwind database.

First, create two parameters, one for the column you'd like to sort on, and the other to specify a descending or ascending sort. Build your SQL command into a variable, then execute it using the EXEC command, with the parameter in parentheses. The following code is an example:

CREATE Procedure WF_SCustomers @orderbyclause varchar(13), @ordertype char(4) AS  DECLARE @strcommand varchar(200) SET @strcommand = 'SELECT CustomerID, CompanyName,  ContactName FROM CUSTOMERS ORDER BY ' + @orderbyclause +  ' ' + @ordertype EXEC (@strcommand) GO

To execute this stored procedure, simply execute the following command in your application:

WF_SCustomers 'CompanyName', 'Desc'

Pay special attention to the @strcommand parameter within the parentheses in the stored procedure. If you place the command in parentheses, you can execute SQL statements. Otherwise, SQL Server thinks you want to execute a stored procedure. For example, if the variable had not been within parentheses, the following error would occur upon execution of the stored procedure:

Server: Msg 2812, Level 16, State 62, Line 5 Could not find stored procedure 'SELECT CustomerID,  CompanyName, ContactName FROM CUSTOMERS ORDER BY CompanyName Desc'.