SQL Server: Query Plan Analysis

Learn how to interpret and analyze SQL Server query plans, making performance problems easier to recognize and understand, applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards
Course info
Rating
(494)
Level
Intermediate
Updated
Mar 28, 2013
Duration
2h 14m
Table of contents
Course Introduction
Capturing Query Execution Plans
Interpreting Query Execution Plans
Common Operators
Noteworthy Patterns
Description
Course info
Rating
(494)
Level
Intermediate
Updated
Mar 28, 2013
Duration
2h 14m
Description

SQL Server query plans contain a wealth of information that can be used to diagnose performance problems, but at first glance they appear complicated and difficult to understand. With more than 30 detailed demos, this course shows you how to easily understand and interpret query plans so you can pinpoint performance problems. The course starts by explaining the various methods of capturing query plans for later analysis, and how to recognize the basic elements of query plans along with their CPU, I/O, and memory costs and requirements. It then moves on to explaining all the common query plan operators and when they will be present in a query plan, along with notes on potential problems to watch out for. The final module explains common patterns to look for in query plans that indicate a performance problem that should be investigated and fixed. This course is perfect for developers, DBAs, and anyone responsible for SQL Server performance, from complete beginners through to those with more experience who want a definitive guide to SQL Server query plans. The information in the course applies to all versions from SQL Server 2005 onwards.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

Course Introduction
Hi, this is Joe Sack from sqlskills. com and I'm recording this course for Pluralsight. This is the SQL Server Query Plan Analysis Course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. If you have a specific query that's not performing well, as part of the troubleshooting process, you'll want to look at the query execution plan and see if there are any indications as to why the problem is occurring. There are many pieces of data surfaced in a query execution plan and while you're evaluating a plan, it is just as important to understand its limitations, as well as its strengths. Think of a query execution plan as one data source out of multiple data sources that may need to be evaluated in order to successfully troubleshoot a problem. In this course, the focus will be on how to capture query execution plans, how to interpret them and also what patterns to watch for that might be associated with the root cause of your specific performance problem.

Capturing Query Execution Plans
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the Sequel Server; Query Plan Analysis course and you're watching Module Two, where we'll go over how to capture query execution plans. In this module we'll cover a few methods for capturing query execution plans. It's important to note that not all methods are equivalent, and if you're not careful, some of the methods could add a significant amount of performance overhead to your system. Also, some types of collection are more valuable than others, so even if you've captured plans before, I recommend viewing this entire module.

Interpreting Query Execution Plans
Joe Sack: Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server -- Query Plan Analysis course and you're watching module 3 where we'll discuss how to interpret query execution plans. Once you actually have a plan, what do you do with it? This module covers the fundamentals that you'll need in order to properly interpret a plan. In future modules, we'll cover common operators, so understanding what those operators are and whether you should pay attention to them. And then in a future module we'll also cover noteworthy patterns, so those will be patterns that you'll really want to watch out for when you see them in the plan because they may be associated with the root cause of your performance issue.

Common Operators
Joe Sack: Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server -- Query Plan Analysis course, and you're watching module 4 where we'll discuss the various common operators that you might see in an execution plan. This module is going to review the common operators you might expect to see in a plan and one thing to know upfront is that you shouldn't believe any statements about good or bad operators. There are operators that are appropriate for various situations, and so when you see them, I'd like you to be able to understand what they do. And then also look at their context and understand whether that is applicable to the context or whether it truly is a red flag. And I will definitely call out things to watch out for in the next module, but, again, see these things as areas of investigation for now. You want to look at the different operators and then start asking questions about the context and whether or not something is noteworthy.

Noteworthy Patterns
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Query Plan Analysis Course and you are watching Module 5, where we will discuss noteworthy patterns that you should be looking for in execution plans. There are several different patterns and indicators that should draw your attention and some of these patterns might not end up being associated with your route cause, but the goal of this module is for you to recognize those pattens when you see them in the plans and then draw up the next steps for troubleshooting the issue. So, the idea is that you recognize the pattern, you investigate further, and based on what you find, then you can come up with actionable next steps to help address query performance.