Query Tuning Archives | Page 18 of 30

Why Index Tuning Is An Iterative Process In SQL Server

Posted on March 17, 2020May 16, 2022 by Erik Darling

Here And There

For many people, index tuning means occasionally adding an index when there’s a report about a slow query. Those indexes might come from a query plan, or from the missing index DMVs, where SQL Server stores every complaint the optimizer files when it thinks an index might make a query better.

Sure, there are some people who think index tuning means rebuilding indexes or running DTA and checking all the boxes, but I ban those IP addresses.

Of course, there’s a whole lot more to index tuning. Adding indexes is fine to a point, but you really should spring clean those suckers once in a while, too.

Look for overlapping indexes, unused indexes, and check for any Heaps that may have snuck in there. sp_BlitzIndex is a pretty cool tool for that.

But even for adding indexes, sometimes it takes more than one pass, especially if you’re taking advice from query plans and DMVs.

How The What

Let’s say you’re looking at a server for the first time, or you’re not quite comfortable with designing your own indexes. No judgment, there.

You see a query plan for some piece of code that’s running slowly, and it has a missing index request.

SQL Server Query Plan — Sugar Sugar Sugar

There’s only one missing index request — there’s not a bunch of hidden ones like in some plans — and it looks moderately helpful so you decide to try it.

SQL Server Missing Index Request — Treefiddy

The thing is that as far as “stuff I want to go faster” in the plan, the clustered index scan on Posts is about 3x faster than the clustered index scan on Comments.

And the index that’s being asked for is only going to help us find PostTypeId = 1. It’s not going to help with the rest of or join or filtering very much.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts] ([PostTypeId])
INCLUDE ([CreationDate],[OwnerUserId])

We still need to filter on CreationDate, and join on OwnerUserId later. Sometimes this index will be “good enough” and other times it “won’t”.

If PostTypeId were really selective, or if this query were searching for a particularly selective PostTypeId, then it’d probably be okay-ish.

But we’re not, so we may settle on this index instead.

CREATE NONCLUSTERED INDEX p
ON [dbo].[Posts] ([PostTypeId], [CreationDate], [OwnerUserId]);

With that in place, we only get marginal improvement in the timing of the plan. It’s about 1.5 seconds faster.

Probably not what we’d wanna report to end users.

But we have new green text! This time it’s for the Comments table, which is where our pain point lies time-wise.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Comments] ([PostId],[CreationDate])

We add that, and reduce our query runtime to less than half of what it was originally.

Is 2.6 seconds good? Or great? All depends on expectations.

Could we keep going and experimenting? Sure.

It all depends what we’re allowed to change, what our skill level is (mine is quite low, ho ho ho), and what our priorities are (these are also quite low).

This Is Just One Query

And since we had the luxury of having it in front of us, running it, adding an index, running it again to test the index, etc., we were able to spot the second index request that ended up helping even more than the first one.

If you don’t have that luxury, or if you just poke around the missing index DMVs every 3-6 months, you could miss stuff like this. Sure, that first request would be there, and it might look tempting enough for you to add, but the second one wouldn’t appear until after that. That’s the one that really helped.

Whenever you’re tuning indexes, or releasing code that’s going to use existing data in new ways, you’d be doing yourself a big favor to check in on this stuff at least weekly.

You might be an index tuning wiz and not need to — if you are, I’d be amazed if you made it this far into my blog post, though — or you may catch “obvious” new indexes during development.

But I’m going to tell you something about end users: they’re devious, mischievous, and they’re out to make you look bad.

As soon as they start using those new features of yours, they’re going to abuse them. They’re going to do all sorts of horrible things that you never would have dreamed of. And I’ll bet some different indexes would help you keep your good name.

Or at least your job.

Thanks for reading!

As a postscript to this: I don’t want you to think that missing index requests are the end-all be-all of indexing wisdom. There are lots of limitations, and suggested column order isn’t perfect. But if you’re just getting started, they’re a great way to start to understand indexing, and see the problems they do and don’t solve. And look, the only way to make them better would be to spend longer during compilation thinking about things. That’s not how the optimizer should be spending its time. We’re lucky to get these for free, and you should view them as a learning tool.

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.

Rewriting Multi Statement Table Valued Functions To Fix SQL Server Performance Problems

Posted on March 12, 2020January 20, 2026 by Erik Darling

Hey You Should Do This

Video Summary

In this video, I dive into the world of multi-statement table-valued functions and why they can be a performance nightmare in SQL Server. I share my experience of rewriting these functions to inline table-valued functions using startup expression predicates, demonstrating how to handle complex logic within them without compromising performance. I walk you through the process step-by-step, showing that even if your function has conditional blocks like `IF` statements, there are ways to refactor it into a more efficient form. By leveraging Common Table Expressions (CTEs) and careful predicate handling, we can achieve significant improvements in execution plans and overall query performance. So, whether you’re dealing with simple or complex logic within these functions, this video will equip you with the knowledge to tackle them head-on!

Full Transcript

I’m so ready. So ready to leave. I just have two videos left. After this one, there’s one video, but I have succumbed to my weaknesses, and it is champagne time at Shea Darling. And so, because it is champagne time, we should get this before it’s too much champagne time. So I wanted to talk about rewriting multi-statement table-valued functions, because they are generally bad for performance, even if the query that populates them is fairly simple. You just have like the goofy overhead of the table variable. On versions prior to SQL Server 2017, there was like almost no hope for these things. 2017 got something called interleaved execution, which gave you table cardinality for the, for like, cardinality estimates coming out of the function. Prior to that it was 100 rows for 2014 and one row for that. It was like a bad time. It was a bad time across the board.

The thing is when I talk to a lot of people, they’re like, I can’t rewrite this. It’s too complicated. There are if blocks. I’m like, yes, you can. Don’t be ridiculous. We can do this together. Hold your hand. Run through the, run, run through the field. Pick some flowers. I don’t know. It’ll be fun. Be romantic. So this is our multi-statement table-valued function. We have, it takes some stuff in here, user ID and a post type ID, and we declare a table variable with some columns in it. They’re all very, very useful, necessary columns. And then based on which post type ID we pass in, we will execute a block depending on, on that. And this is where people are often like, well, there’s just no way to do this. There’s no way to rewrite this.

I’m like, I’m very silly. Now, no, you, if, if you were to rewrite this, you couldn’t just do this, right? You couldn’t say this return table as, and then if blah, blah, do this, blah, blah, do this, blah, blah, do this. That will not work. There are red squiggles everywhere. We can see from the red squiggles that this will not work. This will not compile. If I try to create this, we’ll get, we’ll get some funk thrown at us. Incorrect syntax near the keyword if. Sorry to hear that. But what you can do inside of an inline table-valued function that does not require an if branch is take advantage of startup expression predicates.

And what I mean by that is we can stack some CTE. Now, this is a good use for CTE. I mean, granted, we could have used derived tables here or something too, but whatever. So what we’ll do is we’ll still pass in a user ID and a post type ID.

And what we’ll do is we’ll have a CTE called questions, and that’ll take care of the post type ID equals one stuff up here. And we’ll add a filter inside of here and say you only fire off if post type ID equals one. And we’ll do the same thing for answers and say you only fire off if post type ID equals two.

And we’ll do the same thing for other where if post type ID is greater than two, then we’ll fire this off. And then we’ll have a third CTE that is a union all of those three, right? So we have questions, answers. I don’t know why I put that in the kind of funny order, but questions, answers, and other.

And then we’ll just select the top one from that order by score descending. And this will work just fine, and it will work just wonderfully. And something that I think is nice about this is that even if we don’t need data from one of them, they won’t fire.

So the execution plan will be complicated. But if we look at it kind of closely, we’ll see that we only fired off and hit the post table the once. The rest of the time we got constant scans from the other two accesses there.

Now, oh rather, sorry, we have to quote this one out. That’s what I was doing wrong. Ha ha ha. If we quote this out, ooh la la, and we zoom out a little bit, we can see that we only touched the post table once.

We did not touch it three times because of the startup expression predicates. The three other times we got, or the two other times, rather, we got these constant scan operators. What I messed up before is that, you know, I ran the cross-apply part.

So even with this quoted out, that did happen. But if I flip things around and I bring this in and I bring this out and I get rid of you, and actually I should probably get rid of you too. There we go.

And we run this, the same thing will happen where SQL Server will look at what was going on and say, oh, I don’t need that first one. I’m only going to use that second one this time. And then if I need both, of course, then we’ll run both and we’ll get stuff from both, I guess. Lucky us.

All right. We got all that good stuff there. Yep. So we hit things twice because we had the two applies run. But anyway, that’s not really the point. The point is that there are ways to write more complicated multi-statement table-valued functions as inline table-valued functions.

It does take, you know, some practice. It does take some getting used to. But you can do it.

And you can often get much better performance from them in general. So that’s that. I don’t know. If you don’t like it, you don’t have to. Just do whatever you want.

It’s your life. Anyway, I’ll drink to that. I will see you over in the next video. We will talk about where filtered indexes are still broken.

See you there. Thanks for watching.

Going Further

Tuning Performance Problems With Aggregates In SQL Server, Part 3

Posted on March 11, 2020May 3, 2023 by Erik Darling

3/3

Going Further

Tuning Performance Problems With Aggregates In SQL Server, Part 2

Posted on March 10, 2020January 20, 2026 by Erik Darling

2/3

Video Summary

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!

Full Transcript

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.

Going Further

Tuning Performance Problems With Aggregates In SQL Server, Part 1

Posted on March 9, 2020May 3, 2023 by Erik Darling

1/3

Going Further

But WHY Does ISNULL Have To Scan A SQL Server Index?

Posted on March 5, 2020January 20, 2026 by Erik Darling

No Answers

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.

Video Summary

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.

Full Transcript

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.

Going Further

When Should You Index Temp Tables In SQL Server?

Posted on March 4, 2020May 16, 2022 by Erik Darling

What I Mean Is

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?

Options

You can do one of these things:

Inline, when you create the table
After you create the table
After you load data into the table

This requires a bit of testing to get right.

Inline

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:

If you can’t get a parallel insert even with a TABLOCK hint
Sorting the data to match index order on insert could result in some discomfort

After Creation

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.

After Insert

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.

All Things Considerateded

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!

Going Further

How Functions Can Make Performance Tuning SQL Server Queries Harder

Posted on March 2, 2020May 15, 2022 by Erik Darling

Sensational

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?

SQL Server Query Times From Execution Plan — Hi there!

Estimations

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!

Going Further

This Won’t Interest You At All

Posted on February 26, 2020January 20, 2026 by Erik Darling

wtfudf

Video Summary

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!

Full Transcript

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

Going Further

My Favorite SQL Server Cursor Options: Local Static

Posted on February 24, 2020January 20, 2026 by Erik Darling

Cursory

Video Summary

In this video, I dive into the world of cursors and explore why my favorite cursor option is the local static cursor. Despite some contention in the SQL community about which cursor options are best, I share one compelling reason for preferring local static cursors over others like the fast forward cursor. I explain how using a fast forward cursor can force your entire query to run serially, leading to suboptimal performance. By contrast, a local static cursor allows for parallel execution, significantly improving query performance in certain scenarios. The video also covers the importance of testing different cursor options and understanding their impact on execution plans and performance metrics.

Full Transcript

This is still just plain black coffee and I’m having a tough time with that this morning. I’m recording video number two today on this cold, chilly, rainy day. Brrr, day where it sure would be nice to have a heaping helping of some Lafroig or Lagavulin or Ardbeg in a fellas coffee cup. Sure would be nice. Ah, boy. So I want to talk about my favorite cursor options. I know that there is some contention.

I almost knocked my coffee over because I’m apparently too sober to function. So there is some contention about what the best cursor options are and why. And you should always use this and never use this. And I’m going to throw my hat in the ring.

And my favorite cursor options are local static. And there are very smart people out there like Erlend Somerskog who agree with me. And I’m going to show you one of my reasons why I like the local static cursor better than other cursor types. But first, we have to go look at that other cursor type.

So this is a type of cursor that a lot of people will tell you to use a local fast forward cursor. And if you look at the documentation for cursor options, you’ll see that fast forward has some optimizations. Optimizations.

What those optimizations are, we don’t know. I don’t know. I don’t think they’re documented. But one optimization about fast forward cursors that I heartily dislike is that when you run them, much like many other things in SQL Server, like inline scalar, non-valued functions, non-inlined scalar valued functions, modifying table variables, reading from multi-statement table valued function table variables, accessing some system views, blah, blah, blah, blah, blah, will force your entire query to run serially.

Let’s go take a look. So with the fast forward cursor in place, we can see that this query plan up here is entirely serial. And it runs for just about 11 and a half seconds. If I go look at the properties of this open cursor, right?

Something again, I think I say this in a lot of videos when I’m talking about execution plans. One should always be looking at the properties window when they are looking at query plans. There is so much good, rich, detailed information in there about what is going on with your query.

Without it, you would be lost. Sad babe in the woods. We can see immediately that there is a non-parallel plan reason. Now, why none of these things have spaces in them is beyond, is a little bit beyond me.

I’m not quite sure why. You know, I understand that sometimes space bars stop working. You could probably just ask Microsoft’s IT department for a new one.

They probably have some spares sitting around. But anyway, we have a non-parallel plan reason that says no parallel fast forward cursor, which is a bit of a departure from most non-parallel plan reasons.

Most of the time when you see a non-parallel plan reason, it will either say maxed offset to one or could not generate valid parallel plan. And could not generate valid parallel plan is an umbrella for a lot of things that we cannot have query parallelism for.

Now, am I saying that parallel queries should always be used or that they are the best or that you should always strive to have a parallel query? No. No, of course not. And if we’re being honest about the query that’s running, there are probably some indexes that would help this thing run a lot faster, where you wouldn’t notice a difference in the type of cursor that you are using.

But not everyone is in a position where they can, you know, change indexes or, you know, tune queries or tune logic or any of that other stuff. And you might have a big honking crazy query that drives the initial result set of the cursor you’re going to use. And you might not have too many options for tuning that.

This is one thing where changing the cursor option can help you a little bit. So this query takes 11 and a half seconds when it runs serially with the local and fast forward options assigned to the cursor. But over here we have a query or we have a cursor.

We have a… With my favorite cursor options, local static. And when we look at the query plan for local static, we can see that we do not have the same parallelism inhibitor going on that we did in the fast forward plan.

We actually have parallel, parallel, parallel, parallel up until we gather streams here. And this runs for about 2 point… Well, let’s be fair.

About 2.8 seconds. So we got about 12 seconds versus about 3 seconds. Right? Okay. So about a 9 second difference in the query that drives the initial population of the cursor temp table doodad thingy. So, kinda nice that you can…

You should be careful with your cursors and all that. And, you know… I think…

I think cursors are a bit inevitable in some cases. And, you know… Well, you know, some people might watch this video and hiss because I’m talking about ways to tune a cursor rather than just getting rid of the cursor. Well, I mean, like, you know, what if I was going to take the results of this cursor?

What if I was going to take ID, display name, and score? And what if I was going to feed those into a stored procedure and I was going to have to run that stored procedure over a whole bunch of rows? Like, how else am I really going to do that?

If I write a while loop, is it going to make a difference in how I call a stored procedure? If I dump those rows into a temp table, is it really going to be all that different than if I use a cursor to populate a static temporary object? No.

It’s not going to be all that different. So, you know, well, a lot of people will get mad about the cursor in general. And I understand why because there are some very bad uses for cursors. And, you know, I see them and I got to help people with those kind of performance problems pretty often.

But, you know, if I had the choice between spending a long time tuning a cursor query and just changing the cursor option and, you know, saving nine seconds right off the bat, you know, if I’m in a hustle, if I’m trying to like, you know, really get some stuff done, I might just go with the different cursor options to move things along. Maybe get to something else that’s, you know, a performance bump in the night for something I’m trying to tune.

Anyway, the moral of the story is don’t use cursors if you don’t have to. But if you have to or if you’re stuck with them for some other reason, choose a cursor. Look at your cursor options carefully.

Don’t just jump right to the fast forward cursor. It can, you know, do some do some performance testing, you know, run the run the query inside the cursor and see how it goes. And then then run the query inside the cursor and see if see if you get the same execution plan.

See if you get the same time, the same performance metrics, because, you know, if you run the query, then, you know, you don’t use outside of the cursor and you get a nice parallel plan and everything’s fast. And then you put it in the cursor and it takes 20 minutes, you’re gonna be like, oh, this damn cursor. But it’s you. It’s your fault. You chose the wrong cursor options.

Don’t blame the cursor. Blame the you. That’s it. Anyway, I’m gonna go now. I’m going to go invest in Scotland and have a nice day. So, thanks for watching. I hope you I hope you learned something.

I hope that you enjoyed yourself. Maybe. And I will see you in another video. Maybe even another one today.

I don’t know. I don’t know where I don’t know where today may take me in. And if I’m feeling proper Scottish, I might I might feel very record-y today. I don’t know. We’ll see. It’s a surprise for everyone.

Anyway. Thank you. And I will see you in another video where maybe I’ll still be able to stand up. Goodbye.