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
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.
SET @query= N'SELECT * FROM dbo.emp_student where pk_student_id=' + CAST(@vendorid AS VARCHAR(100))
In this option only one plan is cached and is used for different values of vendorids.
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.