SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

SQL SERVER - Stored Procedure Optimization Tips - Best Practices spoptimizationtricks

  • 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 *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short 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. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Reference: Pinal Dave (https://blog.sqlauthority.com)

Best Practices, SQL Coding Standards, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Difference Between Update Lock and Exclusive Lock
Next Post
SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

Related Posts

181 Comments. Leave new

  • Nikhil Khoda
    June 15, 2010 5:33 pm

    Hi Pinal and all the readers

    Nice article..

    Btw one more tips of using DDL and DML query..
    Always use the DDL queries like create table or drop table first and then use DML query like Select, Update or Delete

    As DDL queries always need a recompilation of procedures in order to make the changes in our DML Queries

    Reply
  • Nice Artical.

    Reply
  • Lakshmi Narayanan R
    June 25, 2010 6:36 pm

    Thanks a lot. Very nice explanation

    Reply
  • Manu Parashar
    July 8, 2010 8:50 pm

    gr8 article ,,thx Pinal

    Reply
  • hi all,

    regd. sql sp, i face the prob. regd. passing date value…

    it gives error ‘Invalid syntax at date-part..’

    here i give u the code 4 sp …

    CREATE Procedure BimstInsert
    (@Date Datetime,@Pc Numeric(6,0),@Ic Numeric(6,0),@Qt Numeric(6,0))
    As
    Begin

    declare @ra Numeric(6,2)
    Declare @ch Numeric(6,0)

    Select @ra=I.Rate from ItemMst As I
    Where @Ic=I.Code
    Select @ch=IsNull(Max(B.ChNo),0)+1 from BiMst As B

    Insert into BiMst Values
    (@ch,@Date,@Pc,@Ic,@Qt,@ra,(@Qt*@ra))
    End

    ———————————————————–

    exec BimstInsert (’10/jan/2010′,1,1,10)

    i also tried for diff. date style…

    pls. help me

    thanks in advance…

    bye…

    Reply
  • where are the values which you passing in
    Insert into BiMst Values
    (@ch,@Date,@Pc,@Ic,@Qt,@ra,(@Qt*@ra))

    the value must me in datetype format which you will be passing in @date

    Reply
  • Dipak Kansara
    August 3, 2010 4:57 pm

    Hi sir,
    I am fresher and i am working in welcomenetwork company
    i have string like ‘0012340056789’
    but i want only the string like ‘12340056789’
    that is starting 0 should be removed so how can i
    do in sql server function
    Reply
    Thanks & Regards,
    Dipak B. Kansara

    Reply
  • Charlie Rubin
    August 5, 2010 8:32 am

    Some of my most impressive performance improvements have come from replacing cursor-based code with carefully constructed set-based processing. That tip alone can pay the bills…

    Reply
  • Is it better to have multiple small stored procedure or one big stored procedure (Combining multiple sp into one and having some conditional logic in it to choose which sql should be used) ?

    I love to have multiple stored procedure specific to the user request in place of having one which can serve all kinds of request but i need some better reasons for it.

    Thanks for the all your help over the years…

    Reply
    • I think it is better to have seperate procedures so that it is easy to maintain. If the result set contains different number of columns, it is not possible to design a report based on it

      Reply
  • Solution for optimizing SQL stored Procedures are WITH (ROWLOCK) for insert, update and select, and if you dont do any calculation on your syntax you can use also WITH (NOLOCK).
    Nice site..

    Reply
  • Good Site

    Reply
  • Hi, Pinal.

    Could the number of parameters being sent to a stored procedure from a VB .Net application affect the performance of an If Exists (…) Update… statement in the procedure?

    Specifically: The procedure takes 144 parameters some of which are varchar(200), and others are varchar(50). The procedure is called from a live data broadcast handler sub-routine written in VB .Net.

    The updates seem to take excessively long time, specially when the broadcasts are quicker. So much so that the time lag grows into several hours over a day.

    I have not maintained an index on the table, because it causes another procedure (with insert commands) to slow down.

    Any suggestions would be appreciated.

    Reply
    • Why are you using 144 parameters? How many columns does your table have?

      Reply
    • their in many more way divedi procedure into 2 and call one by one
      our system also have limit if over than load program in system then it also slow
      why the data base not slow it also effect

      Reply
  • 144 columns :-)

    Reply
  • Sir,
    Thanks for the information or tips you provide for optimazation in SQL. I have been working on an application which uses XML file and database as well. We read XML files and extract required information and update that information in database. We have approx 18 stored procedure which updates 7-8 tables for a single XML file.
    And we have about 22500 Xml files. It takes about 3 hours on local computer and if we update remote server using this application it takes about 10 hours to run completely. Please tell me more about optimization in SQL stored procedures.

    Thanks

    Reply
  • > The reuse of the existing complied plan will result in >improved performance.
    It is true for sp_executesql if we have uniform distribution of data. In other case we will have degradation of performance. You can save a bit time on reuse existing plan but you can lose a lot time when you reuse bad plan. So in my opinion if you have big table with non-uniform distribution of data reusing of plan is bad idea

    Reply
  • i want to auto insert records in a muster entry table with imp fields in time & out time as a data type date time

    should i prefere trigger to auto insert my in time daily
    at 9:00 am except weekends????

    Reply
    • What is the purpose of this? This cannot be automated using a trigger. You should use a job to do this

      Reply
  • declare @CountryID [int]
    select @CountryID=isnull(max(CountryID),0)+1 from Country

    no need to use trigger simple query u can auto increment

    Reply
  • 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

    Reply
  • Use schema name with object name

    I’m not able to find the difference between
    SELECT * FROM dbo.MyTable — Preferred method
    – Instead of
    SELECT * FROM MyTable — Avoid this method

    Tell me the difference . I dont undestand this concept

    Reply
  • VENKATA KRISHNA
    December 29, 2010 7:20 pm

    Hi Dave,
    As you said
    “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.”

    – so i have created the stored procedure sp_test in my own database and same in master database.

    i have excuted the stored procedure from my local database and i found that local stored procedure is getting executed all time.

    please clarify me..

    Reply

Leave a Reply