SQL Server Performance Tuning for Stored Procedures

Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including:

• Reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
• Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don't remove any result sets columns). This saves developer time.
• Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access. This saves DBA's time.

Keep in mind that just because you use a stored procedure does not mean that it will run fast. The code you use within your stored procedure must be well designed for both speed and reuse. [6.5, 7.0, 2000, 2005] Update 6-6-2005

*****

One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications.

Here are some examples how stored procedures reduce network traffic:

• When an application executes a stored procedure, only a simple, small RPC (remote procedure call) is made from the client to SQL Server. But if the application is not using stored procedures, but sending Transact-SQL code directly from the client to SQL Server, network traffic can often very high. For example, if the amount of Transact-SQL code is 500 lines (and this would not be all that unusual), then it would take hundreds of network packets to transmit the Transact-SQL code from the client to SQL Server. On the other hand, if the 500 lines of Transact-SQL code are in a stored procedure, this code never has to travel the network, as it is already located on the server.
• When an application needs to retrieve one or more rows from SQL Server and then takes some action on this data, such as INSERTing, UPDATing, or DELETing rows in the database based on the data retrieved, network traffic is significantly reduced if all this code is stored in a stored procedure. As before, it only takes a single RPC call to execute a stored procedure. But if all the code to perform these steps is not in a stored procedure, but located in the application, network traffic can be high. For example, first, the application has to send the Transact-SQL code to SQL Server (lots of potential network traffic). Then SQL Server has to return the result set back to the client, then the client has to use the data, and then send additional requests (INSERT, UPDATE, DELETE) to SQL Server, and then SQL Server has to respond back to the client, and so on, until the task is completed. As you can see, this can generate a lot of network traffic. But if all the work is being done from within a stored procedure, network traffic is greatly reduced.
• Along the same lines as above, putting the business logic of your application in stored procedures can help your application's performance. By locating virtually all of the processing on SQL Server, round-trip network traffic is greatly reduced, helping boost performance.

The goal should be to limit network traffic from the client to SQL Server to simple RPCs, and limit the traffic from SQL Server to the client as finished results. [6.5, 7.0, 2000, 2005] Updated 12-6-2005

*****

To help identify performance problems with stored procedures, use the SQL Server's Profiler Create Trace Wizard to run the "Profile the Performance of a Stored Procedure" trace to provide you with the data you need to identify poorly performing stored procedures. [7.0] Updated 12-6-2005

*****

By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. Rarely is this information useful to the client. By turning off this default behavior, you can reduce network traffic between the server and the client, helping to boost overall performance of your server and applications.

There are two main ways to turn this feature off. You can also turn this feature off using a server trace setting, but it is unnecessary as there are easier ways, as described here.

To turn this feature off on at the stored procedure level, you can include the statement:

SET NOCOUNT ON

at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.

If you want this feature turned off for your entire server, you can do this by running these statements at your server:

SP_CONFIGURE 'user options', 512
RECONFIGURE

You may or may not want to do this for your entire server, as it affects every transaction on your server. For example, some application programs need the count information, otherwise they will not work. If this is the case, you don't want to turn this option for the entire server, but just for the stored procedures you write that don’t need the count information. [6.5, 7.0, 2000] Updated 12-6-2005

*****

Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.

Two ways to help reduce the length of a transaction are to: 1) break up the entire job into smaller steps (or multiple stored procedures) so each step can be committed as soon as possible; and 2) take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server. [6.5, 7.0, 2000, 2005] Updated 6-6-2005