If you recall yesterday’s post, we added a couple two column indexes to the Posts table.
Each one helped a slightly different query, but either index would likely be “good enough”.
This post will focus on another common scenario I see, where people added many single column indexes over the years.
In this scenario, performance is much more variable.
Singletonary
Here are our indexes:
CREATE INDEX ix_spaces
ON dbo.Posts(ParentId);
CREATE INDEX ix_tabs
ON dbo.Posts(Score);
Taking the same queries from yesterday:
SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.ParentId < 1
AND p.Score > 19000
AND 1 = (SELECT 1);
SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.ParentId > 21100000
AND p.Score < 1
AND 1 = (SELECT 1);
This is what the new plans look like:
Curious
The first thing you may notice is that the top plan performs a rather traditional key lookup, and the bottom plan performs a slightly more exotic index intersection.
Both concepts are similar. Since clustered index key columns are present in nonclustered indexes, they can be used to either join a nonclustered index to the clustered index on a table, or to join two nonclustered indexes together.
It’s a nice trick, and this post definitely isn’t to say that either is bad. Index intersection just happens to be worse here.
Wait, But…
You may have noticed that both queries get pretty bad estimates. You might even be thinking about leaving me a comment to update stats.
The thing is that I created these indexes, which means they get stats built with a full scan, and it’s a demo database where nothing changes.
We just get unfortunate histograms, in this case. If I create very specific filtered statistics, both plans perform a key lookup.
CREATE STATISTICS s_orta ON dbo.Posts(ParentId) WHERE ParentId > 21100000 WITH FULLSCAN;
CREATE STATISTICS s_omewhat ON dbo.Posts(Score) WHERE Score < 1 WITH FULLSCAN;
CREATE STATISTICS s_emi ON dbo.Posts(ParentId) WHERE ParentId < 1 WITH FULLSCAN;
CREATE STATISTICS s_lightly ON dbo.Posts(Score) WHERE Score > 19000 WITH FULLSCAN;
This is necessary with the legacy cardinality estimator, too. Rain, sleet, shine.
Bad estimates happen.
When your tables are large enough, those 200 (plus one for NULLs, I know, I know) steps often can’t do the data justice.
Filtered stats and indexes can help with that.
Something I try to teach people is that SQL Server can use whatever statistics or methods it wants for cardinality estimation, even if they’re not directly related to the indexes that it uses to access data.
With filtered statistics, things go fine for both plans:
Sunshine
When Could This Cause Trouble?
Obviously, plans like this are quite sensitive to parameter sniffing. Imagine a scenario where a “bad” plan got cached.
Multihint
Having one instance of this query running doesn’t cause much of a CPU uptick, but if user concurrency is high then you’d notice it pretty quickly.
Parallel plans, by definition, use a lot more CPU, and more worker threads. These both reserve and use 8 threads.
Stretch
Those two plans aren’t even the worst possible case from a duration perspective. Check out these:
5-4-3-2-1
Doubledown
When talking index design, single column indexes are rarely a good idea.
Sometimes I’ll see entire tables with an index on every column, and just that column.
That can lead to some very confusing query plans, and some very poor performance.
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.
When I’m working with clients, people who don’t spend a lot of time working with indexes have a lot of questions about indexes.
The general rule about leading column selectivity is an easy enough guideline to follow, but what happens if you’re not looking for equality predicates?
What if you’re looking for ranges, and those ranges might sometimes be selective, and other times not?
LET’S FIND OUT!
Chicken and Broccoli
Let’s take these queries against the Posts table. The number next to each indicates the number of rows that match the predicate.
SELECT COUNT_BIG(*) AS records /*6050820*/
FROM dbo.Posts AS p
WHERE p.ParentId < 1
AND 1 = (SELECT 1);
SELECT COUNT_BIG(*) AS records /*3*/
FROM dbo.Posts AS p
WHERE p.Score > 19000
AND 1 = (SELECT 1);
SELECT COUNT_BIG(*) AS records /*23*/
FROM dbo.Posts AS p
WHERE p.ParentId > 21100000
AND 1 = (SELECT 1);
SELECT COUNT_BIG(*) AS records /*6204153*/
FROM dbo.Posts AS p
WHERE p.Score < 1
AND 1 = (SELECT 1);
In other words, sometimes they’re selective, and sometimes they’re not.
If we run these without any indexes, SQL Server will ask for single column indexes on ParentId and Score.
But our queries don’t look like that. They look like this (sometimes):
SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.ParentId < 1
AND p.Score > 19000
AND 1 = (SELECT 1);
SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.ParentId > 21100000
AND p.Score < 1
AND 1 = (SELECT 1);
When we run that, SQL Server asks for… the… same index.
Huhhhhh
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);
Steak and Eggs
When we run those two queries again, each will use a different index.
If we force those queries to use the opposite index, we can see why SQL Server made the right choice:
SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p WITH (INDEX = ix_spaces)
WHERE p.ParentId < 1
AND p.Score > 19000
AND 1 = (SELECT 1);
SELECT COUNT_BIG(*) AS records
FROM dbo.Posts AS p WITH (INDEX = ix_tabs)
WHERE p.ParentId > 21100000
AND p.Score < 1
AND 1 = (SELECT 1);
Having two indexes like that may not always be the best idea.
To make matters worse, you probably have things going on that make answers less obvious, like actually selecting columns instead of just getting a count.
This is where it pays to look at your indexes over time to see how they’re used, or knowing which query is most important.
There isn’t that much of a difference in time or resources here, after all.
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.
In this video, I dive into the challenges of working with large and complex SQL queries, particularly those that are overly nested or contain numerous CTEs (Common Table Expressions). I share a specific example where I had to optimize a query that was running for about 30 seconds. By breaking down the query into smaller, more manageable parts and using temporary tables, we were able to significantly reduce execution time—from around 15 seconds to just under two seconds. This process not only improved performance but also made it easier to analyze and tune each component of the query independently. The video emphasizes the importance of avoiding overly complex queries and suggests that breaking down large queries into smaller, logically separated parts can lead to more efficient and maintainable code.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data. Still, apparently, I guess. I guess we’re cool for the summer. And I realized that I had been writing and working with and talking a lot, and I even have a couple blog posts scheduled about how much I hate people who write big queries because they’re very misguided. People think, a lot of people who I’ve talked to, are like, well, you know, I have a CTEof this view or this drive table and SQL Server should cache that result and then do something else. And that’s just not what happens. And kind of like the more you just string together these, these little constructs, or nest them as deeply as your heart desires, or, you know, keep sort of tagging joins onto things. You know, it, it, it just doesn’t, just doesn’t scale well. I’ll say that. At some point, it makes sense both from a performance perspective and from an understandability and tunability perspective to break your query up at sort of logical stopping points to just not keep adding things on to your query to give you the results you want or give you some new piece of information. It’s not a good idea.
So I have this, this query in particular that I wrote from scratch. And while I was writing it and running it and everything, SQL Server came up with a number of missing index suggestions for me. It actually came up with six. And I’ve thoughtfully labeled them missing index one through six down here. And I’ve added all these already. I’m not going to sit here and make you watch me add these indexes. But they’re all stuff that SQL Server was like, hey, if you add this, it’ll make the query faster. And to some extent, they did. But this query still runs for about 30 seconds. So we have this question post thing up here that basically finds questions with some filters on them to make sure they have like a positive score and they’ve accepted an answer and they haven’t been closed or community owned. And sort of the same deal with answers down here. I just make sure they have a positive score and that they’re not community owned. And then what we do is join those two tables together on the parent ID of the answer equaling the ID of the question to make sure that we don’t have someone who’s self answered. Right? So that’s the only point of that.
And then we go and hit the votes table to see if there are any bounties assigned to those questions or answers. And then we come out of there and we do some sort of annoying, complicated stuff to join back to the users table, get some information about comment scores. But this is, you know, I know it’s not fun looking at code like this. You don’t have to understand it all. You just have to understand that this is the kind of code that I see when I work with people that I end up doing this exact same process that I’m going to show you. So let’s look at the query plan real quick. This inserts 2,000, 29,380 rows and it runs for 30 seconds right there. We zoom in on this. That’s 30 seconds of wall clock time that we spend running this query.
If we zoom out and we look at the query plan, zoom to fit, I want nothing to do with this. I will not sit there and try to troubleshoot this plan as it is. That is completely misguided. It’s not a way to, not a good way to do anything. What I do when I see a query like that is I think, well, that query is too big for me and it’s probably too big for the optimizer too. I bet the optimizer is not having a very good time with that. So what I do mentally is I start looking for breaking points in the query and scroll down a little bit. Let’s say we can take these first two CTE, right? These first two, the answer post thing and the question post thing.
And let’s just control, let’s highlight the right part first and hit control L and SQL Server thinks that about 184,000 rows are going to come out of there and that about 773,000 rows are going to come out of there. And if we go the whole nine with that and we look, SQL Server thinks that about 8,000 rows are going to come out of that. That might be a good guess. It might not be. We don’t, we’re only looking at the estimated plan, so we can’t figure that out. So if we wanted to, if we wanted to do something smart with this, we could take the results of that initial set of joins and just dump those into a table, ensuring that there is no collusion with, no self-collusion on here.
Whenever there’s self-collusion, I get worried that there are other perversions afoot. So let’s run this query. Let’s get query plans turned on. Let’s run this and see what happens. Okay. That takes about, let’s go to the execution plan, about three seconds.
Okay. That’s fair. Three seconds is totally fair because we’re, we’re hitting not, if we look at the, the estimates and the actuals over here, zoom in on that. Wait, no, sorry. Zoom in on, zoom in on this one. Here we go. SQL Server thought that 8,300 rows are going to come out of there, but we ended up with 2.05 million rows.
So let me ask you a question right from the get-go. If SQL Server is making a bad guess by that much here, how much do you think that bad guess hurts us downstream? How much do you think SQL Server being off by, oh, 2 million rows is hurting the other query?
I would guess a pretty good amount. I would also guess that it would be really, really tough to track down that poor estimate if you were just staring at that great big lump of query plan. Staring at this much smaller lump of query plan, it’s a lot easier to figure these things out.
I’m not going to dig in on why this takes two, three seconds and we get the estimate wrong by that much. I’m just going to say, okay, we have, we have that set of data in a temp table. This is a pretty self-contained issue.
We can come back to this later. All right. So now let’s look at bounties, right? So this is the second, well, this is technically the fourth CTE in there. We had the first two that joined to each other, right, and that third CT. So this is number four.
This is bounties. So let’s run this and let’s see how long bounties takes. Let’s see how we do with bounties. Bounties sure is dragging on for a long time, isn’t it? Bounties is, oh boy, bounties is chugging. Okay.
So that took a while, right? Let’s look at the query plan. And this is just about 16 seconds. Yowza. 15.7 seconds. What are we going to do about that? Well, this is a really strange looking execution plan to me. If it’s not strange looking to you, I don’t know.
Perhaps you’re from another planet. But we have these, if we look over here, let’s start with the right because everyone tells you to read from right to left. So let’s start with the right. We have these constant scans. And they concatenate together. And they compute a scalar apparently. And I think it’s very funny that we go from these thick arrows to these thin arrows to this thick arrow to this thin arrow to this thick arrow.
It’s just like thick, thin, thick, thick, thick, thick, thick, thick, thick, thick. A lot of thicks. A lot of switches from thick to thin in there. It’s worse than me. I’m balloon, yo-yo dieting. But where these constant scans come from is a little tough to track down, especially in older versions of SSMS, where you don’t have these row counts automatically by operators. But if you look at this arrow here, we have that 2.05 million number, right? And if we go look here, and that’s the same number. We look here, that’s the same number. So these two constant scans are actually emitted from here. And what they’re doing is they’re trying to make sense of this join on an or clause. Sometimes you’ll get that constant scan thing. Other times you’ll get a table spool. Depends on how SQL Server is feeling that day. But it does something really goofy.
It takes these 2 million rows and these 2 million rows, and it turns them into 410 million rows. And then it sorts 410 million rows, right? Oh, it’s top end sort, right? 410 million rows. Attempts to merge them together, but doesn’t because we still get 410 million rows on the other side.
So this entire thing was an exercise in futility. Then we seek into this index 410 million times. Okay. I’m starting to understand why this thing is slow. And if you’ve read my blog at any point in the past, I don’t know, week or so, week and a half, you’ve probably seen that I hate joins with or clauses.
So let’s not do this. Let’s get rid of this. Let’s do this differently. So what I’m going to show you is what happens when we just union all those two things together. Right? So exact same query, just we have one join here and one join here, and I union all them together. Right? Because if I use union, SQL Server is going to try to make a distinct result set. And if I try to make a distinct result set, that can slow things down.
So if we look at this execution plan, we are down to about two seconds just by splitting up that join with the or clause in it. Right? Did two separate queries, yet somehow it was way, way faster. Right? It was like 15 seconds down to 1.9 seconds. I’m pretty cool with that. So since I’m so cool with that, what I’m going to say is we’re going to take that and dump it into a temp table on its own.
All right? And that will still be pretty quick. That should be about two seconds. Yeah, 1.8 seconds there. Not bad. Not too shabby. Right? And so now all we have to do is look at how this last part of the query performs with those temp tables rather than with all those CTE chained together.
And this finishes very quickly too. This finishes in about 2.2, well, 2.3 seconds. Right? So that finishes quick. So now we have three queries essentially. Right? We have that first CTEthat we stick in a temp table. The second CTEthat we stick in a temp table.
And now this third query that just hits that last temp table. So we’ve broken the query down into three steps. And the sum of those three steps is much, much shorter than all of those steps put together into one big chained together CTEquery. All right? So now because I want to show you something cool, what I’m going to do is take all of these.
I’m going to take all of that and I’m going to do it all in one go. I’m going to drop out those temp tables that I created before. And I’m going to do the exact same thing. So have this here.
And that’s going to stick into a temp table. And then this is going to go and stick into a temp table. And then this is going to go and run from the final temp table. And then we’re just going to check to make sure that we have the same number of rows going. Now this should take, if I turn the query plans off, it takes around about five seconds. With the query plans turned on, it’s a little bit longer.
All right. Eight seconds. I’ll live with that. But now what we have, and I think this is really the whole point of this exercise, what we have is three queries that we can try to figure out that are all much, much smaller in size. We have, you know, I don’t know.
We already added six indexes for this thing. There’s another missing index request there and another one there and another one there. Maybe they’d help. Maybe they wouldn’t. I don’t know. But we have three distinct queries now that we can work with. All right. We have, you know, we can investigate why each one of these is slow individually. And that’s much, much less difficult than it is to figure out why those chained together CTE and other nonsense are all performing poorly.
So we could go further from here. We could keep going. We could keep trying to tune things further. But I’m pretty happy going from roundabout 30 seconds, roundabout.
Well, I mean, that was eight seconds with the query plans. Oops. I have to get rid of those temp tables first. So if we turn query plans off and look at this, it should be a little bit quicker. But, you know, it’s nice to be able to test things in different ways and get your results back and everything.
But anyway, we went from, oh, it took seven seconds that time. Good for us. Anyway. So anyway, the moral of the story is when you get called in to tune a very, very big query like that, generally speaking, it’s not a good idea to try to consume that query as a whole.
The first steps I always take are just like what we saw here. I start looking at individual parts of the query, individual statements, how they tie together. And I start breaking them up into logical points like that, where I’ll stick one set of data into a temp table, work off that set of data. Just because, you know, we could totally try indexing temp tables and other stuff.
But I just find that, you know, materializing results, which CTE, derived tables, views don’t do, materializing results and giving SQL Server a known quantity to work off of, to build stats on, to, you know, figure out relationships with, is usually much, much better than asking it to do that over just the results of a whole bunch of queries, kind of tacked and hammered and duct taped and glued and, I don’t know, bound and gagged.
Oh, it’s getting, oh, it’s getting, sorry, I didn’t mean to bring my personal life into this. But it’s like kind of like lumping them all together into one sort of crazy mess. So anyway, that’s sort of the moral of this story.
Stop writing big queries. Unless you really know what you’re doing. If your last name is Mechanic or White, not Ozar, if your last name is Obish, yeah, then you can write big queries. Otherwise, I don’t want to hear about it.
Anyway, I hope you enjoyed watching this. I hope you learned something. I hope you take what you learned here and start fixing some problems with it. And I don’t know. I will apparently see you in the next video. Goodbye.
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.
I get called in to tune a lot of pretty crazy queries. Hands down, the most common scenario is that at some point someone decided to abstract away some of the logic.
Sometimes it’s views, CTEs, or derived tables. Sometimes it’s functions. obviously functions can have a weirder set of effects, but the general idea is the same.
If you start chaining things, or nesting them together, you’re making the optimizer’s job harder and likely introducing a lot of overhead.
Deep Thinkers
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.
Scustin’
With functions, I mean, one is generally bad enough for a demo. When you start nesting them, introducing loops or recursion, or even mixing scalar and multi-statement functions, things get way worse.
Depending on where the compute scalar that handles the function is placed in the query plan, it can end up “only” running once per row returned by the query.
This is true of scalar valued functions, and MSTVFs that are cross applied. MSTVFs that are simply joined may not exhibit this behavior, though inner joins may be optimized as lateral (apply) joins under different circumstances. So uh. Yeah. Keep fighting that fight.
Is, shockingly, still relevant today. A question I’ve started asking people is something along the lines of “when you’re writing a query, or trying to figure out why a query’s slow, do you ever search around for articles about SQL Server performance?”
The answer usually isn’t “yes”. A lot of the problem is that people don’t know what to search for.
They use <some other programming language> and functions are just fine.
Why would functions be bad in a database?
As another example, if you remove a bunch of elements from an array, you have an array without those elements.
When you filter a bunch of rows out of a query with a CTE (or whatever), you don’t have a copy of the table without those rows in it.
Terminus
A lot of people have been trying to get this information in front of as many people as possible for a long time.
I used to think it was just a matter of blogging, presenting, or recording more videos to get people to stop making the same mistakes.
Now I think it’s mostly a case of “I want someone else to do this for me”, and all those things are your street cred.
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.
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;
Gas Pedal
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:
Killing Time
To compare the “fast” spills, here’s yesterday and today’s warnings.
More Pages, More Problems
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.
5-7-9
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.
Gigs To Spare?
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:
Billy Budd
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.
It’s like what all those query tuners keep telling you.
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
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.
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.
Completely unfounded
Not sure where to look? Here’s a close up.
Grounded
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.
Still got it.
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.
Z to the Ero
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:
Girlfriend In A Cartoon
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.)
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
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.
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.
Phoney
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;
Aliveness
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.
Bad Bed
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;
Wrong One
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
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.
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.
Big hitter
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.
Unlucky
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;
Why send me silly notes?
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.
That’s a lot of writing.
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).
In the next couple posts, we’ll look at different ways to index for queries like this.
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.
I get why these things happen. You’re the <new person> somewhere, and someone asks for you to add something to a report, or something
You look at the original query, and it’s like 1000 lines long.
There’s dozens of joins, and a half-mile where clause full of ands and ors.
There’s no way you’re messing with that. You just tack your left join on and walk away.
Fine.
Everything’s Eventual
Don’t get me wrong. Though some combination of skill, luck, hardware, or size of data, this might work for a while.
SQL Server might even help you out with a parallel plan. They’re sort of the Great Equalizer™ for performance.
Optimizer thinks this is gonna be a doozy? Have some more CPU!
Be my guest. They’re free, right?
Eventually, though, this will get slower and slower.
This is usually about the time someone gives me a call.
Chewy and Chompy
See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.
This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.
Remember, cheap plan fast. Not perfect, not great, maybe good enough.
Cheap and fast.
Even worse, the bigger a query plan is, the less likely it is to be helpful to analyze.
Costs get so spread out, it’s hard to focus on what might make a difference.
Hatchet Act
When I have to tune a query like this, there’s some stuff I’ll try out first to get a feel for what’s going on, but ultimately your best friend is breaking things up.
The optimizer is just like you and me. The more chances and choices we have, the more likely we are to screw one up.
Really big queries usually have some logical stopping points, that you might wanna try materializing by sticking them in a #temp table.
CTEs
Derived tables
Subqueries
UNION/UNION ALL
Initial Inner Joins
The last point there might be a little unclear. I mean that usually your query starts off with some inner joins, then people start tacking left joins on.
If you grab the most restrictive stuff first, that’s sometimes a good starting place.
But really, all of those things are valid. It’s easier to tune a bunch of small queries than one big query.
The Hounds Of Hinterville
This is also where I’m a big fan of hints — not because I want them to stay, but because I wanna see how the plan changes.
Join and aggregate hints, recompile, trying to force a parallel plan, FAST 1, etc. are all valid experiments to see if there’s something the optimizer isn’t figuring out on its own.
Figuring out why is harder, but hey, the only way to get good at that is to keep tuning.
Hints are great to learn from, and sometimes the only way to get the plan you want.
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.
One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have.
While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert to put some data in there.
No matter how big, bad, ugly, or costly your insert statement is, SQL Server can’t parallelize it.
Dimmo
Here’s our select statement.
SELECT DISTINCT
u.Id
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND c.UserId = u.Id
WHERE c.Score >= 5;
This goes parallel and runs for about 3 seconds.
Now my heart is full~
But if we try to insert that into a @table variable, the plan will no longer go parallel, and will run for ~6 seconds.
DECLARE @icko TABLE (id INT);
INSERT @icko ( id )
SELECT DISTINCT
u.Id
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND c.UserId = u.Id
WHERE c.Score >= 5;
Boy Racer
If we hit F4 to get the properties of the INSERT, well…
Chocolate Nonpareils Reason?
Let’s Say For Some Crazy Reason
You need to keep using a table variable.
Let’s say, I dunno, the crappy 1 row estimate gets you a better plan.
Or like… I dunno. Temp tables recompile too much.
I’m reaching, I know. But hey, that’s what consultants do. Have you read any blog posts lately?
If we change our insert to this, we get parallelism back:
DECLARE @icko TABLE (id INT);
INSERT @icko ( id )
EXEC(N'SELECT DISTINCT
u.Id
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND c.UserId = u.Id
WHERE c.Score >= 5;')
Glamorous Glue
The dynamic SQL executes in a separate context, and the insert happens in… idk some magickal place.
But THAT’S PRETTY COOL, HUH?
This would also work if we put the query inside a stored procedure, or stuck the statement inside a variable and used sp_executesql.
In either case, the INSERT…EXEC pattern is your friend here.
Of course, you could just use a #temp table.
Sigh.
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.
