SQL Server: Common Query Tuning Problems and Solutions - Part 1

Learn to recognize and diagnose numerous types of SQL Server query performance problems. This course is perfect for developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onward.
Course info
Rating
(310)
Level
Intermediate
Updated
Jul 7, 2014
Duration
2h 31m
Table of contents
Course Introduction
Validating Assumptions
Troubleshooting Bad Assumptions
Tuning Imprecision Problems
Description
Course info
Rating
(310)
Level
Intermediate
Updated
Jul 7, 2014
Duration
2h 31m
Description

There are a wide variety of common performance problems that you will encounter when query tuning. Part 1 of this two-part course begins by showing how to validate query execution assumptions around statistics and cardinality in order to ensure you're troubleshooting the true root cause of a query performance issue. You'll then learn about common areas where bad assumptions can occur that negatively impact overall query performance, and about common problems and solutions related to being imprecise when querying a database. The course is very demo-centric, with lots of practical tips and tricks you'll be able to apply in your environment. This course is perfect for developers, DBAs, and anyone responsible for query performance tuning on SQL Server, from SQL Server 2005 onward.

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 part one of the SQL Server Common Query Tuning Problems and Solutions course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. When it comes to SQL Server performance tuning, tuning how you write your T-SQL workload represents only one part out of a very wide performance tuning surface area. Even though T-SQL is a declarative language where you tell it what to do, but not exactly how to do it, there are still several areas where how you've written your code has a nontrivial impact on query performance. I've found that the most successful SQL Server query tuners have a solid understanding of the SQL Server engine and also have a wide diversity of performance tuning experiences for varying applications, database designs and workload types. If you're able to cultivate effective query tuning skills, there's definitely demand for your abilities in today's job market and the goal of this two part course is to relay several common problems and solutions in the SQL Server query tuning space.

Validating Assumptions
Hi this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is part one of the SQL Server common query tuning problems and solutions course. And you're watching the validating assumptions module, where we'll cover how to validate key query optimizer assumptions before going too far down the query tuning path. A common mistake is to begin tuning a T-SQL workload before actually checking that the original assumptions being made by the query optimizer are even correct. The query optimizer often can be correct, but when assumptions are bad, the downstream performance impact can be significant. In this module we'll be talking through the validation of query optimizer assumptions in order to make sure we're troubleshooting the true root cause later on, and not a downstream side effect.

Troubleshooting Bad Assumptions
Hi this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is part one of the SQL Server common query tuning problems and solutions course, and you're watching the Troubleshooting Bad Assumptions Module, where we'll look at the common patterns related to bad assumptions and demonstrate techniques that may address them. We learned in the previous module that when row estimates are skewed, they can drive poor query execution plan choices that in turn, impact performance. Now in this module we'll cover specific query patterns, and I'll demonstrate both the common problems you might encounter, and the associated solutions that may help address the root cause.

Tuning Imprecision Problems
Hi, this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is Part I of the SQL Server Common Query Tuning Problems and Solutions Course, and you're watching the Tuning Imprecision Problems module, where we'll begin exploring query construction issues that cause performance degradation due to imprecision. When you write a query, it's important to ask for what you need in the most precise way possible. While designing for the "just in case" scenario may provide options in the future, it can also lead to significant performance problems. SQL server developers may not question the requirements given to them by others, and yet, asking a few simple questions can improve the performance and scalability of the developed work load significantly. This module will cover various imprecision related query construction issues and associated recommendations for resolution.