Category: Execution Plans

A Trick For Working Around Scalar UDF Performance Issues In SQL Server

Posted on by Erik Darling

Mr. Duster


Thanks for watching!

Video Summary

In this video, I share a performance tuning shortcut that came in handy during a recent client project. When working with complex queries involving scalar-valued functions, sometimes the best approach is not to rewrite everything from scratch but to take a “band-aid” route. By temporarily removing these functions and dumping the data into a temporary table, we can significantly improve query performance without losing functionality. This method allows us to run our big, complex queries faster while still applying necessary formatting later on, ensuring that scalar-valued functions don’t hinder overall performance. It’s a practical solution for those of us who need quick wins in SQL Server tuning until more comprehensive optimizations can be implemented.

Full Transcript

Howdy folks, Erik Darling here with the old Erik Darling data. And I want to show you a kind of a funny little performance tuning shortcut that actually came in handy earlier this week. Now, when I’m working with clients, my job is to get them the fastest possible solution to their problem that, you know, that it’s like possible, right? So it’s like, some times I can touch more stuff than others. Other times I can touch less stuff than others. Either way, my job is to get people moving faster in a hurry. Sometimes, like, you know, in a perfect world, we can all take a lot of time to perfectly tune this, that, and the other thing to change, you know, functions and indexes and queries all over the place to get the perfect code. Other times, it’s, we can take what I like to call the band-aid route and we can put a band-aid on it. That gets us to a much, much better place for now until we can get to that wonderful Arcadian pasture that is perfect performance. I know, right? So what I’m going to show you is something that came in handy earlier this week. When I was tuning, it was a much bigger query than the one you’re looking at on the screen. It was a big tangle with, like, a 500-line where clause and all this other stuff.

And there’s a lot of complex stuff going on, even in the select case expressions and whatnot. But what it came down to was that there were a bunch of functions. Now, in the query that I’m looking at, I only have one function, and it’s called initial cap. And it does exactly what it sounds like. It’ll take a string, and it’ll make the first letter a capital, and it’ll make any letter after a space a capital. It’s a scalar valued function. And while we’re all breathlessly awaiting these things not sucking in SQL Server 2019, there are a whole heck of a lot of us who have to deal with versions of SQL Server that do not have the magic of Freud. Now, the real query that I dealt with had a bunch of formatting functions in it.

So, like, there were a whole bunch of things that formatted, like, phone numbers and postal codes and dates and all this other crazy stuff. And I know what you’re thinking. You can’t just, like, use convert to formatted date? No, apparently not. Apparently, you need to scale our valued function to do that. So, rather than sit there and rewrite every single function and mess with logic and results, here’s what I did. Now, I’m going to show you exactly how this reproduces with this query. I’m going to turn on query plans, and I’m going to fire this thing off. And this thing is going to run for around about 11 or 12 seconds.

And part of the problem with the way this query runs is that because of this scalar value function in there, it’s forced to run serially. Now, another problem that scalar valued functions have is that they run once per row, not once per query. We can kind of see that if we look in the query plan and we look at this compute scalar operator.

If we hit F4 here, we can see that this is where that init cap function gets called. All right? So, that’s where we do it. But we do it at the very end. And we only do this over 500 rows. So, this function running 500 times isn’t going to be a big deal.

What’s a much, much bigger deal for this query is that it’s forced to run serially. If we look at the properties of the select operator, we have this non-parallel plan reason could not generate valid parallel plan. This started in the SQL Server 2012 plan XML. This would start showing up in there.

So, if you’re on 2008 or 2008 R2, you’re not going to see this. If you’re on 2012 plus, you will see that if you have some construct in your query plan forcing things to run serially. Now, since there were a bunch of functions in the actual code that I was tuning, what I decided to do was, rather than spend my time and waste someone’s money rewriting a bunch of functions and trying to toggle with indexes and this, that, and the other thing, I just wanted to see how the query ran if I took the functions out.

And that’s what I did in this query right here below it. Is rather than have a reference to the formatted display name, I just have the display name. So, I quoted out the functions and I kept the columns in that the functions were formatting.

And, oops, I hit F5, but I highlighted the whole thing as a professional query performance tuner. I cannot impress upon you enough the importance of highlighting the entire query. So, I highlighted the entire, in real life, I highlighted the entire query.

And I hit F5, and it ran much faster. That finished in 2 seconds, rather than, what, 12 seconds or something? Yeah, it took 12 seconds.

So, we improved this query by 10 seconds, just by not having the scalar function in there. Of course, the reason this query runs much faster isn’t because that function did anything so heinous. It’s because this query was allowed to go parallel.

So, now that we have all this parallelism going on in here, the root query finishes much faster. Now, there are a lot of caveats, and I’m not saying that a query going parallel is always a good thing, or that you should have as many queries going parallel at once as possible, because that has effects on concurrency, how many threads get used by queries, how many threads get used by overall workload.

But, in this case, it didn’t hurt too much. So, what I did is I took that query, and I said, well, you know what? This query gets us close to what we need. We just need to format that data at some point.

And I’m going to tell you something about scalar valued functions. In small doses, they’re not that bad. In small doses, they’re okay.

They’re not too terrible, which I know is not a great thing to say. Everyone’s just going to come up with torches and pitforks and tell me that scalar valued functions are the devil. And a lot of the times, I’ll agree with them.

But in this case, where we’re just formatting data, the scalar valued functions aren’t going out to other tables and doing all sorts of other crazy things. We’re just formatting some data in place.

Well, that still will suck, but we can make it suck a whole lot less if we don’t let it impact our big queries and if we only do it on a limited number of rows later. So, what we can do, and this is a trick that you can try out, is if you have your big queries and lots of scalar valued functions in them, you don’t want to rewrite a bunch of scalar valued functions and have a whole bunch of awful things.

I get it. It’s hard. It’s daunting. But what you can do is you can take your big, mean, nasty, awful queries, and you can dump them into a temp table.

And the dump into a temp table will still take about two seconds to finish. All right. So, that still takes, you know, a couple seconds to get the data into the temp table, but it’s a lot faster than the 12 seconds that we waited for when we just needed to select all the data.

And then after we have data in a temp table, well, now we can just call our functions on what’s remaining. So, now if I hit F5 here, well, that finishes just about as quick as you could want it to finish. So, we still have, like, the old, like, we still have a big query that we have to run and do stuff with, but that query isn’t impacted by the side effects of scalar valued functions.

So, that’s one way to kind of skirt around the issue and then get the data that, get whatever the scalar valued functions need to do at a later point. Anyway, I thought that I would share that with you in video form because I care about you and I care about the health and well-being.

No, I’m kidding. Yeah. No. No. Good luck. Good luck. I wish you luck out there. Anyway, I’m Erik Darling.

Thanks for watching. I hope you learned something. I hope you enjoyed yourselves. And I will see you in some other video some other time. Goodbye. Bye. Bye.

Video Summary

In this video, I share a performance tuning shortcut that came in handy during a recent client project. When working with complex queries involving scalar-valued functions, sometimes the best approach is not to rewrite everything from scratch but to take a “band-aid” route. By temporarily removing these functions and dumping the data into a temporary table, we can significantly improve query performance without losing functionality. This method allows us to run our big, complex queries faster while still applying necessary formatting later on, ensuring that scalar-valued functions don’t hinder overall performance. It’s a practical solution for those of us who need quick wins in SQL Server tuning until more comprehensive optimizations can be implemented.

Full Transcript

Howdy folks, Erik Darling here with the old Erik Darling data. And I want to show you a kind of a funny little performance tuning shortcut that actually came in handy earlier this week. Now, when I’m working with clients, my job is to get them the fastest possible solution to their problem that, you know, that it’s like possible, right? So it’s like, some times I can touch more stuff than others. Other times I can touch less stuff than others. Either way, my job is to get people moving faster in a hurry. Sometimes, like, you know, in a perfect world, we can all take a lot of time to perfectly tune this, that, and the other thing to change, you know, functions and indexes and queries all over the place to get the perfect code. Other times, it’s, we can take what I like to call the band-aid route and we can put a band-aid on it. That gets us to a much, much better place for now until we can get to that wonderful Arcadian pasture that is perfect performance. I know, right? So what I’m going to show you is something that came in handy earlier this week. When I was tuning, it was a much bigger query than the one you’re looking at on the screen. It was a big tangle with, like, a 500-line where clause and all this other stuff.

And there’s a lot of complex stuff going on, even in the select case expressions and whatnot. But what it came down to was that there were a bunch of functions. Now, in the query that I’m looking at, I only have one function, and it’s called initial cap. And it does exactly what it sounds like. It’ll take a string, and it’ll make the first letter a capital, and it’ll make any letter after a space a capital. It’s a scalar valued function. And while we’re all breathlessly awaiting these things not sucking in SQL Server 2019, there are a whole heck of a lot of us who have to deal with versions of SQL Server that do not have the magic of Freud. Now, the real query that I dealt with had a bunch of formatting functions in it.

So, like, there were a whole bunch of things that formatted, like, phone numbers and postal codes and dates and all this other crazy stuff. And I know what you’re thinking. You can’t just, like, use convert to formatted date? No, apparently not. Apparently, you need to scale our valued function to do that. So, rather than sit there and rewrite every single function and mess with logic and results, here’s what I did. Now, I’m going to show you exactly how this reproduces with this query. I’m going to turn on query plans, and I’m going to fire this thing off. And this thing is going to run for around about 11 or 12 seconds.

And part of the problem with the way this query runs is that because of this scalar value function in there, it’s forced to run serially. Now, another problem that scalar valued functions have is that they run once per row, not once per query. We can kind of see that if we look in the query plan and we look at this compute scalar operator.

If we hit F4 here, we can see that this is where that init cap function gets called. All right? So, that’s where we do it. But we do it at the very end. And we only do this over 500 rows. So, this function running 500 times isn’t going to be a big deal.

What’s a much, much bigger deal for this query is that it’s forced to run serially. If we look at the properties of the select operator, we have this non-parallel plan reason could not generate valid parallel plan. This started in the SQL Server 2012 plan XML. This would start showing up in there.

So, if you’re on 2008 or 2008 R2, you’re not going to see this. If you’re on 2012 plus, you will see that if you have some construct in your query plan forcing things to run serially. Now, since there were a bunch of functions in the actual code that I was tuning, what I decided to do was, rather than spend my time and waste someone’s money rewriting a bunch of functions and trying to toggle with indexes and this, that, and the other thing, I just wanted to see how the query ran if I took the functions out.

And that’s what I did in this query right here below it. Is rather than have a reference to the formatted display name, I just have the display name. So, I quoted out the functions and I kept the columns in that the functions were formatting.

And, oops, I hit F5, but I highlighted the whole thing as a professional query performance tuner. I cannot impress upon you enough the importance of highlighting the entire query. So, I highlighted the entire, in real life, I highlighted the entire query.

And I hit F5, and it ran much faster. That finished in 2 seconds, rather than, what, 12 seconds or something? Yeah, it took 12 seconds.

So, we improved this query by 10 seconds, just by not having the scalar function in there. Of course, the reason this query runs much faster isn’t because that function did anything so heinous. It’s because this query was allowed to go parallel.

So, now that we have all this parallelism going on in here, the root query finishes much faster. Now, there are a lot of caveats, and I’m not saying that a query going parallel is always a good thing, or that you should have as many queries going parallel at once as possible, because that has effects on concurrency, how many threads get used by queries, how many threads get used by overall workload.

But, in this case, it didn’t hurt too much. So, what I did is I took that query, and I said, well, you know what? This query gets us close to what we need. We just need to format that data at some point.

And I’m going to tell you something about scalar valued functions. In small doses, they’re not that bad. In small doses, they’re okay.

They’re not too terrible, which I know is not a great thing to say. Everyone’s just going to come up with torches and pitforks and tell me that scalar valued functions are the devil. And a lot of the times, I’ll agree with them.

But in this case, where we’re just formatting data, the scalar valued functions aren’t going out to other tables and doing all sorts of other crazy things. We’re just formatting some data in place.

Well, that still will suck, but we can make it suck a whole lot less if we don’t let it impact our big queries and if we only do it on a limited number of rows later. So, what we can do, and this is a trick that you can try out, is if you have your big queries and lots of scalar valued functions in them, you don’t want to rewrite a bunch of scalar valued functions and have a whole bunch of awful things.

I get it. It’s hard. It’s daunting. But what you can do is you can take your big, mean, nasty, awful queries, and you can dump them into a temp table.

And the dump into a temp table will still take about two seconds to finish. All right. So, that still takes, you know, a couple seconds to get the data into the temp table, but it’s a lot faster than the 12 seconds that we waited for when we just needed to select all the data.

And then after we have data in a temp table, well, now we can just call our functions on what’s remaining. So, now if I hit F5 here, well, that finishes just about as quick as you could want it to finish. So, we still have, like, the old, like, we still have a big query that we have to run and do stuff with, but that query isn’t impacted by the side effects of scalar valued functions.

So, that’s one way to kind of skirt around the issue and then get the data that, get whatever the scalar valued functions need to do at a later point. Anyway, I thought that I would share that with you in video form because I care about you and I care about the health and well-being.

No, I’m kidding. Yeah. No. No. Good luck. Good luck. I wish you luck out there. Anyway, I’m Erik Darling.

Thanks for watching. I hope you learned something. I hope you enjoyed yourselves. And I will see you in some other video some other time. Goodbye. Bye. Bye.

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.

Operator Time Oddities In SQL Server Query Plans

Posted on by Erik Darling

SILENCE YOUR CELL PHONES


Thanks for watching!

Video Summary

In this video, I dive into the complexities of operator times and execution plans in SQL Server, particularly focusing on how they behave differently in batch mode versus row mode. I explain that while these new features are incredibly valuable for query performance tuning, there are some quirks that can make them confusing. For instance, I demonstrate how operator times are measured per operator in batch mode but add up along the plan in row mode, and how there’s no clear visual indicator of when a switch between modes occurs. By walking through these examples using SSMS 18, I aim to help you better understand and navigate these peculiarities, making your query tuning efforts more efficient.

Full Transcript

Howdy folks, Erik Darling here with Erik Darling Data. Still six months in I have managed not to fire myself which is amazing because I always considered me to be one of my most underperforming employees so it’s a real tribute to I guess nepotism that I haven’t fired myself yet. Anyway, I’m here to talk about operator times and execution plans. I want to talk about them because they can be really, really confusing. And hopefully me recording this video will help you understand exactly what’s going on with them. Now, they’re only available in SSMS 18. So if you aren’t using SSMS 18, I actually understand why because it’s really buggy. crashes more than normal SSMS. But this is a great new feature for especially unemployed query tuners who, you know, need to make a buck here and there, spare changing outside bodegas and, you know, trying to get some money up for a beer and a beer and a banana or something. But yeah, these things are weird and I’m going to try to explain why. Now, in SQL Server, well, I mean, 2019 CTP3 batch mode is generally available for rowstore queries. You could still see this in SQL Server 2017 or 16 or wherever else, you know, batch mode is available or wherever you have chosen to sneakily introduce batch mode into your rowstore queries with the temp table trick or with the filtered index trick. But anyway, this gets really odd. Now, the first thing I want to explain is that in batch mode plans, the timing is per operator. But there are two things that suck. One is that there’s no general indicator of which operators ran in batch mode. There’s one operator in this plan, that window aggregate that can only happen in batch mode. So we know that’s batch mode.

But if you look at the index scan, the sort, the compute scaler, the filter, any of those other operators, there’s no visual indicator that they happened in batch mode. The other thing that sucks is that there’s no visual indicator when we switch between row mode and batch mode. There’s nothing that says, there’s nothing that says, hey, like there’s no, there’s no, there’s no like little like interruption in the arrow or like, you know, little like, like, like little flippy sign that says, hey, that we switched here. We made it, we made an adjustment. Now, I’m going to show you why this is important. So I run this, I run this query, and this query does run for about four and a half seconds that that timing isn’t up for debate. That did happen in about four and a half seconds.

But what you’ll see across the bottom of the execution plan is that each, like the operator times for each one of those operators looks funky. They don’t add up. So this ran for 1.2 seconds, this ran for 2 seconds, this ran for 0.15 milliseconds, this ran for 0.9, 900 milliseconds, or 157 milliseconds, 900 milliseconds, 0.37 milliseconds.

So all of these are individual times. And if you add them up across the line, they’ll add up to about 4.3. But what gets weird is this is batch mode, right? So batch mode, this is batch mode. In fact, all of these are batch mode up to right here. Where they stop being batch mode is that this gather streams. This is row mode. And this top is also row mode.

There’s a difference between batch mode and row mode here. And the way that these operator times operate. And that is, for batch mode, it’s per operator. For row mode, it adds up along the plan. I know that sounds confusing, so I’m going to change this query slightly and rerun it. And I’m going to show you the difference. So remember what this looks like. Each one of these operators does a thing, and we measure that thing separately until we get to the row mode part.

And the row mode part reflects an adding up of everything here, right? So when we get to that gather streams, we’re adding up all of the child operator times. Prior to that in batch mode, each operator time is individual. Now I’m going to run, I’m going to change the query a little bit, and I’m going to rerun it. And this is going to execute fully in row mode. So this index seek is row mode, this source row mode, segment’s row mode, this is row mode.

Nested loops, of course, is row mode. I wish bash mode nested loops would become a thing, but it just hasn’t yet. But what the difference here is that when we look across these query times, or when we look across these operator times, they add up. So we have 1.04, 1.84, 1.99, 2.16, 2.33, go up here to 2.6, 2.65.

They add up as we go across, right? So that top operator reflects adding up all of the child operators together because they’re all row mode, right? So each row mode operator is a reflection of it and whatever time the operator or operators before it took.

So if that’s not confusing enough, again, like I was saying, when operators in a plan might switch between batch and row mode, we don’t have an indicator of when that happens, right? So in a really complicated plan where there might be a lot of switches, this might be even harder to try and figure out. So rather than me saying, well, you need to get your times right, I think it just might be easier for us as query tuning people to have a visual indicator of when an operator is in batch mode and or when we flip or even if we had like a visual indicator of when we switch between batch and row mode.

Like maybe the arrow gets interrupted, maybe there’s something between the operators, like a little swirly thing that says, hey, we switched here. So you could at least have an indicator that one thing was batch or row mode and the other thing made a switch to batch or row mode from whatever the previous operator was. Another place where this can get kind of confusing is in plans that look like this.

Now, this plan is all row mode. All of these operators are row mode. There is no batch mode happening in any of these, I promise you. But what happens is at the very end, gather streams, finish it.

Well, it says 11.242, but that nested loop says 11.499. So where these pretty accurately add up across the whole thing, they sort of get funky at the gather streams. So gather streams says it finished about, I don’t know, 300 or so milliseconds before the nested loops did, which is a little awkward.

Again, this isn’t like a big query tuning dilemma. It’s just sort of an oddity. And it’s happened across multiple queries for me.

I have another example of it happening over here where, again, all of this stuff happens. Well, I can’t say that’s entirely true. Anyway, all of this stuff happens in row mode.

Where it’s supposed to add up, it doesn’t quite add up because we have this oddity over here where the hash join says that it finished in 5.572 seconds. And then the gather stream says it finished in 5.132 seconds. This is also a little bit of a weird plan because even though it’s in row mode, at least last I checked it was all in row mode.

Who knows what happens between runs? It looks like it’s still in row mode to me. But this stream aggregate.

So these two operators are killing me. So we have this stream aggregate that ran in 1.83. And then this repartition streams that ran in 1.73. So this was supposed to add up, but it didn’t add up for some reason.

So that’s another thing that’s kind of odd. Sometimes, despite your best efforts to understand these things, you run a demo and then you look a little more closely while you’re recording a video. And you spot something even stranger.

So now I have to go figure out just what that was about. But that’s going to require a lot of day drinking. So I need to go get started on that. Anyway, I do think this is incredibly valuable.

As query performance tuners, usually when we’re looking at an execution plan, it’s a lot of guesswork as to, okay, let’s figure out what went wrong here. Let’s figure out what we need to focus on. Let’s figure out what’s bad.

This does help us focus quite a bit of our efforts on, okay, which operator really did take a long time to run? What’s sticking out like that awful sore thumb? Anyway, I’m Erik Darling with Erik Darling Data.

Still haven’t fired myself, and I will see you around in another video when it’s less hot and wearing headphones doesn’t make my ears sweat. Have a lovely Saturday. Can you talk to me today?

Have a lovely night. Hi. Hi. Thank you.

Video Summary

In this video, I dive into the complexities of operator times and execution plans in SQL Server, particularly focusing on how they behave differently in batch mode versus row mode. I explain that while these new features are incredibly valuable for query performance tuning, there are some quirks that can make them confusing. For instance, I demonstrate how operator times are measured per operator in batch mode but add up along the plan in row mode, and how there’s no clear visual indicator of when a switch between modes occurs. By walking through these examples using SSMS 18, I aim to help you better understand and navigate these peculiarities, making your query tuning efforts more efficient.

Full Transcript

Howdy folks, Erik Darling here with Erik Darling Data. Still six months in I have managed not to fire myself which is amazing because I always considered me to be one of my most underperforming employees so it’s a real tribute to I guess nepotism that I haven’t fired myself yet. Anyway, I’m here to talk about operator times and execution plans. I want to talk about them because they can be really, really confusing. And hopefully me recording this video will help you understand exactly what’s going on with them. Now, they’re only available in SSMS 18. So if you aren’t using SSMS 18, I actually understand why because it’s really buggy. crashes more than normal SSMS. But this is a great new feature for especially unemployed query tuners who, you know, need to make a buck here and there, spare changing outside bodegas and, you know, trying to get some money up for a beer and a beer and a banana or something. But yeah, these things are weird and I’m going to try to explain why. Now, in SQL Server, well, I mean, 2019 CTP3 batch mode is generally available for rowstore queries. You could still see this in SQL Server 2017 or 16 or wherever else, you know, batch mode is available or wherever you have chosen to sneakily introduce batch mode into your rowstore queries with the temp table trick or with the filtered index trick. But anyway, this gets really odd. Now, the first thing I want to explain is that in batch mode plans, the timing is per operator. But there are two things that suck. One is that there’s no general indicator of which operators ran in batch mode. There’s one operator in this plan, that window aggregate that can only happen in batch mode. So we know that’s batch mode.

But if you look at the index scan, the sort, the compute scaler, the filter, any of those other operators, there’s no visual indicator that they happened in batch mode. The other thing that sucks is that there’s no visual indicator when we switch between row mode and batch mode. There’s nothing that says, there’s nothing that says, hey, like there’s no, there’s no, there’s no like little like interruption in the arrow or like, you know, little like, like, like little flippy sign that says, hey, that we switched here. We made it, we made an adjustment. Now, I’m going to show you why this is important. So I run this, I run this query, and this query does run for about four and a half seconds that that timing isn’t up for debate. That did happen in about four and a half seconds.

But what you’ll see across the bottom of the execution plan is that each, like the operator times for each one of those operators looks funky. They don’t add up. So this ran for 1.2 seconds, this ran for 2 seconds, this ran for 0.15 milliseconds, this ran for 0.9, 900 milliseconds, or 157 milliseconds, 900 milliseconds, 0.37 milliseconds.

So all of these are individual times. And if you add them up across the line, they’ll add up to about 4.3. But what gets weird is this is batch mode, right? So batch mode, this is batch mode. In fact, all of these are batch mode up to right here. Where they stop being batch mode is that this gather streams. This is row mode. And this top is also row mode.

There’s a difference between batch mode and row mode here. And the way that these operator times operate. And that is, for batch mode, it’s per operator. For row mode, it adds up along the plan. I know that sounds confusing, so I’m going to change this query slightly and rerun it. And I’m going to show you the difference. So remember what this looks like. Each one of these operators does a thing, and we measure that thing separately until we get to the row mode part.

And the row mode part reflects an adding up of everything here, right? So when we get to that gather streams, we’re adding up all of the child operator times. Prior to that in batch mode, each operator time is individual. Now I’m going to run, I’m going to change the query a little bit, and I’m going to rerun it. And this is going to execute fully in row mode. So this index seek is row mode, this source row mode, segment’s row mode, this is row mode.

Nested loops, of course, is row mode. I wish bash mode nested loops would become a thing, but it just hasn’t yet. But what the difference here is that when we look across these query times, or when we look across these operator times, they add up. So we have 1.04, 1.84, 1.99, 2.16, 2.33, go up here to 2.6, 2.65.

They add up as we go across, right? So that top operator reflects adding up all of the child operators together because they’re all row mode, right? So each row mode operator is a reflection of it and whatever time the operator or operators before it took.

So if that’s not confusing enough, again, like I was saying, when operators in a plan might switch between batch and row mode, we don’t have an indicator of when that happens, right? So in a really complicated plan where there might be a lot of switches, this might be even harder to try and figure out. So rather than me saying, well, you need to get your times right, I think it just might be easier for us as query tuning people to have a visual indicator of when an operator is in batch mode and or when we flip or even if we had like a visual indicator of when we switch between batch and row mode.

Like maybe the arrow gets interrupted, maybe there’s something between the operators, like a little swirly thing that says, hey, we switched here. So you could at least have an indicator that one thing was batch or row mode and the other thing made a switch to batch or row mode from whatever the previous operator was. Another place where this can get kind of confusing is in plans that look like this.

Now, this plan is all row mode. All of these operators are row mode. There is no batch mode happening in any of these, I promise you. But what happens is at the very end, gather streams, finish it.

Well, it says 11.242, but that nested loop says 11.499. So where these pretty accurately add up across the whole thing, they sort of get funky at the gather streams. So gather streams says it finished about, I don’t know, 300 or so milliseconds before the nested loops did, which is a little awkward.

Again, this isn’t like a big query tuning dilemma. It’s just sort of an oddity. And it’s happened across multiple queries for me.

I have another example of it happening over here where, again, all of this stuff happens. Well, I can’t say that’s entirely true. Anyway, all of this stuff happens in row mode.

Where it’s supposed to add up, it doesn’t quite add up because we have this oddity over here where the hash join says that it finished in 5.572 seconds. And then the gather stream says it finished in 5.132 seconds. This is also a little bit of a weird plan because even though it’s in row mode, at least last I checked it was all in row mode.

Who knows what happens between runs? It looks like it’s still in row mode to me. But this stream aggregate.

So these two operators are killing me. So we have this stream aggregate that ran in 1.83. And then this repartition streams that ran in 1.73. So this was supposed to add up, but it didn’t add up for some reason.

So that’s another thing that’s kind of odd. Sometimes, despite your best efforts to understand these things, you run a demo and then you look a little more closely while you’re recording a video. And you spot something even stranger.

So now I have to go figure out just what that was about. But that’s going to require a lot of day drinking. So I need to go get started on that. Anyway, I do think this is incredibly valuable.

As query performance tuners, usually when we’re looking at an execution plan, it’s a lot of guesswork as to, okay, let’s figure out what went wrong here. Let’s figure out what we need to focus on. Let’s figure out what’s bad.

This does help us focus quite a bit of our efforts on, okay, which operator really did take a long time to run? What’s sticking out like that awful sore thumb? Anyway, I’m Erik Darling with Erik Darling Data.

Still haven’t fired myself, and I will see you around in another video when it’s less hot and wearing headphones doesn’t make my ears sweat. Have a lovely Saturday. Can you talk to me today?

Have a lovely night. Hi. Hi. Thank you.

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.

CMEMTHREAD Waits Generated By Query Store In SQL Server

Posted on by Erik Darling

Memethread


Thanks for watching!

Video Summary

In this video, I delve into a critical issue related to QueryStore and its impact on CmemThread weights in SQL Server. Initially, my investigation led me to incorrectly attribute the problem to the plan cache, but further analysis revealed that it was actually due to QueryStore being enabled. I walk through setting up various configurations, including turning QueryStore on and off, optimizing for ad hoc workloads, and forcing parameterization, to demonstrate how these settings affect CmemThread weights and query store size. The video highlights the potential pitfalls of relying solely on QueryStore without proper management, emphasizing that optimized for ad hoc workloads does not address this specific issue. I also provide practical solutions and insights into managing query store effectively to avoid excessive memory grants and plan cache bloat.

Full Transcript

Hello! Erik Darling here with Erik Darling Data and I am here to apologize and make right on a video that I recorded yesterday about CmemThread and the plan cache. I was devastatingly, horribly, horribly wrong. And I didn’t realize it until later that I had a setting on on my server, on my laptop, on my database called QueryStore turned on. And the CmemThreadWeights were not the fault of the plan cache, they were the fault of QueryStore. And I’m going to show you exactly what happened. So right now, I’ve got some queries up on the screen. This query here will tell me if I have optimized for ad hoc workloads turned on, if I have parameterization set to forced or not, and the state of my QueryStore thing for the Stack Overflow 2013 database. I’ve also got some queries here to turn QueryStore on and off and to clear it out. So my capture mode is going to be read-write, my max storage size is going to be 100 megs, my capture mode is going to be auto, meaning I don’t capture absolutely everything. I only capture stuff that SQL Server thinks is important. And I’ve got size based cleanup mode turned on, meaning when QueryStore hits 100 megs, size based cleanup should kick in and clear everything right out. I’ve also got some queries here to look at how many cached plans I have, and to look at if I’m hitting CmemThreadWeights, and to look at the current size of QueryStore for the Stack Overflow 2013 database. I’ve got some other utility queries down here. This is to clear out the plan cache and to clear out weight stats on my server so that I can refresh things and between runs to show you differences. I’ve got my O stress command here set to run 20 copies of my randomizer store procedure, which I’ll talk to you about in a minute. And that does some stuff over there. And then I’ve also got queries to turn optimized for ad hoc workloads on and off and just set parameterization to simple and forced. My randomizer query looks like this. It grabs some weird values from the sys.messages table, six minute cursor, six minute cursor, and basically iterates over that cursor. And depending on what seed value this thing has, let me stick a semicolon there to make sure that I have everything correct. I would hate to not terminate a statement properly.

But depending on what number comes up here, I’m going to select from a different table in the Stack Overflow database, depending on that. So if it’s one, badges, two comments, three posts, four users, five votes. And I’m going to pass the two little funny values that I get from sys.messages up here, which looks something like this, into the end clause down here. So that’s what that randomizer store procedure does. It’s pretty simple. Pretty simple stuff, right? Good stuff there.

If we come back over here, what I’m going to do is I’m going to look at what I currently have going on. And in my data’s, various data’s, I have 169 plans in the cache. I have no seam emptied weights. And query store, because it’s off right now, has a size of zero megs. Now what I’m going to do with query store off, and this is to set up a baseline, clear that out.

And what I’m going to do is I’m going to kick off that O stress command, and I’m going to look at what happens. Now we’re running that query across 20 threads, just to simulate like, you know, sort of a heavy ad hoc workload. And as we hit F5 on these, the plans in the cache will start going up, and Cmem thread weights will stay relatively low.

Right now we have 10,000 plans in the cache and no Cmem thread. So if I keep running this and looking at things, I don’t know why that changed context, Tommy. That was a little bit weird. But if I keep running these, now I have 51,000 plans in the cache.

21 things have waited on Cmem thread, and that’s not a lot. I go over here and I keep running these. 80, about 73,000 plans in the cache. Still relatively low Cmem thread.

Only 70 things have waited. I’m not worried about this kind of Cmem thread weight. If I just keep doing this, Cmem thread is not going to go up a whole lot. That’s the baseline. That’s to show you that it’s not the plan cache’s fault that our ad hoc workload is generating a bunch of Cmem thread.

It’s not the plan cache. Whoever says it’s not the plan cache out there, I agree with you 100%. It’s not the plan cache’s fault. Now, I want to show you what happens that’s different when I turn query store on.

So I’m going to hit this, and I’m going to set query store to on for this database. And so we are now capturing data. If we go back up here and rerun this query, we will see that query store is set to read write.

And so the desired and the actual state are both in read write mode. So cool, we have query store turned on. We should be rocking and rolling at this point.

Let’s go rerun our workload again. F5 there. Not F5. Hit enter there. Sometimes I get confused.

But now when I start running this, Cmem thread weights are going to start jumping up really, really quickly. So I hit this, and we’re at 12,000 plans. And as we go up, Cmem thread weights are going to go up and up much faster than they do without query store turned on.

So you can see that I’m hitting some weights here. I’m already up to 1,000 Cmem thread weights. And you can see the size of query store steadily increasing.

We’re up to 63 megs. We’re up to 69 megs. And as we keep going up here, Cmem thread is going to keep increasing. Cmem thread is going to keep going up and up and up.

So this was not happening with the plan cache. This is only happening with query store turned on. So now I’m running all this stuff. Now we’re up to 4,200 Cmem thread weights. And now the query store size is bigger than the max size that I set it to.

If I keep hitting this, query store is going to keep getting bigger and bigger and bigger. And our Cmem thread weights are going to keep going up and up and up. Right now the current size of query store is 166, 173.

This is just going to keep going up. And Cmem thread weights are going to keep going up with it. Query store is now at about twice the max size that I set it to.

Size-based cleanup should be kicking in and clearing stuff out. I just don’t think it can keep up right now. So I’m sticking all these queries in.

I’m hitting this. And you can just see everything piling up. Query store is now nearly 250 megs. And Cmem thread weights keep on going up and up and up and up and up and up.

Now, this can be a real problem for a lot of people. If you have an ad hoc workload like this, you might be very concerned that, you know, the query store is going to overfill, that, you know, cleanup is never going to quite catch up.

And now you’re going to have all these funky Cmem thread weights going on in your server. At this point now, query store is nearly three times the size of the max size that I set it to. And cleanup doesn’t really seem to be doing its thing.

We’re getting up really close to 300 megs there. So what I’m going to do is I’m going to kill off of this. And I’m going to talk about a couple options that I thought I would walk through to try and fix this.

Now, the first one that I tried was optimized for ad hoc workloads. Now, if we look at, to turn optimized for ad hoc workloads on, if you’re in Azure SQL DB, you can do this with a database scope configuration.

If you’re on regular SQL Server, you can. It would be nice if that made it there. I tried it last night and I just kept getting errors. And then I looked more closely at the documentation. It was like, oh, yeah, only for Azure SQL DB.

So right now we can only turn this on at the server level for SQL Server 2017. So I’m going to do that. I’m going to turn on optimized for ad hoc workloads. And I’m going to give one more check-in up here to see what things look like.

And you’ll see that now that our query store size is nearly 400 megs, size-based cleanup is not doing its thing. So we’re going to do its job for it.

And hopefully this will work. Hopefully. We might be sitting here for a while. Oh, there we go.

That only took 13 seconds. Good for us. Okay. So let’s see how big query store is currently. All right. Zero megs. Good. We cleared all that stuff out. And let’s go check our stuff over here. So now we have optimized for ad hoc workloads turned on.

That is at 1. Sweet. Now, with that at 1, let’s go clear out all that stuff that we had before. And let’s check back in.

Let’s make sure our queries are returning close to zero. All right. So four plans in cache. I can live with that. That’s some system stuff going on in the background. See memthreads at zeros across the board. And query store is off.

So we’re at zero there. Because we have a baseline with the ad hoc, with the workload running without any special settings and see memthread being low, I’m not going to repeat it with query store off and ad hoc turned on.

There’s no point. That’s not helping us here. That’s not helping the effect that we’re having. The effect that we’re specifically having is that with query store turned on, we hit a lot of cmemthread weights.

So I’m going to turn query store back on. So now if we go check in on this, we should see query store set to read write. And we should see the value in use set to 1 for ad hoc workloads.

Okay. With that in place, let’s go back over here. Let’s hit F5. Let’s see. We have 10 plans in the cache now. Good heavens. We still have no cmemthread.

And, of course, query store size is zero. Because why would it be anything else? We just turned it back on. Let’s hit F5 there. Or enter or up arrow, enter or whatever you want to call it. Whatever.

O stress. And then I’m going to start running these. And when I run these, plans in the cache are going to start piling up. And so is cmemthread.

And so is the size of query store. And if I keep doing this and we keep looking at stuff. And, again, this just keeps bizarrely changing context on me. We’ll see kind of the same pattern start to happen.

Where my mouse refuses to click and work in the right way. I don’t know what it is. I’m going to stop spending $100 on mice that stop working after a few months. It’s pretty ridiculous.

But if I keep running these queries, we’re going to keep seeing. We’re going to see the number of plans in the cache pile up. We’re going to see the cmemthread weights pile up. And we’re going to see the size of query store just keep going up and getting bigger and bigger. The two important things to take away from here is that optimized for ad hoc workloads is not fixing this specific problem.

Where we’re hitting cmemthread weights. And the size of query store is going up way above the max that we set it at. Size-based cleanup is also not doing its job in cleaning this thing up fast enough.

So we’re actually facing the exact same problem. Optimize for ad hoc workloads isn’t helping us here. I’m not going to let that run for as long because we already know kind of what’s happening.

So let’s get rid of query store. We already killed our workload. Going to get that off. Okay, cool. Now let’s turn off optimized for ad hoc workloads. I want to make sure that I have a fresh start here.

And let’s turn on forced parameterization. So forced parameterization is turned on. And let’s clear out all of this stuff. And let’s see what we have here.

Oh, wrong button. It’s a morning of wrong buttons. All right. So four plans in the cache. No cmemthread weights. Query store is, of course, at zero megs because we have it turned off right now. So I’m going to go turn query store on.

Bada bing, bada boom. Let’s go make sure that we have all the stuff we want here. Perfect. So read, write, read, write. Parameterization is set to forced.

All right. Let’s check in on these numbers. Make sure we don’t have anything too weird. We don’t. Nine plans in the cache. No cmemthread. Query store is at zero because we just turned it on. And we’re going to go hit whatever damn, whatever you want to call that button, we’re going to hit it.

I’m going to run this thing. Run it. Come over here. Highlight you. And let’s start running this thing.

All right. So what we’re going to notice with forced parameterization turned on, at least if this demo works as it has worked all morning, which it very well may not. Who knows?

As soon as you’re recording, every bad thing happens. Everything goes wrong when you start recording. Anyway. So what we see here is the number of plans in the cache is skyrocketing. We’re up to 100 and something thousand, 157,000, but very low cmemthread.

The other thing I want you to notice is that the size of query store is zero megs. It’s not that we’re not collecting anything. It’s just that when SQL Server is doing a pretty good job of only putting one copy of the parameterized plan in there.

So as we go through and we look at this, we can see that query store size is not ballooning. Cmemthread weights are staying relatively low compared to other times when we ran this. But the number of plans in the cache is sort of astoundingly high.

Now, I’m going to keep running this. And basically what we’re going to see here is that forced parameterization does help us avoid the majority of the cmemthread weights and the ballooning size of query store. For some reason, that’s not helping us with the plan cache.

With the plan cache, we’re still not getting very good plan reuse. So that’s an issue, but that’s an issue for another day. This video is pretty specifically aimed at cmemthread weights as they relate to the query store and how big the query store gets and how it doesn’t clean up and all the other kind of crazy stuff.

I had a much, much worse time with this last night when I was originally running through the demo trying to get it worked out. At one point, query store was 700 megs, and the only way I could get it to turn off and clear out was by restarting my entire laptop. And that was not pleasant because in my head I’m thinking, my God, if you’re doing this in production, you don’t want to have to restart production just to get query store to clean out.

Anyway, my name’s Erik Darling, that one, with Erik Darling Data. And thank you for watching. I hope you got some useful information out of this.

And I will see you, I don’t know, maybe in a week or so because it’s the weekend and I feel like doing other things. 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.

Does SQL Server 2019 Help With Multiple Scalar Function Calls?

Posted on by Erik Darling

Waybad Machine

In yesterday’s post, we looked at a clever way to reduce calls to a scalar UDF using APPLY.

Today, we’re going to see if 2019 changes anything, and if our old trick still tricks.

Because, you know, what else do you do when you need to write 5 blog posts a week?

LOVE YOU!

Don’t Be A Donkey

I’m going to abridge this a little bit, since all the code is referenced at the link up there.

I’m also going to show you some stuff using Plan Explorer.

Why?

Because SSMS kept opening the plan XML as XML, and that makes for crap screenshots.

Here’s the results for the plan with two function references. It runs for ~2.2 seconds.

SQL Server Query Plan
Honesty, at last.

If you remember yesterday’s post (and why wouldn’t you, hm?) the query plans didn’t show us touching other tables at all.

Just seeking into the Users table and then magically computing scalars and filtering.

One of the nice things about scalar UDF inlining: honesty.

But, you know, the two where clause references end up expanding. We’re hitting pretty big tables, here, too.

Apply-ish-ness

Using APPLY has a similar *ffect here. The function is only referenced and filtered once, and the duration is cut roughly in half.

Now, I know you’re probably thinking, because YOU REMEMBER YESTERDAY’S POST!

SQL Server Query Plan
Ming the Merciless

How come these queries are so much slower with the functions inlined?

Well, they’re not. With query plans turned off, the first one runs in ~900ms, and the second one runs in ~500ms.

Yesterday’s plans run for 1.6s and 600ms respectively with plans turns off.

Apparently observation has overhead. If only there were a clever phrase for that.

Not All Functions

The idea behind FROID is that it removes some restrictions around scalar valued functions.

  1. They can be inlined into the query, not run per-row returned
  2. They don’t force serial execution, so you can get a parallel plan

If your functions already run pretty quickly over a small  number of rows, and the calling query doesn’t qualify for parallelism, you may not see a remarkable speedup.

That’s fine, though, because inlining has other benefits:

  • Query plans are honest about the work they do
  • Measuring the query will show you work that used to be hidden behind the function call(s)

Even if every query doesn’t magically finish before you run it, you’ll see pretty good gains.

Thanks for reading!

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.

A Hidden Value Of Apply With SQL Server Scalar UDFs

Posted on by Erik Darling

Look, Functions Suck

That’s why smart people have been working on making them suck less.

The things I see people doing with them range from “you know there’s a system function that does that” to “oh wow, you wrote an entire program in here”.

I’m not kidding. I once saw a function that was a wrapper for ISNULL that returned the results of ISNULL. I have no idea why.

If I had to think of a DBA prank, writing scalar UDFs that are just wrappers for system functions would be pretty high up there.

Especially if they had the same names as the system functions.

Turning Down The Suck

A while back, Jonathan Kehayias blogged about a way to speed up UDFs that might see NULL input.

Which is great, if your functions see NULL inputs.

But what if… What if they don’t?

And what if they’re in your WHERE clause?

And what if they’re in your WHERE clause multiple times?

Oh my.

Tick, Tick, Tick

Here’s our function.

CREATE FUNCTION dbo.TotalScore(@UserId INT)  
RETURNS BIGINT  
WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING  
AS   
BEGIN    
    DECLARE @TotalScore BIGINT;        
    SELECT @TotalScore =   
    (  
        SELECT ISNULL(SUM(p.Score), 0)  
        FROM dbo.Posts AS p  
        WHERE p.OwnerUserId = @UserId  
    ) +  
    (  
        SELECT ISNULL(SUM(c.Score), 0)  
        FROM dbo.Comments AS c  
        WHERE c.UserId = @UserId  
    )        
    RETURN @TotalScore;    
END
GO

What it does is go out to the Posts and Comments tables and sums up the Score columns for a user.

We’ll use it in our query like this:

SELECT u.DisplayName, 
       u.Reputation
FROM dbo.Users AS u
WHERE u.Reputation >= 100000
AND dbo.TotalScore(u.Id) >= 10000
AND dbo.TotalScore(u.Id) < 20000
ORDER BY u.Id;

We want to find people with a total score between 10 and 20 thousand.

Right on.

When we run the query, the plan looks like this, showing 2 seconds of runtime.

SQL Server Query Plan
Two seconds for 260 rows is kinda wack tho

Tock, Tock, Tock

I know, I know. Get to the point. Make it faster, bouncer-man.

Our goal is to get the function to run fewer times, so we’ll replace multiple calls to it with one call.

SELECT u.DisplayName,
       u.Reputation
FROM dbo.Users AS u
CROSS APPLY
    (
        VALUES (dbo.TotalScore(u.Id))
    ) AS t (Score)
WHERE u.Reputation >= 100000
AND   t.Score >= 10000
AND   t.Score < 20000
ORDER BY u.Id;

Using this technique, the query runs for about 780ms.

SQL Server Query Plan
Check you out.

Tale of the XE

What happens that makes this faster is more evident if we use the XE session from Jonathan’s post for similar reasons, and look at how many times the function was called.

If we look at the activity sequence, it goes up to 1060 for the first query:

SQL Server Extended Events
Moved Out The Hood

And only 615 for the second query:

SQL Server Extended Events
Thinner~

Exeunt

Right now, if we want scalar UDFs to run faster, we can:

  • Tune the underlying query (if there is one)
  • Have them run fewer times
  • Wait for SQL Server 2019

In tomorrow’s post, I’ll look at the same scenario using CTP 3 of SQL Server 2019.

Thanks for reading!

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.

Eager Index Spool Suggestions For SQL Server

Posted on by Erik Darling

Funtime

I’ve added a couple ideas to User Voice.

The ideas are pretty simple:

Eager Index Spools Should Generate Missing Index Requests

In query plans where an Eager Index Spool is directly after a data access operator, a missing index should be generated in the query plan, and/or missing index DMVs that describes the definition of the index needed to make the spool unnecessary.

I would not expect this to happen when a Lazy Index Spool occurs above a subtree.

I’d appreciate it if you’d consider voting for it. It’s something that I was able to implement pretty easily in sp_BlitzCache.

Eager Index Spools Should Generate Wait Stats

In query plans where an Eager Index Spool is directly after a data access operator, wait stats should be generated while the Spool is built. In a parallel plan, EXECSYNC waits are generated, but in a serial plan, you don’t see anything. Problem scenarios will become more common when FROID is released and adopted.

I would not expect this to happen when a Lazy Index Spool occurs above a subtree.

Thanks for reading!

And voting as many times as possible ?

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.

The Coming Froidpocalypse In SQL Server 2019

Posted on by Erik Darling

Legal Notice

I’ve trademarked: Froidrage, Froidulent, and Froidpocalypse.

If you want to use them, you have to pay me $10,000.

Alright, I’m being told by my lawyer that writing them on cocktail napkins and showing them to confused bartenders doesn’t actually register a trademark.

Nevermind.

Here’s What’s Gonna Happen

And it’s not a problem that you need SQL Server 2019 to see. All you have to do is try to rewrite a function.

Here’s our Villain, a scalar UDF.

CREATE FUNCTION dbo.Villain (@UserId INT)
RETURNS INT
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS	
BEGIN

    DECLARE @Score INT
	SELECT TOP (1)
	         @Score = p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = @UserId
	AND   p.PostTypeId = 1
	ORDER BY p.Score DESC;
    
	RETURN @Score;

END
GO

Here’s the query that’s gonna call it:

SELECT      TOP ( 10 )
              u.DisplayName, 
			  u.Reputation, 
			  dbo.Villain(u.Id)
FROM        dbo.Users AS u
ORDER BY    u.Reputation DESC;
GO

I’m Going To Show You Two Things

The estimated plan, and the actual plan.

I need to show you the estimated plan so you can see what the function does, because that’s not included in the actual plan.

Yes, the estimated plan is more accurate than the actual plan.

Marinate on that.

SQL Server Query Plan
On The Dancefloor

The important thing is the second plan, which is the function’s execution plan. Notice that it generated a missing index request, and doesn’t spool anything at all.

It handles the query logic with a Top N Sort.

Here’s the actual plan:

SQL Server Query Plan
A NONCLUSTERED INDEX SCAN!!!

Let’s talk about a couple things:

  • A nonclustered index scan that costs 100% and runs for 0.000s
  • A compute scalar that costs 0% and runs for ~3s

The compute scalar thing is well documented by… Well, not by official Microsoft documentation.

But they’ve been blogged about by Cookies Cunningham, and Paul White.

Thanks, you two.

Any Reasonable Person

Would say “I can rewrite that function and make things better”.

Because of course an inline function is always better than a scalar function.

Enter our Hero.

CREATE FUNCTION dbo.Hero (@UserId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS	
RETURN

	SELECT TOP (1)
	         p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = @UserId
	AND   p.PostTypeId = 1
	ORDER BY p.Score DESC;

GO

Here’s the query that’s gonna call it:

SELECT      TOP ( 10 )
              u.DisplayName, 
			  u.Reputation, 
			  h.*
FROM        dbo.Users AS u
CROSS APPLY dbo.Hero(u.Id) AS h
ORDER BY    u.Reputation DESC;

I Only Need To Show You One Thing

Since the function is an inline type, the query processor is honest with us about the full query plan.

SQL Server Query Plan
Spiced Ham

Two things happened here:

  • The “function body” no longer goes parallel
  • The TOP (1) is run against an eager index spool rather than the clustered index

What’s The Point?

This is what FROID does for you without a rewrite. It’ll inline the scalar UDF.

The plan may be better, or it may be worse.

The scalar UDF plan ran for 3 seconds, and the inline version ran for almost 13 seconds.

Stay tuned for tomorrow’s post. I have a couple suggestions for how The SQL Server team can help end users stay on top of these problems in SQL Server 2019.

Thanks for reading!

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.

How Select List Column Sizes Change How Big Spool Operators Are In SQL Server Query Plans

Posted on by Erik Darling

I’ll Use Those Columns Later, Maybe

This is a short post, since we’re on the subject of index spools this week, to show you that the columns that go into the spool will impact spool size and build time.

I know, that sounds obvious, but once in a while I care about “completeness”.

We’re going to look at two queries that build eager index spools, along with the time the spool takes to build and how many writes we do.

Query 1

On the side of the query where a spool gets built (inside the apply), we’re only selecting one column.

SELECT      TOP ( 10 )
            u.DisplayName, 
			u.Reputation, 
			ca.*
FROM        dbo.Users AS u
CROSS APPLY 
(   
    SELECT   TOP ( 1 )
			   p.Score
    FROM     dbo.Posts AS p
    WHERE    p.OwnerUserId = u.Id
    AND      p.PostTypeId = 1
    ORDER BY p.Score DESC 
) AS ca
ORDER BY    u.Reputation DESC;

In the query plan, we spend 1.4 seconds reading from the Posts table, and 13.5 seconds building the index spool.

SQL Server Query Plan
Work it

We also do 21,085 writes while building it.

SQL Server Extended Events
Insert comma

Query 2

Now we’re going to select every column in the Posts table, except Body.

If I select Body, SQL Server outsmarts me and doesn’t use a spool. Apparently even spools have morals.

SELECT      TOP ( 10 )
              u.DisplayName, 
			  u.Reputation, 
			ca.*
FROM        dbo.Users AS u
CROSS APPLY 
(   
    SELECT   TOP ( 1 )
               p.Id, p.AcceptedAnswerId, p.AnswerCount, p.ClosedDate, 
               p.CommentCount, p.CommunityOwnedDate, p.CreationDate,
               p.FavoriteCount, p.LastActivityDate, p.LastEditDate,
               p.LastEditorDisplayName, p.LastEditorUserId, p.OwnerUserId, 
               p.ParentId, p.PostTypeId, p.Score, p.Tags, p.Title, p.ViewCount
    FROM     dbo.Posts AS p
    WHERE    p.OwnerUserId = u.Id
    AND      p.PostTypeId = 1
    ORDER BY p.Score DESC 
) AS ca
ORDER BY    u.Reputation DESC;
GO

In the query plan, we spend 2.8 seconds reading from the Posts table, and 15.3 seconds building the index spool.

SQL Server Query Plan
Longer

We also do more writes, at 107,686.

SQL Server Extended Events
And more!

This Is Not A Complaint

I just wanted to write this down, because I haven’t seen it written down anywhere else.

Thanks for reading!

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.

Eager Index Spool Annoyances In SQL Server

Posted on by Erik Darling

Don’t Mask Spools

Certain spools in SQL Server can be counterproductive, though well intentioned.

In this case, I don’t mean that “if the spool weren’t there, the query would be faster”.

I mean that… Well, let’s just go look.

Bad Enough Plan Found

Let’s take this query.

SELECT TOP (50) 
    u.DisplayName, 
    u.Reputation, 
    ca.*
FROM dbo.Users AS u
CROSS APPLY 
(
    SELECT TOP (10) 
        p.Id, 
        p.Score, 
        p.Title
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = u.Id
    AND   p.PostTypeId = 1
    ORDER BY 
        p.Score DESC
) AS ca
ORDER BY 
    u.Reputation DESC;

Top N per group is a common enough need.

If it’s not, don’t tell Itzik. He’ll be heartbroken.

The query plan looks like this:

SQL Server Query Plan
Wig Billy

Thanks to the new operator times in SSMS 18, we can see exactly where the chokepoint in this query is.

Building and reading from the eager index spool takes 70 wall clock seconds. Remember that in row mode plans, operator times aggregate across branches, so the 10 seconds on the clustered index scan is included in the index spool time.

One thing I want to point out is that even though the plan says it’s parallel, the spool is built single threaded.

SQL Server Query Plan Properties
One Sided

Reading data from the clustered index on the Posts table and putting it into the index is all run on Thread 2.

If we look at the wait stats generated by this query, a full 242 seconds are spent on EXECSYNC.

SQL Server Wait Stats In Query Plans
Armless

The math mostly works out, because four threads are waiting on the spool to be built.

Even though the scan of the clustered index is serial, reading from the spool occurs in parallel.

SQL Server Query Plan Properties
Spange

Connected

Eager index spools are built per-query, and discarded afterwards. When built for large tables, they can represent quite a bit of work.

In this example query, a 17 million row index is built, and that’ll happen every single time the query executes.

While I’m all on board with the intent behind the index spool, the execution is pretty brutal. Much of query tuning is situational, but I’ll always pay attention to an index spool (especially because you won’t get a missing index request for them anywhere). You’ll wanna look at the spool definition, and potentially create a permanent index to address the issue.

As for EXECSYNC waits, they can be generated by other things, too. If you’re seeing a lot of them, I’m willing to bet you’ll also find parallel queries with spools in them.

Thanks for reading!

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.

Common Parameter Sniffing In Entity Framework Queries For SQL Server

Posted on by Erik Darling

Still Not A Developer

I’m going to use a funny example to show you something weird that I see often in EF queries.

I’m not going to use EF to do it, because I have no idea how to. Please use your vast imagination.

In this case, I’m going to figure out if a user is trusted, and only if they are will I show them certain information.

Here goes!

Trust Bust

The first part of the query establishes if the user is trusted or not.

I think this is silly because no one should ever trust users.

DECLARE @UserId INT = 22656, --2788872
        @PostId INT = 11227809,
		@IsTrusted BIT = 0,
		@SQL NVARCHAR(MAX) = N'';

SELECT @IsTrusted = CASE WHEN u.Reputation >= 10000 
                         THEN 1 
						 ELSE 0 
				    END
FROM   dbo.Users AS u
WHERE  u.Id = @UserId;

The second part will query and join a few tables, but one of the joins (to the Votes table) will only run if a user is trusted.

SET @SQL = @SQL + N'
SELECT p.Title, p.Score,
       c.Text, c.Score,
	   v.*
FROM dbo.Posts AS p
LEFT JOIN dbo.Comments AS c
    ON p.Id = c.PostId
LEFT JOIN dbo.Votes AS v
    ON p.Id = v.PostId
    AND 1 = @iIsTrusted
WHERE p.Id = @iPostId
AND   p.PostTypeId = 1;
';

EXEC sys.sp_executesql @SQL,
                       N'@iIsTrusted BIT, @iPostId INT',
					   @iIsTrusted = @IsTrusted,
					   @iPostId = @PostId;

See where 1 = @iIsTrusted? That determines if the join runs at all.

Needless to say, adding an entire join in to the query might slow things down if we’re not prepared.

First I’m going to run it for user 2788872, who isn’t trusted.

This query finishes rather quickly (2 seconds), and has an interesting operator in it.

SQL Server Query Plan
Henanigans, S.
SQL Server Query Plan Tool Tip
Pump the brakes

The filter has a startup expression in it, which means it’s sort of a gatekeeper, here. If the parameter is 0, we don’t touch Votes.

If it’s 1… Boy, do we touch Votes. This is another case of where cached plans can lie to us.

Rep Up

If we run this for user 22656 (Jon Skeet) afterwards, we will definitely need to touch the Votes table.

I grabbed the Live Query Plan to show you just how little progress it makes over 5 minutes.

SQL Server Query Plan
Dirge

The cached plan will look identical. And looking at the plan, it’ll be hard to believe there’s any way it could run >5 minutes.

SQL Server Query Plan
CONFESS

If we clear the cache and run this for 22656 first, the plan runs relatively quickly, and looks a little different.

SQL Server Query Plan
Bag of Ice

Running it for an untrusted user has a similar runtime. It’s not great, but it’s the better of the two.

Fixing It?

It’s difficult to control EF queries with much granularity.

You could branch the application code to run two different queries based on if a user is trusted.

In a perfect world, you’d never even consider that join at all, and avoid having to worry about it.

On the plus side (at least in this case), the good plan for trusted users runs in the same time as the good plan for untrusted users, even though they’re different.

If you’re feeling extra confident, you can try adding an OPTIMIZE FOR hint to your code, or implementing a plan guide.

Thanks for reading!

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.