When we talk about backend performance, stored procedures play a major role in SQL Server. They are precompiled, reusable, and secure but if not written properly, they can become slow and inefficient. In this article, we’ll learn how to write high-performance stored procedures with simple explanations, tips, and an example flowchart.

What is a Stored Procedure?
A stored procedure is a group of SQL statements stored in the database. Instead of sending multiple queries from your application, you can just call one stored procedure — making the process faster and easier to maintain.

Example
CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END

You can execute it like:
EXEC usp_GetCustomerOrders @CustomerID = 1001;

Why Performance Matters?
When you work with large data — millions of rows — even small inefficiencies can slow everything down.
Optimizing your stored procedures can:

• Reduce CPU and memory load
• Improve response time
• Make the system more scalable
• Lower the chance of deadlocks or timeouts

Best Practices for Writing High-Performance Stored Procedures

Let’s go step by step.

1. Use Proper SET Options
Start every stored procedure with:
SET NOCOUNT ON;

This stops the “(X rows affected)” message from returning, improving speed slightly and reducing unnecessary output.

2. Avoid SELECT *
Don’t select everything — it wastes memory and network bandwidth.

Bad
SELECT * FROM Orders;

Good
SELECT OrderID, CustomerID, OrderDate FROM Orders;

3. Use Proper Indexing
Indexes are like shortcuts in a phone contact list. Without them, SQL Server must scan every row.

Example
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

Always check query performance using Execution Plans in SSMS.

4. Avoid Cursors (If Possible)
Cursors process rows one by one — very slow for large data.

Bad
DECLARE cur CURSOR FOR SELECT OrderID FROM Orders;

Good
Use set-based operations instead:
UPDATE Orders SET Status = 'Closed' WHERE OrderDate < '2024-01-01';

5. Use Temporary Tables or Table Variables Wisely
Temporary tables (#TempTable) can help when you have complex joins.

But don’t overuse them — they increase I/O on tempdb.

Use them only when needed:
SELECT * INTO #TempOrders FROM Orders WHERE Status = 'Pending';

6. Parameter Sniffing Control
Sometimes, SQL Server caches an execution plan based on the first parameter it sees, which may not work well for others.

To fix it
DECLARE @LocalCustomerID INT = @CustomerID;
SELECT * FROM Orders WHERE CustomerID = @LocalCustomerID;

This helps avoid performance issues due to parameter sniffing.

7. Use Transactions Carefully
Transactions ensure data consistency, but if they are too long, they can lock resources.

Use
BEGIN TRANSACTION;

-- Your statements
COMMIT TRANSACTION;

Don’t keep a transaction open for long-running logic or unnecessary steps.

8. Use TRY…CATCH for Error Handling
It’s important for clean rollback and error logging.

Example
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    EXEC spLogException ERROR_MESSAGE(), ERROR_LINE();
END CATCH;

9. Avoid Unnecessary Joins
Too many joins, especially on non-indexed columns, can slow performance drastically.

Keep joins only where needed, and always join on indexed keys.

10. Use Execution Plans and Statistics
Before finalizing your procedure:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Then execute your procedure and check the output to understand how much time and I/O it consumes.
Also, view the execution plan (Ctrl + M in SSMS) to find table scans, missing indexes, or inefficient joins.

This flow shows how SQL Server optimizes execution by reusing cached plans whenever possible.

Example: Optimized Stored Procedure
Here’s a real-world optimized procedure example:
CREATE PROCEDURE usp_GetActiveOrders
    @StartDate DATE,
    @EndDate DATE,
    @CustomerID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SELECT O.OrderID,
               O.OrderDate,
               O.TotalAmount,
               C.CustomerName
        FROM Orders O
        INNER JOIN Customers C ON O.CustomerID = C.ID
        WHERE O.OrderDate BETWEEN @StartDate AND @EndDate
          AND (@CustomerID IS NULL OR O.CustomerID = @CustomerID)
          AND O.Status = 'Active'
        ORDER BY O.OrderDate DESC;
    END TRY
    BEGIN CATCH
        EXEC spLogException ERROR_MESSAGE(), ERROR_LINE();
    END CATCH;
END

Key Points

• Used SET NOCOUNT ON
• Controlled optional parameter
• Filtered with indexes
• Handled errors safely