One stored procedure serves both tiny and enormous requests, and one cached plan struggles with both. OPTIMIZE FOR and RECOMPILE are two ways to influence that choice, each with a cost.
Prove Parameter Sensitivity First
A parameter-sensitive query has materially different good plans for different parameter values. Capture actual plans and resource use for representative values. Look at estimated and actual rows, join choice, memory grant, CPU, reads, and duration. A single slow execution after a deployment can have another cause, such as stale statistics or blocking.
I begin with the data distribution. If one customer owns most rows and another owns few, plan choice can differ. If every value returns a similar shape, a hint can solve nothing. Which values represent the common workload and the expensive edge? Test both before fixing a plan to one value.
How the OPTIMIZE FOR Hint Works
OPTIMIZE FOR tells the optimizer to compile as though a parameter had a specified value, while execution still uses the real value. The cached plan can then be reused. This works when one representative value produces an acceptable plan for the workload. It can be poor when the distribution changes or no single plan serves all values well.
I treat the chosen literal as a maintained assumption. Document why it represents the workload and review it after growth. A value copied from a test database can be misleading in production. The hint is stable only if the data pattern behind it remains stable.
DECLARE @StateCode int = 2;
SELECT name
FROM sys.databases
WHERE state = @StateCode
OPTION (OPTIMIZE FOR (@StateCode = 0));Know What RECOMPILE Does
OPTION (RECOMPILE) asks the engine to optimize that statement for each execution and discard its plan afterward. It can fit varying parameter values, but compilation consumes CPU and can become expensive on a high-frequency path. A statement-level hint is narrower than recompiling an entire procedure.
I compare compile cost with execution savings across the actual call frequency. A report that runs occasionally can justify a fresh plan. A small lookup called thousands of times can turn compile work into the new bottleneck. The example uses a catalog query to show syntax. It is not a recommendation to add the hint to that query.
DECLARE @StateCode int = 2;
SELECT name
FROM sys.databases
WHERE state = @StateCode
OPTION (RECOMPILE);Compare the OPTIMIZE FOR and RECOMPILE Costs
OPTIMIZE FOR pays less repeated compilation but can force some values into a compromise plan. RECOMPILE spends compile work to tailor each run and avoids one reused plan for all values. Neither guarantees a good plan if statistics, indexes, or predicates are poor. Measure both under representative concurrency and parameter mix.
I look at total workload cost, not only the worst single run. A hint that halves one rare report but adds small overhead to every common call can be a net loss. Use Query Store or a controlled test to compare plan variants over a full business cycle. Avoid claims based on one execution with a warm cache.
Check Engine Features First
Recent SQL Server versions include features that handle some parameter-sensitive workloads with multiple plan variants. Their behavior depends on compatibility level and query eligibility. Query Store can also capture plan history and support targeted hints. Check which features apply before hard-coding a literal or forcing compilation on every call.
I review the database compatibility level and actual plan evidence. A hand-written hint can prevent the optimizer from using a newer adaptive path. If the engine already produces suitable variants, leave the query alone. The absence of a problem is a good reason to avoid an extra hint. SQL Server needs fewer unowned hints, not more decorative ones.
Consider Query Design
A missing index, broad SELECT list, implicit conversion, or optional predicate pattern can be the real reason one plan struggles. Rewrite the query or index for the needed access path before choosing a hint. A filtered index can help a stable subset but can introduce parameter-matching questions. Test the complete application path.
I have seen a hint used to preserve a plan that was compensating for a poor index. It worked until the table grew. When the access path itself is wrong, freezing compilation behavior only postpones repair. Ask whether the plan you want is possible and supported by the schema. Then decide if a hint is still needed.
Avoid Blanket Procedure Hints
Procedure-level recompilation affects every statement in the procedure. If only one statement is sensitive, apply the narrow option there and measure it. A procedure can contain cheap setup statements and one expensive report query. Forcing all of them through compilation on every call adds work without helping the rest.
I keep hints close to the statement and record the reason. The next developer should know which parameter pattern prompted the choice and what test would justify removing it. A hint without that history becomes a superstition. It can survive long after the original data distribution disappears.
Test Several Value Classes
Choose common, rare, new, and boundary parameter values from the actual workload. Use a safe environment with representative statistics and data distribution. Compare plans, runtime counters, and concurrency. A hint can perform well for one value and fail for another. Include the application’s SET options so the test compiles in the right context.
I do not use invented timings from a demo as proof. Capture numbers from your own server and record the collection method. Keep the baseline plan files. If a new hint changes memory grants, monitor overlapping requests too. One query getting faster can still reduce throughput for everyone else.
Set a Review Trigger for OPTIMIZE FOR and RECOMPILE
A fixed OPTIMIZE FOR literal can age as data changes. A RECOMPILE hint can become unnecessary after an engine upgrade or query rewrite. Set a date or workload trigger to review either choice. Query Store makes plan shifts visible, but someone still has to interpret them.
Which value will be common next quarter? If you cannot answer, a fixed literal needs careful justification. The best plan strategy is the one the team can retest and remove when circumstances change. A hint should remain a measured exception, not a permanent replacement for understanding the query.
Related reading on this blog: SQL SERVER 2022: Parameter Sensitive Plan Optimization (PSPO) and Understanding WITH RECOMPILE in Stored Procedures.
A plan hint is not a free fix, it is a trade-off between reuse and compilation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
