SQL Stored Procedure Performance:
How to Fix Parameter Sniffing and When to Use RECOMPILE
Slow SQL queries are one of the most common—and most frustrating—problems in production systems. A query that runs in milliseconds today can suddenly take seconds tomorrow, even though nothing appears to have changed.
In most cases, the root cause is parameter sniffing.
This article explains parameter sniffing in clear terms, shows how OPTION (RECOMPILE) fixes it, and—most importantly—explains when you should and should not use RECOMPILE in real-world SQL Server systems.
What Is Parameter Sniffing in SQL Server?
Parameter sniffing occurs when SQL Server creates an execution plan based on the first parameter value it receives when a stored procedure or parameterized query runs.
That execution plan is cached and reused for all future executions—even when later parameter values behave very differently.
This behavior is not a bug. It is a performance optimization that works well until data distribution is uneven.
Why Parameter Sniffing Causes Slow Stored Procedures
Consider a patient search table in a healthcare database:
| Last Name | Number of Records |
|---|---|
| SMITH | 200,000 |
| ZANE | 3 |
First execution
EXEC search_patient_by_name 'SMI';
SQL Server expects a large result set and creates a scan-heavy execution plan.
Second execution
EXEC search_patient_by_name 'ZAN';
SQL Server reuses the same plan, even though only three rows match.
The result:
- Excessive I/O
- Higher CPU usage
- Slower response time
- Inconsistent query performance
This is the classic SQL Server parameter sniffing problem.
Why SQL Query Performance Feels Random in Production
From a developer’s perspective:
- The query does not change
- The indexes do not change
- The data does not change
Yet performance fluctuates.
This usually happens because:
- The first execution after deployment
- A rare search value
- Or a plan cache reset
silently changes the cached execution plan for everyone.
How OPTION (RECOMPILE) Fixes Parameter Sniffing
Using:
OPTION (RECOMPILE)
forces SQL Server to:
- Generate a new execution plan every time
- Use the actual parameter values
- Avoid reusing a bad cached plan
Benefits of RECOMPILE
- Eliminates parameter sniffing
- Produces optimal execution plans
- Delivers predictable query performance
- Especially effective for name search and lookup queries
Although recompiling adds a small CPU cost, modern SQL Server environments handle this easily.
Are Stored Procedures Still Useful with RECOMPILE?
Yes—absolutely.
Stored procedures provide far more value than execution plan reuse.
Benefits of Stored Procedures
- Strong database security
- Protection against SQL injection
- Centralized business logic
- Controlled access to sensitive tables
- Easier monitoring and auditing
Even with RECOMPILE, stored procedures remain a best practice in enterprise and healthcare databases.
When NOT to Use OPTION (RECOMPILE)
While RECOMPILE is powerful, it should not be used blindly.
1. High-Frequency Simple Queries
If a query:
- Executes thousands of times per second
- Always returns a small, predictable result
- Has no data skew
Then recompiling every time adds unnecessary CPU overhead.
Example
SELECT patient_id
FROM patients
WHERE patient_id = @id;
In this case, plan reuse is ideal.
2. Evenly Distributed Data
When data distribution is uniform:
- Execution plans remain optimal
- Parameter sniffing is unlikely
- Cached plans are safe
Using RECOMPILE here provides little to no benefit.
3. Complex Queries with Expensive Compilation
Large analytical queries with:
- Multiple joins
- Aggregations
- Complex logic
may incur significant compilation cost. Recompiling such queries repeatedly can reduce throughput.
How to Detect Parameter Sniffing Using Execution Plans
Detecting parameter sniffing requires comparing execution plans and runtime behavior.
Step 1: Capture Actual Execution Plans
Run the same stored procedure with different parameters:
- One common value
- One rare value
Enable Actual Execution Plan and compare both runs.
Step 2: Look for Row Estimate Mismatch
Warning signs include:
- Estimated rows = 1, Actual rows = 200,000
- Estimated rows = 100,000, Actual rows = 3
Large differences indicate parameter sniffing.
Step 3: Identify Plan Reuse
If:
- The same execution plan is reused
- But performance varies drastically
parameter sniffing is likely the cause.
Step 4: Confirm with Recompile Test
Temporarily add:
OPTION (RECOMPILE);
If performance stabilizes, parameter sniffing has been confirmed.
Best Practices for SQL Stored Procedure Performance
To optimize SQL Server performance in production:
- Use
OPTION (RECOMPILE)for highly variable queries - Avoid leading wildcards in name searches
- Normalize searchable columns
- Limit result sets with pagination
- Monitor execution plans regularly
- Use stored procedures for security and control
Final Thoughts: Predictable Performance Beats Cached Plans
A query that is fast sometimes and slow other times is a production risk.
In systems like healthcare databases—where reliability matters more than raw speed—predictable performance is the real goal.
Used correctly, OPTION (RECOMPILE) is not a hack.
It is a deliberate strategy for eliminating parameter sniffing and stabilizing SQL query performance.
