SQL Server: Analyzing Query Performance for Developers

This course will teach you everything you need to know as a developer to start making sense of query plans and improving query performance. Apart from developers, this course is also applicable to anyone who is responsible for SQL Server performance.
Course info
Rating
(42)
Level
Beginner
Updated
Sep 29, 2017
Duration
4h 3m
Table of contents
Course Overview
Introduction
Finding Information About Queries
Understanding Query Performance Metrics
Reading Query Plans
Operators in a Query Plan
Important Information in a Plan
Description
Course info
Rating
(42)
Level
Beginner
Updated
Sep 29, 2017
Duration
4h 3m
Description

Developers are sometimes charged with fixing performance issues, and they have no idea where to start. They've never seen a query plan, or when they look at one they're not sure what it *really* means. The developers know how long it takes for a query to execute, but they don't know how to see how much IO, CPU, or memory it uses. And sometimes they just create an index and that seems to fix the problem (even though they have no idea why!). In this course, SQL Server: Analyzing Query Performance for Developers, you'll learn about all the data that SQL Server generates when a query executes - it's more than just the query plan - and you'll learn where it exists and how to find it. First, you'll discover what the data represents and how to read a query plan. Next, you'll explore some of the query plan operators you'll see most often. Finally, you'll learn what information is included in the plan, which may not be immediately obvious, but can be extremely valuable when troubleshooting. When you're finished this course, you'll have the skills and knowledge to start examining and understanding query plans and making query changes for better performance!

About the author
About the author

Erin Stellato is a Principal Consultant with SQLskills and a SQL Server MVP. She has worked as a SQL Server professional since 2003 and her interests include Internals, Performance Tuning, High Availability and Disaster Recovery. Erin is an active member of the SQL Server community as a presenter and blogger.

More from the author
SQL Server: Introduction to Query Store
Beginner
3h 2m
Jan 31, 2017
More courses by Erin Stellato
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hello. My name is Erin Stellato, and welcome to my course, SQL Server: Analyzing Query Performance for Developers. I'm a SQL Server consultant with SQL Skills, as well as a Microsoft data platform MVP, and many of the clients I work with don't have a database administrator, but they do have database developers. These developers are sometimes charged with fixing performance issues and they have no idea where to start. They've never seen a query plan or when they look at one they're not sure what it really means. The developers know how long it takes for a query to execute, but they don't know how to see how much I/O, CPU, or memory it uses, and sometimes they just create an index and that seems to fix the problem, even though they have no idea why. If any of these sound like you, then this course is one you should watch. We will talk about all that data that SQL Server generates when a query executes. It's more than just the query plan, and we'll cover where it exists and how to find it. We will step through what the data represents, how to read a query plan, and we will review some of the operators you will see most often. We'll wrap up the course discussing what information is included in the plan, which may not be immediately obvious, but can be extremely valuable when troubleshooting. If you've been lost when looking at query plans, or aren't sure what other metrics matter when you're talking about query execution, then join me as we create a foundation for looking at query performance. If you're not a developer, but are new to SQL Server, then you'll still benefit from the information in this course, SQL Server: Analyzing Query Performance for Developers.

Reading Query Plans
Hi. This is Erin Stellato from sqlskills. com, and I'm recording this course for Pluralsight. This course is analyzing query performance for developers in SQL Server, and this module's topic is Reading Query Plans. In this module we're moving away from execution statistics for a bit, and we're going to focus on execution plans. We'll start by making sure that we are clear on what a query plan represents, what it's really telling us, and then we'll talk about the different options for reading an execution plan, and what information we want to look at first in a plan because this often helps us determine the next steps when troubleshooting query performance. I've stated before that every single query that gets submitted to SQL Server has a plan generated, and this starts with the query optimizer. You can think of the optimizer as a framework within SQL Server for finding a query plan. There's a parsing phase where there are basic checks of the SQL syntax to make sure it's correct, and from that a parser tree is generated, which is the internal representation of the query. That goes to the binding phase where the optimizer validates that all these objects exist, and they're accessible to the user, and the output from that binding phase is the query treat. The query tree then goes into the optimization phase, and the query tree has logical operations in it. These describe what will occur, such as reading data from a table, sorting data, performing a join, and the optimizer then uses transformation roles to generate physical operations from those logical ones. Reading data from a table gets turned into an index seek, a join becomes a nested loop, and those physical operations are then assembled to create the query plan, and you as a developer can then use that plan to understand what SQL Server did when it executed your query.