Query Tuning Internals for the Advanced SQL Developer

Skilled SQL developers know that the SQL Server query optimizer uses a multi-step process to produce execution plans. But what about deeper components like the parser, the binder, the simplifier, the algebrizer, and the undocumented internals of optimizer itself?

This session teaches you advanced techniques of examining query optimizer internals, as well as surprising behaviors of the query optimization process that can have a dramatic impact on performance. We pay special attention to the processes that build the internal query memo structure and query transformation heuristics, such as associative, commutative, and transitive transformations.

We will examine a variety of everyday queries whose performance can be greatly improved by apply a deeper understanding of these internal behaviors. Lots of examples and demos!

Learn advanced and undocumented methods to see the steps of parsing, binding, algebrizing, simplifing, and query optimization. Explore the SQL Server internal memo structure to see how SQL Server uses the heuristics of the algebrizer and query optimizer. Walk through a variety of SQL queries whose behavior can be greatly improved when you apply what you’ve learned about the algebrizer and query optimizer.