Understanding Execution Plans
For troubleshooting long running queries, looking at the execution plan is often a good starting point. Once you know how the query is executed, you know why it’s slow and what you can do to speed it up. But what if the execution plan is just beyond your understanding? What if it uses operators you have seen before, but do not really understand? What if you look at the execution plan, but just don’t see the problem?
In this full-day workshop, you will learn everything you need to be able to read and understand any execution plan. We will start with an overview of execution plans as a whole. All execution plans are actually pretty simple – there are just two elements in them! Icons, depicting the various operators; and arrows depicting the flow of rows between those operators. So we’ll start the day by looking at the traits that all icons and all arrows have in common.
The complexity of execution plans comes from the high number of different operators, the many properties that influence their behaviour, and the enormous amount of combination possibilities. The key to mastering this complex subject is gaining an in-depth understanding of the building blocks – so we will take a close look at all of the operators you commonly encounter in execution plans. Easy to understand explanations and insightful visualizations will be used to make you understand exactly how each operator contributes to both the end result of the query, and its execution time and CPU and memory footprints.
At the end of the day, we will shift our attention to new functionality in SQL Server 2012 and SQL Server 2014 and how this affects execution plans – columnstore indexes, batch mode, Hekaton, etc.
Throughout the day, we will have several exercises. Most of them will be relatively small – and probably not the tuning exercises you would normally expect. They will require, and teach you, out of the box thinking – exactly the kind of thinking that you sometimes need to bring that three-hour monster query down on its knees and yield.
Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to share and enhance his knowledge of SQL Server by frequenting newsgroups and forums, reading and writing books and blogs, and attending and speaking at conferences. Microsoft has granted Hugo the MVP award in recognition for his community contributions in 2006, and every year since.
At this time, Hugo is contracted to be the technical editor for the third edition of Grant Fritchey’s book “SQL Server Execution Plans”.