Improve stored procedure performance in SQL Server

This blog covers simple yet useful tips and optimization to improve stored procedure performance.

1. Use SET NOCOUNT ON

SQL Server returns informational messages when running select or DML operations. In case a procedure has many such statements a cursor or a while loop SQL Server will display lot of such messages increasing network traffic. These messages can be suppressed with SET NOCOUNT ON and can increase performance by decreasing network traffic.

2. Use fully qualified procedure name

A fully qualified object name is database.schema.objectname. When stored procedure is called as schemaname.procedurename, SQL Server can swiftly find the compiled plan instead of looking for procedure in other schemas when schemaname is not specified. This may not be a great boost to the performance but should be followed as best practice. All objects inside procedure should also be referred as schemaname.objectname.

3. sp_executesql instead of Execute for dynamic queries

The sp_executesql allows for cache plan reuse and protects from SQL Injection. Let’s see an example of the plan reuse.

DBCC FREEPROCCACHE
GO
Declare 
	@dynamic_sql varchar(max),
	@salesorderid int
SET @salesorderid=43660

SET @dynamic_sql=' SELECT * FROM Sales.SalesOrderDetail where SalesOrderID=' 
	+ CAST(@salesorderid AS VARCHAR(100))
EXECUTE(@dynamic_sql)

The above query executes a dynamic query using EXECUTE command for two values of salesorderid 43660 and 43661. Let’s analyze the cached plans.

As shown in above snapshot, there are two separate plans for the two salesorderids. Let’s now execute the same query with sp_execute SQL and analyze the cached plans.

DECLARE @dynamic_sql NVARCHAR(100)
SET @dynamic_sql = N'SELECT * FROM Sales.SalesOrderDetail where SalesOrderID=@salesorderid'
EXECUTE sp_executesql @dynamic_sql, N'@salesorderid int', @salesorderid = 43661

The above query uses sp_executesql to execute the dynamic query for 2 different values of salesorderid. Let’s analyze the cached plans.

As shown in above snapshot, only one plan is cached and is used for different values of salesorderid.

4. Using IF EXISTS AND SELECT

IF EXISTS is used to check existence of a record, object etc.. And is a handy statement to improve performance of queries where in one only wants to check existence of a record in a table instead of using that record/row in the query. When doing so use IF EXISTS(SELECT 1 from mytable) instead of IF EXISTS(Select * from mytable) as only thing we are interested in is to check the presence of record/s. So, if the query return 1 then record is present else it’s not. It’s needless to return all column values.

5. Avoid naming user stored procedure as sp_procedurename.

If a stored procedure begins with sp_ then SQL Server first searches it in master database and then in the current user database. This might cause slight performance issues and moreover it may result in wrong results if a stored procedure with same name exists in master database.

6. Use set based queries wherever possible.

T-SQL is a set based language and thus loops don’t work well in here. Cursors and while loop are to be used only when a set based query is either expensive or can’t be formulated.

7. Keep transaction short and crisp

The longer the transaction the longer the locks will be held based on isolation level. This may result in deadlocks and blocking. Open a new query window and execute the below query

use AdventureWorks2014
GO
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT * FROM Sales.SalesOrderDetail

Note the session id for the query. Open a new query window and execute the below query. Note down the session id of the query.

begin tran 
Update Sales.SalesOrderDetail 
SET OrderQty=50 WHERE SalesOrderDetailID=1

The above update query will wait on the select query on shared lock. Let’s analyze the locks for these two sessions.

As shown in above snapshot, session 58 the update query is waiting on shared lock taken by session 57.

Do follow these tips and let me know how does it increases procedure performance. Will come back with some more tips and best practices.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook