Category - SPROC

Quick Guide: SPROC Performance Check
SQL-Server
Quick Guide: SPROC Performance Check
Hrutvik Barot
Jan 27, 2024

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  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.  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.  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.  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.  Check for Blocking: Blocking can cause performance issues in a stored procedure. You can use the sp_who2 command to check for blocking.  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. 

Read More
magnusminds website loader