I Love New Gadgets
Thanks for watching!
Video Summary
In this video, I share one of my favorite features in SQL Server execution plans that often goes unnoticed but can provide invaluable insights into query performance. I dive into the actual time statistics property, which allows you to see exactly how long each operator ran for without needing live query plans. By highlighting examples from a recent podcast and demonstrating with a practical query plan, I show how this feature can pinpoint where your queries are spending most of their time—whether it’s on sorting or other operations—and help identify areas for optimization. If you’re curious about optimizing your SQL Server queries or just want to understand execution plans better, make sure to check out the pre-con I’ll be giving at SQL Bits and start exploring this feature in your own plans!
Full Transcript
What’s up? Erik Darling here with Erik Darling Data, the official sticker of my company, or the official company of my stickers, I guess, I don’t know, whatever. I’m recording a quick video today to talk about one of my favorite new features, or not even new, but one of my favorite features that I don’t see enough people talking about when they’re looking at query plans. And I’ve been watching this podcast called Drink Champs, which is awesome, and it’s just like all interviews with rap dudes from the 80s and 90s and stuff.
And I get jealous because when they record their thing, they get to be like, make some noise! And they have like air horns. All I have is this lame can of compressed air. So when I do make some noise, it’s just… It doesn’t have the same effect. And I think next video I might try to get an air horn in here so that I can make some noise too.
But, so anyway, what I’m going to show you is actually a little bit of a preview from my pre-con that I have coming up at SQL Bits. And I’m really looking forward to that. I’m getting a little nervous because it’s like the Saturday and it’s, man, it’s creeping up. Whew! It’s so close.
But, so one thing that I’m going to be talking about in the pre-con is this cool thing in execution plans that shows you how long an operator kind of ran for. So we have this query, right? Now we have this query plan. And there’s a whole bunch of stuff in it.
And there’s a whole lot of stuff that could be slow. If you look down the bottom of the screen, let me zoom in a little bit down here, we can see this dog ran for 29 seconds. 29! And I know people will be like, well turn on live query plans, rerun it.
Well I don’t want to turn on live query plans, I just want to get my actual plan. And I want to see where I spent all my time. Now if I hit F4 on one of these operators, what’s going to pop up is this new window over on the side, the properties thing. And there’s a property in here called actual time statistics.
And when I open it up, not only can I see the actual CPU and elapsed time that I spend on an operator, but if I click these out, I can see how many rows ended up on threads. I can see if my parallelism got all skewed and stuff got all nasty. So we have this timing here.
So we can see that for this index scan, we spent about six and a half CPU seconds on it. And we spent, we use multiple CPUs to make things faster. And so we spent about one point, well, you know what, I’m going to round up and say 1.8 actual seconds on the index scan.
Right? And if we go down the line, and we look at all of these different operators, we proceed to different operators, we can start to see how much time we spent in different places. And we get over to this sort operator, whoo-wee!
We spent 19 seconds sorting data. That’s crazy. So 19 out of 29 seconds in this execution plan was just spent on this sort. And of course, this sort spills out to disk and it’s all nasty and gnarly.
And that thing spilled almost 1 million pages out to disk. 980,000 pages. That’s bonkers.
Of course, that took 19 seconds. That’s terrible. And as we go down the line, we’ll see different things for, we’ll see different timings. And the timing kind of adds up as you go down. So it wasn’t a full 19 seconds here, but there wasn’t really a whole lot else going on between that scan and that sort.
So we’re going to call that sort the majority of the time we spend in the query plan. Not every operator has this attribute. So if we look at the, if you look at the, sorry, the compute scalar over here, we don’t have that attribute.
And that’s why it disappears. But if you come back to the sequence project compute scalar, we’ll have our actual time statistics back. And we can look at our filter and we can see that as we go kind of to the left, the time will sort of add up.
But it resets in weird places. I haven’t quite figured out the entire dynamic of when things reset and when things change. It’s a little bit weird.
It’s a little bit weird and foreign to me. I don’t think it’s documented anywhere. So I’m going to, I’m going to refrain from making too many guesses. But as we click around, we can kind of see where different parts of the plan spent different amounts of time. And by the time we get to the very end here to this parallelism, where we gather all the streams together, we’re at about 28 seconds.
And then the sort will have us a little bit higher up a little bit closer to 20. So there was some time spent in the query to show us the results, right? So we had to show the results and format that.
But overall, the majority of the time was spent sorting all this data. Now, of course, you’re going to have to come to my pre-con to learn how we can fix this and make this not take 19 seconds. And maybe you, maybe, maybe I’ll see you there.
Or maybe I’ll see you when I, when I talk about this somewhere else, live and in person. I hope I do, because it’s really neat. And you don’t even need an index to fix it. Anyway, that was, that was a short demo of this cool new, well, I keep saying new, this cool thing that I don’t see enough people talking about when they’re looking through execution plans.
And I hope that you watch this video and you start looking at this when you start looking at your actual plans, too. Thanks, and I’ll see you hopefully again soon, maybe. I don’t know.
It’s gearing up to be a weird weekend.
Video Summary
In this video, I share one of my favorite features in SQL Server execution plans that often goes unnoticed but can provide invaluable insights into query performance. I dive into the actual time statistics property, which allows you to see exactly how long each operator ran for without needing live query plans. By highlighting examples from a recent podcast and demonstrating with a practical query plan, I show how this feature can pinpoint where your queries are spending most of their time—whether it’s on sorting or other operations—and help identify areas for optimization. If you’re curious about optimizing your SQL Server queries or just want to understand execution plans better, make sure to check out the pre-con I’ll be giving at SQL Bits and start exploring this feature in your own plans!
Full Transcript
What’s up? Erik Darling here with Erik Darling Data, the official sticker of my company, or the official company of my stickers, I guess, I don’t know, whatever. I’m recording a quick video today to talk about one of my favorite new features, or not even new, but one of my favorite features that I don’t see enough people talking about when they’re looking at query plans. And I’ve been watching this podcast called Drink Champs, which is awesome, and it’s just like all interviews with rap dudes from the 80s and 90s and stuff.
And I get jealous because when they record their thing, they get to be like, make some noise! And they have like air horns. All I have is this lame can of compressed air. So when I do make some noise, it’s just… It doesn’t have the same effect. And I think next video I might try to get an air horn in here so that I can make some noise too.
But, so anyway, what I’m going to show you is actually a little bit of a preview from my pre-con that I have coming up at SQL Bits. And I’m really looking forward to that. I’m getting a little nervous because it’s like the Saturday and it’s, man, it’s creeping up. Whew! It’s so close.
But, so one thing that I’m going to be talking about in the pre-con is this cool thing in execution plans that shows you how long an operator kind of ran for. So we have this query, right? Now we have this query plan. And there’s a whole bunch of stuff in it.
And there’s a whole lot of stuff that could be slow. If you look down the bottom of the screen, let me zoom in a little bit down here, we can see this dog ran for 29 seconds. 29! And I know people will be like, well turn on live query plans, rerun it.
Well I don’t want to turn on live query plans, I just want to get my actual plan. And I want to see where I spent all my time. Now if I hit F4 on one of these operators, what’s going to pop up is this new window over on the side, the properties thing. And there’s a property in here called actual time statistics.
And when I open it up, not only can I see the actual CPU and elapsed time that I spend on an operator, but if I click these out, I can see how many rows ended up on threads. I can see if my parallelism got all skewed and stuff got all nasty. So we have this timing here.
So we can see that for this index scan, we spent about six and a half CPU seconds on it. And we spent, we use multiple CPUs to make things faster. And so we spent about one point, well, you know what, I’m going to round up and say 1.8 actual seconds on the index scan.
Right? And if we go down the line, and we look at all of these different operators, we proceed to different operators, we can start to see how much time we spent in different places. And we get over to this sort operator, whoo-wee!
We spent 19 seconds sorting data. That’s crazy. So 19 out of 29 seconds in this execution plan was just spent on this sort. And of course, this sort spills out to disk and it’s all nasty and gnarly.
And that thing spilled almost 1 million pages out to disk. 980,000 pages. That’s bonkers.
Of course, that took 19 seconds. That’s terrible. And as we go down the line, we’ll see different things for, we’ll see different timings. And the timing kind of adds up as you go down. So it wasn’t a full 19 seconds here, but there wasn’t really a whole lot else going on between that scan and that sort.
So we’re going to call that sort the majority of the time we spend in the query plan. Not every operator has this attribute. So if we look at the, if you look at the, sorry, the compute scalar over here, we don’t have that attribute.
And that’s why it disappears. But if you come back to the sequence project compute scalar, we’ll have our actual time statistics back. And we can look at our filter and we can see that as we go kind of to the left, the time will sort of add up.
But it resets in weird places. I haven’t quite figured out the entire dynamic of when things reset and when things change. It’s a little bit weird.
It’s a little bit weird and foreign to me. I don’t think it’s documented anywhere. So I’m going to, I’m going to refrain from making too many guesses. But as we click around, we can kind of see where different parts of the plan spent different amounts of time. And by the time we get to the very end here to this parallelism, where we gather all the streams together, we’re at about 28 seconds.
And then the sort will have us a little bit higher up a little bit closer to 20. So there was some time spent in the query to show us the results, right? So we had to show the results and format that.
But overall, the majority of the time was spent sorting all this data. Now, of course, you’re going to have to come to my pre-con to learn how we can fix this and make this not take 19 seconds. And maybe you, maybe, maybe I’ll see you there.
Or maybe I’ll see you when I, when I talk about this somewhere else, live and in person. I hope I do, because it’s really neat. And you don’t even need an index to fix it. Anyway, that was, that was a short demo of this cool new, well, I keep saying new, this cool thing that I don’t see enough people talking about when they’re looking through execution plans.
And I hope that you watch this video and you start looking at this when you start looking at your actual plans, too. Thanks, and I’ll see you hopefully again soon, maybe. I don’t know.
It’s gearing up to be a weird weekend.
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.
Excellent videos, this one is really handy thank you.