Press "Enter" to skip to content

Category: Query Tuning

Getting the Last Actual Plan

Published 2019-09-12 by Kevin Feasel

Grant Fritchey shows off an improvement in SQL Server 2019:

I’ve always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It’s not that we’re going to get a completely different execution plan when we look at an actual plan, it’s just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server 2019 (CTP 2.4 and greater) thanks to sys.dm_exec_query_plan_stats

Click through for an example, as well as what you need to do to enable this.

Comments closed

Execution Plan Properties

Published 2019-09-11 by Kevin Feasel

Erik Darling reminds you to check out the properties of execution plan elements:

I read a lot of posts about query plans, and I rarely see people bring up the properties tab.

And I get it. The F4 button is right next to the F5 button. If you hit the wrong one, you might ruin everything.

But hear me out, dear reader. I care about you. I want your query plan reading experience to be better.

Erik provides sound advice here.

Comments closed

When Adding Indexes Hurts Performance

Published 2019-08-20 by Kevin Feasel

Jeffry Schwartz takes us through an odd case:

Recently, a customer requested that we tune a query that took 13 seconds to return 11 rows.  SQL Server 2017 suggested an index to improve performance, so we added it in a development environment.  The improvement made the query run 647 seconds, almost 50 TIMES longer than the original!  This naturally caused much consternation, so we decided to determine what and why it happened as well as how we could still achieve the original objective, i.e., make the query run faster.  This article discusses what caused the original performance problem in addition to the new one that was caused by the introduction of an index, and illustrates how we were able to make the query run significantly faster than it did originally.  We will cover reading query plans, examining the specific details of query plan operators, the effects of index statistics on missing index recommendations, using query plan XML to enable simpler query plan comparison, and the effects of using functions in where clauses. 

Click through to understand how this could be the case.

Comments closed

Power Query and the Benefits of Immutability

Published 2019-08-19 by Kevin Feasel

Chris Webb explains why immutable expressions can be faster to run multiple times than mutable processes:

Instead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m  adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However this query takes 20 seconds to run! Why?

Read on for the explanation.

Comments closed

Logical Equivalence and Query Tuning

Published 2019-08-14 by Kevin Feasel

Erik Darling didn’t warn us that there would be math:

Often when query tuning, I’ll try a change that I think makes sense, only to have it backfire.

It’s not that the query got slower, it’s that the results that came back were wrong different.

Now, this can totally happen because of a bug in previously used logic, but that’s somewhat rare.

And wrong different results make testers nervous. Especially in production.

This is where knowledge of abstract math and logic (like De Morgan’s Laws, both of which I’ve used to tune queries in the past because I’m a nerd) can pay off

Comments closed

SQL Server Execution Plan Operators

Published 2019-08-14 by Kevin Feasel

Bert Wagner takes us through some of the more common execution plan operators:

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.

Spools are my bete noire. I love what Erik Darling calls them: SQL Server passive-aggressively telling you that you need an index without actually saying that you need an index.

At the end of the post, Bert calls out Hugo Kornelis’s operator list. Definitely check that out too, as Hugo is putting together a masterpiece in the original meaning of the term.

Comments closed

Index Column Order and Selectivity

Published 2019-08-08 by Kevin Feasel

Erik Darling gives us multiverse indexing:

Missing index request column order is pretty basic.

Instead, we’re gonna add these:

CREATE INDEX ix_spaces
ON dbo.Posts(ParentId, Score);
 
CREATE INDEX ix_tabs
ON dbo.Posts(Score, ParentId);

SQL Server is capable of using both intelligently. This is something I generally don’t like to do, but if you have queries which absolutely need differently-ordered index columns, it can make sense to do this. Just don’t expect SQL Server’s missing index DMV to tell you which order they should be in.

Comments closed

Tips for Reading Execution Plans

Published 2019-08-07 by Kevin Feasel

Bert Wagner gives us some tips for reading execution plans in SQL Server:

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.

Right-to-left, top-to-bottom gives you the flow of information and that’s quite important. To understand how the engine works, though, you also need to read left-to-right, as Brad Schulz’s outstanding one-act play demonstrates.

Comments closed

The Cost of Abstraction

Published 2019-08-06 by Kevin Feasel

Erik Darling explains that abstraction can be the cause of performance woes in SQL Server:

There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.

Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.

Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.

It’s a logical separation, not a physical one.

In addition, as your query gets more and more complex, the optimizer eventually gives up and gives you what will likely be an ugly version of its implementation because there are too many potential solutions.

Comments closed

Getting the Largest Value Per Group

Published 2019-07-25 by Kevin Feasel

Erik Darling has a series on getting the highest value using CROSS APPLY. Part 1 covers the no-index route:

Let’s say you wanna get the highest thing. That’s easy enough as a concept.

Now let’s say you need to get the highest thing per user. That’s also easy enough to visualize.

There are a bunch of different ways to choose from to write it.

Part 2 covers the yes-index route:

In this round, row number had a tougher time than other ways to express the logic.

It just goes to show you, not every query is created equal in the eyes of the optimizer.

I don’t think I’m spoiling too much by saying that you really want a good index in place when using CROSS APPLY in this manner.

Comments closed