SQL Server: Troubleshooting Query Plan Quality Issues

Learn how to identify, diagnose, and prevent problems where SQL Server chooses the incorrect query plan for your critical queries, applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards
Course info
Rating
(197)
Level
Intermediate
Updated
Jan 11, 2013
Duration
2h 20m
Table of contents
Course Introduction
Why Query Plan Quality Matters
Identifying Query Plan Quality Issues
Query Plan Quality Patterns and Resolutions
Description
Course info
Rating
(197)
Level
Intermediate
Updated
Jan 11, 2013
Duration
2h 20m
Description

There are many problems that can lower the performance of your workload and one of the most common is an incorrect query plan. Often the poor query plan is chosen because the cardinality estimate is wrong - the estimate by the query processor of how many table rows will be involved in the query. This course shows you how to recognize when the query processor has an incorrect estimate, along with explaining and showing a multitude of possible causes, plus how to fix them. The course starts with explaining why query plan quality is important, and then shows how to easily spot cardinality estimate issues from examining query plans. The majority of the course shows all the possible causes of cardinality estimates being incorrect along with how to fix them, and more than 25 demos to walk you through practical examples of the concepts and problems in the lectures. This course is perfect for developers, DBAs, and anyone responsible for performance tuning on SQL Server. The information in the course applies to all version 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
(Background sounds) Joe Sack: Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Troubleshooting Query Plan Quality Issues course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. The query execution plan chosen by the SQL server query optimizer to implement the logical requirements of a query can have significant impact on how well the query performs, how much concurrency it can have with other executing requests, and also what resources it consumes across IO, CPU, and memory. If the query optimizer is working with incorrect information, for example incorrect estimates for roll counts the end result can be a poor quality query execution plan. The purpose of this course is to discuss why it's important to pay attention to plan quality issues and also learn how to identify them when they happen. We'll also cover the more common plan quality issue patterns and review where to look next and also cover how to resolve specific issues. ( Background sounds )

Why Query Plan Quality Matters
Hi! This is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server troubleshooting query plan quality issues course and you're watching module 2. We will discuss why query plan quality plan matters. The SQL Server query optimizer is dependent on the number of pieces of information in order to produce a high quality query execution plan. When the assumptions being provided to the query optimizer are based on significantly skewed data or even missing data, this can result to the bad plan. While the query optimizer does a great job in several scenarios, it isn't uncommon to encounter plan quality issues out in the field and sometimes even though they are query plan quality issues, they aren't recognized as such. And so people end up troubleshooting the side effects of the bad plan rather than the root cause. So in this module, we'll start by covering the key reasons why you should care about query plan quality issues in the first place.

Identifying Query Plan Quality Issues
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Troubleshooting Query Plan Quality Issues course and you're watching Module 3 where we'll discuss how to identify Query Plan Quality Issues. We've discussed why it's important to pay attention to query plan quality issues. And now, in this module, we'll go through how to identify the issues in the actual query execution plan. We'll cover the primary methods for identifying cardinality estimate issues and also review supplemental information which can also assist in helping identify problems.

Query Plan Quality Patterns and Resolutions
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Troubleshooting Query Plan Quality Issues course and you're watching Module 4 where we'll walk through Query Plan Quality Issues and Resolutions. There are a variety of factors that can impact query plan quality so in this module, we'll cover the various patterns that you may encounter and for each pattern, I'll discuss the recommended areas to check next steps and suggested resolutions.