A Little About RESOURCE_SEMAPHORE_QUERY_COMPILE Waits In SQL Server
Video Summary
In this video, I dive into a fascinating topic: resource semaphore query compile weights in SQL Server. I share real-world examples and demonstrate how these weights can cause significant issues, particularly when dealing with overly complex queries that take an excessive amount of memory to compile. By using tools like SP Quickie Store and spwhoisactive, I show you how to identify and mitigate these problems, emphasizing the importance of writing more manageable SQL queries to keep your database running smoothly.
Full Transcript
Erik Darling here with Darling Data. Recently voted by BeerGut Magazine Board of Editors to be the SQL Server Consultancy most likely to get sued by BeerGut Magazine. I’m not sure exactly what they meant by that. I’m not scared. I’m not scared of you, Joel. Sue me if you want. So, today’s video, we’re going to talk a little bit about resource semaphore query compile weights, because this is one that I’ve run into several times in recent consulting history, and it’s always fun to talk about because, I mean, it’s not happening to my server. It’s, you know, happening to someone else’s server. I just get to tell them why it’s happening, teach them what it’s happening, and I’m not sure what it’s happening. I just get to tell them how to fix it, and occasionally even if I’m really lucky, help them fix it, which is usually a process of breaking up those awful giant monolithic queries that developers insist on writing into smaller, more manageable little chunks.
So, I have in front of me, us, you, we, everyone, everyone who can see the screen, this query, which is a really stupid query. It is just a series of crossjoins from this tiny little post type table, which has like eight rows in it. The thing is, if you do crossjoin enough times, the way I have here, and I want everyone out there in developer land to have a newfound level of respect for me because I started my, my CTE numbering at zero.
Would also like to remark, these are probably the most readable CTE ever written. Perhaps that would be a nice award, Beargut Magazine. Perhaps, stop threatening me. Nice to you. So, the end result of this, and I’m going to show you the query plan for it, is a query that takes, well, I mean, first, let’s, let’s marvel at this estimated subtree cost.
Right? It doesn’t even fit on the screen when I zoom in. It doesn’t, I’m not sure it’ll even fit on the screen. I just have to scroll that one across.
If anyone wants to pause the video and count the number of zeros in there and figure out what this number would actually be, I’m sure there’s a national debt joke in there somewhere, right? Sure.
But the important, semi-important thing here, aside from that gargantuan estimated subtree cost, is that this query takes, well, takes about 740 megs of compiled memory. Which, I mean, you know, 740 megs, whatever, but that’s compiled memory.
That’s like, not a query memory grant. It’s not like a table that exists physically on the server. It’s just 740 megs to figure out what the hell to do with this query plan.
And it takes about 35 and a half seconds to compile. So, like, local factors involved, depending on a few things. This will take anywhere between, like, 25 and 45 seconds to compile a plan.
Again, if you fiddle enough with compatibility levels and cardinality estimation models, you might see something on the lower end. You might see something on the higher end.
So, let’s see. Okay. There are two things about SQL Server Management Studio that have been irking the bejesus out of me lately. One is random window switching positions.
I don’t know why that happens. And the other is sometimes, like, you ever, like, control and F to find something? And, like, you see a bunch of orange stuff on the sidebar highlight?
And then, like, if you, like, let’s say you’re in a store procedure and then you, like, hit F5 to change something in the store procedure. And it brings up the results, the thing where it’s just, like, query completed at whatever time. And then, like, you get rid of the results.
And then, like, the orange dots all move. And all of a sudden, like, where there are hits for things, it’s all wrong. It’s been making me feel like a crazy person. I don’t like it.
Anyway. Let’s make SQL Server. Let’s get revenge. Let’s make SQL Server feel like a crazy person. So what I’m going to do is show you a couple things about how you can spot this happening. And, well, I mean, we can talk a little bit about how to fix it.
But I think I already just told you. Stop raiding gigantic queries, you fools. So if we run these two queries while there is nothing going on, we will see that my server, based on the amount of memory that I have, has available to it 64 slots in the small query compile gateway, 16 slots in the medium query compile gateway, and one coveted spot in the big query compile gateway.
Since nothing is going on right now, nothing is very active. Well, I mean, for some reason, the small gateway is active. Probably some background tasks doing stuff, hanging out, maybe.
I don’t know. Maybe it’s active because something has to be active and nothing else is going on. I don’t know.
Some of these DMVs are just wacky and goofy, and I don’t wish to learn much more about them than I have to. So, based on these numbers, I don’t know why I threw that away.
Based on these numbers, I have 64 plus 16, 6 plus 4, 70, 80, plus 1, 81 total gateway slots for queries to compile in. Now, if you have a bunch of small, low-cost queries, you can compile, like, infinite numbers of those.
But as soon as queries require a little bit more effort and thought by the optimizer to compile a plan, they’re going to start flooding into these gateways. And as the optimizer, like, figures out how much stuff it’s going to put into compiling a plan for a query, they’re going to start traveling through these gateways until they, until, well, I mean, in this demo, they’re going to, one’s going to hit the big gateway.
The rest are going to get stuck in other smaller gateways. So, the way that we’re going to do that is by having a pink cursor, of course. And what we’re going to do is use SQL query stress, again, maintained by the lovely, talented Eric EJ.
And what we’re going to do is we’re going to run 100 and, I guess this should probably be 160. 161’s good enough. I guess with 81, it should be 162.
But, you know, you know what they say about off-by-one errors. We’re just going to roll with this because this will do a good enough job. We’re going to flood this server with requests. And we’re going to be connected into the remote DAC.
So we can see these things really start to have problems. All right. So this looks pretty stable now as far as the results go. So I’m going to hit cancel here.
Normally, I’d be game to let stuff keep running and, like, you know, show you what happens as, like, stuff finishes and moves. But you know what? I have to, I have to do a pre-con on Friday in Nashville.
And I don’t want to brick my laptop between now and then. So I’m going to be a little superstitious here and not push my luck. Just not pushing your luck is probably superstitious enough.
So anyway, what we’re going to see is up in this top result set where I’m interrogating the gateway DMV, we will see that all of these slots have become active and that all of these slots have accumulated a number of waiters. All these queries are waiting to compile a plan.
Right now, there’s only one query on my server that can compile anything. All right. And that’s this lucky. Oh, man.
It was almost number 13. Lucky query number 14. It’s like lucky number 11. All right. So this is the only one. I mean, again, I’m going to cross my fingers and hope that there’s only one null row in here and that every other row is showing resource semaphore query compile.
Ooh, la, la. What a beautiful demo that is. So only this one query is allowed in that big gateway. And if you have more memory available, you will have more gateways open to you.
I think once you get past like 128 gigs, you start getting more of them. I forget the exact number. I’m sure it’s documented somewhere very clearly and somewhere very easy to find.
Those Microsoft docs are just legendary for their goodness. Yeah. So one query can run and one query can do anything useful.
Now, again, you know, this is a demo query designed to do something terrible. In real life, your queries probably, I mean, they might also have nine CTE in them.
Who knows? All right. Y’all are crazy. But really, you would just be looking for like really big, complicated queries that SQL server would have to think particularly hard about when a plan is getting compiled for it. All right.
Now, if you’re using stored procedures or parameterized queries, generally, you only take a hit on this when they initially compile or if they recompile for some reason. That’s why I have the recompile hint on here because if this thing initially compiled a plan, other queries would just start using it and laughing at me.
Right. So like once you get past the compilation phase and a plan gets cached, other queries can be like, ah, gotcha. But in real life, this often doesn’t happen with usually just doesn’t happen with like one big, awful query. Doing stuff is usually like like a whole cavalry of awful queries just riding into your SQL Server and shooting the place up.
So one way that you can find queries that take a long time to compile is by using SP Quickie Store. And if you use it in expert mode, it will return additional result sets that have compilation metrics in them. And what I mean by that is you’ll have this top section up here and, you know, you might see there’s probably nothing in here that’s so eye catching, except, I mean, maybe the fact that we couldn’t render this this query plan is XML from the from the query store DMV.
I’ve been meaning to do something in here where like if I can’t render the plan, then I’ll like produce some query so you can get it as like just the raw XML as text. So you can save it off as a SQL plan and reopen it. Reopening query plan XML that’s too big to render as XML like in the database works just fine as opening a file.
I don’t know when I don’t know why Windows is so much better at it than SQL Server Management Studio. Who knows? Maybe it’s SQL Server. Maybe it’s Management Studio. It’s hard to tell. I mean, SQL Server has the error about 128 nested nodes of XML, so it’s probably SQL Server as usual.
I’m not going to let SQL Server off the hook. As much as I love to blame and get mad at Management Studio for things, SQL Server is no angel. But if we look here in the results, we’re going to have this section down here called Compilation Stats.
And just because I know the query that I care about, I’m going to look here and I’m going to say, well, you are query ID 14510. And this is ordered just by query ID ascending for some reason. I didn’t really put a lot of thought into the ordering here.
I should probably have some more useful metric and query ID be the ordering principle here. I’m not sure if I would make it total compiles or average compile stuff, but wow, what was 14509? Where are you in here?
Oh, you were the other. See, look at, there it is. There’s another instance of this query from when I was running it. Look at how prepared I was for this demo. But if we look at these two lines in the Quickie Store results for compilation stats, you will see one of these took about 30 seconds to compile. And another one took about 24 and a half seconds to compile.
So neither one of these from my brief tinkering hit the 45 second mark. But in the past they have. They’re just not in these results.
So sorry about that. I’m sure that’s totally disappointing. And you’re going to hate me forever. And you’re never going to forgive me. This injustice. This curse I put upon your land.
So yeah. What I’ve shown you today is sort of how you can figure out which queries are struggling with resource semaphore query compile. You generally want to look for things that took a long time to compile or stuff that if we come over here a bit, we’re going to see.
No, no, no. Where’s the compile memory line? There we go.
Some compile memory. Lots of compile memory in here. So you want to look for stuff that has high compile memory, probably high compile time. You can find that stuff in query store using spquickiestore.
You can see exactly how much you have available gateway-wise for compiling queries. And you can usually see queries that are stuck waiting for memory to compile by looking at spwhoisactive and seeing the resource semaphore query compile weight pile up through a whole bunch of queries. So if I have to leave you with anything, it’s don’t write queries like this.
Unless you need to show people resource semaphore query compile demos. But in real life, you shouldn’t be writing big, complex, monster queries. You should be kind to your SQL Server because it probably has a lot of other problems.
You don’t want to make them worse. Why pile everything on? It’s like making someone in AA quit smoking.
Why would you want to do that to someone? Giving up their only drill in life. Anyway, I’m going to…
I don’t know what I’m going to do. I suppose pack bags or something. I don’t really have a good game plan for tonight. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. If you like this video, I always appreciate a thumbs up. And if you like learning stuff about SQL Server, I would strongly suggest, before Beargut Magazine sues me into oblivion, subscribing to my channel so that you can get big, glaring, blaring notification every time I finish uploading one of these things.
And hopefully after it’s been processed up to HD, because you don’t want to watch these in low definition. You want to make sure that I am in vivid detail. Don’t let YouTube’s downscaling turn me into, like, Minecraft pixel art.
Turn it up to HD. Erik Darling in HD. It’s the way I was meant to be experienced.
Anyway, thank you for watching. This will probably be the last video I record this week. I’ve gone a bunch. And then next week, I’ll pick back up with some stuff for you, because I care about you.
I want you to know the awful things I know. Anyway, thank you for watching. Let’s do this. ification.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.