Best Practice: Stored Procedure Optimization Tips

Best Practice - SeniorDBA

SQL Server performance isn’t a simple checkbox you check that improves database performance. You have to look at many different factors to gain incremental improvements. Each change might give you a 5-10% improvement, but 10 changes might lead to a 50-80% improvement to the speed of your stored procedure.

In this article by Pinal Dave, we see some of the most common tweaks to your stored procedures that should be addressed as a best practice in your environment.

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
 AS
 SET NOCOUNT ON;
 --Procedure code here
 SELECT column1 FROM dbo.TblTable1
 -- Reset SET NOCOUNT to OFF
 SET NOCOUNT OFF;
 GO
  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
 -- Instead of
 SELECT * FROM MyTable -- Avoid this method
 --And finally call the stored procedure with qualified name like:
 EXEC dbo.MyProc -- Preferred method
 --Instead of
 EXEC MyProc -- Avoid this method
  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)
  • Use the sp_executesql stored procedure instead of the EXECUTE statement

You can read the entire article here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s