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:
   

   --Below code will not return Number of rows affected by DML statements,
   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:
   

   --Existing / Old Code
   SELECT * FROM Customer
   --Best Practice /New Code
   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:
   

   --Existing / Old Code
   IF EXISTS ( SELECT * FROM sys.objects
   WHERE object_id = OBJECT_ID(N'ProcName')
   AND type IN ( N'P', N'PC' ) )
   --Best Practice /New Code
   IF EXISTS ( SELECT 1 FROM sys.objects
   WHERE object_id = OBJECT_ID(N'ProcName')
   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:
   

   --Existing / Old Code
   SELECT * FROM dbo.Customer
   --Best Practice /New Code
   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:
   

   Create Procedure GetCustomerInfoByLob
   (
   @LOBCd NVARCHAR(10)
   )
   As
   BEGIN
   --Select All PL Customers
   IF @LOBCd="PL"
   SELECT * FROM PLData WITH (NOLOCK)
   --Select All CL Customers
   ELSIF @LOBCd="PL"
   --Below Line of Code will lead to Bad Execution code.
   SELECT * FROM PLData
   --Select All(Both PL and CL) Customers
   ELSE
   SELECT * FROM PLData WITH (NOLOCK) UNION
   SELECT * FROM PLData WITH (NOLOCK)
   END