SQL Server

Performance Tuning of Stored Procedure

  1. Include SET NOCOUNT ON

    • By default SQL returns the number of rows affected by DML statements. By default SET NOCOUNT is ON.

    • It is mostly required for debugging purposes or processing based on the return values. If you do not require this information sent to user explicitly SET NOCOUNT OFF.

    Benefit: It will reduce Network traffic.

    SQL Code:
    1. --Below code will not return Number of rows affected by DML statements,  
    2. SET NOCOUNT OFF; 
  2. Refer to database objects by a schema name and the object name instead of only object name,

    • If you do not specify the schema/ fully qualified name of the object, SQL Server will search the object in all possible schemas and decide which schema to use for object.

    • Instead if you use fully qualified object name, you can save search and decide time.

    Benefit: Reduce search and decide time.

    SQL Code:
    1. --Existing / Old Code  
    2. SELECT * FROM Customer  
    3. --Best Practice /New Code  
    4. SELECT * FROM dbo.Customer  
    Reference: SQL Server Best Practices – Implementation of Database Object Schemas 

  3. Avoid using sp_ prefix in Stored Procedure Name

    sp_ prefix designates system stored procedures. When you use sp_ prefix SQL server search Master database first and if it not able to find the find the Stored procedures then it search current session database.

    Benefit: Reduce search time in master database.

    Reference: SR0016: Avoid using sp_ as a prefix for stored procedures

  4. Avoid Lengthy Transaction:

    • Long transaction block will decrease performance by blocking resource.
    • So keep transaction as short as possible.

  5. With IF Exist statement Avoid using Select *, instead use Select (1)

    Select * statement will process all the records in table. Select (1) will check for 1 record in table or condition satisfying the condition.

    SQL Code:
    1. --Existing / Old Code  
    2. IF EXISTS ( SELECT * FROM sys.objects  
    3. WHERE object_id = OBJECT_ID(N'ProcName')  
    4. AND type IN ( N'P', N'PC' ) )  
    5. --Best Practice /New Code  
    6. IF EXISTS ( SELECT 1 FROM sys.objects  
    7. WHERE object_id = OBJECT_ID(N'ProcName')  
    8. AND type IN ( N'P', N'PC' ) ) 
  6. Avoid using Select *, instead use Select ColumnName

    When you require all columns from table, then only use Select *. For all other cases use Select ColumnNames.
    Benefit: Avoid Network traffic

    SQL Code:
    1. --Existing / Old Code  
    2. SELECT * FROM dbo.Customer  
    3. --Best Practice /New Code  
    4. SELECT CustomerId, CustomerName, CustomerAddress FROM dbo.Customer  
  7. Use NOLOCK statement in all cases or it should not be used for all cases.

    If stored procedure uses parameter based code block (if/else) and if you use WITH (NOLOCK) in some cases only then it will lead to bad execution.

    It should be used in all cases or it should not be used at all.

    SQL Code:
    1. Create Procedure GetCustomerInfoByLob  
    2. (  
    3. @LOBCd NVARCHAR(10)  
    4. )  
    5. As   
    6. BEGIN  
    7. --Select All PL Customers  
    8. IF @LOBCd="PL"  
    9. SELECT * FROM PLData WITH (NOLOCK)  
    10. --Select All CL Customers  
    11. ELSIF @LOBCd="PL"  
    12. --Below Line of Code will lead to Bad Execution code.  
    13. SELECT * FROM PLData  
    14. --Select All(Both PL and CL) Customers  
    15. ELSE   
    16. SELECT * FROM PLData WITH (NOLOCK) UNION  
    17. SELECT * FROM PLData WITH (NOLOCK)  
    18. END  
Ebook Download
View all
Learn
View all
Membership not found