Why is my query sometimes fast,
and sometimes terribly slow?
Even weirder, it just gets slow out of nowhere – even when you swear you haven’t changed anything.
Why Is This Query Sometimes Slow? – I show you how parameter sniffing happens, and why reboots/restarts seem to fix it, but it keeps coming back.
How to Start Troubleshooting Parameter Sniffing – a brief introduction of what parameter sniffing is, how to fix it during an emergency, and how to gather the data you’ll need to fix it right for the long term.
Video: Fixing Parameter Sniffing Part 1 – a demo of how parameter sniffing happens, and how to detect it in the plan cache.
Video: Fixing Parameter Sniffing Part 2 – I show why you can’t just comment out CREATE PROC in order to test parameter sniffing, show you how to really test your code, and why OPTION RECOMPILE can produce the wrong query results.
Optimize for Unknown (Mediocre) – this query hint gives you a predictable plan, but it’s just usually predictably BAD, not predictably good.
Slow in the App, Fast in SSMS – Erland Sommarskog’s epic novel (dozens of pages long) about the causes and fixes for parameter sniffing and different execution plans.
Video: Identifying and Fixing Parameter Sniffing
From my session at SQLDay Poland: