[email protected]

@AdamMachanic

The query processor does what the query plan tells it to do A “good” query plan is essential for a well performing 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 Linear scale, but may heavily impact tempdb Common in warehouses

Generally not good for OLTP

Basic join methodology Works best with a small outer (top) input Never-ending “hung” queries?

Almost always inappropriate nested loops!

Much reviled by DBAs Often appropriate

Check your predicates and expectations!

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.trySQLSever.com

http://www.powerbi.com

http://microsoft.com/bigdata

http://channel9.msdn.com/Events/TechEd www.microsoft.com/learning http://microsoft.com/technet http://microsoft.com/msdn