Performance Archives | Page 11 of 140

New in SQL Server Management Studio 22: Open Execution Plans In a New Tab!

Posted on December 19, 2025January 23, 2026 by Erik Darling

New in SQL Server Management Studio 22: Open Execution Plans In a New Tab!

Video Summary

In this video, I share an exciting new feature in SQL Server Management Studio 22 that was recently added as part of the preview release. As someone who frequently deals with query plans and execution paths, I can attest to how frustrating it is when you lose a plan after doing something else within your query window. This feature allows you to open an execution plan in a new tab directly from the query results, ensuring that you don’t have to worry about accidentally losing or overwriting important plans. It’s a simple but incredibly useful addition that streamlines the process of tuning and comparing queries. Additionally, I highlight another issue I’ve opened for SSMS, suggesting the inclusion of an option to keep actual execution plans enabled by default across all tabs. This would be particularly beneficial for query tuners and presenters who rely heavily on these features during their work.

Full Transcript

Erik Darling here with Darling Data. And this is a very short video because it’s Friday and we’re all in love. Just to talk about a new feature that got added in SQL Server Management Studio 22 preview. If you haven’t gotten a chance to download and try that out yet, I don’t blame you. It took me a little bit to get to it too. The SSMS team is working fastly and furiously. Remember the cadence at which you went from 20 to 21 to 22 has been whiplash speed. So this was actually a feature that I suggested and I’d like to thank, of course, Aaron Stilato and the whole crew who works on SSMS for noticing and taking care of this wonderful addition to SQL Server Management Studio. So what it is, is if you run a query and you get a query plan for it, now you can open that execution plan in a new tab so that you don’t lose it if you do something else within your query window. The number of times this has happened to me and I’ve had to rerun a query to get the query plan is absolutely infuriating. And so I just want to show you what it does and how it works. Now, I just got SQL Server Management Studio 22. I have not done any setup or anything on it. But I just want to show you what it does and how it works. Now, I just got SQL Server Management Studio 22. I have not done any setup or anything on it.

So if it looks weird or anything like that, I guess that’s why. But, you know, I think it looks nice, right? Dark mode and all that other good stuff. So what I’m going to do is just run this query and we’re going to see an actual factual execution plan show up down here, right? You can admire all its glory. Look at this. Oh, geez. We’ve got spills and look at all this stuff going on. Oh, boy. We got to tune this thing. But what if we wanted to like, you know, try some stuff and then and then I don’t know, like compare execution plans. What would we do? Well, we could go the clunky route and save it or we could right click and we could say show execution plan in new tab. It’s beautiful. Look at that. And then we do this and it pops up in a brand new tab. And now we can do whatever you want in this window without losing our query plan. It’s fantastic. It’s wonderful. We don’t have to worry about accidentally hitting something. or doing something without remembering to save stuff. We can just pop something off to a new tab and move on with our day, right? Get back to tuning queries, all that good stuff.

Now, there is one other issue that I have open for SQL Server Management Studio. And I think it’s probably an important one for query tuners and people who present about query tuning generally. Because nothing is worse than forgetting to turn on actual execution plans when you are when you’re going to do things, right? Because if you have a lot of different tabs that you’re dealing with or whatever reason, let’s just say, maybe you’re just a fellow old person who works with SQL Server quite a bit.

And the old memory might lapse here and there. I have an open issue to add an option to keep actual execution plans enabled for all tabs. Now, this isn’t obviously this wouldn’t be a default, right? Because not everyone wants execution plans on all the time. This could even maybe be something that you like a setting and presenter mode, because, you know, that’s maybe a little bit more geared towards the type of people who would who would who would like this button there.

But it is something that I think would be a useful addition to SQL Server Management Studio. So we’re going to I don’t know why I just refreshed that we’re going to I’m going to promote that issue in this video as well. The link for this will be down in the video description.

But SQL Server Management 22 Studio 22. I don’t know why I keep forgetting the word studio. This is this is gives you some idea about the forgetful nature of the aged population working with SQL Server. But looking good, looking real nice.

I like it’s all smooth and pretty and got got all the familiar buttons that I’m that I’m used to. So I don’t get lost and have another grandpa episode. Where’s my damn buttons?

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will download SQL Server Management Studio 22 and test it out. Give it a give it a give it a give it a little hoot and a holler and and see how it goes.

And I don’t know. It’s just about it’s Friday. We should we should not have to watch overly long videos on Fridays. We should we should be able to get back to enjoying ourselves day drinking, optimizing our wine storage cabinets, whatever it is that we do to enjoy ourselves.

I don’t know. Smoke four packs of cigarettes and stare at the sky. That’s sounds like a nice time.

Anyway, thank you for watching.

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.

Batch Mode Sorts and Row Mode Indexes in SQL Server

Posted on December 18, 2025January 23, 2026 by Erik Darling

Batch Mode Sorts and Row Mode Indexes in SQL Server

Video Summary

In this video, I dive into the intricacies of batch mode sorts and rowstore indexes in SQL Server, sharing insights from a recent client-facing issue that highlighted the challenges and nuances involved. We explore how forcing parallel plans can sometimes lead to unexpected results, such as the need for additional sorting even when an index perfectly aligns with query requirements. I also discuss the trade-offs between optimizing for speed versus memory usage, providing practical advice on when to use the `MAXDOP` hint to achieve better performance in specific scenarios.

Full Transcript

Erik dishwaskeling here with Darling Data. And today’s video, we’re going to talk about batch mode sorts and rowstore indexes. And hopefully I said that right as I’m reading it from the teleprompter in front of me. Great. So this was an issue that a client of mine had trying to like, like various parts of their workload, you know, row mode execution just doesn’t cut it. You need to get batch mode stuff involved. But sometimes, especially batch mode on rowstore is a tricky thing to sort of get happening in the way that you want it to happen. So we’re going to talk about a little bit of that. And we’re also going to talk about, of course, the little caveat with batch mode sorts and rowstore indexes. So we have much on our plate. Now, I did mention that this was a client facing problem. And, you know, I do have clients. I do have nice people who pay me for my time to do things for them and have a client-facing problem. Help them with things. If you would like to be one of those lucky people, there is a link down in the video description where you can hire me to do SQL Server stuff. It’s a crazy scheme, I know, but it works. Trust me. You can also, by training, become a channel member, ask me office hours questions. And of course, if you find this channel content just so groovy and ghouly that you want to make sure that it spreads far and wide like the herpes simplex virus, you can, of course, like, subscribe and tell a friend. Get me in that sweet YouTube algorithm or whatever.

Past Dita Community Summit. Oh, boy. Oh, it’s so close. Two days of T-SQL pre-cons. Me and Kendra Little. The entire event is in Seattle, November 17th to 21st. So you should be there. Hopefully you bought tickets and all that stuff at this point because we’re only a few weeks away and sometimes planning this sort of travel takes some time. You got to start early, right? My wife tell you when she books travel, it’s like a million years in advance. So I had chat. So like by the time this video publishes right now, it’s still October. By the time this video publishes, it will no longer be October. So I had said chat GPT. My birthday is in November. Can you give me a birthday themed drawing? Just like iterate on the one that we’ve been using and it left in some Halloween this right? We still have a ghost and a vampire thing, but it is a festive birthday and I am kind of a spooky, spooky person. So we’ve got, we’ve got a ghost and a vampire showing up to my party. So anyway, let’s go talk about the stuff that we wanted to talk about. See, I’m so spooky. I have a black background sometimes. All right. So I think I already did this, but let’s just make sure. Yeah. Wonderful. All right.

We’ve got an index. So what I want to show you first is, and I don’t want to run this like actually like at real time run this because it takes a long time. So just really what I just want to show you is the estimated execution plan for this. Now, this is a query where I am forcing a parallel plan. And since if you watched my video about what Microsoft is doing to create their vector disc and indexes, you should know that the enable parallel plan preference hint is used in that code.

So it’s safe for production now, right? Because Microsoft uses it in production, so you can use it in production. So now it’s safe. They’ve blessed it. So what I want to show you specifically here is that when this query executes in row mode, right? And if we, let’s buy ourselves a little bit more query plan real estate up here, we can generally visually infer that this plan ran in row mode.

Because like, like we have a repartition streams and repartition streams there, they don’t like none of the parallel exchanges support batch mode segment and sequence project aren’t used in batch mode. We get a window aggregate operator for windowing functions when we execute in batch mode.

The filter, of course, could be in batch mode. Actually, I mean, it probably would be if I didn’t disallow it. But and then the gather streams is, of course, another parallel exchange operator. So that can’t be batch mode either. So this is a fully row mode plan. But the thing that I want to point out here is that because we have an index, right? And if we scroll back up here, I should probably sell a little bit of my query plan real estate. We have a non-clustered rowstore index that not only fully covers our query, but supports the window function specification exactly right.

So user ID and sort descending, which is what we’re asking for in here, right? So because we have this, we don’t have a sort operator in here where SQL Server has to like, like, like basically rearrange data from the way it’s stored somewhere to the way that the window function needs it to create its row number, right? So we don’t have to do that there. Now, next is me levels like, you know, try like, hey, like, get that out of the way. Like that’s, that’s gonna, that’s gonna come in handy later. The thing about the sort. So keep that in mind. Now I’m like, okay, well, we want batch mode, right? We’re like, like, we’re gonna use our auxiliary columnstore helper table, we’re gonna do this left join to it. It’s got a clustered columnstore index, it’s got no rows in it, we’re gonna do this thing so that we get a batch mode thing. And we’re gonna say no, but we’re gonna use this now safer production query hint in there. The thing is, though, if we get the estimated plan for this, it’s the same as last time, right? And like, we zoom around a little bit, we’ve got row and like, you know, the like, none of this stuff can be in batch mode, like, like repartition streams, segment sequence project gather streams, but in our filter operator is also still in row mode.

Now this was kind of an interesting one. And what it comes down to for batch mode on rowstore in this query plan specifically, is batch mode on rowstore takes one look at the text column in the comments table and says, hell no. The text column is in VARCAR 700, right? So if we remove that from the query select list, right, we’re gonna specifically name our columns in here. And we’re gonna say no text column, right, we’ve removed it from existence. And we run this, right, we all let’s just get an estimated plan for this. Now we see a much more batch mode plan. Right? But we now we have a sort, we’re using that same nonclustered index, right? So the data from the index is in order. But when we read from this, we’re reading from it in batch mode. Okay. So let’s run the query. And let’s look at how this thing fares. So let’s start over here on the left. And we get a do do do do memory grant of 1418 megabytes. So that’s about 1.4 gigs, I think, depending on how you like divide if it’s by 1000 or 1024, you might, you might have a slightly different take on exactly what that is. But let’s just call it 1.4 gigs for the sake of 1400 megs. Yeah, it’s about 1.4 gigs there. All right, all right, cool, we did the math. But this query finishes in about 1.3 seconds. But the thing is that, why is this sort here? And it’s kind of an interesting thing, because this only happens in parallel plans, right? So if we come down here, where we’re going to do almost the same thing, except we’re going to force this query to run at max.1, right, we’re going to say, hey, and just keep in mind for this query, we didn’t even need this to get batch mode on rowstore.

If we check the estimated plan here, right, we’ll see that we do, we have a very similar plan, but without the sort, right? So reading from the row mode index, with a max.1 plan, we don’t have to sort data here, we can actually rely on the order that this data came from.

So, but if we run this, at max.1, okay, so this whole thing, you know, like it’s a fully batch mode plan. So each operator only has the operator time for itself. So we have 5.2, and then another 137 milliseconds, and then another 35 milliseconds. So let’s just say it was about five and a half seconds total. But the memory grant for this is 120, 1024 KB, which is like one meg. So this is an interesting thing. And this is, this is specifically just a product limitation for the SQL servers implement implementation of batch stuff. It can’t like use the, like in a parallel plan, where batch mode is used like this. It cannot trust the order that things come out of the index. And it can, the index is like the, it’s not the, not the, not that the index is out of order, but the order that stuff gets read in, in batches might not be true to like the, the sort thing. So like it might not maintain things correctly. So we still have to sort data here. So the question for you becomes, what do you want to optimize for? If you want to optimize purely for query speed, well, it’s probably fine to have the, the, this thing run in parallel and have a sort in it and use like 1.4 gigs of memory.

If you want to optimize for memory usage and you don’t, and you’re willing to sacrifice some time for that, you can of course force the plan to run at max.1, not sort data and use less memory. But this was a very interesting thing. And I had to do a little bit of research on the batch mode sorting to make sure that I was correct in this. So, so I talked to some nice people who, who worked with the product and they said, yeah, that’s true. And I said, yeah, that’s great. So yeah, batch mode on rowstore, using windowing functions, when you get a parallel plan, right? Even if you have an index that perfectly puts your data in order for the windowing specification, the parallel plan sort of negates all that and you will still have to sort things. When a serial plan, right? You do not, like you see the, the can trust the order coming from getting stuff from the index and you do not have to sort things.

Reading from big tables with a single thread, even in batch mode does take more time, right? 5.2 seconds versus 829 milliseconds. So really depending on what you want to optimize for, you might choose to hint max.1, get rid of the memory grant, take a little bit longer, or say, screw it, use the memory, use the, use the extra threads, finish as fast as you can.

Anyway, that was about it there. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I’ll see you in tomorrow’s video where we will talk about, I don’t know what kit. Well, maybe I’ll just make it up as I go as usual. All right. Thanks for watching.

Going Further

The Geometry of Performance Issues

Posted on December 17, 2025January 23, 2026 by Erik Darling

The Geometry of Performance Issues

Video Summary

In this video, I delve into an experiment I’ve been working on to visualize performance issues in SQL Server’s query store using spatial data types. I explore how adding shapes based on metrics like CPU and duration could potentially help users better understand the relative impact of different queries. This is very much a work-in-progress—far from being production-ready, it’s more of a test to see if this approach holds any value for others. I’d love to hear your thoughts: do you think visualizing performance issues in this way would be useful? Or does it just add unnecessary complexity? Your feedback will help me decide whether to continue pursuing this idea or focus on other areas.

Full Transcript

Erik dishwaskeldarling here with Darlene Data. And in today’s video, so I’ve been tinkering with some big ideas lately, right? And one idea that I had was to try to use, so like SQL Server for years has had this spatial data stuff in it. And one idea that kind of occurred to me was no one ever uses this. I wonder what it’s all about. So what I wanted to do was try to attempt to add some shapes to query store. So that when you’re looking at the results, you might be able to infer a little bit of the sort of sense of proportion of performance problems that one query might be having or causing. So we’re going to talk about the geometry of performance issues in this video. And this video, honestly, is a little bit of market research because I would love to hear if you think this is something that would be useful to you and something that I should pursue working on. Right now is just sort of something that I’ve been testing out a bit and marinating on. It is nowhere near complete or what I want it to be. But I think you might be able to see a little bit of kind of where I’m going with it. So I did want to talk about it to sort of figure out if anyone would care that it’s there.

Or if you would just stare at it, you would just see a new tab show up called like spatial results and scratch your head and walk away. So we’re going to do that in this video. Down in the video description, if you would like to hire me for consulting, buy my training or support the content on this channel, you can do all three of those things there. Those do require giving me money. You can also do things for free like ask me office hours questions. And of course, like subscribe and tell all your friends. Hopefully your friends were not affected by the recent AWS outage. So we do, we do, we do pray for their AI souls if they were. Past Data Community Summit coming up in Seattle, November 17th to the 21st.

Kendra Little and I banging out two days of the most magnificent T-SQL pre-cons that have ever been endured on this planet. So I do hope to see you there. I will have all sorts of neat gifts to give you, to give those who show up. If you don’t show up, beat it. I don’t have no use for you. Anyway, I still haven’t, I still haven’t changed this. I still haven’t quite thought of a good November theme. My birthday is in November, so maybe it’ll be my birthday month theme. Maybe I’ll do that. That sounds nice, right? Cool. Anyway, let’s talk about what I’ve been doing over here. So apparently I just left that up.

So what I’ve been trying to do, and I just stapled this on to the very end of the last Quiky and Quickie store. This is by no means production ready code or what things will look like when they’re done. I’ve got a thoughtfully named column here called G, which attempts to draw a polygon using geometry in the ST Geom from text. And just to start off, I decided to use total CPU and total duration.

I really, in order to get the shapes to be visible, I really had to jack up. I had to do some math on the count of executions for this, because otherwise the squares were like this big, which wasn’t very helpful. And then I thought that, you know, maybe I would look at some slightly different metrics if what I was troubleshooting were a parameter sensitivity issue.

So I’ve got another column down here called P. I didn’t format P quite as nicely as G. Let’s fix that on the fly. There we go. And this one looks at some slightly different metrics, but I still have to really jack up the count of executions locally in order to get things to sort of behave the way I want them to. But anyway, when we run a query store now, and I’m going to just do this from scratch so you can see what happens.

We get some results back, and I haven’t set up to look at like a specific parameter sniffing thing or anything. I just want to show you kind of like what the general layout and stuff is, as well as some of the sort of limitations on spatial results that I can’t do much about. So we get this new tab back up here, right? It’s called spatial results. It shows up between results and messages.

And by default, it will go to the first spatial column. You can see over here, that is the column that I helpfully alias G. And if we want to change the label, we have to do that manually. I cannot currently change. I cannot currently set up a default label. Now, what’s annoying is when you first choose the label column, it doesn’t actually label anything.

But if you just click on the zoom thing, then it shows up. So that’s kind of interesting. But then now we can at least sort of see the queries that are in here. Another thing that I haven’t quite figured out yet is the stacking, right?

So like all these shapes sort of like blow together and they don’t maybe help differentiate things as well as they could. There’s also some that are clearly missing labels, right? I think the labels just get hidden if the shapes have a shape over them or something.

I’m not really sure how that works, but I don’t know. Like some or maybe I don’t even know where this one is supposed to be. And maybe this one was supposed to be here. I honestly don’t know what’s going on in spatial world.

But it is kind of cool that if you hover over, well, of course, it’s going to disappear. If you hover over one of the shapes, you get some information back. One reason why I might need to separate this out into a different result set is so that I can sort of choose the columns that I want here.

By default, it just lists out the columns and the ordinal position of the table, which is not the most helpful thing in the world. So I’m probably going to need a second result set if I want to show more pertinent information in here and not have like the beginning of the query plan XML showing up. Because why on earth would you need that?

Right. But not very helpful to anyone. So the other thing that I can’t do or maybe maybe I can, but I haven’t quite figured it out yet is a way to label the accesses on this thing. Right.

Because we have these two lines of numbers going up and down and across. But what are they? Right. Well, for this, it’s total CPU and total duration. Right. Which, you know, I know because I did it. But if you ran this and got this back, you would say, what are these numbers?

What do they mean? I don’t get it. Right. So maybe figuring out a way to label things and there would be helpful as well. But if we change the spatial column from G to P, we will get some slightly different squares back.

We still maintain the query text label, which is very helpful. And we still get back, I guess, depending on precisely where we click some, you know, the same sort of pop up with the columns that are in there. This block is apparently forgotten to be labeled as his.

Well, I mean, probably the one hiding behind this one has forgotten to be labeled as well. So obviously some limitations, obviously some stuff in here that needs to be like worked out probably mathematically. I’m not quite sure how to do that yet.

I’m also not quite sure how far to sort of pursue this. Given some of the limitations and some of the little annoyances, it might just be more confusing than it is helpful to a lot of people. It might have to be like an optional thing that gets sent back.

Like if you are like cool with however this works. And then, of course, there’s also this sort of dynamic bit of, you know, figuring out, you know, what metrics to draw the squares by. If the count of executions is sufficient to draw a square where the size, you know, is visible on the screen.

So there’s like some stuff that really has to like, you know, get, you know, figured out at first. But I do want to sort of get a general sense of if you, the community, the people who watch these videos, hopefully the people who use my store procedures like SP Quickie Store would find any of this stuff at all useful when troubleshooting performance things.

Like the query store GUI itself is a godawful disaster. And whoever designed it has clearly just a lot of disdain for humanity and user experience. But they do have colorful graphs, right?

They do have charts and graphs. There are some visuals there that people find helpful. The one thing that Quickie Store doesn’t have going for it, pretty pictures. So I figured I’ll try to draw some pretty pictures.

But there we are. Some things that I don’t want to do are leave people with needing to export data somewhere else, right? I don’t want to have to copy and paste data.

I don’t want people to have to like have like Grafana or Tableau or Power BI to go do this stuff because it’s an extra step, right? Most of the time when I’m working with clients, the last thing I need is an extra step with a dependency on it that might not exist. So what I want to do is just have something that works in SSMS, gives you some feedback on the proportion of a query’s performance impact, and gives you a way to sort of like judge based on whatever metrics you care about, like what stuff you should be dealing with.

So if you like it, let me know. If you don’t like it, think it’s stupid, say, Eric, stop wasting your time on these fanciful feats. Go back to work.

Go spend time with your wife and kids or something. I don’t know. But feedback is requested. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

I hope that you will give the feedback that I have requested. And I don’t know. We’ll figure it out from here, won’t we? All right. Thank you for watching.

Going Further

Introducing sp_QueryReproBuilder: Quickly Get Executable Query Examples from Query Store

Posted on December 16, 2025December 5, 2025 by Erik Darling

Introducing sp_QueryReproBuilder: Quickly Get Executable Query Examples from Query Store

Going Further

SQL Server Performance Office Hours Episode 40

Posted on December 15, 2025December 5, 2025 by Erik Darling

SQL Server Performance Office Hours Episode 40

Questions:

I know that you praise indexed views as the best alternative to columnstore in Standard Edition. If their tragically limited syntax supports my query, how do you feel about using them for performance improvement on Enterprise Edition?
You mentioned you like select into #tmp and creating the indexes after. I find not having control over the nullability of the column is a bit problematic if you want a primary key after. Is alter table #tmp add unique cluster sufficient? Or do you have any tips on controlling the nullability, even isnulling it doesn’t seem to help sometimes.
What is your choice of background music when working away on T-SQL?
Had any surprises, good or bad, with Accelerated Database Recovery?
Hi, Erik! Can scalar UDFs be sniffed the same way as stored procedures?

To ask your questions, head over here.

Going Further

PASS Data Community Summit SWAG-A-THON!

Posted on December 12, 2025October 24, 2025 by Erik Darling

PASS Data Community Summit SWAG-A-THON!

Going Further

A Follow Up On Fixing Parallel Plan Row Skew In SQL Server

Posted on December 11, 2025January 23, 2026 by Erik Darling

A Follow Up On Fixing Parallel Plan Row Skew In SQL Server

Video Summary

In this video, I delve into the issue of parallel skew and share a practical solution to address it in SQL Server queries. Parallel skew can be a tricky problem, especially when dealing with highly skewed data distributions that lead to inefficient thread utilization. I demonstrate how creating an artificial distribution by using a `VALUES` clause and joining it back to the main table can help achieve better thread distribution while also making the query more batch-friendly. This approach not only improves performance but also showcases the importance of considering parallelism in query optimization, particularly when natural solutions are insufficient. Join me for the next video where we’ll explore other aspects of SQL Server query optimization!

Full Transcript

Erik Darling here with Darling Data, and in today’s video, we are going to talk about how to fix parallel skew, or rather how I fix parallel skew for a particular query. All right. I can’t pretend this is going to be the thing that you do every single time you have this problem, because this is hopefully a rare problem, at least in today’s day and age, but you can still run into it plenty of times. Lord knows I do. And I find it interesting, and I’m going to talk about this. I’m going to be a quick-enough to talk to you about, and I’m going to be able to tell you a bit about it. And I’ll talk to you about it. So, if you find this interesting, well, stay tuned. Down in the video description, all sorts of helpful links. Perhaps you have lots of parallel skew, and you would like a young, handsome consultant with reasonable rates to come fix it for you. You can find all sorts of helpful links to do that. You can also buy my trainings. You can also support this channel with money, not just with various forms of internet applause. You can ask me office hours questions. That is a totally free endeavor for you. And I will answer five of those every Monday. And of course, if you enjoy this content, please do like, subscribe, tell a friend or two, tell your AI girlfriend who hopefully wasn’t too terribly affected by the recent AWS outage. We pray for her well-being.

I will be at Pass Data Community Summit in Seattle, November 17th to the 21st, giving two days of the best T-SQL pre-connery that has ever existed with Ms. Kendra Little. And then, I don’t know, well, hopefully I can talk about my other upcoming stuff soon. It’s just, people are very slow to announce things. Apparently, they just like to wait for excitement to boil over. Anyway, let’s talk about this parallel skew stuff. I’m going to make sure I go to the right copy of Management Studio here. So, let’s start off on this tab, because this tab is going to show us why we have such skewy problems with these.

So, what I’ve done with the votes table is something kind of mean. And so, to give a little background on this, when you have very skewed data, that can lead to very skewed parallelism. And in the public version of the stack overflow database, swallowed a bug. Most of the voting history has been anonymized, of course, because voting is a private endeavor. It should not be made public.

And so, what I’ve done is I’ve made a copy of the votes table. And I’ve set the user ID column equal to whatever the vote type ID was. So, vote type ID is a number from like 1 to 8 or something that has various types of voting that people can do on questions, comments, and answers, and whatnot. Well, I guess not comments in this table. But, so what I’ve done is I’ve effectively made a very skewed user ID column.

And I’ve created an index and all this other good stuff. But, this is the way that the user ID column breaks out. So, for vote type IDs between 1 and 4, these are the most common vote types. Well, actually, three of them are very common. One of them is not terribly common.

But, vote type of 2 is an upvote. Vote type of 1 is accepting an answer as the answer, the little green checkbox. And a vote type of 3 is a downvote. So, sorry, upvotes, accepted answer, downvote if I have my voting history correct here.

And 4 is like offensive or something. So, not too many offensive flags. Because Stack Overflow is a place for friends. It’s also going the way of MySpace, a place for friends at this point.

But, at some point, I’m going to be giving demos on Stack Overflow. And someone’s going to be like, what is Stack Overflow? I’ve never heard of it. It’s going to be funny.

Anyway. Anyway. Yeah. So, this is the way that these integers spread out. And these are the numbers for those. Because we have a group buying account and whatnot.

So, 37 million, 3.7 million, 3.5 million, and 733. All right. So, when we run a query that does, like this, that does this outer apply and whatnot. And we look at the query plan.

You may remember this from the original video that I did. And we come over and we look. I mean, not there. The tooltip is utterly useless for discerning what our problems are here. If we look at the way that rows were distributed across our DOP 8 plus 1 threads, you’ll see that they all ended up on thread 3.

Right? That number there would be the total count of the 37 million plus the two around 3.5 millions plus the 733. They all ended up.

So, the parallel page supplier runs this hashing function. And depending on how things hash out, that’s how the rows get distributed to threads. It’s dealing out all these rows. And unfortunately, our hashing function kept hashing these things out this way.

Now, sometimes when you do this, you can set DOP to different numbers, like especially odd numbers, in order to get the different thread distributions. But I tried that for every DOP between 2, which got me a serial plan all over again. So, theoretically, in reality, it was really between like 3 and 16 in order to get a different spread here.

But they all ended up, I mean, not always on thread 3, but always all on a single thread. And you can sort of visualize how this happens a little bit differently if you rewrite the query to turn this from one seek into, I guess, four different seeks. So, if you do one select for vote type ID 1, one select for vote type ID 2, and one for 3, and one for 4, and so on.

And then you get the dense rank over those. Then you get a slightly, well, you get a very different execution plan, but it helps you to sort of visualize things a little bit, right? So, if you look at these, we get one, two, right?

You can tell that’s 37 million, or sorry, I think that’s, yeah, that’s 3.7 million. That’s 37 million, probably. That’s 3.5 million.

And there’s our 733, right? So, we can see all four of the individual seeks when we do this. And if we go look, again, not at the tooltip, which keeps really making its presence felt here. We’ll see all of the rows end up on thread 1 here, right?

Which is, you know, not great still. And if we look down here, we’ll see all 37 million rows, well, they all ended up on thread 1. And then down here, there’s 3.5 million rows all on thread 1.

And over here, there’s 733 rows all on thread 1. So, all of these ended up hashing out to the same thread. Right?

Just in different seeks. And then if we come over about here and look, we’ll see thread 1 gets all 44 million some odd threads, rows on one thread. So, things do not end up better when we do this.

SQL Server does not like, unfortunately, SQL Server does not like spread those out better or do anything else smarter. It all just sort of ends up a complete washout. Now, the one way that I was able to rewrite this query in order to get better thread distribution was to do this.

So, rather than correlate on the four vote type IDs that I cared about, remember, because in the original query, what I had done was written where vote type ID is between 1 and 4 here. Right? So, we kind of narrowed it down.

We kind of forced ourselves to have a crappy parallel distribution on this one. But it’s a lot easier to do that intentionally than it is to get it to happen by accident. So, we kind of forced the issue on that one.

But what I was able to do to get this one worked out was to sort of nest things a little bit and put the numbers 1 through 4 into a values clause. Right? And call that vote type.

Alias that column that comes from these as vote type ID. And then left join the select here and join that on vote type ID there and say where user ID equals user ID out here. Right?

So, oh, it’s still logically a correct query. And when we look at the query plan for this one, this is kind of at the intersection of fixing parallel skew and being batch mode friendly. Because what you’ll notice in this plan, which is going to be different.

So, like, just to come back to these a little bit. You see we have a parallel nested loops here. And for this one, we have a parallel nested loops here as well, if I’m remembering correctly. Ah, I was.

Wonderful. Yeah. So, parallel nested loops. Good, good, good. Everything correct there. Now, we get something different. We get hash joins. Right? We have two hash joins instead. And if we look, we have this constant scan.

So, this constant scan is going to be the four rows, the numbers one through four that I put into that values clause. And if we look, I mean, there’s obvious signs that we got batch mode, like the window aggregate here. The important thing is this query finishes in about four seconds.

And the other queries over here, this one finished in 29 seconds. And this one finished in 59 seconds. So, I guess writing the four seeks out as multi-seeks was at least somewhat helpful by about 20 seconds.

But probably not where we want things to be anyway. But this query, if we come over and we look at the index scan over here, again, not the tooltip. We’ll see that the rows ended up hashing out very nicely on this one.

Right? I mean, there’s a little outlier here. Like, this is maybe not some, maybe not perfect, but way better than all 44 million rows ending up on a single thread. So, this is sort of at the intersection of fixing parallel skew and writing batch mode friendly queries, right?

Somewhere in there. But, yeah. So, you know, obviously, this is happening in batch mode. If you look up there, this sort will be happening in batch mode.

And since this sort is a child aggregate of this batch mode window aggregate, this sort operator is a child operator of the batch mode window aggregate. There we go. Batch mode sort, blah, blah, blah, blah, blah.

Then the window aggregate can read from the batch mode sort using parallel threads. Usually, the producing side of a batch mode sort will be single threaded, but not when there’s a window aggregate involved. And so, we get batch mode pretty much across this whole thing.

And this is exactly what we want. It’s just batch, batch, batch all the way down. And we get a much faster query with much better thread distribution. So, when you’re dealing with parallel skew, particularly like the kind that you cannot resolve naturally, maybe by forcing a different dop.

Again, particularly odd number dops tend to fix stuff like this. But it didn’t happen for me here. So, I was forced to rewrite the query in sort of a weird way.

But, you know, I’ve used the values trick in other contexts before in order to get different query transformations from things. Usually, you would take values and cross-apply to them. But doing cross-apply here, or it would be outer-apply because it’s a left join.

Using outer-apply here produced the same skewed parallel nested loops plan as before, which didn’t buy me anything. But doing the join, that means you can’t correlate inside of this. You have to do it outside of this.

So, you actually force SQL Server to do all this stuff and then join onto it here and then correlate to the user ID that gets produced here after that. So, it was a much more efficient use of threads and times. And, of course, getting batch mode involved was a very big help in making this thing go much, much faster.

Because we are processing a lot of rows, right? 44 million rows. You want batch mode for that many rows. That’s a good number of rows to batch up and not deal with on a row-by-row basis.

Because internally, that’s what row mode is. One row at a time. And batch mode is, of course, multiple rows, SIMD instructions, CPUs, and all that. So, this was just a fun way to rewrite this query to, A, fix the parallel skew, and as a happy side effect.

A very happy side effect. There are no fiascos, just happy screw-ups. I’m going to skip the alliteration on that one.

But this is just a happy accident that batch mode also got involved when I changed the format of the query. Anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you over in tomorrow’s video where we will abandon all parallel skewness. All right. Thank you for watching.

Going Further

How Many Plan Variants Can You Get With The Parameter Sensitive Plan Optimization In SQL Server?

Posted on December 10, 2025October 24, 2025 by Erik Darling

How Many Plan Variants Can You Get With The Parameter Sensitive Plan Optimization In SQL Server?

Going Further

Faster Better Wronger

Posted on December 9, 2025January 23, 2026 by Erik Darling

Faster Better Wronger

Video Summary

In this video, I delve into an often-overlooked aspect of query tuning: ensuring that the results are correct and sensible. Erik Darling from Darling Data highlights how optimizing a query for speed can sometimes lead to incorrect or nonsensical results if the underlying logic is flawed. He demonstrates this with a real-world example, showing how a poorly written query can produce absurdly large numbers due to incorrect joins, leading to misleading data. The video walks through fixing these issues by rewriting the query and emphasizes the importance of validating both the correctness and sensibility of the results, especially when working on complex queries that affect multiple tables.

Full Transcript

Erik Darling here with Darling Data. And we’re going to talk today about an important aspect of query tuning that does not get enough attention. And that is making sure that the, not even necessarily, I mean, of course, necessarily the results of the query that we made faster, like making sure that those are correct. Like maybe comparing those to the results of the original query, but also making sure that the results of the original query that doesn’t really make any sense whatsoever. Because despite all of the good intentions that exist in the world, people may write queries with incorrect logic. They might write queries in a way that does not return sensible data. We all need sensible data in our lives. I’m not talking, of course, about no-lock hints, which only dangerous and irresponsible people use in their production queries. We’re talking about just general logic and correctness. So we’re going to talk about that a little bit today.

Down in the video description, you will see all sorts of helpful links. You can hire me for consulting by my training, become a supporting member of the channel, be an official Darling Data supporter. And you can also find links to Ask Me Office Hours questions, which are, of course, free. Yay! And then, the usual, if you enjoy this content in any way, shape, or form, however minor, please do like, subscribe, tell a friend so I can get a bigger YouTube or something.

Okay. Seattle. I’ll be in it. November. With Kendra Little doing two days of the most fantastical T-SQL pre-cons that the world has ever known. I’ll have all sorts of the swaggerific new stuff there, stickers and t-shirts and whatnot.

And I will also be giving all attendees access to all of the T-SQL training. So you’ll have that going for you, too. So think of all the wonderful things you’ll get for free just by showing, by having your employer pay for you to show up. Right? It’s a good exchange. This is absolutely bribery.

So let’s go talk about query correctness stuff. Now, that’s the wrong thing. Cool. All right. Let’s pretend that didn’t happen. And let’s go over here and let’s look at this query. This query is slow. Right? If we look at the execution plan for this query, it ran for one minute and 23 seconds.

Not a good time that right. Took a long time to run. We can see all sorts of strange things happen in this query that we don’t like. Right? All sorts of things that we would never want to see. Right? We have bad estimates and we have long running nested loops joins because of those bad estimates.

And everything just goes to hell in a handbasket. But what really goes to hell in a handbasket are the results. Someone wrote this query here. Now, we could do a bigger top here, but we would be here for a long time.

This took a minute and 23 seconds for one user. If we had more users, well, by God, by the time this thing finished, I’d probably be packing it up to go to Seattle, which is in about a month now. Actually, exactly one month now. I should probably book that travel.

But someone wrote this then, you know, we get the top one user and we select from that CTE and we join to posts and then we join to comments. But the way that these joins occur, we have this sort of incorrect multiplicative effect on things. Right? Look at the numbers we get back. John Skeet, despite being an absolutely prolific human being, does not have 236 million total posts and comments.

And these posts and comments scores are also very large numbers. These are like almost foreign phone number long. Right? They are crazy. So how did this happen? Well, let’s say that we wanted to rewrite this query in a way to make it faster. Right?

That would also sort of show us why we have this bad multiplicative effect on things. Right? And why we got such large numbers. So if we rewrite the query like this, right, we’re going to do the same thing here and we’re going to, but we’re going to separate the join to posts out. We’re going to pre-aggregate that.

And then we’re going to cross join to comments. Right? And we’re going to correlate the cross join to the, the user’s CTE down here. So let’s run this query and let’s, let’s, well, first we’re going to note that it is much faster. Right? That finishes instantly, not in a minute and 23 seconds. Good for us. And we get the same results back.

But the only way that we can get the same results back here is by giving like, again, the multiplicative effect. We would have to multiply post score by the total number of comments. And we would have to multiply the comment score by the total number of posts.

And when we would have to multiply the comment count times the post count in order to get the numbers to match. And that’s probably not right. Right? Because we know John Skeet does not have 236 million posts and comments.

And we can validate that. Right? If we go back to the source data and we say, hey, John Skeet, how many posts and comments do you have? Well, these are, these are the numbers that live in the actual tables. Right?

We have about 331,000 post score. We have about 14 and a half thousand posts. We have the comment score of 46,000 and we have a comment count of about 16,000. So like when you look at all this, you might wonder, well, how did we end up with those crazy numbers? Right?

And it’s really because of the way that the two inner joins work. Right? They’re both one to many relationships. And when we join the one to many relationships together, we get many more relationship. Right? Like if we come back to the original query, we joined, where was it? Oh, how far? Oh, those are indexes.

There we are. We joined that CTE to posts. Right? And on owner user ID to ID, that’s a one to, that’s a, well, I guess then backwards, that’s a many to one relationship. But then joining to comments, that’s also a many to one relationship. So we really get sort of this, I don’t, I don’t want to use the word Cartesian product, but I do want to say it sort of explodes the results in an incorrect way.

Where joining posts and comments on these things in the same sort of set of joins, it blows the results up and gives us incorrect numbers. Right? That like these sums and counts here, we’re just way, way, way out of proportion. So if we wanted to rewrite the query in a way to give us just what we wanted back, you know, we could, we could rewrite it like this. Right?

And we could just stick the select top one and join to the pre-aggregation on posts here and then join to the aggregation on comments here. And this would not explode the results. We would still have to add post count and comment count together in order to get the total like posts and comments. But that’s not so big a deal. And this number is correct. And these numbers match what we had in the source table for that one user.

We could also just have taken our original query and not blown things up with the multiplicative effect that we saw from the, the two jo- the two joins that we did that exploded our results earlier. And we would get, of course, correct results back from this. So when you’re writing queries like this, it’s so for me, as a consultant, it’s hard.

And this is why I do like prefer to like do things with like on zoom with like people who have more domain knowledge. So like when we’re reviewing results of things, we can sort of like, you know, like sanity check stuff on our own and be like, like, I can say, hey, like, I don’t know this data very well. You probably know it better than I do. Is there any way that like these numbers are accurate?

Cause this looks funny to me. Like, like, like, you know, like I usually like on a sales call, someone would be like, we’re a blah, blah dollars a year company. And I’m like, cool. Want to pay me more? Like, oh, you, so you have money. Great.

So like, but you know, like then like you might see some reports come back and like, you know, you’ll see like one customer has spent like $10 billion in the last month. And you’re like, well, you sure? Cause that sounds, sounds a little wrong, but these are important things for you to spot check and validate as you are making queries faster. Is that the, not only are the, are the results correct in that, like, maybe like they match whatever previous thing, but also that the results make sense.

Right. Cause you don’t want to give people data that is not sensible back. Right. Doesn’t do you any good to write a faster query that still gives you crappy results. Anyway, that was all I had to say today.

All right. Mind your data. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I think this is dropping on a Friday. So I will see you next week with the office hours, Monday video.

All right. Thank you.

Going Further

SQL Server Performance Office Hours Episode 39

Posted on December 8, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 39

Questions:

What are your favourite alternatives to alerts for Page Life Expectancy? I know that I have memory issues, but alerting on high I/O waits seems silly.
You mentioned you often prefer select into and adding the index later for parallel inserts. Not having real control over the nullability is causing me issues with creating primary keys after select into. Is a unique clustered basically the same thing?
You look like you’re bulking up again. I thought you were dying. Are you on steroids? What is your routine?
What are your lest favorite things to see in a query plan?
I forgot to ask how much protein you ate with steroids

To ask your questions, head over here.

Video Summary

In this video, I dive into answering five user-submitted questions for free during a live office hours session. We tackle topics ranging from alternative methods to monitor page life expectancy when dealing with memory issues, to the pros and cons of using `SELECT INTO` for parallel inserts and adding indexes later. Additionally, we explore what I consider less favorable elements in query plans, such as eager index spools, unnecessary scans, and parameter values embedded directly into the query text without being reflected in the plan properties. The session was packed with valuable insights and practical advice, making it a great watch for anyone looking to improve their SQL Server skills. If you have any questions or topics you’d like me to cover next, feel free to drop them in the comments below!

Full Transcript

Erik Darling here with Darling Data and today we’re going to do office hours in which I answer five, 1, 2, 3, 4, 5, that’s this many fingers, user submitted questions for free, right, that’s a whole point I guess. Down in the video description if you would like to ask a question there’s a link down below where the fingers are pointing down below just look, look down, right, my links are down here pal. And you can also find links there if you if you would like to hire me for consulting, buy my training, become a member of the channel in a paid way to support my endeavors here. And of course, the usual, if you like this blah, blah, blah, like, subscribe, tell a friend, you know, that, that. This Seattle, I’ll be in November with Kendra Little teaching two days of the finest T-SQL pre-cons you have ever seen. I will have all sorts of new swag-er-ific items for you, T-shirts, stickers, training materials. So there is no reason for you not to show up, is there? No, you can’t, you can’t give a good re-, you can’t give me a good reason for that. So, let’s answer these questions, right, let’s do, let’s do this thing, cuz I guess I promised you I would. Uh, what are your favorite alternatives to alerts for page life expectancy? I know I have memory issues, but alerting on high IO weights seems silly. Well, if you have memory issues, there are certainly going to have high IO weights, so why is that silly. Um, alerts? I don’t know, like, I, I don’t, I am not in a situation where I receive alerts from servers, so I don’t think a lot about what I, what I would, uh, what, what, like, alerts I would send myself, uh, in, in the course of the day. Um, you know, for me, it’s, it’s more general monitoring. Um, you could look for spikes in page IO latch SH or EX weights. Those would be worth, uh, worth monitoring.

I think for me, uh, if I was sure that I had memory issues. Um, I also may want to alert on, um, when a significant amount of memory goes towards query memory grants, perhaps even when resource semaphore weights happen. Uh, those are things that I would rather alert on than PLE, cuz PLE is a stupid dump. All right. Uh, oh, I should have, I should have highlighted that question. There we go. Yes. Um, anyway. Um, these are usually how memory issues manifest themselves. So, uh, that’s, that is what I would keep an eye on. Uh, uh, uh, uh, here we go. You mentioned you often prefer select into and adding the index later for parallel inserts. Not having real control over the nullability is causing me issues with creating primary keys after select into.

Uh, is unique clustered index basically the same thing. Uh, so to, to hopefully remedy your first situation, if you wrap whatever column you wish to not be null, uh, in your select list in the is null function and give it an appropriate replacement, uh, your, the resulting column will, uh, have a not null, uh, thing to it. So use is null. You can’t use coalesce for it. Coalesce doesn’t work, but is null. Uh, if you wrap your column in that, the result will be a not null, uh, not nullable column. Um, is unique clustered index basically the same thing? Yes. Aside from the fact that a unique clustered index will allow one null value in there.

So, uh, depending on how you prefer to approach this, uh, you could add a unique clustered index. If you’re sure there are no nulls in there, or you could also wrap the column in, uh, is null and have a better time. Okay. Uh, come on, cursor. Where are you? Oh, you’re over there. Ah, there we are. All right. Uh, you look like you’re bulking up again. I thought you were dying. Are you on steroids? What is your routine?

Uh, all right. Well, um, I guess thanks for noticing. Uh, I, I, I, I have, I have been, uh, a little bit better about, well, actually I’ve always been good about the gym, but, um, um, I, I have been good about, um, I guess more, uh, progressive training. Um, I am not on steroids. Uh, I, if, if I were on steroids and I still looked like this, I would get my money back. Uh, my routine is generally, uh, basic barbell exercises, squats, deadlifts, bench press, overhead press, uh, depending on, uh, what I’ve done.

Uh, you know, deadlifts typically one set of five squats, either a three sets of three, three sets of five, uh, somewhere in there. Um, if, if I’m feeling real wiped out, I might do a lighter weight on squats with three sets of eight, uh, or three sets of 10, just to get some extra work in. If, if, uh, my legs are feeling particularly burned out, uh, overhead press, I do one day of, uh, 10 singles at a pretty heavy weight.

The last, the last one I had was, uh, 200. So, uh, for the 10 singles. And then, uh, I’ll also do a slightly lighter day on overhead press for, with three sets of five. Uh, also in the mix, I’ll, uh, usually make room for some rows, some, uh, chin-ups, uh, in there.

Uh, occasionally, uh, I guess RDLs. Those are, those are nice hamstring work. Uh, if you get them heavy enough. And, uh, yeah, that’s about it. I don’t, I don’t, I don’t do anything. Uh, I don’t have like a, a bodybuilding routine with lots of hypertrophy and whatnot mixed in.

Uh, to do. What are your less favorite things to see in a query plan? All right. Lest, lest we forget. Uh, I don’t know. Um, okay. Let’s think of some stuff in here. Uh, I hate when I see spools in a query plan, uh, especially eager index spools.

Um, uh, lazy table spools are also not, not something that I usually love seeing. Uh, I don’t like when I see really big lines going into a sort operator. That usually tells me we have, we have an issue.

Um, uh, let’s see. Uh, I dislike seeing a top above a scan. That’s another one that I don’t like to see. Uh, and I don’t like when I see, uh, the sort of constant scan, um, concatenate stuff, sort merge interval, and then a nested loops join.

Because that usually tells me that, um, we either have a join with an or clause or that we have, um, a mismatched data type. That quite frequently, if you have, like, let’s say, a date time, uh, uh, date time to column, and you have a date or a date time parameter, SQL Server has to do some extra work to, to work things out. So those are things that I typically dislike seeing.

Um, of course, you know, the usual stuff, um, unnecessary scans, right? Like you have a, you have like a scan where there’s a predicate on it that’s not a bitmap. You’re like, oh, maybe I should create an index there.

Sometimes you’ll get the helpful little green text, sometimes not. But, um, you know, those are things that I typically dislike seeing. If I have an actual execution plan, uh, that’s a little bit different because then I have operator times to sort of guide my tuning efforts in a, in a smarter way. Uh, estimated plans, you know, you can spot some red flags in those, but typically, um, you know, you’re like, eh, uh, it takes a little bit more effort to figure things out.

Um, table variables, uh, dislikes, uh, table variables and plans. One thing that I, uh, really dislike in, uh, seeing an execution plans is when like there are parameter values, uh, like as part of like the query text. But then you go and you look in the, the, like you get the properties of the root operator in the plan and there are no parameter values in there.

Cause that usually tells me, uh, unless there’s a recompile hint where the literal values get, where the parameter embedding optimization kicks in and you get literal values in the query plan. That usually tells me that someone is using local variables and I think, hmm, I shake the fist at you. Um, not necessarily because the local variables are causing a problem, but just because I can’t get the values for local variables.

Cause SQL Server does not sniff and use them, right? Like it does with a parameter. So those are things I dislike seeing. There’s probably others if I thought, uh, I forgot to ask how much protein you ate with steroids.

Uh, all right. So once again, I’m, I’m clearly not on steroids. Um, I, I, I, I, it’s not, not my thing.

Um, you know, life is hard enough without trend making me emotional. Uh, but as far as protein goes, um, I drink two protein shakes a day, uh, that are 50 grams of protein a piece, uh, unflavored Isopure, uh, powdered Pedialyte, some, uh, psyllium husk to, to keep things. Uh, orderly.

And, uh, I, I have the scoopable creatine. So I’ll do 10 grams of creatine a day and across the two shakes. Um, and then of course you must, you must eat real food. What lest you become a skeleton.

Uh, and I usually try to get around another hundred or so grams of protein from actual, uh, meat sources. So chicken, steak, stuff like that. Uh, not a big fan of pork.

Um, not a religious thing. Just, you know, it’s not, it’s not that it’s bad. It’s just, I, I like other stuff better. Uh, so I think, I think that about does it.

Interesting mix today. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video, uh, whenever that may be. Uh, I guess tomorrow.

All right. Thank you. Goodbye.