Parameter Sniffing

When does a Query Get Trivial Optimization?

Last Updated May 26, 2020

We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here’s a little glimpse into what we talked about. For really simple queries, SQL Server can use “trivial optimization”. If there’s a very limited number of ways to run the query, why do a…

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

Last Updated May 26, 2020

Kendra Little

Execution Plans, Parameter Sniffing, SQL Server

What could go wrong? Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a “bad plan” gets compiled and starts to be reused. This is “bad” parameter sniffing. “Bad plans” can…

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

Last Updated May 26, 2020

Kendra Little

Parameter Sniffing, SQL Server

There’s an important rule for tuning stored procedures that’s easy to forget: when you’re testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values. Don’t re-write the statemet you’re tuning as an individual TSQL statement using local variables! Where it goes wrong…

RECOMPILE Hints and Execution Plan Caching

Last Updated April 2, 2021

Kendra Little

Execution Plans, Parameter Sniffing

The DBA version of an XKCD classic. When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use,…

Optimize for… Mediocre?

Last Updated May 26, 2020

Kendra Little

Parameter Sniffing, SQL Server

Some query hints sound too good to be true. And, unfortunately, usually they aren’t quite as magical as they might seem. Frustration with unpredictable execution times People often learn about parameter sniffing when query execution times stop being predictable. Occasionally you’ll hear about a stored procedure taking much longer than normal, but the next time…