create temp table sql Archives | Page 17 of 139

SQL Server Performance Office Hours Episode 31

Posted on October 1, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 31

Questions:

I have a very slow delete statement (44 mins for 2000 records). Execution plan shows painful Clustered Index Delete is culprit. Parent and child foreign keys are indexed on two associated tables. Where else to look? Merge join is feeding problematic operator but associated sort isn’t causing pain. What would you check next?
What topics will be covered in the advanced material of your T-SQL course?
I’ve been thinking about career goals. Becoming an MVP sounds good. I already do a lot for the community. Good idea or bad idea?
You often mention pessimistic read committed holding locks for too long because of key lookups WITH UNORDERED PREFETCH. Does Read Committed always show WITH UNORDERED PREFETCH in plans where it hold locks for too long?
In theory, with the Microsoft push of all things to the cloud, query tuning and optimization should be a major priority for companies because CPU time (CUs) is money. In your experience, are companies actually lowering costs through query tuning or are they more focused on improving speed and reducing loads? I rarely hear stories of companies reducing Azure capacities or lowering licensing costs due to query tuning but I don’t have much exposure to it.

Video Summary

In this video, I dive into some interesting questions and discussions from our recent Office Hours session. We tackled a slow delete statement that was taking 44 minutes to process just 2,000 records, which led us to explore the execution plan details and suggested looking at actual rather than estimated plans for more accurate performance metrics. Additionally, I shared insights on my upcoming advanced course content, emphasizing its in-depth nature and wide breadth of advanced topics, from T-SQL to data storage strategies. The conversation also touched on career goals like becoming an MVP and the realities of query tuning in cloud environments, highlighting both the benefits and challenges companies face when optimizing their SQL Server workloads for cost savings or performance improvements.

Full Transcript

Erik Darling here with Darling Data and we have a very, very exciting episode of Office Hours today. We are joined by absolutely no one. Who would want to stand here? Have a bad time. So, if you look down in the video description, you’ll see all sorts of helpful links where you can hire me for consulting by my training, become a Supporting With Money member of the channel, and even ask me Office Hours questions. If you enjoy this content, and you want to support me in some other way that is not giving me money, you can always like, subscribe, and of course tell a friend if you think that this channel would help them and whatever they’re dealing with in their life. Usually it’s SQL Server, but of course people find lots of solace in this channel for other reasons. If you want to see me live out in the world, the nice folks at Redgate are dragging me, well, to a few different places, at least through the end of the year. We got Dallas in September the 15th to 16th. We got Utrecht, that’s the one in the Netherlands from October 1st to 2nd. And we have Past Data Community Summit in Seattle from November 17th to the 21st, where I will be doing two wonderful days of two T-SQL pre-cons with Kendra Little.

And we do look forward to seeing you there. All right. Anyway, let’s talk about Office Hours, because we’re going to have some stuff to go through here, don’t we? We have to answer five questions, not an accidental four like last time. So that’s one, two, three, four, five. I didn’t mess that up. I can count. Wonderful. We have passed the first test of Office Hours.

All right. I have a very slow delete statement. 44 minutes for 2,000 records. All right. The execution plan shows a painful clustered index delete is the culprit. Okay. Parent and child foreign keys are indexed on two associated tables. Where else to look? Merge join is feeding problematic operator, but associated sort isn’t causing pain. What would you check next? Well, I’m going to go out on a limb here, and I’m going to say that you are looking at the estimated execution plan and not the actual execution plan.

If you’re looking at the actual execution plan, you would see helpful things like operator times, unless you’re using like SQL Server 2012 or something, in which case, well, you have bigger problems than this delete statement. So what I would suggest is to either find a place, a development server, where you can run this query and get the actual execution plan, or profile your production server in a way where you can catch this slow delete running. So, you know, just between you, me and the wall, or the camera, or the thing behind me, there are some things that you should look at first.

First, make sure that this delete statement was not just blocked for 43 minutes and 59 seconds and then finished in one second, because 2,000 records is not really where I’m going to think, oh, this makes total sense that this would take 44 minutes. So, you could look at the underlying table. Who knows?

There could be all sorts of things there, like it could be way over indexed, have like 999 indexes on it or something. I suppose it could be like a, there could be a columnstore index involved. That might slow things down a bit, but not like 44 minutes slow it down.

So, my suggestion would be to, you know, get some contextual information, make sure, like, you know, maybe fire up the block process report and make sure that this delete isn’t just getting blocked for a very long time. And then, absent a development server with sufficient data to reproduce a long-running delete, I would profile the production server in a way where I can collect the actual execution plan and not rely on very misleading cached or estimated execution plans, either from the plan cache or query store. There are various ways of doing that.

That SP human events, my store procedure, does give you that ability. So, I would maybe do that. Otherwise, you know, really just stop looking at estimated execution plans and looking at operator costs as being a good source of durable performance metrics because they are not.

All right. Next question. What topics will be covered in the advanced material of your course?

A lot of them, and they’ll all be advanced. So, there’s a lot of stuff in the beginner content, which, of course, also has an advanced component. And then there’s also sort of maybe not directly T-SQL things, but things, you know, around performance, maybe some things around various, you know, ways of storing data that has upsides and downsides, things like that.

But there’s going to be a lot in there. It’s probably too much to really list and go through here, but it’s going to be a very, very in-depth and have a wide breadth of material of an advanced nature. So, keep your eyes out for that.

I am currently, I’ve written a lot of the starting, well, not starting. I’ve written a lot of the, you know, like early modules in it. And as soon as I have like a little bit more of that done, I’m going to get that tech reviewed and start recording it.

I’m going to release the material for the advanced course a little bit differently than I did for the beginner course. I think I’m going to piecemeal it out in some smaller batches. Whereas with the beginner stuff, I really like put out a lot discontinuously over like two or three weeks until it was done.

This one, I’m going to release some sort of smaller chunks, I think. Next question. I’ve been thinking about career goals.

Really? How old are you? You’re just thinking about them? All right. Becoming an MVP sounds good. Well, if it sounds good to you, don’t, why are you asking me? I already do a lot for the community.

Good idea or bad idea? I don’t know. I would personally never do it for various reasons that I will not go into here. But it would not be something that I would consider.

But if you already do a lot for the community and you feel that your contributions are ones that Microsoft would see sufficient free marketing labor in, then you should go ahead and become an MVP. You might make some good connections.

You might meet some nice people. You might, you know, get around the world a little bit in that way. But, you know, whether it’s a good idea or not sort of depends on, you know, how humbled and honored you are to carry marketing water for a, what is it, like $250 billion a year corporation? I don’t know.

I don’t know. I don’t know. Who knows? Maybe it would have some benefit for you. Don’t let me stop you. Anyway, let’s go on to the next question. You often mention pessimistic read committed locking holding locks for too long because of key lookups with unordered prefetch.

Well, it could be an ordered prefetch too. It could just, or it could just be an optimized nested loop. Does read committed always show with unordered prefetch and plans where it holds locks for too long?

Well, it’s not holding locks for too long. It’s just holding locks for longer than most people expect and even escalating those locks in a way that causes read queries to block write queries. So, like I said, you will either see unordered prefetch, ordered prefetch, or maybe even optimized nested loops in those query plans.

But more importantly, you should generally see the queries that generated those executable plans in a block process or deadlock report. So, don’t be afraid to use those to track things down as well. All right.

This is a long one. Oh, boy. I’m going to have to think about this one while I’m reading it, which means I might read it wrong. In theory, we do love theories here at Darling Data. With the Microsoft push of all things to the cloud, it’s been going for like a solid decade now.

Not just Microsoft. Lots of people who have cloud to sell want everyone to be in the cloud. Query tuning and optimization should be a major priority for companies because CPU time is money.

It sure is. A big component of my consulting. A little free marketing for me.

Not quite a $250 billion a year corporation, but I’ll do some free work for me. A big part of my consulting is tuning stuff in the cloud so that people need less cloud and spend less money on it. In your experience, hey, there we go.

Well, I led right into that one. Are companies actually lowering costs through query tuning? Well, they are when they work with me. I don’t know about anyone else, but whatever, you know. Or are they more focused on improving speed and reducing loads?

Well, query tuning is improving speed and reducing loads, at least in my estimation. I rarely hear stories of companies reducing Azure capacities or lowering licensing costs due to query tuning, but I don’t have much exposure to it. Well, it does happen, usually shortly after, you know, hiring me.

But, you know, if you’re not really trafficking in that world, you might not see a lot of success stories around it. Now, there is a flip side to that coin, because there are people I work with who went with the absolute cheapest things they could get in the cloud and have terrible performance problems because the hardware is just so, so underprescribed for the workload they’re expecting it to run. But, like, there are Microsoft salespeople out there who don’t know this thing from the thing behind them who will just try to do anything to get people started in the cloud, and they will forecast these terribly small resources and be like, oh, but it’s only going to be this much a month.

And then people get there, and they’re like, our workload doesn’t run anymore. What happened? And they’re like, oh, no, you might have to spend more money.

So, like, but, you know, I end up in some situations where, like, you know, you do have to, you know, hard truth people and be like, look, you, like, the hardware you came from was, like, let’s say, this big, and the hardware you’re on now is, like, this big, and it’s not going well. Like, you, like, there’s no automatic performance benefit just by right of being in the cloud. You still need to, like, do things to make things faster.

So, there are some cases where you do have to, you know, tell people, like, hey, you made a bad choice with this hardware, or some Microsoft sales rep gave you bad advice on this hardware. You really do need more, at least in the meantime, until we can tune stuff, until you can maybe shrink stuff down. But a lot of people have, you know, either perhaps, you know, oversubscribed a bit, or perhaps they’re just unhappy with the cloud build generally.

And they’re like, there’s got to be some stuff we can do to fix this. And that’s where, you know, I come in a lot, and I get queries and indexes and workload various bits and bobs tuned. And then they can reduce the size of those servers without causing any major workload regressions.

So, I hope that answers your question. And perhaps even gives me a nice sales pitch. So, thank you for that.

I appreciate you. Anyway, that’s about it for me. I’m going to go do something else. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you tomorrow where we will talk about some more T-SQL learning. We’re going to talk about, I think, quote name and dynamic SQL, and maybe some other little dynamic SQL tricks that I haven’t talked about 7 million times here already.

So, all right. Cool. Thank you for watching.

Goodbye.

Going Further

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

Learn T-SQL With Erik: Cursor Loops Are Better Than While Loops

Posted on September 30, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Cursor Loops Are Better Than While Loops

Video Summary

In this video, I discuss the merits of using cursor loops over while loops in T-SQL programming, sharing my personal belief that cursor loops are often easier to write and maintain. I walk through a basic example of how to use cursor variables in SQL Server, highlighting their self-cleaning nature and demonstrating how they simplify the process compared to manually managing row increments with while loops. By comparing this approach to yesterday’s video on while loops, I illustrate how cursor loops can be more efficient both mentally and for database performance, especially when dealing with non-contiguous data sets.

Full Transcript

All right, Erik Darling here with Darling Data, as the sign indicates. And today we are going to talk about how I think, it’s my personal belief, that writing cursor loops is much easier than writing while loops. So we’re going to talk about cursor loops in today’s video.

This is of course part of my little itty-bitty teaser bits of my Learn T-SQL course. If you would like to buy that course, hire me for training, become a supporting channel member, or even ask me office hours questions, all of those helpful links are down in the video description right below this.

So I would encourage you to click on those, should you be interested in pursuing our relationship further. And, tilly You feel so kind.

If you’re feeling so kind, you could always like, subscribe, and tell a friend about this channel. Of course, I need to leave the house a few times this year for the Pass on Tour event and, of course, for Pass Data Community Summit. I will be in Dallas September 15th and 16th, Utrecht, October 1st and 2nd, in Pass Data Community Summit in Seattle, November 17th to 21st, where I have not one but two magnificent days of pre-cons with Kendra Little all about T-SQL.

We’ll be covering some of the material that I’m going over here. So with that out of the way, let our database party begin. We will get right into our cursor loops.

Now, like I said in the intro to this video, I really do believe that once you learn how to write a cursor, it simplifies a lot of things that you might have to consider and try to be smart about when using a while loop. So I think probably the best way to write a cursor in order to simplify even that part is to use what’s called the cursor variable.

And the nice part about cursor variables is that you don’t need to close or deallocate them. They automatically are self-cleaning, which I think is nice. So what I’m going to do is just run through a basic cursor loop to just populate some data and just kind of show you like the basic fundamentals of it.

And then I’m going to compare or rather I’m going to do a demo like in yesterday’s video with while loops, where things are sort of greatly simplified for us over needing to think about how to increment and how to find what to work on next. So I’ve got my cursor stuff declared up here.

Zoom, it would be so kind as to actually zoom. We’ve got this stuff up here. And then I’m going to be using a table variable to hold some of our data because I’m just putting a single row at a time in there. I don’t really care so much about the performance of this.

So the table variable is just fine for us here. And then I’m going to set the cursor variable with the options that I want and also tell the cursor variable what the query that populates the cursor looks like. That’s this portion down here.

I talk way more about the cursor options in the full class. So if you’re curious about these, well, please consider purchasing. And then we will open the cursor.

And while that cursor is open, we will do some fetching into the cursor. And then we will, you know, populate some stuff. And what we’re going to do is what I have in here is some additional stuff to kind of show you how the cursor makes progress through all of the values and stuff.

Because what I want you to see is how the fetch status changes as the cursor finishes. And then we will be looking at the results down here. So after I finish highlighting this gigantic loopy piece of code, we’re going to run this.

We’re going to get a result back very quickly with all of the data that we populated our table variable with right here. Over in the messages tab, though, this is where we’ll see sort of how the while loop, rather the cursor loop made progress. And how this is a little bit easier than the while loop equivalent.

See, we didn’t have to tell the cursor loop what row it was going to get next. Like, we didn’t have to increment some value and then go find that value. The cursor loop is based on this query.

And this query here just goes and finds the next thing for us to do anything with. Actually, the cursor keeps track of this position for us. So we got all of the stuff that we wanted.

And of course, while the fetch status was zero, which is for all of these iterations through, we went and we got a new row to work on, worked on what we needed to do. And then at the very end of the while loop, the fetch status changed to negative one. That means that the loop killed itself and we didn’t have to tell it to do anything.

So this is how, one way how cursor loops can be a little bit simpler to write once you understand the basic syntax than writing while loops. Now, to compare the cursor loop to the while loop in yesterday’s video, there were lots of things that I had to do in yesterday’s video in order to figure out like a starting and ending position for the while loop. How to increment data, how to increment to get the next row in the while loop versus like, not like doing a naive incrementing, just like adding one to the next ID to get, having to think about how to increment things so that I was making sure that I got the next value.

So I’m going to show you how this is simplified for the cursor loop. Now, in yesterday’s video, I showed you like both a naive and then a smarter way of doing things. But in the smarter way of doing things, of course, was more efficient.

But of course, in this case, we can get more efficiency without having to worry about thinking about stuff too much, which is nice because we can now think about other things like where to go for dinner and what sort of wine to drink. And I don’t know, like where we want to go on vacation next. We’ll become more efficient people.

And so we have better things to do with our minds than figure out what to do in a while loop, right? Which is not fun. Which is not making good use of time, right? It’s not a productive use of our brains, whereas thinking about food and wine and vacations, terribly productive use of our brains.

So what we’ve got from this table is, of course, the same non-contigious situation as we had yesterday, where like the IDs that we’re working off do not just count up like one through ten or whatever, the way that the row number function does. The row number function rows are over here. So we can see that we have some big gaps in a lot of this between what number is next or not.

So what we can do to write a cursor to do this is pretty much what we did before. But I want to show you just how like we don’t really have to do as much setup for this, right? We don’t have to find a min and a max ID.

We don’t have to decide how to increment. We don’t have to like do a lot of pre-work to figure out our cursor starting place, current position, and ending place. The cursor does that for us.

So let’s run this. And I am going to show you some of the sort of guts of what happens as we run through this. And just like in the last demo, we’ll see that the cursor does a great job of, you know, while the fetch status is zero, just going and finding the exact next row that we needed based on the query we gave it to populate the cursor.

So this is all very nice. And we go through and we don’t like double work anything. And we don’t have any sort of no ops where we like maybe incremented by one and found a row that didn’t exist.

And then at the very end, after we hit 100, we have our fetch status of zero. This does double print, but it does not double work 100 here. It’s just a double printing thing that I don’t know.

I’d say I’d fix it in post, but we’re already posted. So anyway, running through the cursor loop like that, we end up with no dupes, right? Every row has a count of one.

Whereas when we did the naive looping with the while loop yesterday, we had a lot of dupes where we were just like finding where we’re either working with the same number over and over again, or we would have double worked and sort of no opt things. So cursor loops, usually much more efficient mentally and I guess in the database physically than writing while loops, especially if you do not write while loops in a way that intelligently goes through your data and finds what to work on next. So I do hope that you will perhaps spend some time learning how to write effective cursors in T-SQL.

It can be done. I know that cursors get a lot of bad press for performance and that is true. Like cursors can perform poorly when compared to sometimes set-based operations.

But even sometimes cursor loops versus while loops, cursor loops are generally on the more efficient side, at least for us as the writer and maintainer of the code. And especially on the database side, where if we make a mistake with a while loop, we could pay quite a penalty in the database. Whereas if we make a, you know, we can’t, we don’t have the ability to make that mistake with a cursor because the cursor is keeping track of things for us.

And, you know, computers, pretty darn good at keeping track of things. Anyway, that’s it for me. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will see you, well, this is going out on Friday. So the next video will be office hours. And I realized in the last video that I accidentally only copied four questions. And I’ve maybe lied about answering five questions.

So, I don’t know. Should I do six questions in the next one? Or should I just do the normal five? I haven’t decided yet. I don’t want to set a bad precedent here.

Start miscounting and playing catch up and all that. Anyway, thank you for watching. All right. Goodbye. Happy Friday, too. Because you should at least get one of those in your life. All right.

Goodbye.

Going Further

Learn T-SQL With Erik: Writing Smarter While Loops

Posted on September 29, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Writing Smarter While Loops

Video Summary

In this video, I delve into the world of while loops and share some insights that might surprise even seasoned database professionals. We explore how using `continue` and `break` can alter a loop’s behavior in unique ways—like skipping numbers or quitting early when bored with repetitive tasks. Additionally, I discuss the importance of proper incrementing to avoid inefficiencies and ensure your loop processes each value only once. This is crucial for maintaining database performance and avoiding redundant operations. The video also touches on cursors as an alternative that can simplify loop logic and handle increments automatically, making them a valuable tool in your T-SQL arsenal. Whether you’re just starting out or looking to refine your skills, there’s always something new to learn about effectively communicating with databases.

Full Transcript

Erik Darling here with Darling Data, and we are going to continue our T-SQL learning because that’s what we like to do around here. We like to learn about databases, and more importantly, we want to learn how to communicate more effectively with our beloved databases. They are important things in our lives, and we should be able to talk to them openly and honestly about things. So that’s what we’re going to keep doing. That jumped way too far ahead. If you like this material and you would like to buy this full training course, you remember these are just little bite-sized snippets of things. Down in the video description, there are links for all sorts of things that you can do to interact with me, like hire me for consulting, buy this training that you’re seeing little pieces of, become a supporting channel member, ask me questions for my Office Hours episodes, wow! And of course, the usual like, subscribe, tell a friend if you feel like your friend is having me, and difficulty effectively communicating with your database partners in their lives.

And maybe they could learn a thing or two over here and have healthier database relationships. The nice folks at Red Gate are putting me in front of full public display via the Pass on Tour and Pass Data Community Summit events. We’ve just wrapped up the New York event, so now we’ve got Dallas in September, the 15th and 16th, Utrecht in October, the 1st and 2nd, and Pass Data Community Summit in Seattle, November 17th to 21st, where I will have two days of T-SQL pre-cons with Ms. Kendra Little.

And that’s going to be great because then everyone in Seattle is going to know SQL and maybe that’ll help clean up some of Seattle’s well-known maladies out there. But anyway, let’s, let’s, let’s get on with our database party here and let’s, let’s go talk about while loops. Now, there are a couple of things that you can do in while loops that I learned probably a little bit later in my data database career than I feel fully comfortable saying, or fully comfortable admitting to, but that’s okay. You know what? We all learn, we all grow. It’s fine.

So the, the two things that I learned about probably, I, I, no, like continue, I, I, I learned this one, this one came along way later. I didn’t realize this was a thing until I saw someone else use it and I was like, continue, what the hell? But instead of break, I would often just use return instead, but you know, six of one, half dozen of another.

But continue is neat because continue will bring us back to the first begin in the, in a while loop, which is, I think a neat thing to be able to just say, ah, I’m done here. Let’s go back to the beginning. And of course, break will end things early. So let’s say that we have our, our task is to count to 100, but like many people in the tech world, we do not have the patience for repetitive tasks.

So I don’t know, will we get to a hundred? Probably not. Are we, are we, are we going to skip some numbers? We probably are. But this is one way that you can sort of use continue and break in a loop to, you know, make the loop behave maybe a little bit differently than just cycling through and counting from one to one. 100. For example, we can use continue to, let’s say, count up to nine or every time, or every time we get to a new 10, rather a new number that with a modulo of 10 equals zero.

We can just add on 10 and skip ahead a little bit because it gets really boring. And then maybe we might decide that, I don’t know, we’ll get to around 89 or so and we’ll, we’ll just quit. And that’s what our loop will do. Every time we hit a new 10, we’ll skip a few because we got bored.

And then when we get down to the end of our loop, we will say, oh, I got tired. Sorry, not happening for us. So these are neat things, continue and break. And also, you know, I guess return and break would be pretty similar, but you can use continue and break or return to sort of control the flow of a loop a little bit in slightly different ways.

But one thing that is important to consider when dealing with a while loop is, of course, how to increment through things so that you maybe don’t get stuck on repetitive tasks in a loop. So this is something that I talked about in one of the other videos earlier this week, where if you assume that whatever task you’re doing within the loop is working with a, like a perfectly sequential number, then you might run into situations where either you don’t find a next value or something else you do in the loop means that you just keep like you might attempt to run the loop for values that aren’t actually there.

So like if you just say, if you’re just always incrementing whatever loop, whatever is driving the loop by one, you can end up in situations where your loop is sort of ineffective or doing a lot of repetitive work in places. So we’re going to create some tables here for a while loop to work off of. And we’re going to stick some data in these tables.

And this is the somewhat boring part, but that’s okay. We have, we have, we have more interesting things to think about. So we’ve got some data in our worker table. And if we look at the what’s in the worker table and we start looking at the gaps in here, keep in mind that like the ID values that we put in, like, like they do, they are not perfectly sequential.

This goes like where it should, like, you might be expecting it to go like from like one, two, three, four, five, six, seven, eight, nine. I guess I should have gone down to 10 there. Here we’re jumping around quite a bit.

It’s like one, four, six, seven, 10, 11, 13, so this is not a perfectly sequential set of numbers that we’re working off of. This is something that you need to think about quite a bit when you’re writing while loops. And for the reasons that I said before, you don’t want to either do repetitive work or do essentially a bunch of no ops while you’re running through things.

So if we go and we run this while loop doing, let’s, let’s call it naive incrementing, where all we do is set the value plus equal one, then this will run through and do things. But it’s not really going to want, but it’s not really going to do things the way that we want. Because now if we look at what we actually processed, we will end up having with a bunch of like having processed a bunch of dupes until we found other rows, right?

Like these are all values, all of these I values are values that we ran multiple times through the loop until we found a new value. So this is not a good time, right? We ended up like processing parts of this loop either too many times, or like, you know, if we maybe there was like a no op sort of thing, we would have just like been spinning in a circle doing like no work really.

And that’s, that’s not good, right? We don’t want to write inefficient loops because loops are already sort of inefficient generally. You know, like loops and databases, usually not the first thing you want to use. They’re generally set based things you would want to use instead.

There are of course perfectly good reasons to use loops, especially to like batch modifications, but like batch units of work. But you know, we don’t always want to just jump right to loops, right? We want to make sure that we are using the loop for the right reasons and then in the right way.

So if we were to change the way that we increment the loop, and rather than just saying like to increment the loop by one, if we were to set the, if we were to drive our loop by using whatever increment value we found for the last time around, then we would have a much tighter loop that actually does like significant meaningful work on each trip through.

So rather than just add one, we’re going to add whatever increment we find in there. And this loop will have done a less work because you wouldn’t be meaning, meaninglessly spinning through things. But we would have absolutely no dupes processed in here either.

Rather than having like all bunch of fours and threes and fives and stuff. We just ended up, we just, we only process each value once. So each time through the loop, we got a brand new value set to the value that we should be using for the next turn through the loop.

So just be very, very careful when you’re doing this. In the next video, we’re going to talk about using cursors for something similar, but also how cursors do a lot to simplify loop logic. If you are writing a very, like a traditional while loop like this, you know, cursors in these cases will do just about the same performance wise.

So if you’re already doing a loop where you’re incrementing through things and doing units of work, a cursors is not really going to be worse. But cursors actually do a lot to simplify the logic required to set up and run through the loop. And to even help you not have to figure out what you need to increment to because cursors will keep track of that for you.

So there are a lot, in a lot of ways, it pays to learn how to write cursor syntax over a while loop, because the cursor syntax will actually do a lot of the thinking for you. And you don’t have to get into some of the like, you know, nuances of while loop handling and how you want to go through the while loop, because cursors will will fix that.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video about our beloved cursors. So I look forward to seeing you there. Anyway, thanks for watching.

Going Further

Learn T-SQL With Erik: Control Your Flow

Posted on September 26, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Control Your Flow

Video Summary

In this video, I dive into the world of control flow language in T-SQL, explaining key elements like branching, looping, and error handling. I highlight common pitfalls such as null evaluation and improper use of `go-to` labels, emphasizing the importance of proper termination to avoid unintended code execution. The discussion is part of a series aimed at helping developers navigate these often-overlooked but crucial aspects of T-SQL programming. Whether you’re a seasoned SQL Server professional or just starting out, there’s always something new to learn when it comes to mastering control flow. So, if you enjoyed this content and want more insights like this, consider becoming a member of the channel or even joining me on my upcoming tour events with Red Gate in Dallas and Utrecht. Your support helps keep the knowledge flowing!

Full Transcript

Erik Darling here with Darling Data. Sometimes I want to say Darkwing Duck Data, but that wouldn’t make any sense. We’re going to keep going with the Learn T-SQL sort of preview material. And today we’re going to talk about some elements of control flow language. We’ll get more into exactly what all that means when we get to the video, but for now, just understand that I am controlling the flow of information and that’s how I choose to do it. So if you look at the down in the video description, there are many helpful links where you can do things like hire me for consulting, buy my training with discounts included in there, become a member, a supporting member of the channel, ask me office hours questions, and all that good stuff. And of course, if you enjoy this content, but you don’t want to give me money for anything, that’s cool too. Just do all the usual like, subscribe, tell a friend stuff. And I guess we can consider ourselves maybe even in some way. You never know, right? The bigger your reach gets, the better chance you are that someone will maybe want to give you money someday. I am going to be going on a partial tour of the world. Just finished up Pass On Tour New York City, but the fine folks at Red Gate are going to be wheeling me out for events in Dallas, September 15th to 16th, and Utrecht. That’s in the Netherlands, a beautiful little hamlet, October 1st and 2nd.

And then of course, there will be the Pass Data Community Summit taking place in Seattle, November 17th to 21st, where I have two days of T-SQL pre-cons with Ms. Kendra Little. And I do look forward to all of that. So with that out of the way, let us do our T-SQL partying here. Now, SQL Server has a variety of control flow elements in it. ZoomIt is doing this weird thing. Like, what happens down there, ZoomIt? What are you doing? Why are you messing with me? So if you want to do some branching, there’s if and else. If you want to contain certain things within a little flow of control, you have begin and end.

You have navigational stuff like a go-to label, and then you have looping elements like while loops. And then of course, we have the usual things that we can do within a loop to make or break progress. And then additional control flow stuff is also error handling.

Now, one thing worth noting here is that even though this, I’m going to cover this more when we talk about performance stuff in the advanced material, is that control flow elements don’t necessarily control which query plans are compiled at all, ever. You have to do some extra work in order for that to happen. But branching queries within code often has very, very profound effects on cardinality estimation and query plans.

So be very careful if you are the type of person who does that. Anyway, we’re going to cover just some like basic control flow stuff in here that have sort of more specific modules that we’re going to get to over the next couple of weeks. few weeks to talk about things more in depth where it makes more sense.

But one thing that I see quite a few people mess up and be caught off guard by is exactly what branching really controls once you start sort of evaluating expressions. For example, if we look at this right here, you know, like I see a lot of stored procedures start with these if things. And I’m looking at the code and I’m like, I think there’s a bug here.

And they’re like, no, no, no, it’s fine. Why? And I’m like, well, because like this will only do this first thing. And by that, I mean, like if we look at this code branch right here, we have a local variable declared. It’s an integer and it has a value of 10.

And then we say if the value is greater than five, then we print a couple of things. And if the value is less than or equal to five, then we’ll print a couple other things. But look what happens when we run this and we look at the messages tab.

We get value is greater than five. Hello world. Goodbye world. So what happened was we printed the value was greater than five. Let me move this thing.

That little highlight line can get a little annoying. So because the value was greater than five, we printed this as an immediate consequence of that. But then we also printed this. And then the second if branch, which was the value is less than or equal to five because it wasn’t, we didn’t immediately print this, but we did print goodbye world.

Right. So we like this conceptually is difficult for a lot of people. And I, you know, I do run into bugs around this sort of logic quite a bit, where if you want to contain these things, you do need to have explicit begin and end around them in order to make sure that you only print, you only follow the correct path.

And in these sort of branched logic places, you like the when you have something like this, the if will only do the immediate will only do or not do the first thing immediately after it. So in this case, it’s the print, this first print statement. So when we ran this one, it didn’t print this, but it did go and print this, which, you know, that is something that I see people struggle with quite a bit.

Another thing that people will struggle with quite a bit is null evaluation or like what to do if you hit a null. A lot of people just don’t plan for it. So if we were to run this, what SQL Server just says the value is not greater than five.

Goodbye world, which, you know, is correct. Null is not greater than five, which I agree with. That’s not where that’s not where the thing is.

It’s just that if you were to go and try to do something with a null, well, that might that might upset whatever logic you’re working with to to figure things out or whatever logic you might have to you might be like, you know, running queries for a null value ending up in there might not be a good thing. So when you’re doing this sort of stuff, it’s best to explicitly handle nulls in some way and either like stop the execution of whatever like like like either take maybe take a different branch like this sort of like a no op that just says no, this was null. We can’t do anything with null from there and move on.

You know, you could like you could maybe like use is null or something, but it wouldn’t get you the same like like handling for something like a situation where you didn’t want to work with nulls further down. So always make sure to plan for nulls in your data and handle them accordingly, even in this sort of branching logic, because, you know, this make sure you like always test with a null at some point, because if you don’t and you end up with a null, even if you swear up and down, this could never be null. Well, I don’t believe you because I’ve seen too many people be wrong about this sort of thing.

Another thing that is worth talking about here a little bit, I don’t see these used a lot, but where I do see them used in code, people often do not terminate go to logic correctly. So if we were to run this whole thing, right, which we have the local variable declared, it’s an integer set to null. And we’re going to have these different go to units in here or go to labels in here.

And I have the these returns coded out just for a little bit of convenience. But this is where each one of these things would end up. Right.

And a lot of people think that when you go to a label, that just means the end of something happening. But that’s not true at all. If we run this, look what we get back. Value is null.

Unknown world value is greater than five. Hello, world value is not greater than five. Goodbye, world. Why are we here? So if you want sort of the termination of code after you have hit a label, you really do need to add these returns in or else you will just keep on going. You will just keep on running right through code.

I think that was yeah, that was all. So if we run this, then we get the correct termination of whatever block we entered, whatever label we jumped to after that. So be careful with if branching using begin and end properly.

Always test for nulls. And if you are going to use sort of go to labels and you’re going to jump around within code, make sure that you have a return to to like the stop execution after you have done whatever you need to do within that label. Otherwise, you will end up doing probably a lot more than you bargained for.

Anyway, next up, we’re going to be talking about while loops. So I’m generally far more excited about that. But I don’t know.

These are things that I find people struggling with. So, you know, I talk about them because I don’t want you to struggle. I like you. You seem like a nice person. You know, I don’t necessarily think we need to hug, but you seem OK.

So I want you to have a nice life. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

And I will see you in the next video about while loops where we will. Well, I guess it’ll it’ll be a surprise, right? If I tell you what I’m going to do, then ruins everything.

I’m going to keep you on your toes out there. Anyway, thank you for watching.

Going Further

Learn T-SQL With Erik: SET, SELECT, and Data Types for Local Variables

Posted on September 25, 2025September 21, 2025 by Erik Darling

Learn T-SQL With Erik: SET, SELECT, and Data Types for Local Variables

Going Further

SQL Server Performance Office Hours Episode 30

Posted on September 24, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 30

Questions:

Hi Erik, Thank you for the great content. Why is it better to have a higher sample rate for update statistics?
Hi Erik! I had a procedure with a chain of CTEs and UNIONs. It was taking almost 40 seconds and I was tasked to come up with a solution immediately due to production issues. I went in desperation and popped an OPTION (RECOMPILE,HASH JOIN) in the end of the CTEs where they were inserting into a #Table. This only took it to less than 2 seconds! How on Earth can this has such a performance effect and SQL Server could not find out by itself? Thanx
I recently purchased the Everything Bundle and am enjoying each module. Is there a recommended viewing order? For example, the Index Tuning module seems to reference the Hardware modules.
If you were curating a Paul White’s Greatest Hits compilation what articles would you consider essential reading? And what profession would best suit his personality if he wasn’t consistently dunking on poorly performing SQL Servers. Remember: All of Paul’s posts are at https://www.sql.kiwi/

To ask your questions, head over here.

Video Summary

In this video, I dive into answering five of your most pressing SQL Server questions during one of our live Office Hours sessions. Whether it’s about the benefits of a higher sample rate for update statistics, optimizing performance in complex queries using OPTION(RECOMPILE) and HASH JOIN, navigating through the Everything Bundle modules, or delving into Paul White’s invaluable blog posts, I cover it all. I also share some upcoming events where you can interact with me in person, including the PASS On Tour event in Dallas and Utrecht, as well as the Pass Data Community Summit in Seattle. If you have any questions or topics you’d like to discuss, make sure to leave them in the video description or reach out directly—your feedback helps shape future content!

Full Transcript

Erik Darling here with Darling Data, and it’s time to do one of those there Office Hours episodes where I answer five of your most burning SQL Server questions, or whatever you ask me about. Anyway, five. If you want to interact with me in any way, you know, virtually, physically, whatever, look down in the video description. All sorts of helpful links. You can hire me for consulting, buy my training, become a member of this channel, and support my efforts to bring you high-quality SQL Server content. Ask me Office Hours questions on episodes just like this. You could be one of the lucky five people who gets a question answered. And of course, if you enjoy the things that I do here and you think someone else might enjoy them as well, you know, like, subscribe, tell a friend, all that good stuff. I will be live and in person a few more places through the end of the year. Dallas, September, September 15th to 16th for the Pass On Tour event there. Utrecht, same event, different place, October 1st and 2nd. And of course, Pass Data Community Summit in Seattle from November 17th to 21st, where Kendra Little and I have two days of T-SQL pre-cons in which you should go to both so that you can spend, you can maximize your time with us. And with that out of the way, let’s go answer these questions. These have been sitting around for a little bit because I pre-recorded a lot of stuff while I was on vacation.

And getting back into things. And boy, did you ask a lot of questions. So first up, we’re going to do this one right here. Let’s make sure Zuma’s working. So hi, Eric. Thank you for the great content. Hello, you. You’re welcome. Why is it better to have a higher sample rate for update statistics? Well, the good news is it’s not always better. Sometimes the default sample rate is good enough to pick up on a good general description of what data, lives in your indexes and describes that in the histogram. Other times you do need to up the sample rate in order to catch more granular things. If you are using a higher sample rate for statistics and getting better representation of your data in the histogram, there’s no reason to, you know, fear or think that you need to do that for all of them. But that is just sort of how that works. If you are in a situation where even a full scan update of statistics is not doing a good job of describing your statistics, you might try filtered indexes or filtered statistics so that you have a smaller range of data.

You might create a filtered index or statistic on a particularly interesting data point for you and have a more accurate description of just that interesting data point. So again, not always better. Sometimes you do have to increase that. You know, I’ve worked on a few different systems where a regular default sampled statistics update did not do a good job.

And there was like a I think we hit a sweet spot for some of those between about 10 and 20 percent. But I have a few demos that I do in my performance tuning stuff where I have to do 25 or 30 percent. And then there’s even one where I have to do a full scan in order for SQL Server to get things right.

So it is not a sort of across the board thing. It’s a very specific thing. And I wouldn’t recommend doing it for every index because or every statistic because that may be counterproductive to your maintenance time. But if you find that there are some statistics where it does work better, you did a good job and you should keep doing it.

All right. Next up here. Hi, Eric. Hi, you. How are you doing? Oh, there’s even a thanks on this one with an X. That’s a spicy thanks. This thing blurry. Is it just my eyeballs?

Let’s see. Can I get it? Oh, there we go. I’m a little bit more. I’m a little bit more clear now. Hi, Eric. I had a procedure with a chain of CTE and unions. You’re off to a good start. It was taking almost 40 seconds and I was tasked to come up with a solution immediately due to production issues.

Oh, you have a high pressure job, my friend. I went in desperation and popped an option recompile hash join in the end of the CTE where they were inserting into a temp table. This only took this only took it to less than two seconds.

How on earth can this has such a performance effect and SQL Server cannot find out by itself? Well, you left a few details out of here. You know, I mean, obviously, I’m not asking to see the full definition of the query because chain of CTE and unions.

That’s that’s that’s that’s that’s pay me money territory. And the fact that you have a recompile on there makes me wonder if there are any parameters or local variables involved. It could be perhaps influencing the optimizers estimation process.

But there are lots of cases where SQL Server may misunderstand the number of rows that might, you know, come out of certain operations. You know, I get by your where and join clauses, survive group by having type stuff and make progress through the plan. And you might end up with well, I mean, you know, I’m going to say in general, you might end up with a lot of nested loops joins that execute way, way more than the optimizer anticipated them than them executing.

So without seeing the query plan, my guess would be that you had a lot of nested loops joins in there in that plan and that SQL Server was getting stuck in them, perhaps even serial nested loops joins. I guess there’s also the possibility that you had some untimely merge joins, especially if you had to sort data before going into those merge joins. But the reason why the optimizer didn’t find it?

Well, I mean, you know, it could be a lot of things. It could be that you have statistically inaccurate information in your database. It could be that you had a parameter sensitivity issue.

It could be that you were using local variables and SQL Server was not estimating things well. You could be using a table variable in there, too, and getting a bad estimation. But also SQL Server could just be doing a poor job of cardinality estimation without any of those things.

And you ended up with some nested loops joins that were completely inappropriate for the number of rows that you were managing. As for why SQL Server couldn’t find out by itself, well, you know, that’s a query complexity thing. The optimizer only has so much time that it’s willing to spend on a query figuring stuff out.

And sometimes it does not have a chance to explore or accurately or correctly cost all of the alternatives. So, you know, this is where stuff like query hints really do come into play. You could try simplifying the query a bit more, maybe removing any elements from the query that would harm estimation, local variables, table variables.

Maybe, you know, if there’s any sort of parameter sensitivity, you could find ways of dealing with that. But if you’ve got things solved with recompile hash join, I don’t see a reason for you to keep tinkering with things. Sounds like you’ve got it under control.

So, you know, the optimizer is a big piece of software and it’s just like, you know, it’s made by humans, just like most software. You know, I’m going to say, I’m going to draw the line a little bit at LLM generated software because that’s not generated by humans and that’s full of mistakes too. But, you know, the optimizer can’t do everything.

It can only make a good enough guess most of the time at a good enough plan. All right. Next question here. I have, I recently purchased the everything bundle.

Woohoo. Thank you for your support. And I’m enjoying each module. Is there a recommended viewing order? For example, the index tuning module seems to reference the hardware modules. So I think as long as you do the starting modules first and I think it’s, what’s it called, the essentials modules first, you should be in pretty good shape.

If there’s anything in the other modules that reference stuff back, you can always go watch those other modules afterwards and sort of like, you know, figure out where, where things, you know, maybe connect. But I don’t really have a recommended viewing order aside from like starting in essentials and then kind of whatever you find interesting and appealing or sort of whatever applies to problems that you’re trying to solve. So that’s about all the, that’s about all the advice I have there.

All right. Last question. Gonna finish on a strong one. Oh, my dear friend, Paul. All right.

If you were curating a Paul White’s greatest hits compilation, what articles would you consider essential reading? And what profession would best suit his personality if he wasn’t consistently dunking on poorly performing SQL servers? All right.

So I’m going to answer the second question first. Paul White would be an excellent sandwich maker. Paul White would make the best sandwiches you’ve ever tasted. They would leave you with a sense of peace and well-being, just like his blog posts do, that you didn’t know was humanly achievable. So that, that, that’s, that answers that question.

As far as curating Paul White’s greatest hits, Paul has a number of like, just absolutely, I mean, like the SQL.Kiwi site where all of his posts live now. This is actually something that a lot of people miss. Like a lot of people will Google like Paul White and a topic and they’ll go to like the old SQL performance site, or they might even find like a dead SQL blog link.

But all of Paul’s posts these days are on SQL.Kiwi. And I’m going to put this in the, the, the, the video description because it’s that important. All of Paul’s posts are there.

You can find lots of one-off posts that are amazingly long, detailed, full of, you know, great information. But I think if you were to, if I were to curate Paul White’s greatest hits, it would be any post that is part of a series of posts. Because I know when Paul White writes a series of posts, that man has got his head around something.

And that’s where I think the best stuff comes in because, you know, you have sort of like this learning pathway of, you know, like, like, like, like great, like a series of things about a particular topic that wouldn’t all fit into one post. So I think whenever Paul White writes a series, that’s the stuff that I would put in like the greatest hits. Like if I were going to make a great post-Paul book in tribute to Paul White’s writing, I would start with all of the posts that are a series.

You know, as far as stuff that has, I think the series of posts that has influenced me the most from him is certainly the Isolation Level series. I have spent a lot of time with that one, especially as I’ve been presenting more and writing more about Isolation Levels. So that one has had the, I think, the most recent big impact on me.

But there are many posts of his that, you know, I have read at different points in my career. Paul White has, of course, been blogging for quite a while. He was actually the first blogger I ever read when I was trying to figure out how to write fast performing paging queries.

He had a couple of posts on SQL Server Central. But yeah, like there are posts that I’ve had to read at like various stages in my career because there were things in each of them that I didn’t fully understand until I understood a lot of other stuff first. And so like it took a lot of puzzle pieces for me to actually get some of the points that he was making, even from many years ago.

So I don’t know. Of course, I always thank you to Paul for everything you’ve done over the years. You are just an amazing human being and teacher.

So thank you for that. But yeah, it’s, you know, I would start with anything that’s a series. That’s the really, like, that’s a really crazy, like, you know, you have to sit down and concentrate. You got to like spend some time with those in order to fully get them.

But anyway, that’s five questions. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in another video another time. Oh, okay.

Let’s just, we know it’s tomorrow at noon Eastern. So let’s just stop pretending. Anyway, cool. Thank you for watching.

Going Further

A Little About Using Windows Performance Recorder

Posted on September 23, 2025January 23, 2026 by Erik Darling

A Little About Using Windows Performance Recorder

Video Summary

In this video, I delve into using Windows Performance Recorder to uncover deeper performance issues in SQL Server queries. After running a set of demos where certain queries took significantly longer than expected, I couldn’t pinpoint the exact cause based on traditional query plan analysis and weight stats. To get to the bottom of it, I turned to Windows Performance Recorder, which provided valuable call stack information that led us to discover the impact of read ahead reads, specifically trace flag 652, in this particular scenario. This video is a great example of how sometimes you need to go beyond the query plan and use additional tools like performance recording to truly understand what’s happening under the hood.

Full Transcript

Erik Darling here with Darling Data. And today’s video is an interesting one. So this video is going to talk about using Windows Performance Recorder to track down like a deeper level of performance problem in SQL Server. This is actually based on a demo that I had recorded a little before I went on family vacation and it bothered me the whole time. And when I got back, I started looking at it again. And, uh, like, really couldn’t figure it out. And so, like, I showed a few people and they were like, I don’t get it either. So I was like, okay. So I used Windows Performance Recorder and found some stuff. And then it was actually, uh, my, my good dear friend, Forrest McDaniel, who reminded me about a trace flag that we could use to fix this one. But, uh, this is an interesting one. So I hope everyone’s buckled up. Anyway, uh, if you look down at the video description, there you’ll see all sorts of helpful links where you can hire me for consulting, buy my training, become a member of the channel, and support the stuff that I do here to bring you this content. Uh, ask me office hours questions for my office hours episodes. Those usually come up Monday where I answer five of your questions. And of course, if you enjoy this content, but you don’t want to pay me to do anything, you can always like subscribe, tell a friend and help this channel get bigger and better in other ways. Uh, of course, I still have some speaking events coming up. Just finished, uh, pass on tour New York City, but there is still events in Dallas, uh, coming up in September where I have a half day, pre-con and a regular session. Uh, and then there is the one in Utrecht where I have just about the same thing. Then of course, there is pass data community summit coming up in Seattle, November 17th to 21st. Uh, I have two days of T-SQL pre-cons with the wonderful and talented Kendra Little. And I look forward to seeing you at both of those, not just one of those. You should go to both of those because that’s how you maximize your knowledge and learning. So with that out of the way, let us get to the database party.

Now, just to sort of, uh, bring things back to where, where they were, right? Uh, I had this set of demos where, um, there were a couple of queries that I ran. And in one of the queries, I used this, uh, option use hint, disable optimizer row goal. And what ended up happening in these query plans was, uh, the first query, uh, with row goals, uh, enabled ran for about five and a half seconds and 5.6 seconds. Good enough there. But the other query ran for 40 seconds. And all 40 of those seconds were sort of spent in a place that didn’t make any sense to me. So I’m just going to shuffle these query plans around a little bit. And the biggest time suck in these query plans was right in this branch. So this one took 3.386 seconds, which isn’t bad, right? This is the fast query plan. This one took 37 seconds, which is bad, right? That’s, that’s like a 10 X slowdown for this one operator. And I couldn’t for the, at first, when I listened to this happen, I couldn’t for the life of me figure out what was going on with it.

Because when I looked at the weight stats for the queries, so if we, uh, get the properties over here and, uh, we open up weight stats, there was not a whole lot of weight stat evidence about what might be going on in here. Right? Like, like, like the weights for the, the quick query were of course, you know, not really anything interesting, but then the weights for the slow query, let me just make sure that’s the right one there. The weights for the slow query really didn’t add up to anything either. Like, like, like just looking through these, like nothing in here said, this is why I took 30 seconds, right? This is none of, none of these weights are doing very much.

Uh, like there’s very, very little weight wise going on in here. So what I did was I fired up my friend, the windows performance recorder, which since this is a VM I had to do over here. So, uh, what I’m going to do is show you sort of what I do, uh, to get windows performance, uh, windows performance recorder stuff. Now, of course, the first thing you have to do is actually like run it to collect things, but I’ve already done that because I don’t want you to sit there and wait for a long time for stuff.

So, uh, once I have, uh, once I had things recorded for the two queries, uh, what I usually do is go into the CPU stacks and then I’ll go into SQL Server and I’m going to make this bigger and do some zooming. So you can all see what, what, what I get up to. So the first thing you want to do is select everything in here and then you want to say, look up symbols.

So this is going to go out to Microsoft and this is going to look up symbols and this is going to resolve the names of any public symbols that it can. This is going to take a couple of seconds to run. Uh, maybe you should have done this before I did the video, but you know, I’ve got pretty fast internet, so I’m not too, too worried about it.

So after, uh, while, while this is running, what I’m going to do is there are a few things in here that I like to change, uh, for the group paths and the fold percentage. So I usually just delete that and hit enter. And then for the fold percentage, I put in zero here and I hit enter.

And this will sort of expand everything to what we care about. Now, uh, looking at the stuff in here, I’m just going to slide this over. So it’s a little bit more clear.

Oh, that didn’t, that didn’t help much at all, did it? So like, if we look at what’s going on in here, we have, um, you know, like this locate column internal thing, but there’s not really like a lot of time spent in this one where we care about stuff. Where this be, where this sort of becomes more useful is when you start comparing one, uh, set of call stacks to another, but we’re good, but we need to sort of open up the other one and do stuff there first.

So the CPU stacks here just show some stuff like locate column internal. I think I actually have to squish that a little bit so that I can, uh, use zoom it to zoom in on things. Otherwise, uh, it doesn’t really cooperate with me, but for some reason, zoom it doesn’t want to cooperate when this thing is up anyway.

So we’re just going to skip that part, but we don’t really have anything too, too interesting going on in here. All right. This is the, this is the fast plan.

So let’s go look at the slow one and let’s open, crack open this bad boy. And we’re going to go back into CPU stacks and we’re going to go back into SQL Server and let’s make this as big as we can. And what we have in here is, uh, some interesting stuff.

Once we get rid of the group paths and once we get rid of the, once we do the full percent zero. And what we’re going to see is that we have a new thing in here. Uh, and we, the couple of new things that we have in here are read ahead helper and read ahead.

And this is where, I’m sorry. There’s a, I think this one too was interesting. So if we were to compare, uh, one set of call stacks to another, um, like this is really where we had way more stuff going on.

Right. This is where like we spent way more CPU time. And this is the only way that I can figure out what happened.

Cause again, just looking at the like weight stats and other stuff for the query, there was no sign at all that this stuff was coming up to the forefront. But then if we sort of bring these percents over, excuse me, uh, we’ll see that these numbers, uh, you know, were like much like a, like these things like were present in this call stack trace where they weren’t in the other one really. And they have like fairly high percentages, uh, as far as like, like stuff that executed.

Um, there’s all, all sorts of other stuff that you could look at in here, like make, look at flame graphs of things, but I don’t really, you know, I don’t, I don’t really, I’m not, I’m not enough of a developer to understand those fully. But, uh, so this is how I was like, huh. And then like, you know, as I was sharing this stuff with the people who I was just like, what, what, this is a weird thing.

Uh, again, my friend Forrest McDaniel was like, Hey, there’s a trace flag to disable read ahead reads, uh, 652. I think Paul White’s blogged about it, but, uh, Forrest remembered it. So Forrest gets full credit on that one.

But if we, uh, run this query and we turn on this trace flag 652, 7418, uh, just changes how operator times show, uh, their time in the query plan. Uh, uh, trace flag 7418 changes things. Um, so in a, in a row mode plan, the operator times are cumulative going from right to left.

Uh, but in batch mode plans, uh, they are per operator. So 7418 makes row mode plans act like batch mode plans and just has the operator times, uh, show as per operator. So this one isn’t part of what solves a problem.

652, which disables read ahead reads for the query. This is what solves the problem. So if we rerun this query with those trace flags enabled, and we look at the execution plan without read ahead turned on. And like what I don’t want you to think is that read ahead is a bad thing.

Uh, read ahead is generally a very, very good thing. It turned out to be a negative thing in this plan because of how many of them we had to do. Like there’s two kinds of killer latencies.

Typically in computer systems, there’s the stuff that of course has a long latency, right? You might see that for like lock weights and other things that, uh, you know, when they drag on for too long, other things start having problems. But then there’s the type of latency where it’s sort of like the death by a thousand cuts swarm of mosquitoes latency where you have to do one thing.

And even though that one thing is really fast, if you have to do a lot of it, well, you can end up in a pretty tough spot. So if we look at the execution plan for this with read ahead disabled, this actually ends up running, uh, pretty quickly even compared to the fast plan before. It’s about 500 milliseconds faster.

Not that like that’s the meaningful difference, but really it was with the, the difference between this one and the, uh, row goal being disabled query. That was where it got really interesting. So, uh, if you, when you’re a troubleshooting query performance, you might find all sorts of good things to look at, uh, contextually for the query.

Uh, the query execution plan is always going to be like your first source of truth for like, you know, where, uh, time was spent in the, in the, in the query plan. And then like getting the actual execution plan for that, then you might find where, um, you might look at weight stats for the query plan and see interesting things in there. But sometimes, or like, uh, you might even look at the sys.dmexec session weight stats for your session and see if there’s anything in there.

Because like I, I’ve talked about a few times before, Microsoft screens certain weights out of actual execution plans. It doesn’t report on them. So you might look at the sys.dmexec session, uh, weights, uh, dmex session weight stats view, and you might look in there to see if there’s anything additional that would be useful for troubleshooting.

Other times you have to go deeper and figure out like, you know, stuff that a query plan would never report on. And call stacks are one of them. And windows, windows performance recorder is a much easier way to just record general call stacks for when a query is executing.

Than, say, like something like using the debugger and having to like attach to a thread and like, you know, like click through and wait until you like, like set breakpoints and stuff. Because that, that’s where it gets really difficult. And of course, the nice thing about windows performance recorder is that if you find interesting call stacks in here, you can actually use something like window bug to set breakpoints on the interesting things you find in here.

So you’re just not hunting through this haystack of things and like, you know, just like hitting enter and letting the debugger make progress after each new step. So this was an interesting one. Um, again, readaheads generally not bad.

Like don’t go disabling readahead. I don’t want you to take that away from this video. I don’t want you to think that readahead reads are awful. Generally, they’re very good things and they help performance quite a bit. But, uh, in this case, they ended up being the problem for this one particular query and tracking down what the problem was, was to me very interesting and very fun.

And of course, uh, I, I do always have to thank my, my dear friends for helping me remember the things that I cannot possibly keep, uh, all contained in my head at once. So anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something and I will see you over in, uh, the next video, which should be an office hours episode because the next video will be on a Monday. Today being Friday. All right.

Cool. Goodbye.

Going Further

A Little About Skewed Data and Skewed Parallelism

Posted on September 22, 2025January 23, 2026 by Erik Darling

A Little About Skewed Data and Skewed Parallelism

Video Summary

In this video, I delve into a fascinating SQL Server performance issue: how skewed data can lead to skewed parallelism, which in turn can significantly impact query performance by sacrificing CPU resources to reduce wall clock time. I walk through an example using the Stack Overflow database, where I artificially skewed the `votes` table and ran a query that highlighted this problem. By examining the query plan and analyzing various metrics such as CPU time, elapsed time, and memory grants, we can identify when parallelism is ineffective due to skewed data distribution. This video serves as an excellent introduction to recognizing and understanding this common performance bottleneck, setting the stage for future content where I will explore solutions to address these issues.

Full Transcript

Erik Darling here with Darling Data. And in response to many miserable, wretched people complaining about the fact that I need to make a living and owing to the fact that I needed to edit some slides. Anyway, I’ve redone my intro deck. So we’re gonna experiment a little bit with that today. In this video, we’re going to talk about how skewed data can lead to skewed parallelism. And skewed parallelism, of course, means where many rows end up on either one or a small number of threads. And kind of undo all of the, you know, like potential speed improvements by sacrificing CPU in order to reduce wall clock time. They can happen because of that. And how you can find it. I’m not gonna talk about how to fix it in this one, because I’ve got something else coming up for that. But this is a good way to introduce the problem and to help you out. identify it in a few different ways. So if you look at the video description, you will see all sorts of helpful links. You can hire me for consulting by my training, of course, at a discount, because I do care about the people who who buy my training and I’m not going broke when they do so. Becoming a channel member to support my efforts to bring you this high quality SQL Server content. Ask me questions on office hours. And of course, if you if you if you want to get a like this stuff, please do like subscribe and tell a friend and all that good stuff. I just finished Pass on Tour New York City. So that that one is off the list. I still have Pass on Tour Dallas and Pass on Tour in Utrecht, where I have half day pre cons and regular sessions at both. And of course, the Pass Data Community Summit coming up in Seattle November 17th to 21st, where Kendra Little and I are tag teaming two days of T SQL pre cons. But with all that out of the way, let’s let’s party, let’s have ourselves a little database party here. Now, let’s go over to SQL Server Management Studio. What I’m going to do a little bit is explain to you how I created this data. And what the query plan for with with the skewed parallelism looks like in some way that you can start ways that you can start figuring out if you’re having this problem. And we’ll get we’ll get to the solving it part later. Different video. So what I did was the votes table in the stack overflow database has the user ID column and the user ID column in the votes table is skewed and is rather is scrubbed, not skewed. We made it. I made it skewed is scrubbed in the public data releases because you know, they don’t mean, of course, you’re supposed to be able to vote in anonymity.

On stack overflow, no one knows who you upvoted downvoted, you’re free to divulge that information if you’d like. But maybe the nice folks at stack overflow don’t just want to go publicly tell everyone what you did. Now, what I did after I selected everything from the votes table into this new table is I updated that table to replace the null user IDs. There are a couple vote types for bounties. Bounties are when you like want to give people additional reputation coming from your reputation for having a really good answer or something. But so there are some of them populated. So what I did was I set all of the null user IDs, which is the majority of the table, equal to the vote type ID that is in the vote type ID column. And there are not too many vote type IDs are mostly upvotes and downvotes.

There are a few other ones, but they’re not terribly interesting, nor do they comprise much data in the table. Of course, then we had to create a clustered primary key on the table so that we were not dealing with some terrible heapness, which you can blame things on. And then, of course, I needed to create an index on the table to support the query that we’re about to run or rather that I ran ahead of time.

Why did I run it ahead of time? You might ask. And I might I might tell you if you were to ask me such a thing because it runs for a little bit. And what this query does is it selects from the users table because users table is kind of where most queries start. You want to figure out what user stuff belongs to who did what.

And then we are going to outer reply this votes skewed table down here correlated on that new on that newly populated user ID column. And then we are saying where vote type ID is between one and four. This will this is going to be part of what gives us the skew because the vote type ID, of course, is ends up as the user ID for these because these are these are these are the ones that were null before.

So this is where things kind of get fun. So if we look at the query plan for this, right, we will see that we indeed have all of the hallmarks of a parallel execution plan. And we have parallel exchanges.

Well, actually, we have one parallel exchange. OK, singular. We have one parallel exchange. We also have all sorts of little parallel indicators across all of these wonderful operators telling us that SQL Server used multiple CPUs to run parts of the query. But with all those one up here, too, let’s go get that.

Oh, let’s let’s be completists over here. Now, this one here, this is this one doesn’t count because this one this one had this sort had a spill. The spill doesn’t really matter all that much for this, but it is actually kind of interesting because in a way that we’ll talk about. So if we start up here in this part of the query plan and let’s make this a little bit bigger so we can see what’s going on here.

And we expand this actual number of rows thing, node, whatever you want to call it. And we zoom in. You’ll see that the each each of these eight threads in here did a fairly even amount of work.

Right. It’s like you might, you know, pick a few bones about some of these. But and for most of this, it’s fine. Right.

So this part of the starting part of the query plan, the scan of the users table, we do not have any problems there. And when we go into the sort and I don’t know why that decided to do that to me. But if we look at the sort operator, we’ll see that parallelism remains pretty even across all of these threads here, too.

We didn’t we didn’t really lose anything here. And when we go into the nested loops join, things are still OK here. We did.

We’re still doing all right. But now this is where things start to get a little wonky on us. And this is this is why this this lower branch of the plan kind of, you know, is where we start having issues is if we go to the clustered index C here, we will see that all of the rows ended up on a single thread.

The reason why this happens with skewed data is because SQL Server uses this thing called the parallel page supplier in order to hand rows out to threads. Part of how it decides which go which rows go on which threads is by using sort of a modulo hash function to say, oh, if well, if this number divides by this, then we send you to this thread. In this case, all of the rows just happened to hash out to thread one.

And this is going to be true when we go across. Now, I said that the sort didn’t really matter, but there is something interesting about it. And the interesting thing about it is, of course, because all query plans start as serial execution plans and parallel plan exploration isn’t started unless the subtree cost of the serial plan is higher than the cost threshold for parallelism.

That means all of the stuff that SQL Server sort of initially assigns to a query, one of those stuffs being memory grants, are decided before a parallel plan is explored and aren’t changed if a parallel plan is chosen. Now, if you have a DOP 8 query, the memory grant is not then multiplied by DOP. The memory grant is divided by DOP.

So the memory grant for this query, which is, if we hover over here, the memory grant for this query is 359 megs, which isn’t big by any stretch of the imagination. But this number is divided by 8 for us because we have this query running at DOP 8, which means that our sort operator asked for enough memory to sort all that data. But look what happened.

One thread, because we had one thread that had any rows on it, entirely spilled because that one thread had one eighth of the memory. If this one thread had all 359 megs, maybe it wouldn’t have spilled. But since it had 358 or 9 divided by 8, so this thread had like one eighth of the memory grant, it didn’t get nearly enough.

So it spilled a whole bunch. Now, this was a particularly long running spill, right? So we had 4.6 seconds spent, or let’s just call it 4.7 seconds spent here.

And then we have 45 seconds here minus that 4.7. So let’s just call this 40 seconds of this one thread spilling out to disk. So this did cause a performance problem for us because this one like memory requiring operator got one eighth of the memory.

Rather, the thread that we wanted, the memory was spread across all eight threads. So each thread got one eighth of the memory. And that one thread doing all the work means that one thread didn’t have nearly enough memory.

So this spilled out a whole bunch. Now, there are a couple ways where you might, you know, aside from looking at the rows on the per thread, like we did in the query plan, there are some other ways that you might look at this and decide that you have an issue.

So one thing that I always tell people to look out for when they run a parallel query, and it does not run as quickly as they would hope, is to look at the CPU and elapsed time. One very, very good indicator that parallelism was ineffective is if CPU time and elapsed time are very close together.

In a perfect world, let’s say we have a DOP8 query, then we would want to see CPU time be 8x elapsed time, right? We would want to see all eight threads do an even-ish number of work. And we would want to see all eight of those threads work cooperatively and produce an elapsed time, like a wall clock time, that is one-eighth of CPU time.

So in this case, we have 45 seconds of CPU time and 53 seconds of elapsed time. So we can kind of tell here that parallelism wasn’t very effective. From a weight stats perspective, this is where things get a little disappointing.

If we look at weight stats for this query, we’re not going to see any parallelism weights in the query plan that help us figure stuff out. We are going to see a whole bunch of this I.O. completion weight from the sort that spilled, but we’re not going to see the weight that really makes a difference here, which is CX Consumer.

Microsoft screens CX Consumer weights out of actual execution plans because some guy who works at Salesforce says they don’t matter. But in reality, they actually do for this sort of thing.

So if you’re running a query like this, or rather you see a parallel query running, and the CX Consumer weights are really high, and then you have other indicators like I’m showing you here, where the parallelism is obviously skewed, either looking at the rows per thread counter, stuff like that, or the CPU time and elapsed time are very close, then that’s a pretty good sign that your query has very skewed parallelism.

So these are things for you to watch out for when parallel queries are not perhaps running as quickly as you would like them to. And, you know, just to sort of finish things out, this is what the votes skewed table looks like as far as, you know, like distributions in here go.

So I suppose I should probably limit this to something a little bit more useful because we have where vote type ID is between 1 and 4 here. So let’s actually add that in down here.

Let’s not be silly and foolish with ourselves. Let’s make sure that we have just sort of a look at exactly what data we care about. This is how all the rows distributed, and, you know, because of whatever hash function SQL Server chose when it was doing the parallel page supplying, every single row ended up on one thread.

So that’s what happened there. So this is how skewed data can also lead to skewed parallelism. And can cause you performance problems. So thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will have a follow-up video on this one eventually about how to fix skewed parallelism for this query. So we have that to look forward to.

But tomorrow’s video is going to be sort of a different one where I’m going to dig into a different way of troubleshooting query performance that goes a little bit deeper than just looking at query plans and weight stats and stuff. Because we’re going to look at a query that actually doesn’t have anything helpful in that regard.

And we have to go deeper. And we have to look at some more stuff. So anyway, thank you for watching. And I’ll see you in tomorrow’s video, I hope.

I pray we all survive the night. Anyway, goodbye. Bye.

Going Further

Learn T-SQL With Erik: Query Behavior Under Read Committed Row Versioning

Posted on September 16, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Query Behavior Under Read Committed Row Versioning

Video Summary

In this video, I delve into the nuances of optimistic isolation levels and row versioning in SQL Server, specifically focusing on how certain query structures can lead to unexpected results when using an optimistic isolation level like `READ COMMITTED SNAPSHOT`. By walking through a practical example involving seat assignments at a dinner table, I illustrate how queries that span multiple phases (select and update) can be problematic. This video is part of a comprehensive beginner content series from Darling Data, where all 23 hours of material are currently available for pre-sale at an exclusive price of $250. With advanced content set to launch after the summer, this offer will double in value, making it a fantastic opportunity to invest in your SQL Server knowledge now and into the future.

Full Transcript

Erik Darling here with Darling Data. And we are going to talk about, in this video, where an optimistic isolation level, a row versioning isolation level, may not be perfect because of the way you have written a query. All right. Now, there are all sorts of things in code that may attempt to enforce integrity of some sort. It may be referenced referential, it may be business rules. It might be in a trigger, it might be in a procedure, it might be in a function, who knows where you’ve hidden these things. And that code may not work the same way under a row versioning isolation level that it would under a locking isolation level, unless you add in locking query hints. Now, before we do that, all 23 hours of this fantastic beginner content is available to you to start watching right this instant. You push button kids. You don’t know how easy you have it. All for 250 bucks currently on the presale price. That’s good for the rest of your life. And that will double in value, at least, when the advanced material goes up after the summer. And you’ll wish that you had spent less now. So you should do that now. Anyway, let’s talk about this stuff. So I am going to create a table and fill it with a table and fill it with a little bit of data. And this is what the data in our table looks like. All right. So I’ve got some lucky fans in here. And what we notice immediately about this table is that there is a there is a seat at this table. Right? There’s an empty seat at this table, we have five lucky fans who are going to eat dinner with someone, but who will they eat dinner with? That’s the question.

Now, when I said, because of the way you’ve written the query, we need to talk about that. So in right now, in my Stack Overflow database, I have read committed the read committed snapshot isolation level turned off, it is disabled, you can see the off button right there. And this is the query that will be running. What we’re going to do is we’re going to declare a variable up here to have a name, right? Because we have to we have to give that seat to somebody in this case, it is going to be Eric. Right? Sounds good to me. I like dinner. And what we’re going to do is we are going to run a query to select the top one seat that is free.

Right? So we had that one free seat. And that that free that seat had is free equals one and had a null for the name, we don’t need to check that though, all we need to see that see is that the seat is free. And then based on this free seat, we are going to update the table. And we are going to set is free to zero and assign that seat to a lucky contestant. And what I’m going to do is essentially output the rows that get inserted. So it’s a little bit easier to see exactly what happened when the query ran.

And then, of course, that that CTE, sorry, that CTE gets joined to the table based on the ID that we find here. So under the normal circumstances, or rather under read committed locking, assuming that you don’t have no lock hints everywhere, because if you have no lock hints on these queries, you’re in, you’re not, you’re going to see weird results no matter what. But assuming that you are using read committed locking and you’re depending on that locking to, to give you the behavior that you want your query to have.

If we begin a transaction and we run this update, we will see that this, this lovely Eric right here acquired seat number one. And we have set his name here and we have said that you are, that seat is no longer free, which means that if we come over here and we try to do the same thing, right? We do this stuff and we try to run the same query.

This query is going to get blocked, but importantly, most importantly here, this query is going to get blocked here. This is where this query is going to get blocked because that other query has an exclusive lock to update the, update the, uh, the dinner plans table. It’s, it’s going to get blocked here.

The select gets blocked. So if I try to run this, it’s going to be blocked and we can, we can tell it’s blocked because it’s just sitting there spinning and nothing good is happening, right? We’re not getting any, uh, not getting anything out of this at all.

We are, we apparently have some sort of police emergency outside. Thank you. Thanks for keeping us safe. Uh, but we are completely blocked up here.

So if I come over and I commit this transaction, this will, uh, this will go, this will be committed now. The, the change that we made here and this query will find no free seats, right? So this thing right here, we got no rows back from the output clause.

And you might think, Hey, this is great. Read committed locking. Amazing. You know, this query was blocked for a while while we were talking about stuff and the, the, the, the, the federalities were outside doing something, who knows what.

Uh, but like this, this gives you, I guess, what would be a correct results. We did, we, we did not lose the update that the first query ran because this, and this query did not overwrite it because this query did not find a free row when this select query finished running. As soon as that lock released and this query was allowed to run, it was like, there’s no free seats.

Wah. Ah, that’s a bummer. This backwards Eric can’t possibly have dinner now. So let’s reset those tables. Right?

So we have set name back to null and we have set is free back to one. And that’s all for seat number one. Uh, and now we are going to come over here and we are going to set read committed snapshot on. This might take a second based on, cause we have the, the, the rollback immediate thing happening, which is always, always the best way to run your, uh, your changing database read committed state.

And, uh, let’s do this again. Right? Let’s, uh, let’s re, let’s rerun this.

So we are going to, uh, run this whole thing and this is going to complete immediately. Right? So, uh, we see that, uh, we see that forwards facing Eric has gotten this seat and this seat is no longer free. Now, since now that we’ve done this, what’s going to change is that when this update ran or like, well, I mean, this update is currently running.

We have not committed this update yet. Uh, this update is generating a version of this row. Right?

The row, like the row that we found here, this update has generated a version of this row where is free equals one. And the name, the name is still null. So if we come over here and run this, it’s still going to get blocked, but it’s going to get blocked in a different place now.

So before, when we ran this, this part of the query, this select got blocked. The select got blocked because of the update lock, but now this part of the query, this select can run and read the version of the row that got created when that update happened. What’s blocked here now is this update trying to change data in the table, actually trying to change that same row.

So if we come back over here and we commit this transaction and we come back over here, we’ll see that now backwards Eric has overwritten forward Eric. Okay. So because of this query got blocked in a different place, the select didn’t get blocked.

The update got blocked. The select found a row in the row versions and said, oh, cool. Look what I found. It’s a free seat.

And then updated the table to give that free seat to someone, even though that seat was taken fair and square by forwards facing Eric. So that’s not good. Right.

So this is not a situation that we want to run into. Now, to make things perfectly clear here, though, if we had just written the query like a sort of normal human being, that situation would not arise because there wouldn’t be two phases to the query. There wouldn’t be the select phase to locate a row to change and then an update to change that row.

If we just ran an update top one to set whatever seat to Eric and said is for equal zero, we wouldn’t have had that because the update would have done would have just blocked the other update and we wouldn’t lose it. If you have code that relies on locking for that sort of behavior and you want to switch to an optimistic or rather a row versioning isolation level, you may need to look at something different. You might need to do something.

You might need to change your queries. So if we come back over here and we reset this, well, this will go back to square one and we still have read committed snapshot isolation enabled and we are still running under the read committed isolation level. Right.

We set this here so that the query is free to use the row versions when it when it runs. And let’s do the same thing now. So let’s run this up to the commit transaction. Do that and now let’s come over here and just run the whole thing.

This query gets blocked again, but now it’s the select that gets blocked again and not just the update. So if we come over here and commit this transaction and we come back over here, this query will go back to not having found a row because now this select would have been blocked rather than just the update getting blocked because we added this read committed lock into it. The read committed lock in makes this select obey the locking semantics of read committed locking and not use row versions to go find this stuff.

This thing got blocked looking at the table. So if you if you are looking at switching to using a row version isolation level and you have maybe code that looks like this or you have code in triggers or other places that might like, you know, enforce some sort of integrity or business rules or something. You might need to start looking at that code to figure out if you need to add those read committed locks lock hints to it.

If all of your queries already have a bunch of no lock hints on them, you don’t need to do that. But you do need to get rid of the no lock hints. So your queries start using the row versions and stop doing dirty reads.

All right. I feel like this was a complete video. I feel like we have we have we have lived up to expectations here. All right.

We have we have completed our intended course of action. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. Next. We are actually done talking about transactions and isolation levels after this, at least for the beginner content.

After this, we have a bunch of programmability stuff to talk about. So, of course, there’s there’s a lot in there. So, oh boy, more videos for you.

All right. You’re welcome. Anyway, that’s good here. Thank you for watching.

Going Further

SQL Server Performance Office Hours Episode 29

Posted on September 15, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 29

To ask your questions, head over here.

In Brent’s post about maintenance he said updating statistics causes parameter sniffing. Should I stop doing that? It’s very confusing.

Hi Erik. You discussed index compression in a YouTube video about 10 months ago. I do not see a video on table compression. What is your professional opinion on table compression, and do you ever use it or recommend it to clients? Thank you sir.

Hi Erik, what are thoughts regarding AI and its long term impact on the dba world. I used Claude for the first time a month ago and I’m now considering retraining to be a plumber… Cheers, Oli the dba.

Which metric best reflects actual execution time – the “Last Total Elapsed Time” in the Messages tab via SET STATISTICS TIME ON? In production, a query ran slowly despite a low time shown in the execution plan (guessing low CPU time); what factors might cause this, and how can I find and resolve them? It wasn’t just slow in SSMS but the app too. So I guess we can rule out high Client time?

I’ve been watching your T-SQL bgeginner content and it’s really great. I thought I was more advanced but I’m learning a lot of surprising things. I can’t imagine whatt eh advanced content will look like. How so you remember all this stuff?

Video Summary

In this video, I dive into a series of Office Hours questions from viewers, providing insights and advice on topics ranging from parameter sniffing and statistics maintenance to table compression and the long-term impact of AI on the DBA world. Whether you’re dealing with performance issues or curious about future trends in technology, there’s something for everyone. I also share my thoughts on the T-SQL beginner content that has been well-received by many, emphasizing the importance of continuous learning and practice in the tech field. If you have your own questions or want to support the channel, make sure to check out the links provided in the video description.

Full Transcript

Erik Darling here with Darling Data, and you guessed it, you smart devil you. We are going to do an Office Hours episode. Why? Because I have five questions to answer. So I’m going to answer them. If you want to ask your own question, there’s a link right there. It’s going to look very much like a link down in the video description where you can go ask your very own questions. It’s all very nice and streamlined for you. If you want to support the channel, there’s also a link down there to do that. It’s just a nice way to say thank you for the endless hours of content that I produce. If you’re into that sort of thing. If you’re into the whole gratitude trip. If you need consulting help, and boy, do you need consulting help. I’ve seen your servers. They’re messes. I am available for all of these things and more. And as always, my rates are reasonable. If you want to get your hands on my performance tuning training, you can get all 24 hours of it for about 150 US buckaroos. That will last you for the rest of the day. The rest of your life. Another link down in the video description to do all that with. And if you want to get in on my T-SQL course, while it is still at the pre-sale price of $250, you can. 23 hours of beginner content is out and available. And this will go up to $500 when the advanced material drops after the summer. And so please buy it now before you send an email to me saying, hey, can I get it for the old price? And I say, no, no, you can’t. Anyway, the nice folks at Redgate, they’re making me leave the house this summer. So August, September and October, I will be in New York, Dallas and Utrecht with the Pass on Tour extravaganza, all leading up to the Pass Data Community Summit taking place in Seattle November 17th to 21st.

And now let’s go answer some office hours questions here. Let’s zoom this in to a reasonable degree so that everyone can see exactly what’s happening here. All right. In Brent’s post about maintenance, he said, updating statistics causes parameter sniffing. Should I stop doing that? It’s very confusing. I know the post you’re talking about. I’m not sure that I would have ended the sentence so abruptly about that. But, you know, to each their own. You can’t. It’s hard to write hard to write a whole book in every post. But, you know, when when when you look at Brent, one of the first things you notice is that he is a fitness enthusiast.

What what what may not be obvious is that his fitness icon is a lady named Susan Powder, who is big in the like, I guess, the 80s and 90s. And her catchphrase was stop the insanity. And so I would what I presume was meant by that is if you are running nightly statistics maintenance and you come in in the morning and you are facing some sort of server meltdown because all your queries got bad, different, weird plans that maybe you should examine the way that you are updating statistics nightly. And perhaps rein that in a little bit, perhaps change your technique a bit. I can’t tell you exactly what to do because I don’t know if you are facing these things.

But, you know, when you update statistics, you, of course, invalidate a lot of execution plans. The execution plans become uncashed or however you want to put it. And then SQL Server compiles a new plan based on whatever comes in next. So, you know, you can either take that to is, you know, if you are facing that, if you if you are facing that issue, then you might consider some things like figuring out which queries are susceptible to that.

And you can either maybe, you know, not not not do the statistics maintenance for tables involved in those queries. You could also, you know, you know, use a plan guide or force a plan with query store that doesn’t result in your server melting down. You could, you know, like, you know, after maintenance, you could run a run a store procedure or query with the with parameters that get you the execution plan that you want.

There are there are there are lots of ways to do that that don’t involve stopping updating statistics wholesale, because if you want to stop that, you would actually have to look at, you know, turning auto update stats off and, you know, some other stuff, which could also cause parameter sniffing. And, you know, it’s just my general feeling and my general experience is that when the statistics that are a good representation of the data in your tables and indexes is a good thing for SQL servers query optimizer. So, you know, it’s a little tough to balance that with not updating statistics.

So, I don’t know, perhaps there will be a clarifying post at some point in the future. But sorry, you were confused by something someone else said. Anyway, hi, Eric.

Hi, you discussed index compression in a YouTube video about 10 months ago. I do not see a video on table compression. What is your professional opinion on table compression? And do you ever use it or recommend it to clients?

Thank you, sir. Sure. So, when you say table compression, I assume you mean that you are talking about applying compression to a heap, since index compression would involve clustered and nonclustered indexes. Both, since they are indexes.

And a clustered index makes for a clustered table, not a copy of the table, but the table. So, the only thing left is a heap. That is a table without a clustered index. And the answer there is no.

And the reason why is because index compression, you know, so, like, just remembering the details of this, for an index, when you apply compression, assuming page compression, then as rows get added in, row compression will be applied. And then when a page is full, then page compression will be applied. That could be, it’s a little foggy for me because I haven’t looked at that in a long time.

But the thing with heaps is that data is not compressed on insert to a heap. You have to rebuild the heap in order to apply it. So, usually when I’m talking about heaps with clients, it’s not should I add page compression to this heap.

It’s should this table be a heap? Check yes or check no. If no, then depending on the size of the heap, perhaps we should explore page compression for when we decide on and add a clustered index to that table to make it a clustered table and take away its heapness.

So, there we go. All right. All right.

Oh, another hi, Eric. Look at these polite people. Hi, Eric. Hi, it’s me. Oh, that was not a good square. That was covering up all sorts of stuff. All right. What are your thoughts regarding AI and its long-term impact on the DBA world? I used Claude for the first time a month ago.

Jeez. Late to the game. And I’m now considering retraining to be a plumber. Cheers, Ali the DBA. Well, maybe we should start calling you Ali the plumber. So, I’ve talked about this stuff a couple of times in the past.

And, you know, again, with the AI LLM thing, you don’t really want to watch where the ball is. You want to watch where the ball is going. And so, like, right now, if you are a, you know, moderate to highly skilled DBA, AI is not going to replace you immediately. Depending on the trajectory of AI, because there are all sorts of variables and factors that are going to play into the trajectory, it could get very good and end up being enough to replace moderate to highly skilled people.

Or other factors may kick in and it may be less of an impact. So, like, right now, the things that a lot of AI people worry about is, like, you know, like, just as far as, like, you know, like, things progressing with it. You know, because, like, LLMs, like, if they, like, on the current trajectory, will just keep getting infinitely better and people will stay about the same.

Like, what a lot of people are just like, well, you know, it’s, like, high energy expenditure. It costs a lot of money to do all this stuff and there’s not a lot of, like, return on investment for it. So, like, there are things that could make AI, like, stop just infinitely getting better and kind of, like, crater it off.

But then there also might be some things that happen that improve either the energy, you know, cost, things like that and make it so that there is some return on investment for it. But I think the biggest problem that people are facing right now is that there are a lot of idiots in the world and a lot of idiots in the world are in charge of people. They are managers.

They are executives. They are C-levels. And they are not very highly technical people. And they think that AI and LLMs right now can replace people. And I’m sure that there are some replaceable people in the world.

To some degree, everyone’s replaceable. But I think the biggest thing right now is they don’t realize how, like, sort of not good, like, LLMs are, like, at, like, the deeper level thought. And I know that because when I try to ask an LLM to do deep research, it ends up quoting my blog post back to me.

And I’m like, there has to be something better. Like, there has to be, like, better out there. Like, why are you coming back to me with my own blog posts?

So, you know, depends on a lot of stuff. It could, of course, you know, be a real nightmare for people. And it, of course, could just end up being, you know, sort of a, I don’t know, like, slightly better Siri or something.

I don’t know. So, you know, it does depend a bit on a lot of things. But really, the worst part about it is the higher-ups thinking that it’s way more capable than it is.

It’s, you know, because they, like, they’re under a lot of pressure to use it and keep up with, like, technology trends and stuff. And, you know, like, I try, like, I use AI, or I try to use AI for a lot of things. And I just find it severely wanting for most of the things that I try to do with it.

Anyway, let’s see what we got here. Which metric best reflects actual execution time? The last total elapsed time in the messages tab via set statistics time on?

Question mark. And that went on? In production, a query ran slowly, despite a low time shown in the execution plan, guessing low CPU time.

No. What factors might cause this, and how can I find and resolve them? It wasn’t just slow in SSMS, but the app, too.

So I guess we can rule out high client time. Well, I don’t, I don’t know. It depends on how many rows your query was returning. But I don’t, I don’t, I don’t, I don’t really use set statistics time on anymore.

Now that query plans have, like, the operator times and stuff in them, I just, I don’t see much of the point in it. You can get all that information from various parts of the query plan without having to switch over to the messages tab and look at a bunch of nonsense. But, you know, easy ways to figure that out.

So assuming that you are looking at the right thing in the actual execution plan, sometimes looking at the last operator is not a great way of figuring out. If, if, what, what, how long a query took, because if it was running in either mixed row and batch mode or just all in batch mode, then there could have been some longer running operators off to the side that you didn’t see.

So if you right click on the root of any query plan, any actual execution plan, rather, there will be an actual time statistics thing in the properties window. And that will tell you the CPU and elapsed time of the query.

And that, that, that’s also a good place to look because if you are returning a lot of rows, that will, of course, also include the time that it took for SSMS to receive and render the results. And that can be, that can take a long time.

It might be very, very easy thing for you to test just dumping the results into a temp table, assuming that it’s not like millions and billions of rows. And just see if the elapsed time reduces because you’re sort of discarding the results. You could also try discarding the results via SSMS or assigning columns to variables or something.

But that can have an impact on the query plan overall. So I don’t usually recommend that. But, you know, you know, I wish you had shared the plan or something with me, because then I could give you a more definite answer.

But like I said, I don’t really use set statistics time anymore. I generally rely on what’s in the execution plan. And what you see for the operators and actual execution plans is, of course, wall clock time, not CPU. For CPU, you have to look at the operator properties to determine that.

All right. Last question here. I’ve been watching your T-SQL beginner content, and it’s really great.

Well, thank you. What a sweetie you are. These polite people with their hierics and their compliments. I thought I was more advanced, but I’m learning a lot of surprising things.

Well, surprises abound with T-SQL. It’s actually what the S in SQL is. Surprise. Surprise.

I can’t imagine what the advanced content will look like. How do you remember all this stuff? Well, most of the time I don’t. Like a lot of people who work in tech, I have no internal dialogue or biographical memories.

So I write a lot of stuff down. And that’s generally how I am able to remind myself of things if perhaps I have forgotten something. But, you know, a lot of it is just the fact that I do it all day, every day.

And, you know, the more you do stuff all day, every day, the more you remember, kind of. The more it kind of just sticks with you becomes kind of muscle memory with things. And it becomes harder to forget.

Or I don’t know. I don’t want to say it becomes harder to forget because, man, there’s always a way to forget. But, you know, it does become a bit easier to have these things in the front of your mind rather than having them fly out of your ears. Anyway, that’s five questions.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video tomorrow. All right. Good night.

I love you.