Optimization Of Stored Procedure In SQL Server

This article describes that how we can optimize the Stored Procedures so that we can get better performance while our application is talking to database. I am enlisting some of the points from my personal experience.


1)    Always Use Fully Qualified Name For All Database Objects:

While working with stored procedure we need to pass name of database objects (table, view, function, other stored procedure(s) etc.) several times. I recommend every developer to use fully qualified object names instead of using only object name. There is a very strong reason behind this recommendation.

  • SQL server always has to find supplied database object name from sys object that took some millisecond of processing time and consumption of CPU resources. And if, we pass fully qualified name then that processing time and CPU resource consumption to search the object from sys objects get reduced to significant amount.
  • Another reason behind the scene is it helps SQL Server to directly finding the Complied Cache Plan if available instead of searching the objects in other possible schema. This process of searching and deciding a schema for a database object leads to COMPILE lock on stored procedure which decreases the performance of a stored procedure.
  • Example
–Within a SQL statement
SELECT * FROM TableName – Not Recommended
SELECT * FROM dbo.TableName — Recommended method
–How to call a stored procedure
EXEC ProcName     — Not Recommended
EXEC dbo.ProcName — Recommended method


2)    Avoid Cursors whenever possible:

  • A cursor is a Devil in SQL Server which kills the SQL performance.
  • It always works with only one Record Set at a time and consumes a lot of resources and overhead processing to maintain current record position degrades the performance of database. This is the reason that while working with cursor we always has to face performance issue.
  • WHILE loop is an alternate of using cursor and we could use this WHILE loop in cases when we need to process our current record one by one as a replacement of cursor.
  • We could use SET-based approach instead of Cursor based approach because the SQL Server engine is designed and optimized to perform SET-based operation very fast.

3)   Never Use Prefix “sp_” For Naming Stored Procedures:

  • “sp_” is the native or inbuilt prefix of SQL server.
  • All the internal stored procedures (master database) of SQL server have been named with this prefix and internally SQL uses this prefix to search and manipulate inbuilt stored procedures.
  • If any user put the same “sp_” prefix before a stored procedure then SQL consider this as an internal stored procedure and start searching in all local/internal stored procedure (in master database) followed by current database session which causes extra processing overhead.
  • So we should always avoid using this “sp_” prefix before any user stored procedure. 
4)   Avoid Using IF EXISTS (SELECT * ) :


  • The IF EXISTS () clause always returns Boolean value (true / False).
  • We use this clause to check for the existence of a record in current or another table.
  • We pass a SELECT statement inside IF EXISTS (a SELECT statement) clause and it returns a Boolean value indicating that record exists or not in database.
  • We could pass SELECT * or a single column name or 1(strongly recommended).
  • In the following table in trial 1 we have used SELECT * and in trial 2 We used SELECT 1.  Use can see how much difference we do have using * vs 1 in SELECT statement.
          PRINT ‘EXISTS’
Client processing time
Trial 2(SELECT 1)
Trial 1 (SELECT *)
Client processing time
Total execution time
Wait time on server replies
  • The main point i want to state here is SQL doesn’t use whatever be the output of select statement, it just checks true or false value.
  • So to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement rather than other values.
  • Also id we use * then SQL have to expand all the columns again to get the list of columns which causes extra overhead in processing the query so we have been recommended to use 1 instead of using * in IF EXISTS() clause.

5)    Always include ‘SET NOCOUNT ON’ statement:

  • Here I will demonstrate the meaning and benefit of SET NOCOUNT ON within stored procedure.
  • Whenever we create a stored procedure we always see SET NOCOUNT ON;
  • With every SELECT and DML statement, the SQL server returns a message which indicates the number of affected rows by that statement.
  • This information is mostly helpful in debugging the code, but after debugging it’s useless.
  • 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 ON can provide a significant performance boost because network traffic is greatly reduced.
  • Example
–Write Procedure code
SELECT column1,column2 FROM dbo.MyTable


6)    For Error Handling Use TRY-Catch Block:

  • Error handling features has been greatly introduced in SQL Server 2005.
  • Now we can handle the error in stored procedure itself which gives a great relief to developers.
  • Now a developer can use TRY CATCH block to handle the error in database itself and raise proper error message with type of error with error no, line no etc.
  • Example of using Try CATCH
–Your T-SQL code
–Your error handling mechanism


7)    Prefer sp_executeSQL ProcName Instead Of EXECUTE() statement:

  • Following are some of the points which i collected with spending a lot of time with Google regarding use of executesql() vs EXECUTE().
  • sp_executesql() gives you the flexibility to work with parameterized statements while EXECUTE does not.
  • A parameterized statement prevents you from SQL Injection and also you have added advantage of using Cached Query Execution Plan.
  • Since sp_executesql() stored procedure supports parameters so it improves the readability of code over EXECUTE() statement.
  • SQL Server creates a Query Execution Plan when sp_executesql() is compiled first time and rest all times the same Query Execution Plan is reused until you changes parameter values.
  • sp_executesql() can be used to execute a T-SQL statements until you changes parameters because the Transact-SQL statement itself remains constant Query Optimizer is likely to reuse the execution plan it generates for the first execution.
  • Use SP_EXECUTESQL rather than EXEC(), it has better performance and improved security.
  • Example:
SET @value = someValue
SET @Query = ‘SELECT * FROM dbo.TableName WHERE  Column = ‘ + value;
EXEC (@Query)
If we again execute the above batch using different @value, then the execution plan for SELECT statement created for @value = someValue would not be reused. However, if we write the above batch as given below,
SET @Query = N’SELECT * FROM dbo.TableName WHERE Column = @value’
EXECUTE sp_executeSQL @Query, N’@value int’, value = someOtherValue
The compiled plan of this SELECT statement will be reused for different value of @value parameter. The reuse of the existing complied plan will result in improved performance.


8)    Shorten Transaction as much as possible:

  • The length of transaction affects blocking and deadlocking.
  • Exclusive lock is not released until the end of transaction.
  • In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks.
  • For faster execution and less blocking, the transaction should be kept as short as possible.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s