Help! My Query Runs Too Long To Get An Actual Execution Plan!
Thanks for watching!
Video Summary
In this video, I share some tips on how to capture query plans for long-running queries in SQL Server. Specifically, I demonstrate two methods: using the SP Whoisactive feature and my own SP Human Events store procedure. These techniques are particularly useful when you need to gather detailed information about a query’s execution without having it run too long to get an actual plan. I also take some time to highlight the official BeerGut magazine website, which is now live with a collection of historical magazine covers that cover a wide range of topics, including SQL Server and community heroes like Sean Gilardi and Joe Sack. The video concludes with a bit of humor about query tuning and operator times, encouraging viewers to support my work if they find it helpful.
Full Transcript
Erik Darling here with Darling Data. And before we get into anything from this video, I realized that I have not… I did, but I did not… have not announced on YouTube the official website for BeerGut magazine is in fact live and in production. I added a bunch of historical magazine covers to it, and I wanted to and I’ll be adding more as time allows, but there’s quite a few up here now with really just some thought-provoking, really just wonderful topics that BeerGut magazine has covered over the years. This is a personal favorite of mine, about beer and the goth club. It’s a very good issue. There were some other good ones. You know, there’s some about me. That’s clearly probably the finest representation of me that I’ve ever seen, I’ve ever seen in my life. We have issues devoted to SQL Server, Community Heroes. There’s one about my friend, Sean Gilardi here. There’s one about even Joe Sack, the patron saint of databases. We’ve got one about Paul White. Some more about me. I don’t know. There’s one about some bass player who likes Entity Framework or something. We’ve got…
Probably, I think the finest… Probably the finest copy of BeerGut magazine in existence. I’ve got some autographed ones sitting around. BeerGut magazine did St. Patrick’s Day and boy, that was… That was pure chaos. We’ve also got some nice issues about… Well, our friend Taryn Pratt. We’ve got one about Kendra. We’ve got one about our Canadian friend. Michael Swart, who is… Again, a tremendously faithful representation of his likeness there. And, of course, we’ve got one about InfoSec Superstar. Super… Superstar? Not stupid star. That’s messed up. Sherrod DeGrippo. So, we’ve got… I’ve got magazine covers going back a ways here. And I’ll be adding more as my scanner allows. But, you know, it’s been fun, at least so far, creating these and getting that going.
But, anyway, let’s talk SQL turkey. Boy, I ruined that one, didn’t I? Spoiler alerts. Anyway, logo. Proper amounts of time paused on the logo. We’re going to talk about what happens or what you can do when your query runs too long to get a full actual plan. This can happen for a variety of reasons. I’m actually going to cover a slightly different scenario, too, just because I feel like it.
And, gosh darn it, it’s my YouTube channel. Before we do that, let’s talk about turkeys. Specifically, turkeys that cost $4 a month, just like memberships to this channel, if you feel like showing your support with a monetary donation. If you don’t have $4 a month because you spent it all on actual turkeys. I know inflation has been driving the price of turkeys up tremendously lately.
You can like, you can comment, you can subscribe. All will be well and happy in my heart if you do that. If you need help with your SQL Server. Your SQL Server is not performing very well. I don’t know. Maybe, you know, you tried sending it to some improv classes and it was like, this didn’t get much out of it.
If you need any of this stuff, that is my job and, as always, my rates are reasonable. If you would like some high-quality, low-cost training for a SQL Server, I’ve got that in abundance. I hope you consider 24 hours or so of training in abundance because I do.
24 hours is a pretty good amount of training for anything. You can get that for about $150 US dollars for life and you should take advantage of that while it is available. I mean, while your life is available, of course.
If you would like to see me live in person, November 4th and 5th, I will be doing two days of pre-cons with Kendra Little at Past Data Summit. If there is an event near and dear to your heart and you would like me to come there while you are still alive and do a pre-con, let me know what that is so I can submit. Or maybe you’re just so cool you can fast-track me right in.
Whatever works. Either way, let’s get on with this query party. So let’s say that we’ve got a query that runs for, I don’t know. I think for the sake of argument, we’re just going to say that this runs too long to get an actual execution plan for.
There are two ways that you can do this. One is if you are cool with, if you have like a fairly simple, like either just a single query or, you know, a store procedure where you kind of know that one query and it runs for a long time. What you can do is turn on actual execution plans, either using the little button up there that says include actual execution plans.
Or if you know which query in your store procedure causes you the most problems, inside of your store procedure, you can wrap that particular query and set statistics XML on. And then, you know, your query here. Oh, that’s the wrong button.
Your query here. And then at the end of that, set statistics XML off. And what that will do is for that one query, actual execution plans will be enabled. What that’s really helpful for is if you are in a situation where there are a lot of other little queries that do stuff around your big problem query, like assign variables, go look at stuff, maybe stick stuff in temp tables, things like that.
You can do all that stuff without fetching the query plans and then just fetch the query plan for your troublesome query. Once that’s running, now I have a, I mean, this store procedure that I’m using here only has one actual query in it. So we don’t really need to worry too much about anything else.
But if I run this and then I come over to this window, that’ll run for about 14, 15 seconds. And, you know, as we watch the time sort of pile up at the bottom there, we can see that query running all in there. And as we click on the query plan for that, since we have actual execution plans enabled for that query, we can actually see that query making progress with operator times in there.
Now, these times are not going to be absolutely dead to life perfect. Dead to life, that doesn’t make sense. Why did I say that?
Dead to rights? Dead to life. It’s like a hardcore band. Actually, now that I’m thinking about it, I’m going to start a hard, that’s going to be the official Beer Gut Magazine hardcore band is dead to life. What the hell is wrong with me?
But you can at least get some idea of like, okay, you know, we’re spending a long time in here or we’re spending a long time in here. Oh, I don’t like the way this looks. We spent a long time doing this, that, and the other thing.
So as the query makes progress, you can, with at least some, you know, semblance of reality, not like as good as having the actual plan, you can at least grab, like get some idea of like, oh, I see, we’re jammed up here. Let’s try and fix this and then run it again and see how much progress we made.
So that’s one way of doing it. So SP who is active will, at least for more, like, you know, more recent releases of it. Like, you know, there was a release of SP who is active now like three years ago where this was in there, but it was in the release before that too.
So hopefully you have updated SP who is active sometime in the last three or four years. Some of you don’t. Some of you never update anything.
It’s wild. So that’s one way of doing it. Another way of doing it would be to use my store procedure, SP human events. Now, this doesn’t solve the problem of capturing in-flight query plans, but it does, it can help you figure out which queries in a store procedure run for a long time and screen out the ones that don’t.
So the way that I’m running SP human events here is I’m capturing query performance metrics. I am only interested in queries that run for, that have one second or more of duration. And I am limiting things.
Oh, why did you go away? And I’m limiting things to just my session ID, which I have, you know, of course, thoughtfully signed up there. And I’m using the keep alive parameter so that I have a persistent ring buffer session that captures stuff. Now, that will, of course, clear out as it fills up.
But before it does, it’ll stick all sorts of live data in this window. Right. So if you right click on an extended event session and you say watch live data, a window just like this will show up and you’ll be able to add it and you’ll be able to see events as they come in. Where this is useful is because we’re capturing the query post execution plan event.
Right. Or sorry, query post execution show plan event up here. We actually get the full query plan once it’s finished here.
Right. So looking at all this stuff, we can see like the actual times in some of these operators or for all of these operators. These are, I think, mostly correct.
And we can see, you know, oh, we spent four and a half seconds there. We spent 3.8 seconds there. This is all stuff that we could, you know, like try to fix in various ways. This isn’t a query tuning video.
This is to help people who, you know, just need to either get advice about a query, capture a query plan. You know, if you want to ask a question on like Stack Overflow or Stack Exchange or wherever you prefer to do your Q&A stuff from. This is just ways to help you get better answers faster.
Posting pictures of query plans, posting estimated plans will often not get you very good advice very, very quickly unless there is something just so absolutely pathologically wrong with your query that someone can offer good advice on a rewrite. Just, you know, kind of like, you know, psychic finger training on things. So this is a couple ways to capture query plans for stuff.
I guess the nice thing about the extended event option is, you know, aside from being able to, with SB Human Events, aside from being able to screen out queries that, you know, don’t take very long or don’t meet a certain threshold, if you have a long store procedure with multiple steps in it, you can capture all of the query plans for those. Now, that doesn’t help if they all run too long for you to get an actual plan from, but it might help if you are, if that, if you are looking to, you know, capture results in a way that, you know, you can sort of have a record and log of and maybe come back and revisit.
You know, you can kick something off, go, you know, go to bed at night, come back in the morning and see if it’s done, stuff like that. So there are a couple ways to get query plans for things that run for a long time or for, you know, if you, you know, in a pinch and you just want to like do some, you know, like, let’s see how it goes tuning stuff. The SP WhoisActive method is pretty good for that because you can stop and start the query, make changes, see if those changes actually happen.
If SQL Server chooses your indexes, if it, you know, chooses a better plan, all sorts of stuff like that. So just a couple of quick ways of making your query tuning life easier in this video. Not too much else to go on here.
I’m not going to tune this query because I’ve intentionally made this query bad so that it took enough time to run and make sense for this set of demos. So we’re not going to do that. We are going to stop and marvel for a minute, though, at the fact that this query really only ran for 16 seconds about.
But SQL Server might be trying to tell us that it ran for almost 32 seconds. So, again, pretty please. If anyone from Microsoft or if anyone who knows someone at Microsoft, our friend Sam, someone at Microsoft, if anyone is out there, please have some work on the operator time code.
Because some of the things that I’ve seen make me question the very basis of reality. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. I hope that you will continue to watch and learn something. But if you don’t, I don’t know, maybe you’re off to learn Postgres or Big Data. You’re going to be a data engineer now.
You’re going to go DuckDB yourself or something. I don’t know. Whatever you want to do. Have fun. That’s the important thing. Consequences be damned.
Anyway, thank you for watching. Goodbye. I think it’s dinner time. Mmm. Dinner.
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.
