Friday, April 27, 2012

SQL Server Query Optimizer Deep Dive series (Or "How to be the geek-hit at your next DBA get together, with your uber Query Optimizer knowledge...")

Paul White: Page Free Space - Query Optimizer Deep Dive - Part 1

"This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.


The motivation behind writing these sessions is that I have found that relatively few people have a good intuition for the way the optimizer works, partly because the official documentation is rather sparse, and partly because what information is available is dispersed across many books and blog posts. The content presented here is very much geared to my preferred way of learning – it shows the concepts in what seems to me to be a reasonably logical sequence, and provides tools to enable the interested reader to explore further, if desired.

When we write a query, we are not writing a computer program that can be directly executed. SQL is a declarative language used to logically describe the results we want to see. SQL Server goes through a process of compilation and optimization to create a physically executable implementation of the stated logical requirement. To put it another way, a SQL query is the specification for an executable program that SQL Server writes for us. The SQL Server component responsible for finding an efficient physical execution plan for a given logical query requirement is the Query Optimizer.



We all see the Query Plans and maybe use them in a sentence, but how much do we really know about them? This looks like a great series, providing some content and details that you don't see often, let alone in such a readable manner...

No comments: