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

This course teaches how to recognize and diagnose numerous types of SQL Server query performance problems.
Course info
Rating
(128)
Level
Intermediate
Updated
Aug 11, 2014
Duration
2h 17m
Table of contents
Course Introduction
Troubleshooting Inefficiency
Tuning OLTP vs. DSS Patterns
Advanced Query Tuning Patterns
Description
Course info
Rating
(128)
Level
Intermediate
Updated
Aug 11, 2014
Duration
2h 17m
Description

There are a wide variety of common performance problems that you will encounter when query tuning. Part 2 of this two-part course begins by covering common problems arising from writing and using inefficient Transact-SQL code. You'll then learn about query performance problems and associated query tuning solutions for situations where the query and/or design is inappropriate for the workload type. Finally, the course will cover a variety of more advanced query tuning problem and solutions. 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 in 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 two 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. In part one of this course, we covered several reasons why you should validate and troubleshoot the assumptions made by the Query Optimizer before delving too deeply into other standard query tuning activities. In the last module of part one, we also covered how imprecision in how you write your T-SQL code can hurt workload performance. For part two of this course, we'll look into more common query tuning scenarios and patterns around inefficiency, design-related issues, and various advanced tuning problems.

Troubleshooting Inefficiency
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is part two of the SQL Server Common Query Tuning Problems and Solutions Course and you're watching the Troubleshooting Inefficiency module where we'll cover various query performance issues related to inefficient operations, and also show methods for addressing them. Transact-SQL is flexible and allows many options for performing the same task and often times how you write your code gets translated by the query optimizer into identical or similar physical activities. But there are scenarios where this doesn't happen, for example if you're not accustomed to set based processing you might be writing your code in a row by row fashion which can run significantly more slowly than set based alternatives. In this module I'll show several demos where we'll walk through inefficiency patterns and associated resolutions.

Tuning OLTP vs. DSS Patterns
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is part two of the SQL Server: Common Query Tuning Problems and Solutions course and you're watching the Tuning OLTP versus DSS Patterns module where we'll look at how different workload and data to your architectures have specific query and design characteristics that on average should be adhered to. OLTP and DSS workloads have typical resource usage characteristics that vary from one another and require different approaches to both workload construction and data to your design. In this module, we'll walk through common query performance patterns and associated solutions related to OLTP and DSS workload mismatches. Aside from mismatches, we'll also cover problems that are specific to OLTP and DSS scenarios.

Advanced Query Tuning Patterns
Hi this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the part two of SQL server common query tuning problems and solutions course. And you're watching the advanced query tuning patterns module where we'll explore common, but more difficult and subtle query tuning patterns and potential solutions. In this last module of the course, I've aggregated query tuning patterns that don't fit neatly into the categories used in the previous modules. Some of the patterns I'll show you in this module are actually a mix of patterns, including bad assumptions by the query optimizer, imprecision, inefficiency, and workload characteristic mismatches. I'll also call out cases where query is performed badly due to known limitations or implementation issues within SQL server. So in this module, I'll demonstrate several advance query tuning patterns, and associated solutions.