How to Check performance of the SPROC

Stored procedures are an essential part of database management systems. They are used to execute frequently used queries and reduce the load on the database server. However, if not optimized correctly, they can cause performance issues. In this blog, we will discuss how to check the performance of a stored procedure. 

Steps to Check Performance of a SPROC 

1. Identify the SPROC: The first step is to identify the stored procedure that needs to be optimized. You can use SQL Server Management Studio (SSMS) to identify the stored procedure. 

2. Check Execution Time: Once you have identified the stored procedure, you can check its execution time. You can use the SET STATISTICS TIME ON command to check the execution time of the stored procedure. 

3. Check Query Plan: The next step is to check the query plan of the stored procedure. You can use the SET SHOWPLAN_TEXT ON command to check the query plan. 

4. Check Indexes: Indexes play a crucial role in the performance of a stored procedure. You can use the sp_helpindex command to check the indexes of the stored procedure. 

5. Check for Blocking: Blocking can cause performance issues in a stored procedure. You can use the sp_who2 command to check for blocking. 

6. Check for Deadlocks: Deadlocks can also cause performance issues in a stored procedure. You can use the DBCC TRACEON(1204) command to check for deadlocks. 

Examples :

Here are some examples to help you understand how to check the performance of a stored procedure: 
 

To Try this queries yourself I am sharing the Table, Data, SP query so you can direct run and perform this queries :

-- Step 1: Create a dummy table
CREATE TABLE dbo.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID NVARCHAR(10),
    OrderDate DATETIME,
    ProductID INT,
    Quantity INT
);

-- Step 2: Insert dummy data into the table
INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate, ProductID, Quantity)
VALUES
    (1, N'ALFKI', '2024-01-23', 101, 5),
    (2, N'ALFKI', '2024-01-24', 102, 3),
    (3, N'BONAP', '2024-01-25', 103, 7),
    (4, N'BONAP', '2024-01-26', 104, 2),
    (5, N'COSME', '2024-01-27', 105, 4);

-- Step 3: Create a stored procedure
CREATE PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerID NVARCHAR(10)
AS
BEGIN
    SELECT *
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID;
END;

Example 1: Check Execution Time 

SET STATISTICS TIME ON 
EXEC dbo.usp_GetOrdersByCustomer @CustomerID = N'ALFKI' 
SET STATISTICS TIME OFF 

Example 2: Check Query Plan 

SET SHOWPLAN_TEXT ON 
EXEC dbo.usp_GetOrdersByCustomer @CustomerID = N'ALFKI' 
SET SHOWPLAN_TEXT OFF 

Example 3: Check Indexes 

EXEC sp_helpindex 'dbo.usp_GetOrdersByCustomer' 

Example 4: Check for Blocking 

EXEC sp_who2 

Example 5: Check for Deadlocks 

DBCC TRACEON(1204) 

 

Conclusion 

In conclusion, checking the performance of a stored procedure is essential to ensure that it runs efficiently. By following the steps mentioned above, you can identify the performance issues and optimize the stored procedure. I hope this blog helps you in optimizing your stored procedures. If you have any questions or suggestions, please feel free to leave a comment below.