SQL Stored Procedures: Parameter Sniffing - 10s to 4 Min

Every production database eventually hits the same wall: raw SQL queries scattered across application code become a maintenance nightmare and a security liability. Stored procedures solve this by separating the definition of data logic from its invocation. By encapsulating logic inside the database, you get precompiled execution plans, centralised maintenance, and database-level permission control.

However, stored procedures are not a universal solution. They couple business logic to the database layer, complicate version control and deployment pipelines, and introduce parameter sniffing problems that can make performance unpredictable. Understanding when to use them — and when to let your ORM handle the job — is a skill that separates pragmatic engineers from those who over-engineer or under-engineer their data layer.

Anatomy of a Production-Ready Stored Procedure

A robust stored procedure handles more than just running a query. It validates inputs, wraps operations in a transaction, handles errors explicitly, and returns meaningful result codes to the caller. The pattern below is a standard template for secure, transactional procedures.

SET NOCOUNT ON at the top suppresses the 'N rows affected' messages that can interfere with application code that checks rowcounts. BEGIN TRY / BEGIN CATCH provides structured error handling — without it, a runtime error leaves the transaction open and the caller gets an obscure error code. XACT_STATE() inside the catch checks whether the transaction is still committable before attempting a rollback.

CREATE PROCEDURE sp_UpdateProductStock
    @ProductID INT,
    @QuantityAdjustment INT,
    @UserID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Update stock level
        UPDATE Products 
        SET StockLevel = StockLevel + @QuantityAdjustment,
            LastUpdatedBy = @UserID
        WHERE ProductID = @ProductID;

        -- Log the movement
        INSERT INTO InventoryAudit (ProductID, ChangeAmount, ChangedBy, LogDate)
        VALUES (@ProductID, @QuantityAdjustment, @UserID, GETDATE());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        -- Raise error to the calling application
        THROW;
    END CATCH
END;

Parameter Sniffing — The Silent Performance Trap

When SQL Server first executes a stored procedure, it compiles an execution plan optimised for the specific parameter values provided on that call. This plan is then cached and reused for all subsequent calls, regardless of whether the new parameters would benefit from a different plan. This is parameter sniffing.

For most procedures, sniffing is beneficial — the compiled plan is reused without the overhead of recompilation. The problem occurs when the data distribution is highly variable. A parameter value that returns 5 rows might benefit from an index seek. A parameter value that returns 5 million rows might benefit from a table scan. When the cached plan is optimised for one extreme, it performs terribly for the other.

The three main fixes: WITH RECOMPILE forces a fresh plan on every call (eliminates the benefit of caching but prevents stale plans). OPTION(OPTIMIZE FOR UNKNOWN) generates a plan based on column statistics rather than the sniffed parameter value — a compromise. Local variables instead of direct parameters prevent sniffing entirely by breaking the parameter-value link.

-- PROBLEM: Plan cached for small customer gets reused for large customer
CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT order_id, total, created_at
    FROM orders
    WHERE customer_id = @CustomerID
    ORDER BY created_at DESC;
END;

-- FIX 1: WITH RECOMPILE -- generates fresh plan every call
-- Use when data distribution is highly variable and recompilation cost is acceptable
ALTER PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT order_id, total, created_at
    FROM orders
    WHERE customer_id = @CustomerID
    ORDER BY created_at DESC;
END;

-- FIX 2: OPTION(OPTIMIZE FOR UNKNOWN) -- plan uses statistics, not sniffed value
ALTER PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT order_id, total, created_at
    FROM orders
    WHERE customer_id = @CustomerID
    ORDER BY created_at DESC
    OPTION(OPTIMIZE FOR (@CustomerID UNKNOWN));
END;

-- FIX 3: Local variable -- breaks the parameter-value link
ALTER PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @LocalID INT = @CustomerID;  -- breaks sniffing
    SELECT order_id, total, created_at
    FROM orders
    WHERE customer_id = @LocalID
    ORDER BY created_at DESC;
END;

-- Monitor procedure stats to detect sniffing problems early
SELECT
    OBJECT_NAME(object_id)  AS procedure_name,
    execution_count,
    total_elapsed_time / execution_count AS avg_elapsed_us,
    total_logical_reads   / execution_count AS avg_logical_reads
FROM sys.dm_exec_procedure_stats
WHERE OBJECT_NAME(object_id) = 'usp_GetCustomerOrders';

When to Use Stored Procedures vs ORMs vs Inline SQL

Stored procedures are not always the right tool. The choice depends on the complexity of the logic, your team's SQL proficiency, deployment pipeline maturity, and whether you need database-level security.

Stored procedures are the right choice when: you need to grant users or services access to specific operations without exposing the underlying tables (database-level security); the logic involves multiple steps that must be atomic and the business rules are unlikely to change frequently; performance is critical and you want precompiled plans with minimal network overhead; or you are working in a corporate environment where database changes go through a DBA-controlled deployment process.

ORMs are the right choice for: straightforward CRUD operations where the ORM generates correct, parameterised SQL; when rapid iteration is needed and SQL refactoring via migrations is part of your workflow; teams where SQL expertise is limited and the ORM abstraction reduces errors.

Inline parameterised SQL is appropriate for: complex queries that are unique to one endpoint and do not warrant a named procedure; when the query logic needs to live with the application code for maintainability.

-- USE STORED PROCEDURE: multi-step atomic operation with business rules
-- Cannot easily express this in a single ORM call
CREATE PROCEDURE usp_PlaceOrder
    @CustomerID INT,
    @ProductID  INT,
    @Quantity   INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. Check inventory
        DECLARE @Stock INT;
        SELECT @Stock = quantity FROM products WHERE product_id = @ProductID;
        IF @Stock < @Quantity
            RAISERROR('Insufficient stock', 16, 1);

        -- 2. Deduct inventory
        UPDATE products
        SET quantity = quantity - @Quantity
        WHERE product_id = @ProductID;

        -- 3. Create order
        INSERT INTO orders (customer_id, product_id, quantity, created_at)
        VALUES (@CustomerID, @ProductID, @Quantity, GETUTCDATE());

        -- 4. Update customer lifetime value
        UPDATE customers
        SET lifetime_orders = lifetime_orders + 1
        WHERE customer_id = @CustomerID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

-- USE ORM: simple CRUD -- no business logic, let the framework handle it
-- Python SQLAlchemy example:
-- product = db.query(Product).filter_by(id=product_id).first()
-- db.add(Order(customer_id=cid, product_id=product_id, quantity=qty))
-- db.commit()
-- This is cleaner and more maintainable for straightforward operations