Taming Unpredictable Latency in Azure SQL Database with OPTION (RECOMPILE)

The 'Fast Once, Slow Later' Problem

You have a stored procedure that usually runs in 200ms. Suddenly, for a specific set of parameters, it spikes to 30 seconds. You check the execution plan and realize the database is using a plan optimized for a tiny result set (like an Index Seek) to process a massive result set (which requires an Index Scan). This is the classic symptom of parameter sniffing. Parameter sniffing occurs when Azure SQL Database creates a query plan based on the values provided during the very first execution. While this is usually efficient, it becomes a liability when your data distribution is skewed—meaning some parameter values return five rows while others return five million.

When to Force a Recompile

The most direct way to solve this instability is the OPTION (RECOMPILE) query hint. Unlike other optimization strategies that try to find a middle ground, this hint tells the engine to discard any cached plan and generate a fresh one specifically for the current parameter values every single time the query runs.

You should consider this approach when:

• Data Skew is High: The optimal plan for Parameter A is fundamentally different from the optimal plan for Parameter B.
• Execution Frequency is Moderate: The query does not run thousands of times per second, meaning the CPU cost of recompilation is negligible compared to the cost of a bad plan.
• Predictability Trumps Raw Speed: You prefer a consistent 500ms execution time over a gamble between 100ms and 10 seconds.

Practical Implementation

To implement this, add the hint to the end of the specific T-SQL statement causing the issue. You do not need to recompile the entire stored procedure; you can target the specific problematic query within the block.

-- Run this on the Azure SQL Database instance with appropriate permissions
CREATE OR ALTER PROCEDURE dbo.GetOrderReports
    @CustomerID INT,
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
      AND OrderDate BETWEEN @StartDate AND @EndDate
    -- Force a new plan based on the specific @CustomerID and date range provided
    OPTION (RECOMPILE);
END;

Verifying the Impact

To confirm if the hint is working and to measure the CPU trade-off, use the following diagnostic steps in SQL Server Management Studio (SSMS) or Azure Data Studio:

1. Run SET STATISTICS TIME ON before executing your procedure.
2. Execute the procedure with a small parameter set, then a large one.
3. Compare the CPU time. You will notice that with OPTION (RECOMPILE), the CPU time is slightly higher for every run because of the compilation phase, but the Elapsed time remains stable regardless of the data volume.
4. Query sys.dm_exec_query_stats. You will see that the execution_count for the specific plan hash increases, but the plan is not being reused in the same way as a cached plan.

The Compute Trade-off

The primary limitation of OPTION (RECOMPILE) is the increase in CPU utilization. Compilation is a compute-intensive task. If you apply this hint to a query that executes 100 times per second, you will likely see a spike in your Azure SQL Database DTU or vCore usage, potentially leading to CPU exhaustion.

Scenario	Cached Plan (Default)	OPTION (RECOMPILE)
Uniform Data	Fastest; minimal CPU	Slower; wasted CPU
Skewed Data	Unpredictable; risk of timeouts	Consistent; optimized per run
High Frequency	Efficient resource use	High CPU overhead

Closing Action

Do not apply OPTION (RECOMPILE) globally. Start by identifying your top 5 most volatile queries using Query Store. Apply the hint to one query, monitor the CPU metrics in the Azure Portal for 24 hours, and verify that the p99 latency has stabilized. If CPU usage remains healthy, you have successfully traded a small amount of compute for significant operational stability.