Enhance SQL Server Stored Procedure Performance – Tuning Tips

In this article, I will share some tips for improving stored procedure performance and highlight key dos and don'ts when writing stored procedures in SQL Server. To illustrate these concepts, I will use an example based on an employee details table with columns such as FirstName, LastName, AddressLine1, and Country.

Select only the specific columns you need in a query rather than using SELECT *

In the snippet below, I have written two select statements that are used to fetch records from the employee table. The first one is best practices, which means I pick the specific column in the select statement. In the second snippet, I have used select *, but it is not recommended to use this when fetching records from the table.

--- Always do specific columns
SELECT FirstName, LastName, AddressLine1, Country FROM EmployeeDetail

--- Do not Do
SELECT * FROM EmployeeDetail

Avoid using the prefix sp_ for your stored procedure names

Avoid prefixing your stored procedure names with sp_; instead, use a different prefix, such as usp_YourStoredProcName.

Using sp_ is considered bad practice because SQL Server first searches for procedures with that prefix in the master database before searching in your specific database. This behavior causes unnecessary overhead, as SQL Server prioritizes system procedures when it encounters the sp_ prefix, potentially increasing query execution time.

-- Do not create like this
CREATE PROCEDURE [dbo].[sp_GetEmployeeDetails]
@EmployeeId int
AS
BEGIN
    SELECT FirstName, LastName, AddressLine1, Country FROM EmployeeDetail WHERE EmployeeId = @EmployeeId
END

-- Always keep prefix different from sp_
CREATE PROCEDURE [dbo].[usp_GetEmployeeDetails]
@EmployeeId int
AS
BEGIN
    SET NOCOUNT ON
    SELECT FirstName, LastName, AddressLine1, Country FROM EmployeeDetail WHERE EmployeeId = @EmployeeId
END

Use IF EXISTS to determine whether a record is present in the table

Suppose the Exists function is used to check whether a record exists in a table or not. If any records are found, then it will return true; otherwise, it will return false. One benefit of using the If Exists function is that if any match is found, then it will stop execution and return true, so it will not process the remaining records, which will save time and improve performance.

In the example below, I have used the If exists function with select 1 and select *. We should always use select 1 because, for the If Exist function, it doesn't matter how many records there are; it checks for any record, and if it finds one, it will return true.

-- When you want to check if the record is there in the table
-- Do's
Use IF Exists(select 1 from EmployeeDetail where EmployeeId=@EmployeeId)

-- Don'ts
Use IF Exists(select * from EmployeeDetail where EmployeeId=@EmployeeId)

Keep your transaction as short as possible

When performing multiple inserts, updates, or deletes in a single batch, we are required to check that all of the operations either succeeded or failed. In this situation, we should go for the transaction. When we are using the transaction in-store procedure, we need to make sure that the transaction is very small and should not block other operations. The length of the transaction affects blocking, and sometimes it ends up in a deadlock.

Implement a try-catch block to handle errors effectively

Error handling is a core part of any application. SQL servers 2005 and 2008 have a new, simple way of handling errors using a try-catch block.

BEGIN TRY
    -- SQL Statement
    -- DML Statement
    -- Select Statement
END TRY
BEGIN CATCH
    -- Error handling code
    -- Your logic to handle error
END CATCH

Include the schema name when referencing an object name

The schema name should be used with the store procedure name because it will help compile the plan. It will not search in another schema before deciding to use the cached plan. So, always prefix your object with the schema name.

-- Always keep prefix different from sp_
Create procedure [dbo].[usp_GetEmployeeDetails]
@EmployeeId int
As
Begin
    SET NOCOUNT ON
    select FirstName, LastName, AddressLine1, Country from EmployeeDetail where EmployeeId=@EmployeeId
End

Enable the SET NOCOUNT ON statement at the start of the stored procedure

In the first procedure, I did not use the SET NOCOUNT ON statement, so it printed a message for how many rows were affected. This means if we do not use this, it will print an extra message, Which will affect performance.

-- Always keep prefix different from sp_
Create procedure [dbo].[usp_GetEmployeeDetails]
@EmployeeId int
As
Begin

select FirstName,LastName, AddressLine1, Contry from EmployeeDetail

End

-- USING SET NOCOUNT ON STATEMENT
CREATE PROCEDURE [dbo].[usp_GetEmployeeDetails]
AS
BEGIN
    SET NOCOUNT ON
    SELECT FirstName, LastName, AddressLine1, Contry FROM EmployeeDetail WHERE Contry = 'Ind'
END