SQL Server Consulting, Education, and Training
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 delve into a fascinating SQL Server query optimization scenario where we encountered an “eager index spool” in the execution plan—a notorious performance bottleneck. To tackle it, I explored alternative ways to structure our query and improved statistics on key columns to refine aggregate estimates. By doing so, we managed to reduce the query’s runtime from several minutes down to just seven seconds, showcasing the power of better data estimation and query reordering. This video is part of a series focusing on optimizing complex queries with aggregates, and I hope you find it helpful as you navigate your own SQL challenges!
I ain’t got nobody. Just kidding. Just avoiding going out and doing things. Sometimes David Lee Roth karaoke is the best way to do that. Just kidding. It’s recording SQL Server videos on Saturday. Ah, so in the last video we talked about aggregates with bad guesses and I want to show you another aggregate with a bad guess. This one coming to you live and direct from a client inspired event where they had this big crazy query and at the heart of this query was this two counts where we had to filter on stuff. Um, but I, my, my idea, my big idea was we are going to do one count and filter on that rather than two separate counts and filter on those because that’s, we could, we could do that. We could, we could use math to our advantage. Who would use math in a database? Insanity. Certainly not the optimizer. Just kidding. The optimizer is lovely.
Uh, so the, the, the, the query that we were working on was much, much bigger, but this was sort of at the heart of it. And, uh, so let’s say we have, we had an index on the comments table on the post ID column. Um, and I wanted to do my count in here like this. And when I wrote this rewrote this query, um, what happened was, uh, not good. Uh, I ended up with a query plan that looked a bit like this. Yeah. You can see what’s going on here. You can see where my least favorite thing to ever see in a query plan popped up. That is the old eager index spool. Yeah. I hate this thing. I hate this thing for so many reasons.
And you can see why I hate it here. We spent nearly three minutes building this index. Uh, and the reason why it takes so long is because it’s, it says that it’s happening in parallel. But if we go into the properties and we look over here, we can see that, oh boy, all just about 53 million rows from the votes table end up on a single thread to build that index. And this will happen each and every time. We don’t have a missing index request either here and the XML and the missing index DMVs. We guts nothing.
So even though there isn’t physically a lot of distance between here and here, time wise, there is a very large amount of distance between here and here. Uh, the query in full runs for about two minutes and 56 seconds. So we can figure out pretty well that, guess what? We spent most of the time building that stupid spool. So let’s figure out what happened, how we can fix it, what went wrong, what was going on.
Now, the thing is, um, I was confounded by this. And often when I am confounded by things, my first reaction is to do something stupid. So I did what I normally do, which is something stupid. And I just flipped the order that we were union all-ing things from. So I went from up here where comments was on top and votes was on the bottom to votes on top and comments on the bottom.
And when I did that and ran the query, it was a little bit faster. And, um, it w it was faster because the execution plan changed and we no longer had the index spool. And the query was overall faster, but I was still, I was still annoyed with a few things in the query. Uh, so when we look at the plan, we’ll start to sort of see why. All right. We can, we can, we can see some more rather large percentages in here, where perhaps maybe, uh, we don’t need, there shouldn’t be larger percentages in here.
Uh, we’ll start with this one where, uh, the optimizer thought that it could squish our results down by a significant amount, right? From 5.6 million, uh, to, or rather from 17 million to, uh, 141,000, right? The 17 million that came out of here. So it, it, it, it, it’s guess was we could get down to this number, but we can only get down to this number.
So we, we stopped early. Uh, and then of course with along with that bad guess, we get a bad guess here and a bad guess is over here. Uh, some bad guess is over here. Now you’re probably wondering why we have such bad guesses here. And I’m going to show you. So, uh, in a normal batch, uh, sorry, in a normal role mode plan where we had a hash join, we may, in a parallel role mode plan where we have a hash join, we may see a bitmap get created. In batch mode plans, we don’t see that anymore.
But what we will see is in the properties of the hash match operator, we will see bitmap creator true. And we can see that it created optimized bitmap 1085. Very special bitmap. Just kidding. It’s just optimized bitmap 1085. There’s really nothing all that special about it at all.
And if we look down here where we, where, where that bitmaps hopefully get applied, we can see in the, the clustered index scan of votes that, uh, optimized bitmap 1085 was pushed over here. And if we look at the scan of comments that optimized bitmap 1085 was in fact pushed down here. So this bitmap, we, we made some guesses based on this bitmap. And those guesses turned out to be very, very, very, very, very, very wrong.
Mm-hmm. Yes. I mean, we were still, we still did a lot better than, uh, what was it? 30, uh, three minutes, but still not fast enough. Eric, you’re a query tuner. You need to make things faster.
So again, um, let’s just try to improve the estimate here. Let’s not even, let’s not create another index. Um, let’s just, we need to make a better guess. We need to make a better guess on the post table, on the parent ID column, because that’s what we were trying to squish here. Parent ID.
If we make, we have better statistics on what’s going on in parent ID, then we can make better choices elsewhere in the plan. So let’s create those with a sample of 25%. Again, this isn’t, this isn’t even pushing the bound. 25% is like nothing. It’s not a whole lot. It finishes in about four seconds.
It’s something that, you know, even people with the most demanding maintenance windows can get away with. And now when we rerun that query with those statistics in place, things will improve a little bit more. At least they, they, they do without Camtasia running. So we’ll find out what happens with Camtasia running.
So seven seconds. So we cut the time about in half again. And we can see that we started making quite reasonable guesses in many places. I mean, not like, like fully reasonable guesses, but you know, much better guesses, I think.
Like not like thousands of times wrong guesses. And I don’t know, things, things look better to me here. Don’t they look better to you? They look much better to me. I like it.
So just by improving the estimate over on the post table on that parent ID column, we were able to make better guesses about like what, what rows are going to come out of things. Oh, we got optimized bitmap 1077 that time.
A wonderful vintage of that bitmap. Wonderful vintage. Yeah. You know, we were able to improve some guesses. We were able to get faster queries and I think better query plans.
And sure. Like, you know, just like in the other video, we could go the next step and we could add, we could add indexes to tune this further. But I think getting a query down from several minutes to seven seconds is a pretty good start. So we’re going to pause there and actually we’re going to, we’re going to stop there completely.
And we are going to go record another video. Oh, but I wanted to, wanted to say with those statistics in place, we even get a good plan with things in the original order. So we, we still get the plan there. We didn’t even have to change the order anymore.
So lucky for us, isn’t that, isn’t that lucky for us? We can write this in any order we want now and things, things don’t get wacky. Ah, that’s amazing. Anyway.
Uh, thanks for watching and I will see you in the third and final installment of the Angry Aggregates. All right. Goodbye. All right.
Bye.
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.
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.
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 wish I had an answer here, because sometimes when I think about these things it feels a bit silly.
Maybe someday.
This is the post I wrote over at Brent’s site that I reference in the video.
In this video, I delve into the intriguing behavior of SQL Server indexes when dealing with null values, specifically focusing on why a perfectly good index might be scanned rather than sought using `IS NULL` conditions. I walk through various examples, from scenarios where no indexes are present to cases involving an indexed column that is always null, exploring how these situations impact query plans and the optimizer’s decision-making process. Through these demonstrations, I aim to spark discussion on why SQL Server struggles with determining if a column filled entirely with nulls can be efficiently sought using `IS NULL`, even when the index data is known to contain only null values.
Erik Darling here with Erik Darling Data. That’s cool, isn’t it? I’m hoping that second time’s a charm. The first time I recorded this video, I had the screen recorder positioned in the wrong place, so I missed like three-ish quarters of what I meant to record. So hopefully this time, everything is coming up lucky for me. And I wanted to ask this question, because I think it’s a question that will lead to a lot of other questions getting answered. And I think it’s an interesting question, specifically for databases, because of the way that indexes store data. And that question is, why, if we know the order of a column, does using something like isNull on that column result in a perfectly good index being scanned? I’m going to give you some examples. So let’s start off with nothing, right? We have no indexes on this table. I have query plans turned on. And if I run this query, I get two results back, and they are not going to be scanned.
So they are both the same result. 2465713. That is as phony as 50% of the phone numbers that I have gotten in my life. And when I look at the execution plan for these two phony phone numbers, I will see that they did roughly the exact same thing. The top query scan the top query scan the entire clustered index, read all 2465713 rows, and did not ask for an index because I wouldn’t expect it to because there’s no where clause. What would you index to improve this? There’s nothing. But the second query, where I say select count from the user’s table where age is null, I get a missing index request because there is a where clause where a SQL Server could filter data where it is null.
Not very interesting there at all. Where things start to get interesting to me is when I run queries like this. So let’s say that knowing that the entire age column is null, and knowing that nothing has changed in the database, there are no modifications to that column, like what would have gotten changed in the last two seconds?
I’m but a lowly consultant with just a simple demo database working for Erik Darling data. There we go. There we go. There’s that slow-mo bounce that I was looking for. If I run these two queries, I mean, I’m not going to get a missing index request.
Like SQL services like, index on age? Nah. Could never help. What would we do with that? How would we know? How would we know if it was null? We would never know. It’s crazy. It’s crazy to think about.
Even though in the first query we were just replacing a column that’s all null with a null, and in the second query we were replacing a column that’s all null with an empty string, you should be able to figure this out. SQL Server, you cost $7,000 a core.
For a standard edition, we can’t even get you to take more than 128 gigs of memory for the buffer pool. But you can’t figure out if a column that’s all null is null. This is what we’re stuck with.
I mean, if you budged on the memory thing, we might forgive you for this, but… Mamacito, what’s going on? This all gets even more interesting when we actually add an index that could be helpful.
So let’s add an index on this age column, which is always null. Now, it wasn’t always null, but I have it on the authority of the most senior DBA at Stack Overflow. That they recent…
Though, not recently, actually. They broke all my demos like two years ago. They started nullifying this column because of GDPR. So if you want to re-break my demos, I don’t know, repeal GDPR, do something like that.
So this is where things start to get a little bit more interesting for me, is now that we have an index on age just by itself, if we run a count against the age column where age is null, and we look at the query results and the query plan, we now have an index seek.
And this is an important question for all you people out there who get very hung up on if indexes have seeked or scanned to find their data. Is it really a seek if we read the entire index?
Hmm. Hmm. No. I hate to spoil this.
No, it’s not. Likewise, if we run this query where we say select count from users where is null age replaced with a null is null, all of a sudden, I mean, we no longer seek.
We now scan to read the entire index, but… Who cares? I think what’s interesting here is that even though we know that that entire index comprises null values, we do not…
We are not able to seek to any portion of it that might be null. Now, if this were a column that had some null values and some not null values, we create the index on it.
The index data would be sorted by nulls, and then whatever comes sequentially next. If it’s a number column, it could be negative numbers, and then positive numbers.
If it’s a string column, it could be numbers and then letters. It doesn’t matter too much. We just know that nulls come first. There should be no real pathological forensic reason for nulls to come last.
Even if we had the index definition as sort of descending, like what’s the worst that happens? We replace nulls at the end rather than nulls at the beginning.
Now, we have covered examples over several iterations of these queries where we have replaced the null in the column, in the indexed column, with a literal value, either a null or another number, or I think that’s all I’ve done.
But in all of these cases, we have had to scan. Now, I’m less forgiving for these cases because these cases are manure to me. The optimizer is very smart.
It’s been worked on by doctors for 30 years. If I had doctors working on me for 30 years and I still looked like this, I would get my money back. The optimizer has had doctors working on it for 30 years, and it still can’t figure out where nulls fall in an index.
That’s a little weird to me. Now, where I am forgiving, now where I understand why this might cause some weirdness, is if, let’s say, we said is null, a column, and then another column.
I’m partially forgiving of this. And I say I’m only partially forgiving of this because under normal circumstances, if you run is null against a column that does not allow nulls, it will skip the is null check, which is a departure from the coalesce function, which is internally a case expression, which will check that column regardless.
I blogged about this a while back on Brent’s site. If you’re really lucky, I’ll put a link to it in the description of this video. But that is absolutely true. If you have a not nullable column and you say is null not nullable column equals something, SQL Server will say I don’t need to run an is null check on this because I know that this column is not nullable.
Coalesce, it will still run the entire internal case expression. So if you’re ever looking for a reason to use is null over coalesce, there you have it. So where I am more forgiving is, let’s take an example where we select a count from users where we say is null age, even though we know all of age is null.
And then we compare it, maybe replace those null values for the count ID, which is at least a nullable column. Well, okay, fine.
If they’re not in an index together and they’re not sorted together, I totally understand why this would be confusing. But in the case where we take a column like age and replace it with the primary key clustered index, which you know is not nullable, but this could go for any column that is not nullable.
It doesn’t have to be this. I’m a little bit less understanding. Why would you be so confused over what’s null or not? So I think that a pretty fair thing to ask for would be if we have is null wrapped around a column that is indexed and we have that is null expression replacing the nullable column with a literal value, that literal value should be applied to the constant folding portion of index optimization.
We should take that literal value and we should be able to compare that pretty easily to another literal value. I don’t expect this to go for where is null age, some other value equals another column, or is null age, some other value equals, what was I saying?
No, is null age, another column equals something, but I do expect that like if we have an index on age, we should be able to at least see to that, like bare minimum, bare minimum, because we know, we know what’s going on in there.
We have that data sorted the way we want it. Anyway, I think I can hear my wife yelling at me through the door, so I’m going to get going and I’m going to go watch a television program.
I’m going to finish my tea while I watch a television program. But thank you for watching. I hope that I get to see you live and in person at an event so that I can give you a cool sticker.
Look at the rainbows on that thing. You are not having a stroke. You are seeing cool rainbows. So anyway, I hope to see you so I can give you a sticker.
I hope that you enjoyed this video. I hope that you at least thought about something, especially if you work at Microsoft. I hope you thought about something. And I will see you in another video, another time, another place.
Farewell.
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.
You already know that your temp table needs an index. Let’s say there’s some query plan ouchie from not adding one. You’ve already realized that you should probably use a clustered index rather than a nonclustered index. Adding a nonclustered index leaves you with a heap and an index, and there are a lot of times when nonclustered indexes won’t be used because they don’t cover the query columns enough.
Good. We’ve fixed you.
But, like, when should you create the index?
You can do one of these things:
This requires a bit of testing to get right.
In many cases, this is the best option, for reasons outlined by Pam Lahoud.
Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them ends.
Do not alter temp tables after they have been created.
Do not truncate temp tables
Move index creation statements on temp tables to the new inline index creation syntax that was introduced in SQL Server 2014.
Where it can be a bad option is:
This is almost always not ideal, unless you want to avoid caching the temp table, and for the recompilation to occur for whatever reason.
It’s not that I’d ever rule this out as an option, but I’d wanna have a good reason for it.
Probably even several.
This can sometimes be a good option if the query plan you get from inserting into the index is deficient in some way.
Like I mentioned up above, maybe you lose parallel insert, or maybe the DML Request Sort is a thorn in your side.
This can be awesome! Except on Standard Edition, where you can’t create indexes in parallel. Which picks off one of the reasons for doing this in the first place, and also potentially causes you headaches with not caching temp tables, and statement level recompiles.
One upside here is that if you insert data into a temp table with an index, and then run a query that causes statistics generation, you’ll almost certainly get the default sampling rate. That could potentially cause other annoyances. Creating the index after loading data means you get the full scan stats.
Hooray, I guess.
This may not ever be the end of the world, but here’s a quick example:
DROP TABLE IF EXISTS #t;
GO
--Create a table with an index already on it
CREATE TABLE #t(id INT, INDEX c CLUSTERED(id));
--Load data
INSERT #t WITH(TABLOCK)
SELECT p.OwnerUserId
FROM dbo.Posts AS p;
--Run a query to generate statistics
SELECT COUNT(*)
FROM #t AS t
WHERE t.id BETWEEN 1 AND 10000
GO
--See what's poppin'
SELECT hist.step_number, hist.range_high_key, hist.range_rows,
hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM tempdb.sys.stats AS s
CROSS APPLY tempdb.sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE OBJECT_NAME(s.object_id, 2) LIKE '#t%'
GO
DROP TABLE #t;
--Create a query with no index
CREATE TABLE #t(id INT NOT NULL);
--Load data
INSERT #t WITH(TABLOCK)
SELECT p.OwnerUserId
FROM dbo.Posts AS p;
--Create the index
CREATE CLUSTERED INDEX c ON #t(id);
--Run a query to generate statistics
SELECT COUNT(*)
FROM #t AS t
WHERE t.id BETWEEN 1 AND 10000
--See what's poppin'
SELECT hist.step_number, hist.range_high_key, hist.range_rows,
hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM tempdb.sys.stats AS s
CROSS APPLY tempdb.sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE OBJECT_NAME(s.object_id, 2) LIKE '#t%'
GO
DROP TABLE #t;
On the left is the first 20 steps from the first histogram, and on the right is the first 20 from the second one.
You can see some big differences — whether or not they end up helping or hurting performance would take a lot of different tests. Quite frankly, it’s probably not where I’d start a performance investigation, but I’d be lying if I told you it never ended up there.
In general, I’d stick to using the inline index creation syntax. If I had to work around issues with that, I’d create the index after loading data, but being on Standard Edition brings some additional considerations around parallel index creation.
Thanks for reading!
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.
This is the part of SQL Server I often find myself rolling my eyes at: poor feature interoperability, and that V1 smell that… never seems to turn into that V2 smell.
The full script is hosted here. I don’t want a tedious post full of setting up partitioning, etc.
I wanna get to the stuff that you might care about later.
The utility of Partitioning is being able to quickly switch partitions in and out. Data management. Not query performance (unless you’re using columnstore).
If you want to do that with temporal tables, your staging and “out” tables need to match exactly, down to the temporal-ness.
For example, this won’t work:
CREATE TABLE dbo.Votes_Deletes_Stage
(
Id INT NOT NULL,
PostId INT NOT NULL,
UserId INT NULL,
BountyAmount INT NULL,
VoteTypeId INT NOT NULL,
CreationDate DATETIME NOT NULL,
SysStartTime DATETIME2(7) NOT NULL, --Versioning column
SysEndTime DATETIME2(7) NOT NULL --Versioning column
CONSTRAINT dr_rockzo_Stage PRIMARY KEY CLUSTERED (CreationDate, Id) ON [PRIMARY]
) ON [PRIMARY];
You gotta have all the same stuff you used to get your partitioned table set up for temporal-ness.
CREATE TABLE dbo.Votes_Deletes_Stage
(
Id INT NOT NULL,
PostId INT NOT NULL,
UserId INT NULL,
BountyAmount INT NULL,
VoteTypeId INT NOT NULL,
CreationDate DATETIME NOT NULL,
SysStartTime DATETIME2(7) GENERATED ALWAYS AS ROW START HIDDEN
CONSTRAINT df_VotesDeletes_Stage_SysStartTime
DEFAULT SYSDATETIME(),
SysEndTime DATETIME2(7) GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT df_VotesDeletes_Stage_SysEndTime
DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')
CONSTRAINT dr_rockzo_Stage PRIMARY KEY CLUSTERED (CreationDate, Id) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME([SysStartTime], [SysEndTime])
) ON [PRIMARY];
Switch data in or out, you have to turn off the temporal-ness.
Msg 13546, Level 16, State 1, Line 97
Switching out partition failed on table ‘DeletesDemo.dbo.Votes_Deletes’ because it is not a supported operation on system-versioned tables. Consider setting SYSTEM_VERSIONING to OFF and trying again.
“Consider turning off the feature that takes forever to turn back on with large tables so you can do the thing partitioning does quickly”
Don’t worry, the color red you’re seeing is totally natural.
And hey, once you’ve turned it off, you can swap a partition in or out.
The partition you’re going to switch in needs to have a constraint on it that tells the whatever that the data you’re switching in is valid for the partition you’re swapping it into.
Msg 4982, Level 16, State 1, Line 105
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘DeletesDemo.dbo.Votes_Deletes_Stage’ allow values that are not allowed by range defined by partition 8 on target table ‘DeletesDemo.dbo.Votes_Deletes’.
The thing is, this error message sucks. It sucks all the suck. Especially when dealing with temporal tables, you might think something odd happened with the constraints on your versioning columns. They both have constraints on them. WHICH CONSTRAINT IS THE PROBLEM?
If you’re new to Partitioning, you may not have ever switched data into or out of a table before. This error message can be a real head-scratcher.
The fix is to add a check constraint to your staging table — the one you’re swapping in — that tells Partitioning about what’s in the table. In my case, I have the Votes_Deletes table partitioned by CreationDate, by one year ranges. For me, Partition 8 contains values for the year 2013. To make sure it’s safe to swap my staging partition into the partition for that year, it needs a constraint:
ALTER TABLE dbo.Votes_Deletes_Stage
ADD CONSTRAINT ck_yrself
CHECK (CreationDate >= '20130101' AND CreationDate < '20140101'
AND CreationDate IS NOT NULL);
Turn the temporal-ness back on. When you do that, you have an option. Do you want to make sure your data is consistent?
ALTER TABLE dbo.Votes_Deletes SET (SYSTEM_VERSIONING = ON ( HISTORY_TABLE=dbo.Votes_Deletes_History, DATA_CONSISTENCY_CHECK= ON) );
If you don’t, re-enabling is instant. Buuuuuuut you take the chance that some data in your table may have changed while you were tooting around trying to get partitions swapped in and out. I have no idea what the ramifications of skipping the check might be. In the context of this post, probably nothing. If you’ve got a full history table and the specter of changes during this whole shebang…
This is what the query plan for turning it back on looks like.
There’s nothing in the history table. If there were, this could get really bad (trust me, ha ha ha). What checks do we do when the history table is empty?
Le Shrug, as they say in the land of protest.
But a 53 million row assert sure does take a couple minutes.
Thanks for reading!
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’ve posted quite a bit about how cached plans can be misleading.
I’m gonna switch that up and talk about how an actual plan can be misleading, too.
In plans that include calling a muti-statement table valued function, no operator logs the time spent in the function.
Here’s an example:
SELECT TOP (100)
p.Id AS [Post Link],
vs.up,
vs.down
FROM dbo.VoteStats() AS vs --The function
JOIN dbo.Posts AS p
ON vs.postid = p.Id
WHERE vs.down > vs.up_multiplier
AND p.CommunityOwnedDate IS NULL
AND p.ClosedDate IS NULL
ORDER BY vs.up DESC
When I run the query, it drags on for 30-ish seconds, but the plan says that it only ran for about 2.7 seconds.
But there it is in Query Time Stats! 29 seconds. What gives?
If we look at the estimated plan for the function, we can see quite a thick arrow pointing to the table variable we populate for our results.
That process is all part of the query, but it doesn’t show up in any of the operators. It really should.
More specifically, I think it should show up right here.
Thanks for reading!
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 share a minor yet intriguing discovery related to SQL Server 2019 and its batch mode for rowstore operations. Specifically, I delve into how the query optimizer behaves differently when running scale R aggregate queries—those that should return only one result—and how adding a phony GROUP BY clause can lead to unexpected execution plans. The video highlights the transition from row mode to batch mode in SQL Server 2019 and explains why this change might impact memory grants and stop-and-go operators, offering insights for those moving to or already using this version of SQL Server.
Happy Sunday, isn’t it? What a, what a day, what a time, what a date time to be alive. The year get date, we are, we are alive and well and happy. I wanted to record a video before I go off to do other things about a very mild, minor discovery. I’m probably late to this discovery by months. But I don’t care. That never stopped me before. When I, I’ll, I’ll discover new things all day. Just the other day, I discovered South Brooklyn. It’s amazing. Can’t believe how well it was developed for something that I just discovered. Anyway, so with SQL Server 2019, we get batch mode for Roastore if we’re on Enterprise Edition and then compat level 150 and all sorts of things. other good stuff. But yeah, it used to be. Now I learned this from a, a Craig Friedman talk on the query processor, like years ago, that the only option when we run a, a scale R aggregate, meaning an aggregate that will only, only ever return one result. Like if I select some score from comments with this where clause, we’re only going to get one line back or one row back or whatever. But then we’re going to get one line back or whatever. But we’re going to get one line back or one row back or whatever. But we’re going to get one line back or one row back or whatever.
But if I were to say like group by user ID or group by post, then we get a whole list of scores. I mean, without, if we don’t put those columns in the select list, then, you know, it’s not very helpful because we don’t know which user, which, which posts we have the sum of scores for. But we could still do that and return a bunch of sums back, which is not helpful, but something we could do. With the old way of doing things in row mode plans, the only option available to the optimizer when you run a query like this is to use a stream aggregate. We have one stream there. This is a partial aggregate and then one stream over here and that’s a global aggregate and whatever. That’s, that’s nice. That’s all well and good. And I don’t know. It’s interesting. Now, what’s even more interesting, I think, is that even if we add a phony group by and we tell the optimizer, we’re like, buddy, listen, I want you to use a hash group on this. And we run this query.
Well, we, we don’t get an error. A lot of times if you add an option hint to a query and the optimizer can’t come up with a feasible execution plan, you’ll get that error. That’s like, yeah, I couldn’t generate a good plan. I can generate a plan based on those hints. Please remove them and try again. We don’t get that. We don’t get that here, but we also don’t get the hash group. We get one to stream aggregate. So we’re at, we have a very disobedient optimizer. We need to spank this optimizer. Optimizer needs a spank and probably a grounding and take its iPad away or something. But this all changes with SQL Server 2019, assuming you are on enterprise edition and also perhaps assuming that you are in compat level 150.
Now, if we run these queries, right, we select some creation date and slept blah, blah, blah, blah, blah, blah, blah, blah, option hash group. Now, when we run those, we can see that the optimizer had a hash match aggregate in the plan. We did not do the, the partial stream and then go serial and then another than the full stream aggregate. We have just a single hash match aggregate in here. It’s exciting stuff, right? Why, why do we have that? Well, in compat level 140, we were in row mode, but now the magic of compat level 150, our, our clustered index scan over here is, uh, is in batch mode.
Nice, nice. And our hash match aggregate over here is in batch mode. Nice, nice. There we go. All right. So why is that interesting? Why does that make a difference? Why is this a big deal? Well, there’s a couple things at play here. Uh, internally, uh, stream aggregates are, uh, non-blocking, meaning that, uh, like with, so like if you have a, a hash aggregate or a hash join, there is a pause within the query for the hash table to get built. And then when things begin probing, things carry on in the plan. That’s what they’re called blocking or stop and go operators.
Uh, stream aggregates don’t have that. The other thing about stream aggregates is that stream aggregates don’t require a memory grant where hash aggregates do because we need some scratch space to write all that stuff down in. So if you have queries where you’re, uh, uh, performing a scalar aggregate and, uh, perhaps where, uh, you had, you know, some reliance on there being a streaming operation or perhaps just where you didn’t have a memory grant before, you may find yourself having memory grants. Now you might find yourself having stop and go operations in your, in your query plans now, because with this additional choice available, we change the query plans that are available for these queries. So is this incredibly interesting?
Eh, it’s sort of interesting. Um, is this incredibly dangerous? Probably not any more dangerous than any of the, of the other possibilities that, uh, become, become available with, uh, batch mode on rowstore. But it is something interesting to consider. Um, you know, if, if you, especially if you are getting, uh, scalar aggregates for very large data sets, you may find that those, uh, those hash match aggregates ask for potentially large amounts of memory.
That could, that could change the, uh, the face of your workload if this is happening, like, during some overnight process that, like, populates, I don’t know, let’s just call it like ETL ish, right? Like you might load a bunch of summary data into a table. You might actually have an ETL process. You might actually do something. Uh, and like, you might even try to make it concurrent.
So you’re like, I’m going to do a bunch of these sums at once and I’m just going to send them on and, you know, pass stuff over. Whatever it is, whatever it is, it doesn’t matter. Just, it’s different. It’s new. It’s different. And I want to tell you about it. So I want you to be aware of it and I want you to not be astounded or shocked or dismayed when you find these new things in your query plans as you all slowly move to SQL Server 2019, which I think you should, because that’ll give me more interesting stuff to do, other than like, we must fix the function. We, this, this, this, this, this, this, this, this, this, this, this, this, this, this, this, this, this, and we got an inappropriate joy.
Like, there’s a lot of stuff that I, I wish we were all on SQL Server 2019. So I could, I could, I could start fixing more, more, more things than this sort of groundhog day stuff that it’s been a problem in SQL Server forever and ever. Anyway, I’m not going to turn down groundhog day work. Just keep in mind that, um, you know, SQL Server 2019 does fix a lot of the groundhog day stuff that has, has been shocking and dismaying and annoying and aggravating people for 20 years now.
So I’m excited. I can’t wait. I can’t wait for, uh, for it to be widely adopted or who knows, maybe by the time it’s, it’s even close to widely adopted, we’ll be on like SQL Server 2025 or something. Maybe I’ll have opened a gym by then. Maybe I’ll just not even be looking at SQL Server anymore. Who knows? Who knows? I don’t know.
Um, but heck, I’m optimistic about the future. All right. Goodbye. See you in the next video.
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’m diving into a rather mundane topic—specifically, the behavior of scalar-valued functions in SQL Server across different compatibility levels. I walk through a simple query and then nest it within a function to illustrate how performance can vary significantly depending on the version of SQL Server you’re using. The video covers the differences between compatibility levels 140 and 150, highlighting how materialization of branches can impact execution time. If your name isn’t Forrest McDaniel, you might find this content quite dull; however, I appreciate your patience if you stuck around!
I’m recording a very boring video for my friend Forrest. If your friend, if your name isn’t Forrest McDaniel, you will probably find this video very, very dull and uninteresting. You should probably just stop watching right here. I won’t take it personally if the watch time on this thing plummets because your name isn’t Forrest McDaniel and you would be terribly uninterested in this very boring material. Promise. So, without further ado, here’s some very boring material. I have this query. Alright, I declare a couple variables and I set each variable equal to something. Let’s say I’m going to do some stuff with them later. If I run this query with the query plans turned on and I look at the query plan for it, I have one seek into the comments table because I’m a top lad and I created an index that my query could seek into. Then I have one stream aggregate where both of these expressions are calculated. Actually, there are three things that calculate there, but, uh, so because, uh, you know, uh, I don’t know why there are three off the top of my head, but they’re, but the two that I calculated are in there, I promise. They’re, they’re both there. So, what I want to do is put that in a function and when I put that into a function, like, I’m going to call that comment score for some reason. I don’t know why. I’ll put that into a function and inside the function I’m going to declare those things, uh, set them, you know, declare my, uh, my internal variables, set them equal to stuff.
And then I’m going to return, uh, the comment count times the comment score. Good stuff. So, uh, the first thing that I need to show you is that in compatibility level 140, uh, if I run these queries, uh, they’re going to run pretty quickly because even though that’s a scalar valued function, it’s not running over a ton of rows. It’s not doing a ton of work. And I have proper indexes in place for my function. When we look at the query plan, like we know about scalar values, functions, it’s not going to tell us what the function did. It’s going to completely lie to us. If you’re, if you’re like, like creeped out by this, then you should just go watch my, my plan cache liars videos. Anyway, moving right along. If I run this and we get the estimated plan and we see what the function is doing, the function is doing exactly what the query did. We have one seek, we have one stream aggregate, even for the larger query that, uh, that hits more rows. We have one seek and one stream aggregate. The difference between these two is this is just where one user. I’m just getting that for one user. And the down here, I’m getting, uh, I’m getting the comments where for every user with a reputation over a hundred thousand where things start to get weird is in compat level 150. When we turn on scale, our UDF and lining. Now what I’m going to do is run both of these.
So the flow is about 10 milliseconds before not the end of the world, but it, it, it, it, it’s, it’s, it’s noticeable there. It’s even more noticeable in this query where before when it took about a second. Now we’re looking at it taking about a second and a half because each one of these branches with this stream aggregate is materialized. So this top branch takes 475 and this bottom branch takes 7.726 milliseconds. So about a half a second, a little bit closer to a second. And you can see the final tally on this plan is about a second and a half. The first one was should have been around a second or so. This gets even crappier. If we drop the index that I created on comments, I’m just going to get the estimated plan here because, uh, I don’t want this. I don’t want this video to drag on forever and ever.
But if we look at the estimated plan now and we see that we are indeed missing that index that I created earlier. Um, Oh, you know what? It doesn’t show up my, well, that’s my fault. It doesn’t show up in the estimated plan. It only shows up in the actual plan. So what I got to do now is, uh, run this and let’s go over here. Let’s do this. Let’s do this. Any who is active. All right. Yeah. Yeah. One. Run that.
And look at the execution plan and we will see that SQL Server has chosen to do two index spools, uh, one for each branch in there. Now I know that I could get around this by doing the math from the function all in one go. Like I could just, you know, up here, I could just say set, total comment score equals count big times sum. I know, I get it, but I think this is kind of a missed opportunity to fold some expressions in and do everything all in one go, because if you if you have a bunch of these they’re all gonna kind of add up. And I see a lot of scalar value functions that do a lot of variable assignment like this. If it’s not all in one line then you’re looking at having to rewrite the function to prevent all of those branches from expanding. Anyway, I’m gonna go get brunch now, or take a shower and then go get brunch. I’m still sort of in my PJs, but yeah. Again, totally boring stuff. If your name isn’t Forrest McDaniel you’re probably gonna not enjoy a single second of that. Totally uninteresting. But thank you for not watching, and I will not see you in the next video because you didn’t see this because your name isn’t Forrest.
Isn’t that funny? Isn’t it funny how that works out? You listened to me, didn’t you? You listened to me for once. Thank you. I appreciate it. Sweetie. thank you.
amp police
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.