非存储场景下SQL Server糟糕执行计划缓存问题及解决咨询

Hey Joel, let's dig into why your query behaves so differently with OPTION(RECOMPILE) and figure out whether adding it to your code is the right call, or if there's something else to address first.

Why OPTION(RECOMPILE) Fixes the Problem #

The most likely culprit here is parameter sniffing. When you run the query without RECOMPILE, SQL Server generates an execution plan based on the first value of @request_date it sees (the "sniffed" parameter). If that initial value is an edge case—say, a date that returns very few rows or an unusually large dataset—the cached plan will be optimized for that scenario, not for typical dates.

When you add OPTION(RECOMPILE), SQL Server skips the cached plan and generates a new one specifically for the current @request_date value. That's why you see better performance and lower IO: the plan is tailored to the actual data you're querying that time.

Why Clearing Cache/Updating Stats Didn't Help #

• DBCC FREEPROCCACHE clears existing cached plans, but the next time you run the query, SQL Server will just re-sniff the parameter and generate a new plan (again, based on that first run's value). If that value is still an edge case, you're back to the bad plan.
• UPDATE STATISTICS helps SQL Server make better cardinality estimates, but it doesn't fix parameter sniffing on its own. If your data distribution has extreme outliers (like some dates with 10 rows and others with 10,000), stats updates won't eliminate the issue.

Your Options Moving Forward #

Let's break down the pros and cons of each approach:

1. Just Add OPTION(RECOMPILE) to Your Code #

This is the quickest fix, and it's totally valid for many scenarios.

• Pros: Immediate performance improvement, no need to tweak indexes or rewrite queries.
• Cons: Each execution will compile a new plan, which adds a tiny overhead. If this query runs hundreds of times per second, that overhead might add up—but for most business applications, this is negligible.
• Implementation: Simply append OPTION(RECOMPILE) to the end of your SELECT statement in your C# code, just like you did in SSMS.

2. Use OPTIMIZE FOR (@request_date UNKNOWN) #

This tells SQL Server to generate a plan based on average statistics rather than the specific parameter value.

• Pros: Avoids the overhead of recompiling every time, while still creating a plan that works reasonably well for most parameter values.
• Cons: It won't be as tailored as a recompiled plan for edge cases, but it's a good middle ground if recompiling feels like overkill.

3. Optimize Your Indexes #

Even with RECOMPILE, ensuring your indexes are properly designed can make the plan even better (and maybe eliminate the need for RECOMPILE altogether). Looking at your query:

• The WHERE clause filters on mr.[class], mr.[is_request], mr.[finalized_date], and mr.[is_finalized]
• You're joining on mr.[id] with rx_request, plus other columns for joins and selects

Consider creating a covering index on mr_entry that includes all the columns needed for filtering, joining, and selecting:

CREATE NONCLUSTERED INDEX IX_mr_entry_Medication_Requests 
ON mr_entry ([class], [is_request], [is_finalized], [finalized_date])
INCLUDE (
    [id], [source_code], [patient_id], [description], [category], 
    [date_recorded], [request_finalized_date], [request_rc_id], 
    [request_clinician_id], [request_finalized_id]
);

This lets SQL Server retrieve all needed data directly from the index without hitting the table (a "bookmark lookup"), which drastically reduces IO.

4. Verify the Execution Plan Difference #

To confirm it's parameter sniffing, compare the execution plans with and without RECOMPILE in SSMS. Look for differences in:

• Index usage (is it picking your new index only when using RECOMPILE?)
• Cardinality estimates (does the bad plan estimate the wrong number of rows?)

Final Recommendation #

If this query isn't running at an extremely high frequency, go ahead and add OPTION(RECOMPILE) to your C# code—it's a simple, effective fix. If you want to avoid recompilation overhead, try OPTIMIZE FOR first, or invest time in refining your indexes to create a plan that works well for all parameter values.

内容的提问来源于stack exchange，提问作者Joel