amachanicgmail com Adam Machanic The query processor does

amachanic@gmail. com @Adam. Machanic

The query processor does what the query plan tells it to do A “good” query plan is essential for a wellperforming query The optimizer makes mistakes We must learn to identify and fix the mistakes

LOGIC Flows Left-to-Right DATA Flows Right-to-Left

Iterators expose row and execution estimates Slow plan? Analyze estimates against actual values Actuals and estimates way off? Try updating statistics Important: Cost is always an estimate!

Thicker lines means more rows Make thick lines thinner, further to the right, for faster plans!

Slow Plan? Start here… Bonus Iterator! Scan

Your nonclustered index handles your search argument… Lots of rows? You’ve got a problem … but you’re referencing at least one column that’s not included

Used as a “cache” in the query processor Implemented as hidden tables in tempdb Almost never a good sign Lack of adequate indexes or uniqueness information

ORDER BY, Merge Join, Stream Agg, Windowing Check expectations and indexes! Do you really need that sort? Performs worse, relative to input size, as input size increases

Aggregation and join methodology Common in warehouses Generally not good for OLTP Linear scale, but may heavily impact tempdb

Basic join methodology Never-ending “hung” queries? Almost always inappropriate nested loops! Works best with a small outer (top) input

Much reviled by DBAs Check your predicates and expectations! Often appropriate

Most plan problems boil down to a simple set of issues Don’t let yourself get overwhelmed Focus on the obvious: lots of rows and problematic iterators Most of all, have fun! Query tuning is a satisfying endeavor

http: //www. try. SQLSever. com http: //www. powerbi. com http: //microsoft. com/bigdata

http: //channel 9. msdn. com/Events/Tech. Ed www. microsoft. com/learning http: //microsoft. com/technet http: //microsoft. com/msdn