data-konzept

'Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills.

They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill to tempdb.

The tough part is that catching parameter sniffing in action is difficult. It’s sort of like looking for it makes it disappear, and some evidence of it still disappears.

This has improved both with the addition of Query Store in general, and memory grants in DMVs.

Now with tempdb spills added, your job gets even easier. Sort of.

The finding problems part is easier.'...

https://www.brentozar.com/archive/2018/01/sql-server-2017-cu3-adds-tempdb-spill-diagnostics-dmvs-extended-events/