Tips to improve performance of stored procedure - Sql Server

Jun 03, 2019 BY xtreme

1. Use SET NOCOUNT ON

This one line of code, put at the top of a stored procedure turns off informational messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. By removing this extra overhead from the network it can greatly improve overall performance of your database operations.
Note: performance booster

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
AS
   BEGIN
   SET NOCOUNT ON;


2. Use fully qualified stored 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. 
Tip: For more better performance, All objects inside procedure should also be referred with schemaname.

3. Use sp_executesql instead of Execute for dynamic queries

The sp_executesql allows for cache plan reuse and protects from SQL Injection.

Case: dynamic query
This Option use two separate plans for the two vendorids.

DBCC FREEPROCCACHE
        GO
        Declare 
               @query varchar(max),
               @vendorid int
               SET @vendorid=31
               SET @query= N'SELECT * FROM dbo.emp_student where pk_student_id=' + CAST(@vendorid AS VARCHAR(100))
               EXECUTE(@query)

Case:  sp_executesql
In this option only one plan is cached and is used for different values of vendorids.

DBCC FREEPROCCACHE
        GO
        Declare @query Nvarchar(max)
        SET @query = N'SELECT * FROM dbo.emp_student where pk_student_id=@vendorid'
        EXECUTE sp_executesql @query, N'@vendorid int', @vendorid = 31


4. 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.


5. 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. 


6. Using IF EXISTS AND SELECT

IF EXISTS is used to check existence of a record, object etc. It 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. 

Use IF EXISTS(SELECT 1 from mytable) instead of IF EXISTS(Select * from mytable) 

As our main concern here is to check the presence of record/s, not to retrieve or check all records. So, if the query return 1 then record is present else it’s not. 

Related