Execution Plans Archives | Page 25 of 31

Spills Week: How Hash Aggregate Spills Hurt SQL Server Query Performance

Posted on July 31, 2019May 16, 2022 by Erik Darling

Quite Different

Hash spills are nothing like sort spills, in that even with (very fast) disks, there’s no immediate benefit to breaking the hash down into pieces via a spill.

In fact, there are many downsides, especially when memory is severely constrained.

The query that I’m using looks about like this:

SELECT   v.PostId, 
		 COUNT_BIG(*) AS records
FROM dbo.Votes AS v
GROUP BY v.PostId
HAVING COUNT_BIG(*) >  2147483647

The way this is written, we’re forced to count everything, and then only filter out rows at the end.

The idea is to spend no time waiting on rows to be displayed in SSMS.

Just One Int

To get an idea what performance looks like, I’m starting with one integer column.

With no spills and a 776 MB memory grant, this runs for about 15 seconds.

If we drop the grant down to about 10 MB, we spill a bunch, but runtime doesn’t go up too much.

And if we drop it down to 4.5 MB, things go absolutely, terribly, pear shaped.

The difference in both the number of pages spilled and the spill level are pretty dramatic.

Expansive

If we expand the query a bit to look like this, memory starts to matter more:

SELECT   v.PostId, 
         v.UserId, 
		 v.BountyAmount, 
		 v.VoteTypeId, 
		 v.CreationDate, 
		 COUNT_BIG(*) AS records
FROM dbo.Votes AS v
GROUP BY v.PostId, 
         v.UserId, 
		 v.BountyAmount, 
		 v.VoteTypeId, 
		 v.CreationDate
HAVING COUNT_BIG(*) >  2147483647

With more columns, the first spill escalates to a higher level faster, and the second spill absolutely wipes out.

It runs for almost 2 minutes.

As a side note, I really hate how long that Repartition Streams operator runs for.

Predictably

When we get the Comments table involved, that string column beats us right up.

The first query asks for the largest possible grant on my laptop: 9.7GB. The second query gets 10MB.

The spill is godawful.

When we reduce the memory grant to 4.5MB, the spill runs another 1:20, for a total of 3:31.

Those spills are the root cause of why these queries run longer than any we’ve seen to date in this series.

Something quite funny happens when Hashes of any variety spill “too much” — which you can read about in more detail here.

There’s an Extended Event called “hash warning” that we can use to track recursion and bailout.

Here’s the final output aggregated:

SQL Server Extended Events — [outdated political joke]

What happens when a Hash Aggregate bails out?

GOOD QUESTION.

In Which I Belabor The Point Anyway, Despite Saying…

Not to belabor the point too much, but if we select and group all the columns in the Comments table, things get a bit worse.

Three minutes of spills. What a time to be alive.

But, yeah, the bulk of the trouble here is caused by the string column.

Adding in some numbers and a date on top doesn’t have a profound effect.

Taking Up

While Sort Spills certainly dragged query performance down a bit when memory was severely limited, Hash Spills were far more detrimental.

If I had to choose between which one to investigate first, it’d be Hash spills.

But again, small spills are often not worth the effort, and in some cases, you may always see spills.

If your server is totally under-provisioned from a memory perspective, or if there are multiple concurrent memory consuming operations (i.e. they can’t share intra-query memory), it may not be possible for a large enough grant to be give to satisfy all of them.

This is part of why writing very large queries can be perilous, and it’s usually worth splitting them up.

In tomorrow’s post, we’ll look at hash joins.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Spills Week: When Sort Spills Start To Hurt SQL Server Performance

Posted on July 30, 2019May 16, 2022 by Erik Darling

Imbalance

In yesterday’s post, we looked at a funny situation where a query that spilled was about 5 seconds faster than one that didn’t.

Here’s what the query looked like:

SELECT x.PostId
FROM (
SELECT v.PostId, 
       ROW_NUMBER() OVER ( ORDER BY v.PostId DESC ) AS n
FROM dbo.Votes AS v
) AS x
WHERE x.n = 1;

Now, I can add more columns in, and the timing will hold up:

SELECT x.Id, 
       x.PostId, 
	   x.UserId, 
	   x.BountyAmount, 
	   x.VoteTypeId, 
	   x.CreationDate
FROM (
SELECT v.Id, 
       v.PostId, 
	   v.UserId, 
	   v.BountyAmount, 
	   v.VoteTypeId, 
	   v.CreationDate,
       ROW_NUMBER() OVER ( ORDER BY v.PostId DESC ) AS n
FROM dbo.Votes AS v
) AS x
WHERE x.n = 1;

They both got slower, the non-spill plan by about 2.5s, and the spill plan by about 4.3s.

But the spill plan is still 3s faster. With fewer columns it was 5s faster, but hey.

No one said this was easy.

Fully comparing things from yesterday, when memory is capped at 0.0, the query takes much longer now, with more columns:

To compare the “fast” spills, here’s yesterday and today’s warnings.

With one integer column, we spilled 100k pages.

With five integer columns and one datetime column, we spill 450k pages.

That’s a non-trivial amount. That’s like every column adding 75k pages to the spill.

If you’re really worried about spills: STOP SELECTING SO MANY COLUMNS.

For The Worst

I promised to show you things going quite downhill, and for the spill query to no longer be faster.

To do that, we need a different table.

I’m going to use the Comments table, because it has a column called Text in it, which is an NVARCHAR(700).

Very few comments are 700 characters long. The majority are < 120 or so.

This query looks about like so:

SELECT x.Id, 
       x.CreationDate, 
	   x.PostId, 
	   x.Score, 
	   x.Text, 
	   x.UserId
FROM (
SELECT c.Id, 
       c.CreationDate, 
	   c.PostId, 
	   c.Score, 
	   c.Text, 
	   c.UserId,
       ROW_NUMBER() 
           OVER ( ORDER BY c.PostId DESC ) AS n
FROM dbo.Comments AS c
) AS x
WHERE x.n = 1

And the results are… icky.

The top query asks for 9.7GB of RAM. That’s as much as my laptop can give out.

It still spills. Nearly 10GB of memory grant, and it still spills.

If you care about spills: STOP OVERSIZING STRING COLUMNS:

Apparently only spilling 1mm pages is a lot faster than spilling 2.5mm pages.

But still much slower than not spilling string columns.

Who knew?

Matters of Whale

I was using the Stack Overflow 2013 database for that, which is fairly big relative to the 64GB of RAM my laptop has.

If I go back to using the 2010 version, we can get a better comparison, because the first query won’t spill anymore.

Some points to keep in mind here:

I’m testing with (very fast) local storage
I don’t have tempdb contention

But still, it seems like spilling out non-string columns is significantly less painful than spilling out string columns.

Ahem.

“Seems.”

I’ll reiterate two points:

Stop selecting so many columns
Stop oversizing string columns

In the next two posts, we’ll look at hash match and hash join spills under similar circumstances.

Thanks for reading!

Going Further

Spills Week: When Sort Spills Don’t Really Hurt SQL Server Performance

Posted on July 29, 2019May 16, 2022 by Erik Darling

Pre-faced

Every post this week is going to be about spills. No crazy in-depth, technical, debugger type stuff.

Just some general observations about when they seem to matter more for performance, and when you might be chasing nothing by fixing them.

The queries I use are sometimes a bit silly looking, but the outcomes are ones I see.

Sometimes I correct them and it’s a good thing. Other times I correct them and nothing changes.

Anyway, all these posts started because of the first demo, which I intended to be a quick post.

Oh well.

Intervention

Spills are a good thing to make note of when you’re tuning a query.

They often show up as a symptom of a bigger problem:

Parameter sniffing
Bad cardinality estimates

My goal is generally to fix the larger symptom than to hem and haw over the spill.

It’s also important to keep spills in perspective.

Some are small and inconsequential
Some are going to happen no matter what

And some spills… Some spills…

Can’t Hammer This

Pay close attention to these two query plans.

Not sure where to look? Here’s a close up.

See that, there?

Yeah.

That’s a Sort with a Spill running about 5 seconds faster than a Sort without a Spill.

Wild stuff, huh? Here’s what it looks like.

Not inconsequential. >100k 8kb pages.

Spill level 2, too. Four threads.

A note from future Erik: if I run this with the grant capped at 0.0 rather than 0.1, the spill plan takes 12 seconds, just like the non-spill plan.

There are limits to how efficiently a spill can be handled when memory is capped at a level that increases the number of pages spilled without increasing the spill level.

But it’s still funny that the spill and non-spill plans take about the same time.

Why Is This Faster?

Well, the first thing we have to talk about is storage, because that’s where I spilled to.

My Lenovo P52 has some seriously fast SSDs in it. Here’s what they give me, via Crystal Disk Mark:

If you’re on good local storage, you might see those speeds.

If you’re on a SAN, I don’t care how much anyone squawks about how fast it is: you’re not gonna see that.

(But seriously, if you do get those speeds on a SAN, tell me about your setup.)

(If you think you should but you don’t, uh… Operators are standing by.)

With that out of the way, let’s hit some reference material.

Kiwis & Machanics

First, Paul White:

Multiple merge passes can be used to work around this. The general idea is to progressively merge small chunks into larger ones, until we can efficiently produce the final sorted output stream. In the example, this might mean merging 40 of the 800 first-pass sorted sets at a time, resulting in 20 larger chunks, which can then be merged again to form the output. With a total of two extra passes over the data, this would be a Level 2 spill, and so on. Luckily, a linear increase in spill level enables an exponential increase in sort size, so deep sort spill levels are rarely necessary.

Next, Paul White showing an Adam Machanic demo:

Well, okay, I’ll paraphrase here. It’s faster to sort a bunch of small things than one big thing.

If you watch the demo, that’s what happens with using the cross apply technique.

And that’s what’s happening here, too, it looks like.

On With It

The spills to (very fast) disk work in my favor here, because we’re sorting smaller data sets, then reading from (very fast) disk more small data sets, and sorting/merging those together for a final finished product.

Of course, this has limits, and is likely unrealistic in many tuning scenarios. I probably should have lead with that, huh?

But hey, if you ever fix a Sort Spill have have a query slow down, now you know why.

In tomorrow’s post, you’ll watch my luck run out (very fast) with different data.

Thanks for reading!

Going Further

The Fastest Way To Get The Highest Value In SQL Server Part 2

Posted on July 24, 2019May 14, 2022 by Erik Darling

Whistle Whistle

In yesterday’s post, we looked at four different ways to get the highest value per use with no helpful indexes.

Today, we’re going to look at how those same four plans change with an index.

This is what we’ll use:

CREATE INDEX ix_whatever
    ON dbo.Posts(OwnerUserId, Score DESC);

Query #1

This is our MAX query! It does really well with the index.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT MAX(Score) AS Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id

) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

It’s down to just half a second.

Query #2

This is our TOP 1 query with an ORDER BY.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT TOP (1) p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id
	ORDER BY p.Score DESC

) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

This finished about 100ms faster than MAX in this run, but it gets the same plan.

Who knows, maybe Windows Update ran during the first query.

Query #3

This is our first attempt at row number, and… it’s not so hot.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT p.Score,
	       ROW_NUMBER() OVER (ORDER BY p.Score DESC) AS n
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id
) AS ca
WHERE u.Reputation >= 100000
AND ca.n = 1
ORDER BY u.Id;

While the other plans were able to finish quickly without going parallel, this one does go parallel, and is still about 200ms slower.

Query #4

Is our complicated cross apply. The plan is simple, but drags on for almost 13 seconds now.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT * 
	FROM 
	(
        SELECT p.OwnerUserId,
	           p.Score,
	           ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId 
			                      ORDER BY p.Score DESC) AS n
	    FROM dbo.Posts AS p
	) AS p
	WHERE p.OwnerUserId = u.Id
	AND p.n = 1
) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

Slip On

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.

Now, initially I was going to do a post with the index columns reversed to (Score DESC, OwnerUserId), but it was all bad.

Instead, I’m going to do future me a favor and look at how things change in SQL Server 2019.

Thanks for reading!

Going Further

The Fastest Way To Get The Highest Value In SQL Server Part 1

Posted on July 23, 2019August 29, 2023 by Erik Darling

Expresso

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.

In this post, we’re going to use four ways I could think of pretty quickly, and look at how they run.

The catch for this post is that we don’t have any very helpful indexes. In other posts, we’ll look at different index strategies.

Query #1

To make things equal, I’m using CROSS APPLY in all of them.

The optimizer is free to choose how to interpret this, so WHATEVER.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT MAX(Score) AS Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id

) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

The query plan is simple enough, and it runs for ~17 seconds.

Query #2

This uses TOP 1.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT TOP (1) p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id
	ORDER BY p.Score DESC

) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

The plan for this is also simple, but runs for 1:42, and has one of those index spool things in it.

Query #3

This query uses row number rather than top 1, but has almost the same plan and time as above.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT p.Score,
	       ROW_NUMBER() OVER (ORDER BY p.Score DESC) AS n
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = u.Id
) AS ca
WHERE u.Reputation >= 100000
AND ca.n = 1
ORDER BY u.Id;

Query #4

Also uses row number, but the syntax is a bit more complicated.

The row number happens in a derived table inside the cross apply, with the correlation and filtering done outside.

SELECT u.Id,
       u.DisplayName,
	   u.Reputation,
	   ca.Score
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT * 
	FROM 
	(
        SELECT p.OwnerUserId,
	           p.Score,
	           ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId 
			                      ORDER BY p.Score DESC) AS n
	    FROM dbo.Posts AS p
	) AS p
	WHERE p.OwnerUserId = u.Id
	AND p.n = 1
) AS ca
WHERE u.Reputation >= 100000
ORDER BY u.Id;

This is as close to competitive with Query #1 as we get, at only 36 seconds.

Wrap Up

If you don’t have helpful indexes, the MAX pattern looks to be the best.

Granted, there may be differences depending on how selective data in the table you’re aggregating is.

But the bottom line is that in that plan, SQL Server doesn’t have to Sort any data, and is able to take advantage of a couple aggregations (partial and full).

It also doesn’t spend any time building an index to help that one.

In the next couple posts, we’ll look at different ways to index for queries like this.

Thanks for reading!

Going Further

How SQL Server’s Missing Index Requests Can Hurt Performance

Posted on July 12, 2019January 20, 2026 by Erik Darling

DON’T THROW EGGS

Thanks for watching!

Video Summary

In this video, I delve into a peculiar performance issue that arose from a stored procedure and a missing index request. The scenario began when a user passed an unusual negative value to the stored procedure, causing significant performance degradation. To mitigate the problem, developers implemented a catch to convert any negative values to zero, ensuring the execution plan remained stable. However, this fix led to further complications as another query against the same table exhibited poor performance due to a missing index request. The video explores how adding an index suggested by SQL Server’s missing index feature improved one query but significantly slowed down another, highlighting the importance of carefully testing database changes in development environments before deployment.

Full Transcript

Hello, Erik Darling here with Erik Darling Data. And I wanted to talk about sort of a funny situation that I was recently asked to remedy. And that funny situation, well, the funny situation started with a stored procedure, sort of was escalated by a missing index request. And all came tumbling down on top of that stored procedure. So the stored procedure, it didn’t look like this because it was not, it was not, it was not an issue with Stack Overflow. I’m going to level with you. To be very honest, Stack Overflow has never asked me to fix a performance problem. I just keep having to find these random performance problems in the, when I do demos in the database. It’s the damnedest thing, isn’t it? Anyway. In real life, it was a slightly different scenario, but it was a stored procedure. And at some point, at some point, some user, we should call them a loser probably, had passed in a bad piece of data to the stored procedure. In fact, it was someone had passed a negative value into the stored procedure once. And that caused everything to fly off the rails.

Everything went really, really, really badly with this stored procedure. So the developers in charge of it put this catch in to fix things. Where if someone passed in a negative value for their stored procedure, they would, they would revert the value to zero to a positive number so that they did not get their big, golly gosh, awful plan. So this is what the stored procedure looks like. And, you know, there’s a pretty simple select top one query after that. Not a big deal. And in fact, if we go and we look, we can see that ever since we converted this database from access to SQL Server 2000, we have had this index in place, the single key column index in place. And with that index in place, this stored procedure runs relatively quickly. And by relatively quickly, I mean, instantly. If we look at the execution plan, this thing finishes in 87 milliseconds.

There’s a missing index request. But if I have a query that’s ending, that’s finishing in 87 milliseconds, I’m not like jumping up and down and saying, hey, we really, really need to add this missing index request for reasons. Right. Like I can run this a million times every time I run this. It’s quick. And I’m not running this for like a small value. I’m running this for John Skeet and John Skeet’s got all the values. John Skeet has a lot of the most posts in the posts table. So you can, this is not like just like some wimpy value that we’re searching for. Every time we run this, it is reliably under the 90 millisecond mark.

Right. So that’s a very fast stored procedure. At least I think it’s fast. You might, you might not. You, you might be a much better query tuner than I am and have a much faster, have a much different view of what’s a fast stored procedure. Now where things got kind of weird is, and this is even with this like funny catch in place, everyone would, normally you’d see this and chop someone’s head off for declaring a variable inside a stored procedure and then feeding it into a where clause. But, but, but everything’s okay here. Where things got bad was, there was this other query. And this other query was also against the post table, but it had a different where clause.

Now what I’m going to do is I’m going to run this store, I’m going to, not store procedure, this piece of code. And this piece of code is sort of going to look at, look, is going to look a lot like what the other query was doing. And this one takes about two seconds to finish, to get a top one. And it happened because we didn’t have another useful index for this, for this query to use. So we scanned the whole clustered index and the whole thing. Well, I mean, I guess that’s closer to about two and a half seconds there. 2.381 by 120 milliseconds.

We’re under. Now there’s a missing index request for this. I’m going to show you the missing index. Missing index details. Now if we zoom in here, zoom in and look at, look at what SQL Server thinks a helpful index is. It’s, I mean, it’s, it’s on the post table, obviously, because that’s where we’re selecting data from. And it’s on parent ID and then creation date and then last activity date. And we’re including post type ID.

Now, what you might notice at this point is that there’s some overlap between this missing index request and the query we have that’s fast. So A, they’re both on the post table. And B, the where clause for our query inside the store procedure also has parent ID and post type ID in the where clause on top of owner user ID. Right now, we have a single column index on owner user ID. When we seek to that and do a key lookup for everything else, we’re cool. We’re in great shape.

But we have this missing index request. And this missing index request was, was super, was everywhere. It was endemic. It was, I mean, it’s a big missing index request. I’m probably using all sorts of wrong words here. Let me turn off execution plans. And let’s run SP Blitz index and look at the post table. So over on the post table, the second thing we’re going to have down here is missing index requests.

Now, this one up top is the one that’s on the table for that query that we ran. And over in this window, I have run, I have run that query, as they say in the south, a whole mess of times. So this is printed out this phrase every time this, this has been running for doing a lot of stuff. But we have this missing index request. And this missing index request was showing, well, I mean, quite a bit of use, 14, almost 1,500 uses.

It would bring the query cost to zero. Impact is 100%. And the average query cost is absolutely astronomical. It is 3,474.1810 query box on that thing. So when we look at the estimated benefit of adding, I can hear my kids screaming in the background maybe.

If we look at the estimated benefit of adding this, it’s 515 million query box that we would have created or saved by adding this index to our workload. All right. You’ve talked me into its SQL Server. You have shown me that if I add this index, it would have been used 1,500 times by this very expensive query. Now, if I run that very expensive query, oh, wait, I did that. Two and a half seconds. Cool. We got that.

We have a benchmark there, right? This is two and a half seconds to do this. All right. Two and a half. Cool. Now, this is the index that someone came along and added. Someone charged a lot of money to add this index on parent ID, creation date and last activity date, and include post type ID.

Because that’s what SQL Server asked for. SP Blitz Index didn’t make this up. It didn’t conjure this out of nowhere. It showed us what SQL Server’s own DMVs have told us.

What they don’t tell us, though, is what if this index goes and screws up some other query? So let’s create this index. And this will take a moment. This will take a moment here. Create this index. This great index on parent ID and creation date and last activity date, including post type ID. Get that whole post table in there.

That took 12 seconds. But let’s look. Because now I want to make sure that this helped this query. And by God, it does. This thing finishes instantly. Now, if we turn query plans back on and look at this query, holy smokes, that is zeros.

Look it. Zeros. All zeros. We didn’t spend anything doing this. What an amazing index. What a fantastic index. That’s the best index that’s ever been created. Except now, this query slows down. This query is not as fast as it used to be.

This query has taken some extra time. This query now takes 14 seconds to run. Remember, this one was running reliably in under 90 milliseconds. And now, it just took 14 seconds to run. If we go look at the execution plan, we can see that was the entire time this thing ran.

We spent a second here. And we spent 9 seconds here. So that’s 10 seconds. Then we spent 4 seconds doing a nutty loops join. And then we spent, well, we spent to spend any time doing this sort. But, whew.

I mean, that’s bad enough. Imagine if you were tuning queries and indexes and you added that index. It was like, yeah, this is going to make everything much better. And then it made a vital query go much slower. Well, this happened for a pretty funny reason.

And a pretty funny reason is that when we declare a local variable for, what do you call it up there? Where is it going? For a parent ID.

We get a very, very bad guess in here for what’s equal, how many rows this equals several thinks are going to happen. Things are going to evaluate to true for any given predicate on parent ID. In fact, if we go look at the query plan, we go look at this seek, we can see that the estimated number of rows is 1.87.

But the actual number of rows is 6, 0, 0, 0, 2, 2, 3. 6, 0, 0, 0, 2, 2, 3. Yeah, that’s a seven finger number.

That’s a big number. We were off by a lot there. We made a pretty big mistake. If we look at the key lookup, this thing will have executed once for every row that came out of there. And that’s no good either. So we had kind of a funny, perfect storm of things go wrong here.

And if there’s a lesson, it’s that, you know, while SQL Server’s missing index requests are a lot better than nothing, they’re a good sign that we need to do some work. They are like a crying baby.

On your tables and DMBs. Whereas SQL Server says, hey, we could be doing something better over here. We have to be very careful how those indexes change other queries in the workload. And this is why, you know, we must, as responsible data peoples, tell people to test things carefully in a development environment before just releasing these changes into prod. Because you can introduce all sorts of funny regressions here where you might make one query much better.

But you can make another query much worse. Now, granted, this is not the best thing to do here. This is not a good practice. I’m not condoning doing this. But this is what made sort of the perfect storm of weird stuff happen. We’re adding that other index made this query much, much worse.

Anyway, that’s it for me. Thanks for watching. I hope you learned something. I hope you were shocked and horrified by what I showed you. And I will see you in some other video. Goodbye.

Going Further

SQL Server 2019: If You Want Adaptive Joins, You Need Wider Indexes

Posted on July 10, 2019May 14, 2022 by Erik Darling

What You Make Of It

We’ve got this query. Handsome devil of a query.

You can pretend it’s in a stored procedure, and that the date filter is a parameter if you want.

SELECT u.DisplayName, p.Score
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE u.CreationDate >= '20131201';

A long time ago, when we migrated Stack Overflow from Access to SQL Server 2000, we created indexes.

This one has worked alright.

CREATE INDEX ix_whatever 
    ON dbo.Posts(OwnerUserId);

But Now We’re On 2019

And we’ve, like, read a lot about Adaptive Joins, and we think this’ll be cool to see in action.

Unfortunately, our query doesn’t seem to qualify.

Now, there’s an Extended Event that… Used to work.

These days it just stares blankly at me. But since I’ve worked with this before, I know the problem.

It’s that Key Lookup — I’ll explain more in a minute.

Index Upgrade

First, let’s get rid of the Lookup so we can see the Adaptive Join happen.

CREATE INDEX ix_adaptathy 
    ON dbo.Posts(OwnerUserId, Score);

As We Proceed

Let’s think about what Adaptive Joins need:

An index on the column(s) you’re joining

This gives us a realistic choice between using a Nested Loops join to do efficient Seeks, or an easy scan for a Hash Join.

That index has to cover the query

Without a covering index, there’s too much for the optimizer to think about.

It’s not just making a choice between a Nested Loops or Hash Join, it’s also factoring in the cost of a Lookup.

This used to trigger the XE on eajsrUnMatchedOuter, meaning the outer table didn’t have an index that matched the query.

Why Revisit This?

When SQL Server 2019 comes out, people are gonna have really high hopes for their workloads automagickally getting faster.

While there are lots of things that it’ll likely help, it’s going to take a lot of work on your part to make sure your queries and indexes allow for the automagick to kick in.

Thanks for reading!

Going Further

The Difference Between Statistics Time And Plan Operator Times In SQL Server Query Plans

Posted on July 9, 2019May 14, 2022 by Erik Darling

Goal Posts

When you’re measuring query changes to see if your performance changes have made a difference, a common way to do that is to use STATISTICS TIME and IO.

They’re not perfect, but the barrier to entry is super low, and you can get a good enough feel for if you’re on the right track.

In a perfect world, people would only select the rows and columns they need.

Also in a perfect world: that really embarrassing thing you did in 3rd grade wouldn’t pop into your head every time you’re about to do something really important.

Durex

What can make judging differences tough is if you’re returning a lot of rows to SSMS.

Sometimes it feels like you can reduce reads and CPU time, but your overall query time hasn’t changed.

Now with query operator times, that becomes easier to see.

And Earl

Let’s take this query, which returns ~271k rows.

SET STATISTICS TIME, IO ON;

SELECT c.Score, c.UserId, c.Text 
FROM dbo.Comments AS c
WHERE c.Score BETWEEN 5 AND 30
ORDER BY c.Score DESC

In the Stack Overflow 2013 database, this runs for about 3 wall clock seconds.

It says so in the bottom corner of SSMS.

Since we turned on stats time, we can look in the messages window to see that information.

Here are the relevant details:

 SQL Server Execution Times:
   CPU time = 3516 ms,  elapsed time = 3273 ms.

What looks odd here is that CPU and elapsed time are near-equal, but the plan shows parallelism.

Thankfully, with operator times, the actual plan helps us out.

The query itself ran for <900ms.

The situation isn’t so dire.

More Ales

In stats time, elapsed time measures until results are done getting to SSMS.

It might look like this query “ran” for ~3 seconds, but it didn’t. The query finished processing data in under a second, but it took another couple seconds for SSMS to render the results.

You can do a mock test by doing something like this:

DECLARE @blob_eater VARCHAR(8000);

SELECT @blob_eater = c.Score, 
       @blob_eater = c.UserId, 
	   @blob_eater = c.Text 
FROM dbo.Comments AS c
WHERE c.Score BETWEEN 5 AND 30
ORDER BY c.Score DESC

Now when we run the query, stats time is much closer to the operator finish time:

 SQL Server Execution Times:
   CPU time = 2954 ms,  elapsed time = 897 ms.

Thanks for reading!

Going Further

Why Not Just Go For The Big Plan To Improve SQL Server Query Performance?

Posted on July 8, 2019January 20, 2026 by Erik Darling

M’ocean

Video Summary

In this video, I delve into the intricacies of parameter sniffing in SQL Server and address a question posed by Bradley Jamrozik on Twitter regarding optimizing for large values to ensure always getting powerful execution plans. I explain why simply opting for a big value might not be the best approach due to resource constraints, particularly focusing on concurrency limits such as worker threads and memory grants. By examining these limitations through practical examples on my laptop’s hardware setup, I illustrate how different execution plans can impact the number of concurrent queries that SQL Server can handle efficiently.

Full Transcript

Howdy folks, Erik Darling here with Erik Darling Data. You should be used to that by now. If you’re not, I’m sorry. You just have trouble accepting change in your life. Change and then stability, I guess. Anyway, I’m recording this video because I got asked a, not good, but a great question not too long ago on Twitter by Bradley Jamrozik, or Rozeker, Rozeker. I don’t know how to pronounce that, Bradley. I apologize. You can correct me somewhere. I hear that correcting people on the internet is sometimes, sometimes happens. It’s a bit of a national pastime at this point. Anyway, the question was, when you’re dealing with a parameter sniffing situation, in a situation where SQL Server comes up with two or even more different execution plans based on which parameter it was compiled with the first time around, why not always just optimize for a big, why not just optimize for a big, crazy value so that you always get a big, powerful plan, probably parallel, probably ask for a decent chunk of memory, all that other stuff. Well, there are, I think, for me, some pretty fair reasons not to always do that. And those fair reasons come down to, of course, resources.

Now, if I look at the hardware that I have in my laptop, I have a processor in there with four cores that are hyper-threaded, unfortunately. I apologize to everyone out there who hates hyper-threading. And I also have 64 gigs of memory in my laptop, of which about 50 is dedicated to SQL Server. When I run these two queries, I can see how many worker threads I have available for SQL Server, which is 576. And I can see how much memory I have available to give out to queries. If I go down here and I zoom in a little bit, I can see that my total and my available memory are about the same. And this is how much memory in gigs I can give out to queries for memory grants.

Memory grants is, of course, memory that queries ask for outside of the usual. I have to run stuff to do other things like sort or hash or, you know, do some columnstore stuff that, excuse me, that consumes additional memory. And these are limits. When you start up SQL Server, depending on how many cores you have assigned to your server, and depending on how much memory you have in your server and your max server memory, SQL Server sets limits for how much it’ll allow itself to give out for different things.

Whenever a query runs, it has to take a little piece from those things. The more pieces that these queries are asking for, the fewer queries in total you can have running. For example, with a serial query, with 576 worker threads, I can run 576 copies of that query. If I have a query that goes parallel and it reserves more worker threads, well, whatever DOP is, is going to tell SQL Server how many parallel threads it can use in a branch.

And if I have multiple concurrent branches, SQL Server, just for example, on my laptop, I have max DOP set to 4. So if I have two concurrent branches, that’s 8 threads. And if I have three concurrent branches, that’s 12 threads. So the more parallel queries, the more parallel branches in those queries, the more threads they can just reserve and run with.

Ditto memory. If a query comes along and asks for a large memory grant, and SQL Server is able to grant the entire thing, well, if I have a query that asks for one gig of memory, and currently I can run just about 37 of them, or let’s just say 36 to be safe.

If I have a query that asks for 10 gigs of memory, I can run far fewer of them concurrently. So if we go over to this tab, and we look at a store procedure where I’ve recompiled before executing for two different parameters, 9 and 0, I get two different execution plans.

This top plan is a serial plan, and if I look at how much memory it asks for, it’s about 17 megs. 1, 7, 1, 1, 2. And if I look at how many threads it asks for, an F4 over here, it’s just one, because it’s a serial query.

This will come in handy in a minute. If I look at the second query, look at the select operator, this thing has asked for, let’s see, 7794944. That’s a seven-digit number.

So since it’s 779, I’m going to say that’s 7.8 gigs of memory, as opposed to 17 for the serial query. If you remember what’s on that other tab, about 37, I can’t run as many of these at once as I can of the other one at once. Far fewer, in fact.

If I look at how many threads this thing asks for, if I… Sorry, we’re going to have a dance party for a moment. Oh, alright.

You know, I’m always worried about playing music while a video, while I’m recording a video. But you know what? It’s alright with me. Anyway, if we look at how many threads this query reserved, we can see that we had one branch that was available to execute concurrently.

And we reserved four threads. So that’s not a ton. Granted, there are queries where you can have a lot more than this going on.

But for this query, in four threads, we can run far fewer of these copies concurrently than we can of the single-threaded version. So for me, when I think about why not just optimize for a big value, right? Why not just have every query run as forcefully as possible?

It’s a concurrency thing. And I know that when a lot of people think about concurrency, they think of locking and blocking and deadlocks and other things that kind of hold other queries up. But concurrency goes beyond that.

Concurrency also goes into, you know, from a resource perspective, right? So like not a logical resource like a lock, but a physical resource like how many threads you have or how much memory you have to give out to queries. These are hard limits.

The more queries you have that take up more of those resources, the fewer of those queries you can run. On a larger server, like on a big, big server, that might shut up windows. That might make less of a difference.

On a smaller server, say that’s maybe already a little bit underpowered for your workload, you might end up with a pretty bad situation. If you run out of worker threads, you hit a weight called thread pool. If you run out of memory to give out to queries, you hit a weight called resource semaphore.

So when asked why not just go with the big plan, well, it’s because of that. Because you have hard limits inside of your SQL Server for how much you can give out to queries. Of course, if you don’t care about concurrency, then the problem is solved for you.

Anyway, my name’s Erik Darling with Erik Darling Data. And thank you for watching. Bye.

Video Summary

Full Transcript

This will come in handy in a minute. If I look at the second query, look at the select operator, this thing has asked for, let’s see, 7794944. That’s a seven-digit number.

If I look at how many threads this thing asks for, if I… Sorry, we’re going to have a dance party for a moment. Oh, alright.

And we reserved four threads. So that’s not a ton. Granted, there are queries where you can have a lot more than this going on.

Anyway, my name’s Erik Darling with Erik Darling Data. And thank you for watching. Bye.

Going Further

Running Query Confusion In SQL Server

Posted on July 3, 2019May 14, 2022 by Erik Darling

Somewhat Solved

This bit of confusion is largely solved in SQL Server 2019 under compatibility level 150, when FROID (scalar udf inlining) kicks in.

But, you know, we’re a ways off from 2019 dropping, being adopted, and compat level 150 being the operating norm.

So here goes!

Functional Querying

I’ve got a scalar valued function. What it does is unimportant, but I’m calling it in a query like this:

SELECT u.DisplayName, 
       dbo.TotalScore(u.Id) AS TotalScore --<functione
FROM dbo.Users AS u
WHERE u.Reputation >= 200000
ORDER BY u.Id;

When I run this in SSMS, it’s obvious to us what’s going on.

But if I’m watching what’s happening on a server using sp_WhoIsActive, what’s going on might not be obvious.

I’m doing all this with just my query running to show how confusing things can get.

First Confusion: Query Text

This doesn’t look at all like the text of our query. We can guess that it’s the function running in the select list since we know what we’re doing, but, you know…

We can bring some clarity by running sp_WhoIsActive like this:

sp_WhoIsActive @get_plans = 1, 
               @get_outer_command = 1;

The outer command parameter will show us the query calling the function, which’ll look more familiar.

Second Confusion: Phantom Parallelism

We’re hitting more of those harmless, silly little CXCONSUMER waits.

But how? Our query plan is serial!

This part is a little less obvious, but if we get an estimated plan for our query, or track down the query plan for the function, it becomes more obvious.

The query plan for the function is parallel — a cute ~nuance~ about scalar udfs is that they only prevent the query calling them from going parallel.

The function itself can go parallel. So that’s… nice.

I guess.

They Walked Inlined

In compat level 150, things are more clear.

The inner and outer text are the same. There’s more of that CXCONSUMER, though. Hoowee.

But at least now we have a query plan that matches the parallel waits, right?

In the next post, we’re gonna talk more about those wait stats, though.

Thanks for reading!