Query Tuning Internals for the Advanced SQL Developer By Kevin Kline

Session Title: Query Tuning Internals for the Advanced SQL Developer
Speaker: Kevin Kline

Abstract: Skilled SQL developers know that the SQL Server query optimizer uses a multi-step process to produce execution plans and that SQL Server uses a cost-based optimizer to construct query execution plans. But how does does the optimizer decide the cost of a given operator over another equally valid operator, say between a nested loop join and a merge join? This session will teach you advanced techniques using undocumented trace flags to deeply explore the query optimization process. These choices can have a dramatic impact on performance, so we will pay special attention to the algebrizer, including associative, commutative, and transitive transformations. Together, we will Explore the SQL Server internal memo structure to see how SQL Server uses the heuristics of the algebrizer and query optimizer.Â We will examine a variety of everyday queries whose performance can be greatly improved by applying a deeper understanding of these internal behaviors. Lots of examples and demos!