Exploring Query Execution Plans

In the previous chapters, we learned how to construct a Transact-SQL (T-SQL) query, how the SQL Database Engine processes a query, and how the query is optimized, which results in an execution plan that can be cached and reused by subsequent query executions. Now that we understand the steps the SQL Database Engine follows to produce a plan and execute a query, we can investigate an execution plan to examine the results of this process and begin analyzing how we can improve the performance of our queries.

In this chapter, we’re going to cover the following main topics:

• What is a query plan?
• Accessing a query plan
• Navigating a query plan
• Query plan operators of interest
• Query plan properties of interest

Technical requirements

The examples used in this chapter are designed for use on SQL Server 2022 and Azure SQL Database, but they should work on any version of SQL Server, 2012 or later. The Developer Edition of SQL Server is free for development environments and can be used to run all the code samples. There is also a free tier of Azure SQL Database that you can use for testing at https://aka.ms/freedb.

You will need the AdventureWorks2016_EXT (referred to as AdventureWorks) and AdventureWorksDW2016_EXT (referred to as AdventureWorksDW) sample databases, which can be found on GitHub at https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks. Code samples for this chapter can also be found on GitHub at https://github.com/PacktPublishing/Learn-T-SQL-Querying-Second-Edition/tree/main/ch3.

What is a query plan?

Think of a query execution plan as a map that provides information on the physical operators that implement the logical operations discussed in the Understanding Query Processing chapter, as well as the execution context for that query that provides information about the system on which the query was executed. Each physical operator is identified in the plan with a unique node ID.

Note

Query execution plans are often referred to as a showplan, which is a textual, XML, or graphical representation of the plan.

So far, we’ve used the terms query plan and query execution plan interchangeably. However, in the SQL Database Engine, there is the notion of an “actual plan” and an “estimated plan.” These differ only in the fact that an “actual plan” has runtime data collected during actual execution (hence, query execution plan), whereas an “estimated plan” is the output of the Query Optimizer that...

Accessing a query plan

To access estimated plans, which are a direct result of the optimization process, we can use either T-SQL commands or graphical tools. For the examples shown in this chapter, we use SQL Server Management Studio (SSMS).

Note

For most users, query plans in text format are harder to read and analyze; therefore, we will use graphical query plan examples throughout the book.

The SET command options SHOWPLAN_TEXT, SHOWPLAN_ALL, and SHOWPLAN_XML provide text-based information on query plans with different degrees of detail. Using any of these commands means the SQL Database Engine will not execute the T-SQL statements but show the query plan as produced by the Query Optimizer.

Take an example of a query that can be executed in the scope of the AdventureWorks sample database:

SELECT pp.FirstName, pp.LastName, pa.AddressLine1, pa.City, pa.PostalCode
FROM Person.Address AS pa
INNER JOIN Person.BusinessEntityAddress AS pbea ON pa.AddressID = pbea.AddressID...

Navigating a query plan

Up until this point, we have mentioned query execution plans, and even shown simple examples to illustrate some points during the Mechanics of the Query Optimizer chapter. However, it is important for any database professional to understand how to read and analyze a query execution plan as a way to visually identify positive changes in a plan shape. The remaining chapters in the book will show query execution plans in more detail for different scenarios of T-SQL patterns and anti-patterns.

Query plans are like trees, where each join branch can represent an entirely separate query. To understand how to navigate a showplan or query plan, let’s use a practical example of a query executed in the AdventureWorks sample database:

SELECT p.Title + ' ' + p.FirstName + ' ' + p.LastName AS FullName, c.AccountNumber, s.Name AS StoreName
FROM Person.Person p
INNER JOIN Sales.Customer c ON c.PersonID = p.BusinessEntityID
INNER JOIN Sales.Store...

Query plan operators of interest

The different icons that are visible in a query execution plan are called operators. Logical operators describe a relational operation – for example, an INNER JOIN operation. Physical operators implement the logical operation with a specific algorithm. So, when we examine a query plan, we are looking at physical operators.

Each physical operator represents a task that needs to be performed to complete the query such as accessing data with a seek or a scan, joining data with a Hash Match join or a Nested Loops join, and sorting data. Some operators are especially relevant to understand while writing T-SQL that scales well. We will look at these operators, understand what they do and how they implement the physical operation behind the logical operation in T-SQL statements, and become familiar with aspects that will be important in the upcoming chapters where we explore T-SQL patterns and anti-patterns.

Blocking versus non-blocking operators...

Query plan properties of interest

Each operator in a query execution plan has several properties that provide context and metrics around its compilation, optimization, and execution. The plans also have global properties to provide overall context. Examining some key properties for the overall plan and some operators is especially relevant to writing T-SQL that scales well. We will look at these properties, understand their meaning, and become familiar with their significance, which will be important in the chapters where we explore T-SQL patterns and anti-patterns.

Plan-level properties

The root node of a plan has a few properties that are important for understanding the context of execution. Different trace flags or SET options change execution context and may drive query optimization choices, so having this information persisted in the showplan is a valuable tool.

The following example shows a query executed in the AdventureWorks sample database that allows us to examine...

Summary

Hopefully, after reading this chapter, you have a good understanding of the various elements that make up a query execution plan in the SQL Database Engine. Nearly everything we need to understand and troubleshoot the performance of our T-SQL queries can be found somewhere in the plan, either in the visible part of the plan or in the Properties windows, which we can access by right-clicking the operators. In the next chapter and throughout the rest of this book, we will use query execution plans to illustrate various T-SQL patterns and anti-patterns so that we can identify and remediate them in our own code.