SQL Server Execution Plan Operators

Published on: 2019-08-13

Watch this week’s Execution Plan Operators episode on YouTube.

SQL Server Execution Plan Operators

When examining a query’s execution plan, certain operators tend to crop up over and over again as the culprits of many performance problems.

Today I want to share which execution plan operators I typically look at first when checking for performance issues. While seeing these operators in your plans isn’t necessarily a bad thing, they are always worth a double check to see if they are the cause of your query performance bottlenecks.

This is part 4 of a series on execution plans. Need to catch up? Check out part 1’s introduction to execution planspart 2’s overview of statistics, and part 3’s explanation of how to read an execution plan.

Index Seek and Index Scan

Some of the first execution plan operators that many people learn to look for are clustered and non-clustered index seeks and scans. General wisdom says that seeks are good for performance because they represent SQL Server navigating directly to the rows of data it needs, while scans are bad because they represent SQL Server reading down the index to extract many rows, leading to a slower operation.

In general, this is a pretty good generalization. However, it’s often important to check what your seek and scan operators are doing because it might not always be what you expect.

Fast index seek

For example, look at the Actual Number of Rows property for these two index seek operations: 

Slow index seek

In the first result set, we see the index seek returns 1 row based on our query’s WHERE clause. This is the kind of performance we like to see! In the second query however, SQL Server “seeks” 4 million rows. While having an index seek return 4 million rows isn’t necessarily a bad thing if that’s what we actually need the query to do, it does show that not all seek operations are highly targeted, fast retrievals that you might expect when seeing the index seek operator.

And seeing an index scan doesn’t immediately mean bad performance either:

Fast index scan

In this case, the index scan only returns 3 records. You won’t be able to get much better performance than what you already have, even if you try to refactor your query/indexes to get this to become an Index Seek operation.

RID Lookup and Key Lookup

Another duo of operators I look for when troubleshooting performance in query plans are the RID and Key Lookup operators.

RID Lookup is an easy fix – if you see this operator, it means you are missing a clustered index on your table. At the very least you should add a clustered index and immediately get some improved performance for most if not all of your queries.

Key lookups are more nuanced. SQL Server uses a Key Lookup when it knows it can use a nonclustered index to retrieve data efficiently, but then has to go out to the clustered index to look up the remaining values for the rows that weren’t present in the nonclustered index.

Key lookup

Key lookups aren’t necessarily always bad. Having SQL Server go out to the clustered index to retrieve the missing values it needs is pretty efficient compared to having to create and maintain whole new indexes.

However, if all SQL Server is retrieving from the Key Lookup operation is a single column of data, it might be just as easy to add that one column to your existing nonclustered index. Yes, the index size will be bigger by that one column, but if SQL Server can avoid having to go to two indexes to return all the data it needs, it will probably be more efficient overall.

Sort

Sort

Sort operators are simple in what they do: they change the order of the rows of data that are flowing through it.

Sorting is one of the most expensive operations that can occur in an execution plan though, so it is best to avoid them as much as possible.

One of the easiest ways to avoid a sort operator is to have the data stored in that pre-ordered format. This can be accomplished by creating an index with the key columns listed in the same order as what the sort operator is doing.

If SQL Server is having to sort the same data in the same order multiple times as part of your execution plan, another possibility is to break the query up into multiple steps with indexed temporary tables used to stage the data in between each step. While replacing a single sort operator with an indexed temp table won’t benefit performance, if you can reuse that temporary table multiple times as part of your query execution then you will obtain a net performance savings.

Spools

Spools

Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb.

SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow further data operations. The downside to this however is the need to write the data to disk in tempdb.

When I see a spool, I first often try to think if there is a way to rewrite the query to avoid the spool in the first place. If that fails, using the divide and conquer temp table technique can also replace a spool, giving you more control over how SQL Server is writing and indexing the data in tempdb.

Joins

I’ve written about nested loops joinsmerge joins, and hash match joins before, but it’s worth mentioning them in this post as well.

Merge joins are interesting to spot because of how rarely I see them in real-world queries; they cause celebration instead of concern however because they typically are the most efficient of the logical join operators.

Nested loops joins on the other hand I see often. I don’t usually focus on these too much unless something else seems suspicious in the area surrounding them. Nested loops joins do a pretty good job of efficiently joining relatively small sets of data.

Hash match joins I almost always inspect closer. These join operators are typically chosen by the query optimizer for one of two reasons:

  1. The datasets being joined are so large that they can only be handled by a hash match join.
  2. The datasets are not ordered on their join columns and SQL Server thinks calculating hashes and looping through will be faster than sorting the data.

For the first scenario, there is not much you can do besides figuring out a way to join less data.

The second scenario is worth looking at a little more closely. If there is some way to get the data in ordered before joining, like predefining the sort order in an index, then it’s possible SQL Server will choose a faster join algorithm instead.

Parallelism

Parallelism operators are typically viewed as good things: SQL Server chunks your data into multiple parts to be processed asynchronously on multiple CPUs, reducing the total wall clock time that it takes for your query to finish.

Parallelism

However, parallelism can be a bad thing if ALL (or most) queries utilize parallelism. Parallelism isn’t magic, the CPUs are still doing the same amount of work (and taking away resources from other queries that could be running), plus the overhead that you have to account for of SQL Server dividing up and then rejoining all of the data from multiple CPU threads.

I typically become suspicious of parallelism if it seems like most queries I troubleshoot on a server are producing parallel plans. If that’s the case, I may consider revisiting the cost threshold for parallelism setting to see if it is set too low.

More operators

This post is not a definitive list of all execution plan operators; I tried to cover the ones I see causing me problems the most often.

If you are interested in learning about more operators, you can visit the official documentation or [Hugo’s wonderful SQL Server Execution Plan Reference’s Operator List)[https://sqlserverfast.com/epr/operator-list/].

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

5 Things You Need To Know When Reading SQL Server Execution Plans

Published on: 2019-08-06

Watch this week’s episode on YouTube.

5 Things You Need To Know When Reading SQL Server Execution Plans

In the first part of this series I explained what an execution plan is and how to view one. Last week I threw you a curve ball and didn’t show you any execution plans at all, instead focusing on the statistics data that helps SQL Server generate query plans. This week, we’ll finally dive into what you need to know to read an execution plan.

Execution Plan Order

Execution plans show the steps SQL Server takes to execute your query. Each icon in the graphical execution plan is known as an operator, and the most common way to read a plan is by starting with the top right most operator and following the arrows to the left.

When you reach a join or concatenation operator where multiple branches merge into one operator, you can proceed to the right-most operator of one of the lower branches and start the process of reading right to left again. In general, this can be summed up as reading a plan right to left, top to bottom.

Operator execution order

By following the arrows from one operator to the next, you are following the flow of data through the plan. Each operator releases a row of data to the operator immediately to the left of it until all of the data rows have made their way to the left most operator.

Arrows

Arrows identify the direction of data flow between operators.

Arrow sizes

In a SQL Server Management Studio execution plan, they also represent the relative size of the data at that step. Brent Ozar recently wrote a detailed post demoing the differences between arrow sizes between estimated and actual execution plans. In summary, the arrow size represents the estimated number of rows output from the source operator in estimated execution plans, and the number of rows read by the source operator in actual execution plans.

When troubleshooting plans, these relative arrow sizes can be helpful to tip you off of where you might be seeing more data than you expected in your plans.

Operator properties

Hovering over an operator (or an arrow) gives us additional information about what that operator is doing. Not only does the hover overlay provide a description of an operator, it also shows us the number of rows SQL Server expected to read during this step versus what it actually read (if using the actual execution plan), what predicates were applied, warning messages, and more.

Operator properties

Bringing up the properties window (F4 by default) with an operator selected will provide even more information. I typically find myself looking in these properties to discover memory and CPU thread usage, and what transformations SQL Server is doing as part of my query execution.

Costs

Relative operator cost

Under each operator you will notice the percentage cost of that operator relative to all other costs in the plan. These relative costs can sometimes help highlight where the major pain points in your execution plan are occurring.

I say sometimes because these execution plan costs are known to not always be accurate. Starting off your query performance troubleshooting session by identifying the high cost operators can be a good starting point, but you never want to rely on these cost numbers alone.

Warnings

Operator warnings

Warnings, denoted by a yellow exclamation point icon on the corner of the operator icon, indicate that something undesirable may be happening with that operator.

The usual culprits for these warnings are things are operators spilling to tempdb, implicit conversions that SQL Server has to make for a comparison (that potentially prevent an index from being used), and SQL Server telling you that it over/underestimated the amount of memory it needs to use for the query you are executing.

All of these problems might negatively impact query performance so it’s always worth taking a look at the warning to see if it is important enough to correct.

Index Recommendations

Index recommendations

If SQL Server thinks your query’s performance can be improved with an index, it will tell you in reassuring green text at the top of your execution plan. How helpful!

The only catch is that these index recommendations usually aren’t ready to use. It’s not that they are bad, it’s just that they are often incomplete, missing additional columns you could include to make your queries even more efficient than what the base recommendation suggests.

So while these recommendations are a good starting point, you should always give them some additional attention to see if you can improve them further.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Execution Plans: Statistics

Published on: 2019-07-30

Watch this week’s Statistics video on YouTube.

Last week we looked at what execution plans are and how you can view them.

This week I want to discuss what data the query optimizer uses to make cost-based calculations when generating execution plans. Understanding the meta-data SQL Server uses to generate query plans will later help us correct poorly performing queries.

Statistics

Statistics are the primary meta data used by the query optimizer to help estimate the costs of retrieving data for a specific query plan.

The reason SQL Server uses statistics is to avoid having to calculate information about the data during query plan generation. For example, you don’t want to have the optimizer scan a billion row table to learn information about it, only to then scan it again when executing the actual query.

Instead, it’s preferable to have those summary statistics pre-calculated ahead of time. This allows the query optimizer to quickly generate and compare multiple candidate plans before choosing one to actually execute.

Creating and Viewing Statistics

Statistics are automatically maintained when the Auto Create Statistics and Auto Update Statistics database properties are on (in general, you want to leave these on):

auto create and update statistics

Now, anytime an index is created or enough data in that index has changed (see thresholds for the amount of data in the SQL Server documentation), the statistics information will be updated and maintained as well.

To view the statistics for an index or column, you can run DBCC SHOW_STATISTICS:

DBCC SHOW_STATISTICS('dbo.Users','PK_Users_Id')
DBCC SHOW_STATISTICS output

Statistics Data

The data returned by DBCC SHOW_STATISTICS is what SQL Server uses to interpret what the data in your index looks like.

I don’t to turn this into a deep dive on statistics , but here are a few key things you should be aware of in this statistics output: – Rows: the number of rows in the table. – Rows Sampled: how many rows were used to calculate these statistics. This can be a subset or all of the rows of your table. – All Density: a measure of how many distinct values are in your data. – Histogram: shows frequencies for up to 200 ranges of values in your index.

Altogether, this information helps paint a picture of your data for the SQL Server query optimizer to use for deciding how it should retrieve your data.

When this picture is accurate, the query optimizer makes decisions that allow your queries execute relatively efficiently. If it has correct estimates about the number of rows it needs to process and the uniqueness of values within those rows, then the query optimizer will most likely allocate appropriate amounts of memory, choose efficient join algorithms, and make other performance choices that will make your query run efficiently.

When the statistics don’t accurately represent the data, the query optimizer still tries to do the best with the information it has available but often ends up making some less-than-optimal choices.

We’ll save troubleshooting statistics information for another post, but for now be aware that if your statistics are inaccurate, there is a pretty good change SQL Server won’t generate efficient execution plans.

Cardinality Estimators

Having high level statistics about your data is only the first part of the equation. SQL Server needs to use those statistics alongside some assumptions to estimate how expensive it will be to retrieve that data.

The assumptions SQL Server makes about data are contained within the Cardinality Estimator. The Cardinality Estimator is a model that makes assumptions about things like data correlation, distribution, and more.

There are two cardinality estimating models currently available in SQL Server: the Legacy Cardinality Estimator available from SQL Server 7.0 to SQL Server 2012, and the New Cardinality Estimator introduced in SQL Server 2014. Each of these Cardinality Estimators makes different assumptions about your data and can therefore produce different results. General guidance suggests to use the New Cardinality Estimator for new work, however sometimes it can be beneficial to use the Legacy Cardinality Estimator if its assumptions more closely align with your data.

Once again, I don’t want to go too deep on the differences between cardinality estimators, but it’s worth knowing that if you are getting bad estimates from the New Cardinality Estimator, you can easily revert to the Legacy Cardinality Estimator by appending the following hint to the end of your query:

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Conclusion

SQL Server pre-calculates statistics data on indexes and columns to provide the Query Optimizer information about the data it needs to retrieve. When these statistics accurately reflect what the data actually looks like, SQL Server will typically generate execution plans that perform well. When this data is outdated or missing, SQL Server makes an uninformed guess about how to retrieve the data, potentially causing serious performance issues.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!