M’ocean
Video Summary
In this video, I delve into the intricacies of parameter sniffing in SQL Server and address a question posed by Bradley Jamrozik on Twitter regarding optimizing for large values to ensure always getting powerful execution plans. I explain why simply opting for a big value might not be the best approach due to resource constraints, particularly focusing on concurrency limits such as worker threads and memory grants. By examining these limitations through practical examples on my laptop’s hardware setup, I illustrate how different execution plans can impact the number of concurrent queries that SQL Server can handle efficiently.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data. You should be used to that by now. If you’re not, I’m sorry. You just have trouble accepting change in your life. Change and then stability, I guess. Anyway, I’m recording this video because I got asked a, not good, but a great question not too long ago on Twitter by Bradley Jamrozik, or Rozeker, Rozeker. I don’t know how to pronounce that, Bradley. I apologize. You can correct me somewhere. I hear that correcting people on the internet is sometimes, sometimes happens. It’s a bit of a national pastime at this point. Anyway, the question was, when you’re dealing with a parameter sniffing situation, in a situation where SQL Server comes up with two or even more different execution plans based on which parameter it was compiled with the first time around, why not always just optimize for a big, why not just optimize for a big, crazy value so that you always get a big, powerful plan, probably parallel, probably ask for a decent chunk of memory, all that other stuff. Well, there are, I think, for me, some pretty fair reasons not to always do that. And those fair reasons come down to, of course, resources.
Now, if I look at the hardware that I have in my laptop, I have a processor in there with four cores that are hyper-threaded, unfortunately. I apologize to everyone out there who hates hyper-threading. And I also have 64 gigs of memory in my laptop, of which about 50 is dedicated to SQL Server. When I run these two queries, I can see how many worker threads I have available for SQL Server, which is 576. And I can see how much memory I have available to give out to queries. If I go down here and I zoom in a little bit, I can see that my total and my available memory are about the same. And this is how much memory in gigs I can give out to queries for memory grants.
Memory grants is, of course, memory that queries ask for outside of the usual. I have to run stuff to do other things like sort or hash or, you know, do some columnstore stuff that, excuse me, that consumes additional memory. And these are limits. When you start up SQL Server, depending on how many cores you have assigned to your server, and depending on how much memory you have in your server and your max server memory, SQL Server sets limits for how much it’ll allow itself to give out for different things.
Whenever a query runs, it has to take a little piece from those things. The more pieces that these queries are asking for, the fewer queries in total you can have running. For example, with a serial query, with 576 worker threads, I can run 576 copies of that query. If I have a query that goes parallel and it reserves more worker threads, well, whatever DOP is, is going to tell SQL Server how many parallel threads it can use in a branch.
And if I have multiple concurrent branches, SQL Server, just for example, on my laptop, I have max DOP set to 4. So if I have two concurrent branches, that’s 8 threads. And if I have three concurrent branches, that’s 12 threads. So the more parallel queries, the more parallel branches in those queries, the more threads they can just reserve and run with.
Ditto memory. If a query comes along and asks for a large memory grant, and SQL Server is able to grant the entire thing, well, if I have a query that asks for one gig of memory, and currently I can run just about 37 of them, or let’s just say 36 to be safe.
If I have a query that asks for 10 gigs of memory, I can run far fewer of them concurrently. So if we go over to this tab, and we look at a store procedure where I’ve recompiled before executing for two different parameters, 9 and 0, I get two different execution plans.
This top plan is a serial plan, and if I look at how much memory it asks for, it’s about 17 megs. 1, 7, 1, 1, 2. And if I look at how many threads it asks for, an F4 over here, it’s just one, because it’s a serial query.
This will come in handy in a minute. If I look at the second query, look at the select operator, this thing has asked for, let’s see, 7794944. That’s a seven-digit number.
So since it’s 779, I’m going to say that’s 7.8 gigs of memory, as opposed to 17 for the serial query. If you remember what’s on that other tab, about 37, I can’t run as many of these at once as I can of the other one at once. Far fewer, in fact.
If I look at how many threads this thing asks for, if I… Sorry, we’re going to have a dance party for a moment. Oh, alright.
You know, I’m always worried about playing music while a video, while I’m recording a video. But you know what? It’s alright with me. Anyway, if we look at how many threads this query reserved, we can see that we had one branch that was available to execute concurrently.
And we reserved four threads. So that’s not a ton. Granted, there are queries where you can have a lot more than this going on.
But for this query, in four threads, we can run far fewer of these copies concurrently than we can of the single-threaded version. So for me, when I think about why not just optimize for a big value, right? Why not just have every query run as forcefully as possible?
It’s a concurrency thing. And I know that when a lot of people think about concurrency, they think of locking and blocking and deadlocks and other things that kind of hold other queries up. But concurrency goes beyond that.
Concurrency also goes into, you know, from a resource perspective, right? So like not a logical resource like a lock, but a physical resource like how many threads you have or how much memory you have to give out to queries. These are hard limits.
The more queries you have that take up more of those resources, the fewer of those queries you can run. On a larger server, like on a big, big server, that might shut up windows. That might make less of a difference.
On a smaller server, say that’s maybe already a little bit underpowered for your workload, you might end up with a pretty bad situation. If you run out of worker threads, you hit a weight called thread pool. If you run out of memory to give out to queries, you hit a weight called resource semaphore.
So when asked why not just go with the big plan, well, it’s because of that. Because you have hard limits inside of your SQL Server for how much you can give out to queries. Of course, if you don’t care about concurrency, then the problem is solved for you.
Anyway, my name’s Erik Darling with Erik Darling Data. And thank you for watching. Bye.
Video Summary
In this video, I delve into the intricacies of parameter sniffing in SQL Server and address a question posed by Bradley Jamrozik on Twitter regarding optimizing for large values to ensure always getting powerful execution plans. I explain why simply opting for a big value might not be the best approach due to resource constraints, particularly focusing on concurrency limits such as worker threads and memory grants. By examining these limitations through practical examples on my laptop’s hardware setup, I illustrate how different execution plans can impact the number of concurrent queries that SQL Server can handle efficiently.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data. You should be used to that by now. If you’re not, I’m sorry. You just have trouble accepting change in your life. Change and then stability, I guess. Anyway, I’m recording this video because I got asked a, not good, but a great question not too long ago on Twitter by Bradley Jamrozik, or Rozeker, Rozeker. I don’t know how to pronounce that, Bradley. I apologize. You can correct me somewhere. I hear that correcting people on the internet is sometimes, sometimes happens. It’s a bit of a national pastime at this point. Anyway, the question was, when you’re dealing with a parameter sniffing situation, in a situation where SQL Server comes up with two or even more different execution plans based on which parameter it was compiled with the first time around, why not always just optimize for a big, why not just optimize for a big, crazy value so that you always get a big, powerful plan, probably parallel, probably ask for a decent chunk of memory, all that other stuff. Well, there are, I think, for me, some pretty fair reasons not to always do that. And those fair reasons come down to, of course, resources.
Now, if I look at the hardware that I have in my laptop, I have a processor in there with four cores that are hyper-threaded, unfortunately. I apologize to everyone out there who hates hyper-threading. And I also have 64 gigs of memory in my laptop, of which about 50 is dedicated to SQL Server. When I run these two queries, I can see how many worker threads I have available for SQL Server, which is 576. And I can see how much memory I have available to give out to queries. If I go down here and I zoom in a little bit, I can see that my total and my available memory are about the same. And this is how much memory in gigs I can give out to queries for memory grants.
Memory grants is, of course, memory that queries ask for outside of the usual. I have to run stuff to do other things like sort or hash or, you know, do some columnstore stuff that, excuse me, that consumes additional memory. And these are limits. When you start up SQL Server, depending on how many cores you have assigned to your server, and depending on how much memory you have in your server and your max server memory, SQL Server sets limits for how much it’ll allow itself to give out for different things.
Whenever a query runs, it has to take a little piece from those things. The more pieces that these queries are asking for, the fewer queries in total you can have running. For example, with a serial query, with 576 worker threads, I can run 576 copies of that query. If I have a query that goes parallel and it reserves more worker threads, well, whatever DOP is, is going to tell SQL Server how many parallel threads it can use in a branch.
And if I have multiple concurrent branches, SQL Server, just for example, on my laptop, I have max DOP set to 4. So if I have two concurrent branches, that’s 8 threads. And if I have three concurrent branches, that’s 12 threads. So the more parallel queries, the more parallel branches in those queries, the more threads they can just reserve and run with.
Ditto memory. If a query comes along and asks for a large memory grant, and SQL Server is able to grant the entire thing, well, if I have a query that asks for one gig of memory, and currently I can run just about 37 of them, or let’s just say 36 to be safe.
If I have a query that asks for 10 gigs of memory, I can run far fewer of them concurrently. So if we go over to this tab, and we look at a store procedure where I’ve recompiled before executing for two different parameters, 9 and 0, I get two different execution plans.
This top plan is a serial plan, and if I look at how much memory it asks for, it’s about 17 megs. 1, 7, 1, 1, 2. And if I look at how many threads it asks for, an F4 over here, it’s just one, because it’s a serial query.
This will come in handy in a minute. If I look at the second query, look at the select operator, this thing has asked for, let’s see, 7794944. That’s a seven-digit number.
So since it’s 779, I’m going to say that’s 7.8 gigs of memory, as opposed to 17 for the serial query. If you remember what’s on that other tab, about 37, I can’t run as many of these at once as I can of the other one at once. Far fewer, in fact.
If I look at how many threads this thing asks for, if I… Sorry, we’re going to have a dance party for a moment. Oh, alright.
You know, I’m always worried about playing music while a video, while I’m recording a video. But you know what? It’s alright with me. Anyway, if we look at how many threads this query reserved, we can see that we had one branch that was available to execute concurrently.
And we reserved four threads. So that’s not a ton. Granted, there are queries where you can have a lot more than this going on.
But for this query, in four threads, we can run far fewer of these copies concurrently than we can of the single-threaded version. So for me, when I think about why not just optimize for a big value, right? Why not just have every query run as forcefully as possible?
It’s a concurrency thing. And I know that when a lot of people think about concurrency, they think of locking and blocking and deadlocks and other things that kind of hold other queries up. But concurrency goes beyond that.
Concurrency also goes into, you know, from a resource perspective, right? So like not a logical resource like a lock, but a physical resource like how many threads you have or how much memory you have to give out to queries. These are hard limits.
The more queries you have that take up more of those resources, the fewer of those queries you can run. On a larger server, like on a big, big server, that might shut up windows. That might make less of a difference.
On a smaller server, say that’s maybe already a little bit underpowered for your workload, you might end up with a pretty bad situation. If you run out of worker threads, you hit a weight called thread pool. If you run out of memory to give out to queries, you hit a weight called resource semaphore.
So when asked why not just go with the big plan, well, it’s because of that. Because you have hard limits inside of your SQL Server for how much you can give out to queries. Of course, if you don’t care about concurrency, then the problem is solved for you.
Anyway, my name’s Erik Darling with Erik Darling Data. And thank you for watching. Bye.
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 performance problems quickly.
Related Posts
- SQL Server 2022 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does Not Care To Fix Your Local Variable Problems
- SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
- Defeating Parameter Sniffing With Dynamic SQL In SQL Server