I handed AI a plan with no warnings and healthy numbers, yet it read five million rows to return seventeen. That is the real test of AI execution plan analysis.
SQL SERVER – Understanding WITH RECOMPILE in Stored Procedures
The culprit? Parameter sniffing. The solution? You guessed it: WITH RECOMPILE. Comprehensive Database Performance Health Check
Batch Requests and Compilations per Second From T-SQL
Calculate compilations per second from cumulative SQL Server counters, compare batch rates, and investigate single-use plans with context.
KEEP PLAN Hint: Cut Temp Table Recompiles in SQL Server
The KEEP PLAN hint gives a temp table the same recompile threshold as a permanent table. This post counts statistics recompiles with Extended Events for a plain query, a KEEP PLAN query and a KEEPFIXED PLAN query. It shows where the hint helps, where it does nothing and what the stronger hint costs.
Parameter Sniffing Fixes Compared: Which One to Use
Parameter sniffing fixes compared on one table show a clear order, from a cheap guess to a better index. This post runs the plain procedure, a local variable, OPTIMIZE FOR UNKNOWN, a scoped setting, OPTION (RECOMPILE) and a covering index on the same skewed table, and prints the page reads of each in both call orders.
OPTION (RECOMPILE) Hint: When a Fresh Plan Pays Off
The OPTION (RECOMPILE) hint tells SQL Server to build a new plan every time a statement runs. This post measures the page reads it saves on skewed data, the compile cost it adds, the filtered index it unlocks, and how to apply it to one statement of a procedure.
Catch-All Queries and Optional Parameter Plan Optimization in SQL Server 2025
Compare catch-all queries with recompilation, parameterized dynamic SQL, and SQL Server 2025 optional parameter plan optimization.