SQL Server Documentation

Execution Plan Caching

SQL Server caches the execution plan of a stored procedure after the first execution. Subsequent calls reuse the plan, reducing compilation overhead.

EXEC dbo.GetCustomerOrders @CustomerID = 123;

Parameter Sniffing

When a procedure is compiled, SQL Server uses the initial parameter values to optimize the plan. This can lead to sub‑optimal performance for other parameter values.

CREATE PROC dbo.SearchOrders @StartDate DATE, @EndDate DATE AS
BEGIN
    SELECT * FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

Mitigation techniques include OPTION (RECOMPILE), local variables, or OPTIMIZE FOR hints.

Recompilation Strategies

Use OPTION (RECOMPILE) when data distribution varies greatly between executions. It forces a fresh plan each time.

SELECT * FROM dbo.GetLargeDataset()
OPTION (RECOMPILE);

Statistics Maintenance

Accurate statistics ensure optimal cardinality estimates.

• Enable auto‑create and auto‑update statistics.
• Schedule regular UPDATE STATISTICS jobs for large tables.

UPDATE STATISTICS dbo.Orders;

Index Usage

Design indexes that align with the query patterns inside stored procedures. Use the sys.dm_db_missing_index_details DMV to discover gaps.

SELECT *
FROM sys.dm_db_missing_index_details
WHERE database_id = DB_ID();

Monitoring & Tuning

Leverage sys.dm_exec_procedure_stats and Query Store to identify high‑cost procedures.

SELECT *
FROM sys.dm_exec_procedure_stats
ORDER BY total_worker_time DESC;