Why Does My Trigger Have Multiple Plans In SQL Server?
Video Summary
In this video, I dive into an intriguing aspect of SQL Server: why triggers might have multiple execution plans. Erik Darling from Darling Data Enterprises shares his insights on how and why these plans can differ based on the number of rows being processed. He explains that SQL Server caches plans for single-row and multi-row scenarios separately, which can lead to confusion if not understood properly. Along with practical demonstrations using SSMS 21 preview in dark mode (your feedback on this setup is welcome), I walk you through how to identify these different execution plans within the plan cache. This knowledge is crucial for anyone dealing with complex trigger logic and performance tuning in SQL Server environments.
Full Transcript
Erik Darling here with Darling Data, and we have a very exciting video for you today here from Darling Data Enterprises. This is all about why do I have multiple plans for triggers? And there are, you know, probably some other external reasons why you might see multiple plans for the same trigger. For example, if you have the same trigger across multiple databases and you look at the plan cache and you don’t take the database context into account, you might see multiple things in there. But this is a much more interesting internal reason for why you might have multiple plans for your trigger. Before we get into all that, man, I love you all so much for the support that you give this channel. And if you want to be included in the the people who I love for giving support to the people who I love for giving support to this channel, you can do a couple things. You can sign up for a membership. And if you do that using the link down in the video description for as few as $4 a month, or we’ll call that one espresso buck, you can support the content that I create on this channel. If you have spent all your money on caffeinated beverages or other assorted methamphetamines or uppers I don’t know whatever whatever you’re into poppers, maybe you can like you can comment you can subscribe. And if you want to ask a question privately that I will answer publicly during my office hours videos, that link right there by my my very fancy extended pinky is down also in the video description.
Slide, please. If you need help with your SQL Server beyond the scope of what a simple question or a YouTube video or a blog post or anything else can help, and you’re in the market for a young, handsome consultant with reasonable rates, I am the best in the world at all of these things.
That’s a short list of all the things in the world I am the best at, but this covers most of the ground with SQL Server. There are several other things not SQL Server related in the world that I am best at, such as picking the bottle of wine from the wine list that the restaurant has run out of. I am tops at that. Cannot be, cannot be beat. I am undefeated, undefeated at that.
Indefeatable. Invictus or something. If you would like to get some training on SQL Server performance tuning and you don’t feel like spending hundreds of dollars or thousands of dollars a year for a subscription or whatever, you can get all of mine for about 150 bucks and you can get that for the rest of your life. There’s about 24 hours of it and the fully assembled method for retrieving this wonderful deal is also down in the video description yonder over there.
All right. SQL Saturday, New York City 2025 on MAY. That is May the 10th at the Microsoft offices in Times Square. I will be there in various capacities doing things. I don’t know. I’ll probably even be wearing the same outfit, so I will be highly recognizable to you, the general public. But with that out of the way, slide please. Let’s talk about why triggers might have multiple plans. Now, I need some helper objects like some tables and just to make life easy, I’m just going to have a, you know, just a couple rows, trigger test, trigger audit. We’re just going to pretend this is an audit table that captures stuff about what got put into the test table. Then we’re going to have a trigger and it’s going to be an after insert trigger like so. All right. And we are going to just insert whatever stuff from the inserted virtual table exists into the audit table, right? So very simple thing there. Nothing, I hope, too out of the ordinary. We should make sure that we do this correctly, though. We should do create or alter. And I am recording another video here using the SSMS 21 preview with dark mode in there. If there’s any feedback on my use of dark mode or my use of SSMS 21, please let me know because I want to make sure that I’m making the best possible videos. I know some folks out in the world dislike dark mode. Other people love it. So I don’t know. Just kind of tell me how you’re feeling about it. That would be wonderful. So to round out this demo here, we’re going to clear out the procedure cache. And I’m going to pause now to tell you that SQL Server caches plans for triggers in two different ways internally, like a plan caching mechanism.
There is a trigger, a plan for your triggers that will be for a single row. And then there will be a plan for your triggers when there are multiple rows in the inserted or deleted virtual table. So that’s what we’re going to look at here. And that’s what I’m going to show you with my fancy query down below. Now, right now, of course, there should be nothing in the plan cache since I just cleared it. And we’ll tell us about that. And in between recordings, I managed to remember to increase the size of my grid text. So now we don’t have to go blind together staring at that.
But what we’re going to do now is insert a single row into our table. And now let’s interrogate the plan cache. And we will see that we have a plan cached for a single row in there, right? Which is exactly what we did. Now, if we insert multiple rows into our trigger test, we are going to have a second execution plan added that is a multi row. So here we go. We have the one use count for our trigger object type. And the set options, if you do some fancy, I forget what this is called bitwise, something maybe I forget. But if you do this, and 24 for the set options attribute, and DM exec plan attributes, you can decode between multi one row and multi row trigger plans. So if we look at the first plan that we cached, and we look at the inserted plan, we will see that the number of rows in that is all estimated at one. And if we own what we should probably close that out. See, this is the one thing that I dislike about the dark mode is like not everything is dark mode yet. So when you get when you do things like open up query plans, it’s like, you can like go blind. It’s like, like in Big Trouble in Little China, when David Lopin does the eye light thing at Kurt Russell. Jack, whatever his name is in that. And I don’t know if I clicked on the right one there. Let’s go back and try that. Let’s make sure I did. All right. So if we now click on the execution plan for the multi row trigger, and we look at this, we will see that these have changed, right? Or rather, these are just different in this in this plan. These numbers have changed between the single row plan and the multi row plan. Obviously, now we have three rows instead of one. So if you are looking at your plan cache, and you are puzzling as to why you have multiple plans for some of your triggers in there, the answer could be as simple as you are storing a plan for the execution of the trigger for a single row. And you are also storing a plan for the execution of the trigger when it processes multiple rows. Perhaps not the most interesting, perhaps not the most titillating, psychologically traumatizing SQL Server content that I’ve ever produced, but it is a useful bit of SQL Server knowledge and trivia nonetheless.
So yeah, I’m probably just gonna can this one here. We’re gonna talk about some other stuff coming up in other videos. We’ll probably continue on with the store procedure series because I owe you a few things. I owe you a few videos remaining on that. I believe we have four or five left to cover. So we’ll get those done. And I don’t know, see, we’ll just see what happens next. The world is our SQL oyster, or something like that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video. 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.
All About SQL Server Stored Procedures: Dynamic SQL For Performance
Video Summary
In this video, I delve into using dynamic SQL to address performance issues in stored procedures, particularly focusing on the pitfalls of parameter sniffing and if-branch statements. I demonstrate how dynamic SQL can help mitigate bad estimates and unwanted query plan compilations by ensuring that each branch is treated uniquely. By incorporating a “replace me” token within the dynamic SQL queries, we inject specific conditions based on input parameters, forcing SQL Server to generate distinct execution plans for different scenarios. This approach not only tackles cardinality estimation issues but also helps in managing parameter sensitivity across executions. I walk through creating and executing stored procedures that dynamically adjust their query text based on input parameters, showcasing how this technique can significantly improve performance by avoiding the pitfalls of traditional if-branch logic.
Full Transcript
Erik Darling here with Darling Data. And in this episode, where we will continue to sermonize about stored procedures, we’re going to talk a bit about, of course, using dynamic SQL to fix performance problems. There are three main problems that we’re going to talk about, and then one fourth sort of bonus problem. So we got that to look forward to over the next episode. So we’re going to talk about the next, the rest of our lives. But before we go into all that, of course, let’s talk a little bit about some fun stuff, some interesting things in our lives. If you like my content, or, I don’t know, if you find the things that you watch and learn here worth money, you can click the link down in the video description, right about there, and you can you can become a subscribing member of the channel for as few as $4 a month. If you are unable to scrounge $4 a month from the couch cushions, or mom’s purse, or whatever, you can do other stuff to support my efforts here. You can like, you can comment, you can subscribe, and if you would like to ask a question privately that I will answer publicly during my Office Hours episodes, that link is also down below for you to do that.
If you would like me to show up semi-live, probably via Zoom call, but if you want me to show up to your offices, it’s fine with me. I’m not going to complain too much. Money’s money. But I can do all of these things here, and I do them better than anyone else in the world outside of New Zealand. Health checks, health checks, performance analysis, hands-on tuning, responding to performance emergencies, and training your developers so I don’t have to respond to performance emergencies. All worthy goals, and as always, my rates are reasonable. If you would like to get some training for yourself, or maybe a friend, family, colleague, I don’t know, whatever it is, you can get all 24 or so hours of mine for about $150 USD using that discount code at that URL up there.
That is also completely assembled for you down in the video description. SQL Saturday, New York City 2025 is coming your way May the 10th. I will be there slinging sandwiches and cookies and bags of chips right in your face.
We have a performance tuning pre-con on May the 9th with Andreas Walter teaching us about performance doodads and gizmos and whatnot. So I will also be proctoring that. So at the very worst, I can throw a sandwich in your face two days in a row.
With that out of the way, though, let’s talk about dynamic SQL stuff. And I have already done the wrong thing. So I promised a while back that when SSMS 21 had support in SQL prompt that I would do one of these using SQL prompt and the dark mode thing.
So now SQL prompt 10.16 added support for SSMS 21 preview. Refer documentation. So there are a few things that I had to do to get SQL prompt showing up in here.
But that’s okay. So this is SSMS 21 with dark mode. The things are all dark.
Some of the things are all dark. Some of the things are not dark yet. Namely query plans. Query plans are very much not dark. But, you know, there’s only so much you can do. I’m a little blurry here.
I think I want to crisp myself up a little bit. There we go. There we go. Now I’m feeling crispy. Maybe? No, I think I went a little too uncrisp. No, that’s less good.
There we go. All right. Now I’m feeling zombified. All right. So the stuff we’re going to talk about. Dynamic SQL. Very good for things like if branch, plan compilation, parameter sensitivity, and complex runtime logic. Things that you put in your join or where clause where it’s like where parameter equals something.
Then do this other thing. And if the parameters are variable is this other thing. Do this other thing.
And add this other thing on. Explore this branch. Because all that stuff sucks for the optimizer. And we’ll talk about that. We are also going to talk about one bonus topic around filtered indexes in Dynamic SQL. And we will use a somewhat similar pattern to get around some limitations there.
But my goal here is to show you an example of, well, I guess, all, not both, of all of these things. And build on some of the Dynamic SQL stuff that we talked about in the previous video. About using Dynamic SQL safely and correctly.
For a lot of the things that we are going to talk about with Dynamic SQL. I’m going to be just upfront and honest with you. A statement level recompile hint would solve a lot of these problems.
Not a store procedure level recompile hint. But a statement level recompile hint would solve the majority of this stuff. And you would not have to write or worry about Dynamic SQL.
Whether that’s appropriate for whatever situation you are in is up to you. If you want to use a recompile hint, I don’t care. It doesn’t bother me.
I’m not here to, like, warn you of some atrocity. Your CPU is catching fire or anything like that. I use recompile hints all the time. They’re fantastic. They solve a lot of stuff. You just can’t always get away with it.
There are also situations that we are going to talk about. Where nested store procedures. The wrapper store procedures. Like we talked about in another video. Would be either sufficient or preferred.
Usually things around security and permissions. Would drive you to that over Dynamic SQL. I’m not saying that Dynamic SQL can’t be done correctly to do that stuff.
I’m just not the person to get training from about security and permissions. Because I don’t give two toots of a horn. About either one. But once you get into using Dynamic SQL.
There are all sorts of fun and creative ways to use Dynamic SQL. To sort of avoid lots of problems in here. So let’s dive right into it here.
So I’ve created a couple indexes. On the post table. Well sorry.
One on the post table. One on the votes table. And we are going to be using those in our first store procedure example. Now we’ve talked about this in the past. But this is the store procedure series. So we’re going to talk about it again.
Again we’ve got a query here that will run if post type ID is not null. And we’ve got a query here that will run if vote type ID is not null. But as we have talked about in previous iterations of discussing this sort of if logic and store procedures.
SQL Server does not do. Oh I should have a go in there. Just safe.
It’s to be safe. SQL Server does not do a particularly good job of managing plans like this. If I go and I grab the estimated plans for these two things. We’re going to see some stuff that looks rather different. Take a look at this one.
Where the top branch is parallel. And the bottom branch is serial. Single threaded. And now we look at the bottom. The second execution. Where the null and not null parameters have been reversed.
And the top branch is single threaded. And the bottom branch is parallel. What you’re going to notice about both of these. Is that the non-parallel branch only has a one row estimate.
And that’s going to be true for up here as well. Now since I have just gotten estimated plans for these. We have no cache plan.
Which means whatever order I run these in. And compile a plan for. And we cache a plan for. Will be the one that we get the better estimate for. For the non-null parameter value.
So if we execute this. Where post type ID equals four. We get a perfectly fine execution plan. That is the parallel plan that we discussed above. SQL Server is asking for an index on the post table.
But as of right now. The way that we’re hitting the post table. Is not of any significance. This of course goes right down El Tubo. When we run this for vote type ID 10.
Because now post type ID is null. And vote type ID is 12. And this takes a full eight seconds. To give us a query plan.
And we can see. Where that one row estimate. Is no longer our friend. Because we got a whole bunch of rows back. And we spent a whole bunch of time. Doing all this stuff.
If we look in the properties here. And we look at the vote type ID parameter. You’ll see that it was compiled with a null. But executed with a 10. So we are already off to a very bad start. Now of course.
If I flip this around. And I run it for vote type ID. Let’s just say 12 first. So we get a pretty quick execution here. Then what we’re going to end up with.
Is a serial plan here. It has correct estimates now. For that vote type ID. But now when we go and run this. For post type ID equals one. The post type ID plan.
Is going to be the one row estimate. And if you’re familiar with. You know. Either my videos. Or the Stack Overflow 2013 database. You will know that post type ID one.
Has six million rows in the post table. Not one row. So this takes. 16 seconds to run. And you can see where this was. No longer such a great idea. Estimating one row here.
It’ll be the exact same scenario as above. Where post type ID. Would have been compiled. With a null value. From here. And we would not have a good time.
Now this does make an assumption. That either one or the other. Will execute. But this is the way. I see a lot of store procedures. Set up to run. So don’t tell me. That this is unrealistic.
Because a lot of the stuff. That I end up tuning. Looks a lot like this. So. I’m just going to have to deal with that. What we can do. To prevent. The execution.
Of unwanted. Or rather the optimization. And compilation of query plans. For unwanted. Or unexplored if branch statements. Is to make the whole thing dynamic.
What this will fix. Is the bad estimates. That come with. The queries that are in the if branches. This will not fix parameter sensitivity.
Within parameter uses across executions. One thing that it is very important. And important to note. Is that in. Like it doesn’t matter. With the if branch.
And it doesn’t matter. With using dynamic SQL. In this way. Like you still are. You still have the potential. For parameter sniffing. So.
Now. Another thing to keep in mind. Is that we can no longer. Just recompile the store procedure. In order to show plan differences. Now we have to. We do have to clear out the procedure cache. Because.
Or like. We could clearly look up. Like SQL handles. Or plan handles. Or something. To do this a little bit more surgically. But. Just a quick means to an end. For these demos. Is to just run dbcc free proc hash. To clear stuff out.
But just to show you what I mean. About the parameter sensitivity thing. Like if we. Hit control and l. To get an estimated plan here. Notice that we no longer have.
Any of this stuff. Like we no longer have. Like the full query plan. For either of these things. Coming out here. Right. We just have execute proc. Which means that. The.
Like when we run this. That query responsible for vote type id. Won’t do anything. Right. It’ll just be a normal. Normal. Like. It’ll just get passed over. Right. It’s left alone.
It’s only if that. If only if we. When we hit something. That gets executed in here. That a query plan. Arises for this. But this is what I mean. By the parameter sensitivity thing. Even using dynamic SQL.
If we run this for. What was that? Post type id 4 first. And then post type id 1 second. Post type id 1. Reusing the query plan.
For post type id 4. Does not work out so well. Right. This is not a good time. This takes six. Six seconds to run. We end up spilling a whole bunch of stuff.
Here. And here. And it. Like really. Like. We just. Like. We solved. Like one of the performance problems. But we still have. An additional performance problem.
And it doesn’t really matter. Doesn’t really matter much. Well I mean. It does matter that we fix the. First performance problem. With dynamic SQL. But. We still have the parameter sensitivity issue. To deal with.
We can fix that. By looking at. Some. Sort of like the frequencies. That these. Post and vote type id. These occur.
In the tables. And I still have to fix. The font size on this. But for now. We can just use some advanced. Zooming. Unadvanced zoom hitting. Apparently on that.
And we can look at the counts. For these things. And we can figure out. Like maybe. We can sort of do. Our own version. Of the parameter sensitive. Plan optimization. And bucket these things in.
In. Ways that make sense. Right. So what we’re going to do. Is we’re going to create. This procedure. Called if branch. Compilation dynamic plus.
And this is going to take. An extra step. Along the way. What I’ve done. Is I’ve bucketed. What. Well actually. Start up here a little bit. In both of the.
Dynamic SQL queries. We now have this little token. That says replace me. Right. And down. Before we execute this. We’re going to take one more step. With the dynamic SQL.
SQL. And we are going to say. Replace. And. We’re going to look in the. SQL. Placeholder that we have here. For the text. At replace me at. And if post type ID equals one.
We’re going to inject. One equals select one. If post type ID equals two. We’re going to inject. Two equals select two. If post type ID is in four or five. Then we’ll do three equals select three. If post type ID is in three.
Six seven eight. Then we’ll do four equals select four. And if someone passes in. A completely different post type ID. We’ll do five equals select five. What putting this branch in. Or what putting.
Doing that replace me thing does. Is it prevents. It like basically. Like makes the query hash out. To a different value. And it makes SQL Server. Come up with a unique query plan.
For any one of those. Select one equals. Whatever. Two equals. Three equals. Four or five equals. So we’ll get a different query plan. For each one of those. I’ve also done something similar.
With the vote type ID branch. We have the same replace me thing here. And we have a very similar. Replace call. With different vote type ID. Things.
Now remember. For vote type ID equals two. Do I still have those up? No. I got rid of those. For vote type ID equals two. That was an island unto itself. With 37 million rows. So we want that thing.
To be isolated. All on its own. But if we run this now. For post type ID four. Like we did last time. We still get a nice. Quick execution plan. For vote type ID four.
And you’ll see the. And three equals. Select three. Injected into the query there. If we run this. For post type ID equals one. We will.
I mean. Granted. There’s there’s stuff. We could do. Probably to tune these further. I’m not saying that. Like these couldn’t be better. But this does solve. The majority of the issues. That we first saw. With just the normal. If branches.
And like the. The plan compilation. Cardinality estimation thing. And then later. The parameter sensitivity thing. With sharing plans. Across different parameter values. For both post type ID.
And vote type ID. So now. In this one. We have one equals select one. And these two things. Definitely got different. Execution plans. And the same thing. Will work for vote type ID.
If we run this for vote type ID 12. We get this silly little execution plan. We’ll see three equals select three. Injected into the executed query there. And if we run this again for 10.
We will get a completely different execution plan for that. And we will see the one equals select one. Injected into the query text there. So that solves the problem for us.
With both the if branch compilation. Cardinality estimation problems. And then later.
The parameter sensitivity issues. Now next we’re going to talk about. Replacing complex query logic. With dynamic SQL.
This is a very simple demo. You know. Just make sure I can get the point across. And sort of a reasonable time frame. We’re going to have this procedure here. Called complicated runtime logic.
And we have two parameters here. One called check posts. And one called check comments. And what that ends up as. Is an exist check.
If check post equals true. And then another exists check. If check comments equals true. There are all sorts of ways you could arrange this. That won’t make a lick of difference.
You could use case expressions. You could use like. Like an and outside of the exist. You could write this in any number of ways. But as long as you write this in a way. Where SQL Server has to do this thing.
No matter what. You’re going to get weird execution plans from that. So let’s just make sure we have this created correctly. And now let’s do a worst case scenario.
Where we execute this for first. Both things being false. And we get this query plan. Right. Maybe not the best query plan in the world.
But you know. This is what happens. And now if we execute this for both of these being true. This is going to take a little while to run.
Because SQL Server did its cardinality estimation. With those branches not having anything going for them. Now. SQL Server is actually executing the query.
And having to deal with the repercussions. Of such terrible cardinality estimates. If we look at the execution plan for this. This is what it looks like.
We have. 17 million of one. And a lot of one. And a lot of 10. And what happens is.
When you write it like this. SQL Server uses what’s called. A startup expression predicate. And these get sniffed. Just in the same way. That any other parameter can. So if check post equals one or true.
Then this will do something. But it did the cardinality estimate. For check post. For check post being zero. Or false. So we got just a real crappy plan. That took almost 20 full seconds to run here.
This is another just like. We could spend all day looking at. Well not all day. We could probably spend like another couple minutes. Looking at like true false false true for this.
But this is good enough as is. What I want to do here. Is just use a dynamic version of this. To show you how this would work. In real life.
Where. With dynamic SQL. Where we would just simply do this. And just for convenience. Where one equals one here is fine. And then if check post equals true.
Then we’ll tack this exist clause on. And if check comments equals true. Then we’ll tack this thing on in here. And that should be all fairly straightforward. But if we run this for.
Check post equals false and whatever. Then we just get a count from the users table. Which I probably messed something up logically. In the first one here. But you know.
We got zero back from that. Not a big deal though. It’s good enough to get the point across. But now if we run this for check post equals true. And check comments equals true. We get a much different execution plan.
Where when SQL Server actually had. To append these checks in. Then it used them. So we use the indexes that we created. And we scan them.
Which is fine. Because we’re doing hash joins. And we don’t really have much of a where clause on there. But this is another way to solve. A complex query logic problem. So the last thing that I want to show you. Is how you can use sort of a similar thing.
To deal with filtered indexes. With dynamic SQL. So normally when you create a filter. When you create a filtered index.
Right. Which we’ve done here. Where reputation is greater than 100,000. And you run a parameterized query. And even if that parameterized query matches that expression. SQL Server can’t use that index.
Because SQL Server needs to cache an execution plan. That is safe for any parameter that gets passed in. That’s what you see here.
So SQL Server will warn you about this in the query plan too. If you look here. We’ll see this unmatched index thing. It will tell you that we had an unmatched index.
Because of parameterization. That is the index that we created on the users table. And we have this unmatched index warning down here as well. So all this stuff will tell you. That there was a filtered index available to use.
But SQL Server was unable to use it. And of course using an approach just like before. Or we can do this. Right.
And what we’re doing here is just saying. If reputation is greater than or equal to 100,000. Then replace greater than or equal to reputation. With greater than or equal to 100,000.
Like I know. Like this doesn’t actually like help a lot. Right. Because this is just saying. Like if we have like if like reputation.
We passed in reputation. It’s like 100,001. This wouldn’t make any sense. Right. So like just to help you get around this. Just to give you an idea of a way to get around this. This is what you would do.
Right. So what you would. So kind of like the idea here. Is to just give you. Like show you an example of one thing that you could do. Where this would work out. And if we run this.
Now all of a sudden. Our execution plan will show a scan of our nonclustered index. And we no longer have the unmatched index warning. And rather than having a parameter in here.
We just have this in here. Now there are different ways to accomplish this. You could. You know. Instead of using replace with literal values. You could.
You know. Concatenate whatever the reputation parameter is directly into the string. You could also like insert the reputation parameter into. Like a table variable or a temp table.
And do the replace based on like whatever value that is. There are other ways you could do this. That would. That would. That would have. That would work just fine. For like any value that got passed in here.
And also. You know. Just to complete the circle. An option recompile hint. Would also allow you to bypass this. Because you would. Like you would.
Like you would get the parameter embedding optimization. With a recompile hint. That you wouldn’t get otherwise. So another approach to this. Might be to say something like. If reputation is greater than or equal to 100,000. Then add option recompile.
Onto the end of this string. Right. So there are various ways to take care of it. This is just a simple one. To help you sort of understand the problem. And different ways to approach it. So these are typical ways that I use.
Dynamic SQL to fix performance issues. And SQL Server store procedures. Again. Statement level option recompile hints. Do fix a lot of this stuff.
For free. Without. Or not. Not exactly for free. But without having to write a whole bunch of dynamic SQL. And worry about stuff. The option recompile hint. Does have compilation overhead. So if you have.
These queries take a long time to compile. It might not be the best idea. But. You know. I think that’s a fairly rare problem. Anyway. Thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you. In the next video. Where we will talk more about store procedure stuff. And I don’t know. Maybe. Maybe I’ll surprise. Maybe.
Maybe I’ll even surprise myself. It’s hard to. Hard to tell what’ll happen there. Anyway. Cool. La la la la la. Thank you for watching. 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.
Things I Wish Inline Table Valued Functions Helped With In SQL Server
Video Summary
In this video, I delve into the disappointment surrounding inline table valued functions in SQL Server, particularly focusing on their limitations and how they fall short of expectations. I explore why these functions don’t adequately address issues like local variables and kitchen sink predicates, which often lead to suboptimal query plans despite their potential benefits. Through practical examples, I demonstrate how even with indexes in place, inline table valued functions can still result in inaccurate cardinality estimates when dealing with parameters or local variables, leading to inefficient execution plans.
Full Transcript
Erik Darling here with Darling Data. In this video, we’re going to talk about where I feel disappointed by inline table valued functions. Now, every so often, you know, granted, inline table valued functions in general are my preferred mode of user defined function in SQL Server because Scalar UDFs, despite the Scalar UDF inlining feature, they’re like, that obviously can’t fix all of them, and multi-statement table valued functions, which return the results of a table variable, those two types of functions often have many, many performance issues. My, my disappointment with inline table valued functions mostly comes from the things that they don’t address that they seem like they would be a good vehicle for. So things like, you know, local variables, things like, like kitchen sink type stuff, like you would, you would just hope like that there was be some better way of dealing with that stuff, then like the current tools and methods that we have, but inline table valued functions, don’t give us a way to, to deal with that. So I’m going to talk about that in this video. Then if the slide will kind move forward, thank you. And if you can ask for that in a bit, if you’re going to ask for that, to deal with that. So if you want to ask for that in a bit, if you’re going to ask, but what are you’re saying?
If you would like to support me and Bats coming up with this sort of content for you, then you can do that. There is a little button that Bats is pointing to, or rather a link, where you can become a paid member of the channel. And for as low as $4 a month, you can help keep my eyebrows.
In good shape. If you do not have $4 a month, perhaps you have your own grooming routines that take up the majority of your disposable income, well, you can always cut your fingers off. You can like, you can comment, you can subscribe.
That also gives me all sorts of warm, fuzzy feelings. It does not do much for eyebrow shaping, but still feels pretty good. If you want to ask questions privately that I will answer publicly during my Office Hours episodes, you can do so.
That link is also available for you in the video description, and it’s a good time for everyone. If you need help with SQL Server, boy, do you. Let me tell you how much you need help with SQL Server.
More than I can fit on the screen. I am available for consulting. Believe it or not, I do all of these things at a very reasonable rate, and according to many of our nation’s finest publications, I am the best SQL Server consultant in 75% of the Earth’s hemispheres when it comes to performance tuning.
If you want some awesome SQL Server performance tuning training, well, golly and gosh, don’t I have it. I have about 24 or so hours of it. You can get it all for about 150 USD with that discount code right there.
And of course, coming to you live and in person, SQL Saturday, New York City, 2025, May the 10th, with a performance tuning pre-con on May the 9th with Andreas Walter teaching us about performance tuning stuff. I will be there handing out lunches, making sure that everyone’s happy, and I don’t know.
Maybe this will be my chance to return to bouncing. Maybe I’ll work security and just sit there and stare glumly at people and every once in a while just walk into bathrooms and make sure there’s only one set of feet in the stall.
It’s a hard job. Anyway, let’s talk about inline disappointments here. Now, I’ve got an index that I’ve already created on the post table.
It’s a great index, maybe the best index I’ve ever created. It’s basically all we need for this example. And I’ve also got a first inline valued function here, where we’re going to talk about my first disappointment with inline table valued functions and that they don’t really help with local variable problems, right?
So if we run this query here with a literal value, Siegel server is just like you would expect, is able to take that literal value and apply it as an index seek and do accurate cardinality estimation.
If we hover over this, you’ll see that that is actually passed in as a literal value. This might have some foreshadowing for future demos, but let’s not get too far ahead of ourselves. But if we run this for other values like three, or that’s a two, Eric, fingers, fingers, we will get a plan for that with accurate cardinality.
So with literal values, this query runs, even though this is a parameter up here, when we pass a literal value in, SQL Server is like, dope. I got it.
I can figure this out. But as soon as we start doing things where we declare a local variable and set that equal to something, SQL Server, even though it is still able to seek into the index, now we start getting these wacky cardinality estimates.
And it doesn’t matter what we change this to. Like, again, fingers, three. We will get our, like, 160-something rows back, but SQL Server will still guess this number of rows, which is probably not the greatest thing in the world, right?
This is like, why can’t you just pretend everything’s a literal? Why do you have to do this to me? So that’s no fun there. Another, well, what was I doing?
These two, right? Yeah, we still get, we get the wacky cardinality estimates for both. I should probably change these to the same number so that it makes a little bit more sense, right? So we do this.
We start getting the wacky cardinality estimates even from the inline table valued function. If I change both of these to three, we’ll get the same thing. Now, where it gets a little disappointing is with the, is with parameters, because you would want SQL Server, I mean, maybe, to, like, you know, be able to use inline table valued functions and maybe, you know, not give you parameter sniffing problems.
But unfortunately, they do not help us get around this either. If we run this first for three and we look at the cardinality estimates, we see one, six, seven there. And if we bump this back up to four and we get the slightly higher number of rows back, then we will still be reusing the cardinality estimate from the previous execution.
This is just one of those things where, like, you would hope that, like, you know, something inline that returns a select would just do a little bit more for you. But we just don’t, we don’t get any of that, we don’t get any of that good stuff out of it.
Maybe there should be a fourth class of function that handles this sort of thing. I don’t know. There’s just, there’s just so few good ways of handling things. You just, you just hope that something else will, will reach out and save your day.
We’ve also got this function down here called no optionals, right? And this is going to sort of give us our kitchen sink style setup. But just like with, with other stuff, if we, if we run this query, SQL Server takes that literal value and everything is fine here, right?
Everything’s all good. But as soon as we go to declared variable, we end up with a not so hot thing going on. We end up with a very typical kitchen sink predicate.
Instead of a seek, we scan the, we scan that index. You can see very clearly that is an index scan right there. And that is, that is not, that is not a good time. That is not what we wanted.
And of course, if we were to parameterize the query, and let’s say we ran this first for three, not only would we get the scan, and not only would we get the previous cardinality estimate. Oh, because you know what?
I didn’t clear out the plan cache. So if we run, let’s go back in time and do this for four, which is, I guess, the cache plan for this one currently. I did not free the plan cache for this one. We get the correct cardinality estimate from this, but we still scan, right? So SQL servers can still do cardinality for that.
But then if we switch this back to three, we will, we will retain the cardinality estimate and we will retain the scan. But you know, the, the, the, the estimate will not change for this, which is kind of disappointing as well. The only way to get, the only way to get that would be to either use a recompile hint, right?
Which, you know, is a pretty common way of getting around the kitchen sinky stuff. We go back to using the seek because, because SQL Server can, you know, just infer this as a literal value, right? We get that embedded in the query plan rather than relying on parameters.
The other way to get around that is to write somewhat unsafe dynamic SQL, where we concatenate this into the string. And of course, since, you know, it can be a little more forgiving on this because we are, we are still using a, a, a placeholder, a variable or parameter that is typed as an integer. So you can’t put like drop table something in here.
But, you know, you’re still not going to be great. I’m still not crazy about unsafe types of dynamic SQL. The other way of getting around this is to embed a literal, embed this in there is something concatenate this into the string and then run the query like this. So is this the end of the world?
No, it’s just kind of a bummer because, you know, like I said, you just want something other than like, you know, writing a bunch of tedious wrapper store procedures or writing a bunch of tedious dynamic SQL. And that’s a good example to give you some, like just some break from like these types of problems and queries. And you always hope that like, you know, like things like inline table valued functions, which have so much good use and application and can help with a wide variety of problems caused by other types of functions.
that, you know, like they would just give you some respite from these other things, but they don’t. And this is something that I do have to, you know, explain to clients a bit, which is why I’m talking about it here. But, you know, it’s just kind of a sad face for me.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something, even though, you know, it’s hard to enjoy yourselves when you’re being disappointed. It’s a little difficult to maintain enjoyment when disappointment is up here.
Enjoyment tends to fall off down here. But, yeah, just, you know, it doesn’t work is, I guess, my point in all this. Anyway, I’m going to go hopefully figure out something less disappointing to talk about.
So, anyway, 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.
In this video, I’m Erik Darling from Darling Data, and I’m excited to announce that April’s T-SQL Tuesday is happening on my channel. With Steve Jones handing over the keys, I invite you to share your thoughts on any topic related to SQL Server or database management in a video format. Video content has become increasingly important as search engines struggle with AI-generated content, making it harder for creators like us to be found. Recording videos allows for a more engaging and interactive experience that can help build a personal brand and reach a wider audience. Whether you’re new to recording or have years of experience, I provide tips on how to get started using free tools like Windows 11’s snipping tool, PowerPoint screen recording, and Streamlabs OBS. Remember, while videos are the focus, you still need to publish a blog post linking back to your video for me to include in my roundup. So, grab your camera or microphone and let’s create some amazing content together!
Full Transcript
Erik Darling here with Darling Data. And this video is not, well, it is one of my videos, but this video is an invitation for other videos. You see, this, see, I’m hosting T-SQL Tuesday this month. Steve Jones put the keys in my hand. And what I want you to do is talk about whatever you want. There’s no topic, but whatever you talk about, you have to record a video for it. You know, not just writing something, you are recording. and this one. There are some good reasons for that, right? So like writing blog posts is great. I wrote blog posts for years. I might even still write an occasional blog post. But like I’m, I’m just in love with the video thing lately. Uh, blog posts are great because you have, you know, you’re carefully organized thoughts and words. You have your pictures and you have all your, any scripts you want to hand off to reference and like code you can copy and paste and like, it’s, it’s fine. But the thing is that, um, if you’re trying to copy and paste and like, it’s fine. to like build a brand or you’re trying to like build content that people find and find you and like you know know you for that’s getting harder and harder and harder uh search engines now are completely bypassing content all of their llm agents are stealing your content and using it to just answer questions without linking or referencing your stuff or like hiding like like a million lines down wherever they might have fetched some content from and like you don’t even show up so like unless someone is like specifically looking on your site for something there is a very very like like like search engines just making it impossible for people to find you right they’re just finding this answer from their llm which sucks like like if you want to be known for the stuff that you do you need to produce content in a different way that llms can’t just steal from you now like like sure they could probably still index video with words and transcripts and like you know use that at some point but right now like if you want to build like you know personalized good content that people are able to find and recognize you for video is really the like the only way to keep doing that so like you can put still put all the stuff that you would put into a blog post into a blog post written like along with the video but recording uh at least i find reaches a way different and often wider audience like when i was writing written posts like you know they go out there into the world and often like oftentimes the only comments that you would get would be someone telling you if there was like a typo or a broken link or the picture was wrong or something or like something else is off about the post and like you would just have to be like oh fix now so like but once i started recording videos and you know youtube tracks like views and likes and your channel subscribers and like like you just get like comments on stuff like it’s just way more of a like way more interactive experience so at least for me anyway um it also lets you show off your sparkling amazing personality your winning smile your confidence all that good stuff that just may not come across and just you know typed out written word which can just get kind of dull repetitive letters it’s a lot going on when you write stuff and plus you can get creative in different ways with video i don’t do a lot of editing of my stuff aside from the fact that i have like my my green screen set up but if you might be out there in the world with like a real knack for doing like cool video stuff you might have like transitions or explosions or lasers or robots or i don’t know like all sorts of stuff that you can do with videos like transitions from one scene to another i don’t get into that because i’m i’m i’m i’m this guy but if you are the type of person who gets into that stuff you are like their video world is wide open to you so uh if you’ve never recorded anything before if you’re unfamiliar with the world of recording here are a few ways that you can do it for free uh windows 11 has a snipping tool built in which i seems to support uh audio input recording now uh with powerpoint you can do an insert screen recording uh like i just like right now can just break out of uh i can break out of powerpoint a little bit if you go to the insert menu up here and then you scroll over you can do a screen recording and just plop whatever in there so if you’re a company like you know you have powerpoint you can do this right it’s not it’s not it’s not completely out of your reach uh zoomit which is a free tool in the sys internals pack uh also has screen recording built in now at least as at least as i can tell v9 has it i don’t know if like v8 had it or something but at least for the latest version built screen recording is built into that if you want to get a little bit fancier you can use something like i like i use streamlabs obs because i like to like set up my camera and whatever else so i like i show up where i want to show up and i don’t block the words on the slide you know all sorts of like good thoughtful things in there um you don’t need to include video of yourself for this like i’m not saying that you have to be on camera but we might need to hear that voice of yours so you can explain what’s going on on the screen so there are definitely free ways to do this that uh that like shouldn’t impact you too much and i would assume that like since we are five years into a lot of people working remotely you should have some kind of microphone for all those zoom meetings you may or may not go to or teams meetings that you hopefully don’t have to go to because well we don’t have to talk about that here anyway just a couple rules and regulations uh your post even though it is going to be video based uh has to have this logo in it you have to publish a blog post still that has your video in it so i can go watch it and the only way that i’m going to know to go watch it is if you link back to my blog post with this video in it so i get a little handy ping back in the comments if you like don’t ping back my post i’m not going to know to go find your post so the ping back is necessary like linking back to this post is absolutely a requirement here otherwise i’m not going to know it exists uh you might post it on social media and you like if like but you’re not tagging me in it uh or you’re not like you know tagging this post in it i don’t know where to go find you so you have to link back to this post so i get a ping back so i can do my roundup uh you do have to publish this on or around tuesday april 8th i’m not going to be too much of a stickler for this because i’m probably not going to get to the roundup until like thursday or friday or maybe even monday uh depending on how things trickle in and how i have how much time i have to like watch stuff and like you know come up with my little commentary on everyone’s thing so like just you know near tuesday april 8th would be useful so anyway uh that’s this month’s t-SQL tuesday happy recording and i can’t wait to see what everyone comes up with all right cool now go go go go do it
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.
We have an ERP system, the code of which we do not have access to. The system causes locks of DB. We are currently using 2019. Can you give advice on how to avoid these locks? The least we want is to be able to read the data at the moments of locking. Thank you!
Hi Erik! I have a problem with indexing and was wondering if you could point me in the right direction to get started. There’s a relatively old database that’s been around since 2011ish that I’ve inherited and there’s two transaction tables that are heavily over indexed (25+ indexes). It’s gotten to the point where the indexes on the tables are 200Gb (across both tables) vs 50Gb of data. There’s a lot of very specific covering indexes that are rather large. I’d like to reduce the number of indexes but there’s so much data flying around on production it’s very hard to simulate on Dev. Creating a new index can take 20 minutes, where do I even start? Kind regards, Nick
Do you know of any issues using WAITFOR DELAY ’00:00:01′ in a tight loop. And perhaps having a handful of them at the same time on a the same server. Never mind what happens in the loop. I got that covered.
How do I tell if I already asked my stupid question?
Columnstore maintenance on 2022, what thresholds do you use and what maintenance do you run? Niko’s blogs are ancient now.
In this video, I dive into some common SQL Server challenges and provide practical advice on how to address them. We tackle issues like avoiding locks in an ERP system by exploring options such as read-committed snapshot isolation or snapshot isolation at the database level. For those dealing with overly indexed tables, I offer a step-by-step approach using SP_BlitzIndex to identify unused indexes and merge overlapping ones, ensuring that any necessary index changes are carefully managed. Additionally, we discuss potential pitfalls of using `WAITFOR DELAY` in tight loops and how to mitigate CPU usage issues. Lastly, I share insights on columnstore index maintenance for SQL Server 2022, emphasizing the importance of row group size and compression efficiency over traditional fragmentation checks. Whether you’re a seasoned DBA or just starting out, this session is packed with valuable tips and tricks to help optimize your database performance.
Full Transcript
Erik Darling here with Darling Data. And it’s time for Office Hours. My favorite. Alright, if you like my channel and me and this stuff and you want to sign up to support the channel with money, you can do that for as few as $4 a month. If you don’t, I get it. You can like, you can comment, you could subscribe, and you can be nice enough to ask me to do that. If you have any questions here on Office Hours, like, what do you spend four pre-tax dollars on in New York City? That would be a good question to ask. If you would like some real help with SQL Server, so I’m not asking anonymous questions that get answered publicly, you can pay me to consult on your SQL Server. I will do that. I will humbly, happily do that at a reasonable rate. We can do all of these things and more. That’s my job. I do not pay for that. I do not pay rent with YouTube. It has not reached that level of income stream yet. At this rate, I think I would need roughly 994,000 more subscribers in order to make that a reality at the subscriber to membership signup ratio.
So, perhaps someday. If you would like to get your hands on my training content, I have all of it available at that link. And if you use that coupon code, you will get it for 75% off, meaning just about $150 US for the whole kit and caboodle. Lucky you. That lasts the rest of your life. You only need to buy it once. It’s wonderful. If you would like to see me live and in person, dressed up like a lunch lady, smoking cigarettes, flipping flapjacks, I will be at SQL Saturday, New York City, 2025 on May the 10th.
Of course, there is a performance tuning pre-con with Andreas Walter on May the 9th. Full day deal that costs money to show up to. But I’ll be there, too, organizing lunch meats and sloppy joes and American chop suey and whatever other delicacies from your youth you remember fondly from the lunchroom. But with that out of the way, let’s answer some office hours questions.
And boy, do we have some doozies in here today. All right. Let’s see. One, two, three, four, five. Okay. We have the prerequisite number. We have reached the cost threshold for office hours.
So, let’s begin. Whoa. Zoom it. It’s getting a little sloppy on me here. We have an ERP system, the code of which we do not have access to.
Very typical. The system causes locks of DB. Also quite typical. We are currently using 2019. Can you give advice on how to avoid these locks?
The least we want is to be able to read the data at the moments of locking. Thank you. Well, gosh. There are a few things you can do to make your life a little bit easier in this regard. If the locks are happening because there are long-running modification queries, you could look at adding in indexes that help those long-running modification queries run faster.
That would be less locking overall. But more likely, what you are going to want to do is… Well, you do have two options.
How far you want to pursue these options does depend on vendor supportability and other stuff. If you want all the queries to not get blocked by writes, you could turn on read-committed snapshot isolation at the database level, and any read query that comes in and needs to write data would read versions of rows without having to worry about getting blocked.
It’s not dirty reads, of course. Optimistic isolation levels in SQL Server explicitly disallow dirty reads. You are just reading the version of the row prior to that modification query, doing anything with it and completing.
I have lots of videos about that. If you have any questions about it, I would highly recommend the Everything You Know About Isolation Levels is Wrong playlist, which will walk through all of that.
If vendor supportability for that sort of thing is lacking, in other words, if they say, if you turn that setting on, we can’t support you anymore, what you could do is use a setting called just snapshot isolation, not RCSI read-committed snapshot isolation, just SI snapshot isolation.
The difference is that RCSI applies to every read query that enters the database that doesn’t have any more granular locking hints on it, whereas snapshot isolation only applies to queries that ask for it.
So if you have queries that are, like you have added to the workload, let’s say, like you have some custom store procedures that do stuff, or you have custom code that reports on stuff, you could just say for your code only, set transaction isolation level allows snapshot, and you would be the only queries using those versioned rows.
Everyone else, every other query that goes in and hits the database would be subject to the normal rules of either read-committed the default pessimistic locking isolation level, where no row versioning is involved, or whatever locking hints the query supplies up to and including no lock.
So that would be how I would go there. Now we have a long one. Oh, and we have, oh boy, I mean, the crop must not have, let’s anonymize that a little bit.
We don’t need, we don’t need that kind of PII spilling out in office hours here. Hi, Eric. Hi, whoever you are. I have a problem with indexing.
I was wondering if you could point me in the right direction to get started. There’s a relatively old database. It’s been around since 2011 that I’ve inherited, and there’s two transaction tables that are heavily over-indexed, 25 plus indexes.
It’s gotten to the point where the indexes on the tables are 200 gigs. That’s not very much. It’s got to be a huge across both tables, and 50 gigs, versus 50 gigs of data. There’s a lot of very specific covering indexes that are rather large.
I’d like to reduce the number of indexes, but there’s so much data flying around on production, it’s very hard to simulate on dev. Creating a new index can take 20 minutes. Where do I even start?
Well, not on dev, because dev is not going to be a real, dev is not where you’re going to get any useful information for analysis from. I like the SP Blitz Index store procedure for a lot of reasons.
You can point it at these two tables, and you can see what the indexes are on there, and you can see what their definitions are, and you can see what their usage metrics are.
Now, where I would start is by looking for any unused indexes. If there are any of those, I would start by disabling those, not dropping them, just disabling them, because you want to make sure that any index that you get rid of is still maintained in the database metadata in case you need it back in a hurry, unless you’re very good with, you know, the scripting process where you would create, you know, the set of, like, you know, change scripts, and then a set of rollback scripts, so that you could, like, recreate or rebuild indexes, if you find out that you actually did need that index, then I would just start by disabling them.
The second thing that I would do would be to look for overlapping indexes, and that would be indexes where the key columns are either an exact match, so, like, column ABC, or, like, two indexes where the key columns are like columns ABC, and I would start with those, and then look at the includes, and see if the includes need to be merged in together, because the order of include columns in your index definition doesn’t matter, the order of key columns does, and then I would, if, you know, of course pay attention to if those indexes have anything special about them, like uniqueness, or a where clause perhaps, and, you know, factoring in what exactly, you know, you would have to do to come up with, like, like one index to replace multiple duplicative indexes.
The second thing, the second thing you would look at for the duplicative indexes are ones that are sort of superset subset indexes, where, let’s say, you have one index on columns ABC, with some includes, and maybe an index that had, like, only has key columns on columns AB, with no includes, or maybe, like, the same includes, or maybe slightly different includes, merge the includes in, and just keep the, keep the wider index, and get rid of the narrower index, just because when you’re making this first set of changes, it’s often a lot easier to keep the wider index, that’s more useful to more queries, than keeping a narrow index, and hoping that SQL Server still maybe chooses it out of the kindness of its heart.
So that’s where I would start. As far as creating a new index taking 20 minutes goes, I’m not sure where to begin helping you with that one. I would do the index cleanup before I started trying to add new stuff in.
Granted, the index cleanup can involve merging indexes, but it’s up between, it’s between you and your bosses to find a maintenance window for that.
That is, that is not something I can help you negotiate. All right. Next question. Do you have, know of any issues with using wait for delay one millisecond in a tight loop, and perhaps having a handful of them at the same time on the same server?
Hmm. Well, into my country. Never mind what happens in the loop, I got that covered. Well, you know, what happens between loops stays between loops.
But my, my one time messing up with the wait for delay thing was during the, the development of the now deprecated first responder kit store procedure SP all night log, where one of the facets of that store procedure was to like check for databases that needed to be backed up on one end or databases that needed to be restored on another end.
And my initial thing in a wait, it was, I think, I can’t remember if I, if, if I didn’t have a wait for delay of one millisecond in there, or if I had a very short wait for delay of one millisecond in there.
But, um, basically the end result was one CPU spinning at like a hundred percent over and over and over again, while that while loop just kept checking for stuff and kept looking for stuff to do.
And that apparently wasn’t, wasn’t great. Um, so if, if you would like to avoid, uh, a handful of CPUs constantly spinning at a hundred percent, um, or spiking, I don’t know if they’re going to spin at a hundred percent for what you’re asking them to do.
They spun at a hundred percent for what I was asking them to do just to like, look for stuff, uh, look for work to do, then that would maybe not be great. So I would perhaps look at a CPU graph on the server and see if the handful of while, wait for while loops, uh, it, it matches the number of CPUs that are constantly at some high level of utilization and maybe start thinking about giving that wait for delay a little bit more breathing room.
Uh, I don’t, again, I don’t, you got the loops covered, so I can’t give you any advice on how often you should be checking for a change based on, uh, what that loop is intended to do.
But, um, that is, that is what I’ve run into with it. All right. Question number four. How do I tell if I already asked my stupid question? Well, I would have already given you a stupid answer.
That’s an easy one. We got that out of the way pretty quickly. Uh, columnstore maintenance on 2022. What thresholds do you use and what maintenance do you run? Nico’s blogs are ancient now.
Uh, I haven’t really changed much, uh, in this. Um, Nico’s stuff is still, as far as I can tell, the best out there. All the scripts do not take much actual columnstore specific stuff into account. And the Tiger Team stuff is, well, I don’t think anyone actually still works on that either.
Um, you would think maybe Nico, who went to Microsoft, would offer them some help on their, uh, index maintenance stuff in the columnstore realm. Um, maybe he told them stuff to do when someone else did it, but I don’t, I don’t think those still get worked on, uh, really, if, ever, if, if at all.
Um, I, I still find, um, you know, the, the columnstore specific stuff that Nico wrote into his scripts, uh, like, just as applicable today. Um, you know, like, you know, columnstore maintenance is a lot different from rowstore maintenance.
Uh, this is not to answer your question directly. This is just for the other folks out there, uh, where, you know, uh, regular index maintenance, which typically looks for logical fragmentation is a big old waste of time.
Um, if you wanted to make a case for going out and looking for physical fragmentation of rowstore indexes, I would perhaps be a little bit more germane to your arguments for like looking for, you know, indexes that are twice as large as they need to be because there’s a lot of empty space on data pages, but columnstore indexes have a sort of different set of, um, issues.
And like fragmentation isn’t it for, for, for, for, for columnstore indexes either. Um, columnstore indexes, you have to care about row group size. And if row groups are compressed or not, uh, you have to care about like the ghost record tombstone type things.
And you have to care about how big the Delta store is. The Delta store is uncompressed row groups, right? Like that’s which, you know, if those get big enough, those can impact just how efficient your columnstore indexes are.
So those are the things you need to keep an eye on there. And I don’t think anything has changed about columnstore indexes that would make the threshold that Nico, Nico was talking about in his scripts, any less pertinent on SQL Server 2022.
Versus when he was writing them around like 2016, 20, I forget when he went to Microsoft and stopped, stopped existing as a blogger.
Um, I did see a post recently, which my least favorite, my least favorite kind of blog post, which is that I’m going to start blogging again, blog post, which, you know, is like the first post in three years.
And then the last post for another three years. So, um, at least for most people, who knows, maybe, maybe Nico will break the spell, but, uh, I don’t, I don’t really see, uh, a reason to do things any differently, uh, with columnstore because columnstore still has the same sets of issues, uh, for the performance of columnstore indexes, um, that you would, you would look at then that you would want to look at now.
So like, you know, you know, deleted rows, uncompressed rows, um, like row groups, like, like columns, like really small row groups and then some really big row groups. You would like want to get, try to get some uniformity in there if you can.
I think that, that’s, that sometimes helps, uh, things get a little bit better, but, um, yeah, that’s not, not really a lot to say on that. Unfortunately, um, yeah, I can’t really think of anything else on that.
Maybe, maybe I’ll think of something else later, but, for now that’s, that’s it. I might, maybe I’ll come back to this one if I think of something, but right now I get nothing. Anyway, uh, that is the end of these five questions for office hours.
Um, if I’m, if I’m looking at the queue now, I have, I’m up to four questions after this. So as soon as someone asks one more question, I’ll be able to do another one of these.
It should be very exciting for you. Right? Incredibly exciting. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in, uh, well, the next video, I hope about, about something else.
Maybe we’ll figure it out when we get there. I won’t we? Anyway, thank you for watching.
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.
Strange Query Plans With Inequality Predicates In SQL Server
Video Summary
In this video, I delve into a fascinating aspect of SQL Server query plans—inequality predicates and their impact on performance. Specifically, we explore how parameterized queries can lead to strange query plan patterns when dealing with nullable columns. By examining real-world examples and discussing the nuances of index usage, I demonstrate practical solutions such as adding redundant predicates to improve query performance without altering indexes. If you’re facing similar issues or just want to enhance your understanding of SQL Server query optimization, this video is packed with valuable insights that can help streamline your database operations.
Full Transcript
Erik Darling here with Darling Data, and in this video we are going to examine, like, I guess it’s all on the next slide, isn’t it? Strange query plans with inequality predicates. And by inequality predicates I mean not equal to, whether it’s the exclamation point equal sign or the two opposing type characters there. because I’ve run into this, you know, I’ve reached my cost threshold for talking about it publicly, working with clients on a few things. And there are a few things about this that I think are interesting and that I hope can help you with your query tuning life because you can’t always change the indexes, right? So we’re going to talk about this. There are some things that go along with it, like, nullable columns, and parameters and parameters and stuff, but there’s only so much you can put in the title before it just becomes, like, an article unto itself. But before we do that, let’s talk about you and me and my friend Moolah.
So, if you would like to support my endeavors to bring you the very highest quality, most interesting, incisive, probably the most important SQL Server information available anywhere in the world, you can visit the link in the video description below and sign up for a membership. And for as few as $4 a month, you can keep my beard nicely aligned because, you know, the razors are expensive these days. I don’t know if you’ve bought razors lately. Man, more expensive than eggs. It’s insane. If you have spent all your money on eggs and razors or if you’re over there shaving eggs or whatever it is you do with your free time and you have run out of money, you can do other things to help this channel move along in the world.
You can like, you can comment, you can subscribe. We are up over, let me actually get a current tally of things here. I’m going to look at my YouTube app here. We have about 6,200 subscribers and about 60 paying members. So, we are reaching nearly like a 1% status there. Pretty good. Pretty good. Pretty good.
If you would like to ask a question that I will, privately, that I will answer publicly, there is also a link down in the video description to do so. There is a little Google form. You type in your question and then I answer it during office hours. I do them five at a time, which is, I don’t know, just seem like a nice number at the time.
Maybe I will change that if people are, for some reason, five-a-phobic or something. If you need help with SQL Server in a way that asking office hours questions or poking around the internet isn’t doing you much good for, I am a consultant with reasonable rates and I do all of this work with SQL Server quite effectively and quite efficiently.
And, you know, you’d have a hard time finding a better deal on performance tuning SQL Server. At least, if you’re trying to avoid just some numbskull who’s going to look at missing index requests and tell you to add them. Because that ain’t my game.
If you would like some equally reasonably priced training content, you can get all 24, 25 hours of mine at the beginner, intermediate, and expert. Not just advanced, but expert level. For about $150 USD and that is good for the rest of your life.
It just keeps going as long as you keep going. So, stay healthy out there. SQL Saturday, New York City, 2025, May the 10th, Times Square, Microsoft offices. It’s going to be a hoot.
It’s going to be a real hoot. With that out of the way, let’s talk about the subject of today’s video. Now, in order to sort of show you where this gets interesting, I have created two indexes on the post table.
One on a column called parent ID and one on the column called owner user ID. And you’ll notice a couple little red squiggles here, which means I was kind enough to create these indexes ahead of time. And if I run this query with a couple literal values, we get a very sane and rational query plan using both of those indexes.
We have an index seek into P1 and we have an index seek into P0. And when we seek into these indexes, we very efficiently evaluate. Oh, come on, tooltip, stick with me here.
This predicate, the equality predicate on owner user ID. And we seek into this index and we find where the parent ID is greater than 0 and less than 0. Or some combination there.
Both greater than and less than 0. So, not equal to 0. And that all looks pretty good. Now, there is a missing index request here. And if you are able to create composite indexes or change indexes on your server and do all that stuff, great.
We’ll talk about that in a moment. But what gets interesting is when you take a query like that and you parameterize it. So, now I have the exact same query set up.
And this would be the same with the store procedure. This is no different than using the store procedure here. But when I run the query like this, where both of these parameters have the same value, 0 and 22656. And they are the same definition in here.
And they are used the same in here. The query plan takes on a rather strange shape. Look what happens. Now, we have all this additional stuff in our query plan.
We have some constant scans. We have some concatenation. We have some top-end sorting. We have some merge intervaling.
And then we have a nested loops join to the P0 index on the table. And, of course, the P0 index is where we are looking to do our seek on parent ID, that inequality predicate. What’s particularly, let’s say, a bit icky about this one is that this whole thing is in a serial zone.
You’ll notice that SQL Server steps out of the serial zone immediately after doing that and distributes the streams parallelly to the rest of the parallel zone in the query plan. But this is where we spend the majority of the time. This thing runs for about two seconds total.
And we spend 1.7 seconds in this section right here, between the 1.4 seconds there and the 1.8 seconds there, isn’t it? Pretty close. Now, this is because SQL Server has to do some additional protections in case you ever pass in what might be a null here.
It doesn’t have to do that when you have a literal value. Part of why this has to happen is if we hover over, and I’m going to show you what happens when you flip these in a second. But both the owner user ID column, you can see that is nullable there, and the parent ID column, you can see that is nullable there.
And if we were to switch these around, and I’m not saying that this is the correct query, but if we best show you what I mean. I’m going to hit the insert key there. We don’t want that.
If I switch these around so owner user ID is an inequality predicate and parent ID is an equality predicate, the exact opposite will happen. All right. This will run for roundabout the same amount of time.
Well, actually, a little bit longer there, 3.8 seconds. Hoo-wee. But now the index seek have switched places, right? Now the index seek up here for the equality predicate is on parent ID, and the index seek down here for owner user ID is this is where things get all weird, right?
So this is the strange part of the query plan now. But let’s focus on the original form of the query, right? So this is limited to the inequality predicate with a parameterized query with a nullable column.
Now, what you can do if you want to fix this without changing any indexes is add a sort of redundant predicate here and say, and P, that’s supposed to be a dot, and the dot didn’t come through. Parent ID is not null. And if we add this in alongside our inequality predicate, all of a sudden SQL Server has a whole lot less to worry about.
And we get just about the same query performance that we were getting before, right? So this plan looks just about the same. We have an index seek.
We have an index seek. And this all takes just about 550 milliseconds, which ain’t bad at all. Now, I’m going to quote this out for a second. And I’m going to create the composite index that SQL Server was requesting on the post table.
So that’s leading on owner user ID with parent ID as a secondary key column. That’ll create in a second there. And what I’m going to do is just show you that even, like, this does help the performance generally.
But you still get the weird query plan when you don’t have the not null check on the parent ID column for the inequality predicate. So if we run this, this query will run very reasonably fast. But we still have all this weird stuff in there, right?
We still have the constant scan, the concatenation, the top end sort, the merge interval, and the index seek down here, which we can, of course, get rid of if we keep the semi-redundant predicate on parent ID not being null. And we can get a much nicer, neater execution plan when we tell SQL Server to discard any nulls that might exist in that column.
Now, the kind of funny thing here is that in the owner user ID column and the parent ID column, actually, neither one of these actually has any nulls in it. But SQL Server does still have to protect itself because it has to create a query plan.
Because what if some nulls show up? Sure, there are no modifications right now. But what if, like, three seconds later, I insert a null value in there, and all of a sudden, SQL Server has to figure out some way to cope with that?
So if you have inequality predicates in your query plans, even if they are rather quick query plans, but you have all of that, you start seeing weird stuff with the query plan pattern that I showed you before, where you have the constant scan, concatenation, top end sort, merge interval thing going on there.
All it takes is the redundant predicate to weed all this stuff out. Sometimes that is just a useful thing to do to cut down on query plan weirdness, because you never know who’s going to be looking at these query plans and getting very confused by things.
They might see all that stuff happen and say, wow, I have no idea what all that is. I don’t know. I have no clue.
So it’s just a nice formal thing to do to get rid of it with a redundant predicate and say, let’s reject those nulls out of hand, and let’s just have a nice simple index seek.
So if you are having performance problems with this type of query, that’s one way to fix it. Of course, the composite index is another way to fix it. So, you know, you might want to mine that a little bit.
And of course, if you need help with this sort of thing in real life, and you just can’t figure any of this stuff out on your own, well, my rates are reasonable.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that we will meet again soon in the next video. All right.
Thank you for watching.
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.
Things I Wish Inline Table Valued Functions Helped With In SQL Server
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.
All About SQL Server Stored Procedures: Correct Dynamic SQL Usage
Video Summary
In this video, I delve into the correct usage and safe implementation of dynamic SQL in SQL Server, covering everything from parameterization to object name protection. I discuss why `sp_executesql` is essential for safely executing dynamic SQL, highlighting its importance over `EXEC` when dealing with linked servers. Additionally, I explain how using `quote_name` can significantly enhance the security and reliability of your dynamic SQL by protecting against SQL injection via object names. The video also explores practical examples and best practices, such as handling Unicode characters, managing string lengths with `quote_name`, and ensuring proper concatenation to avoid truncations. By the end, you’ll have a clearer understanding of how to write safer, more efficient dynamic SQL that can withstand unexpected challenges.
Full Transcript
Erik Darling here with Darling Data, and we’ve got a wonderful video for you today. And this is going to be all about the correct usage of Dynamic SQL. Slides messed up, zoom is messed up, you click once, and da-da-da. Anyway, so this isn’t going to be the performance part of it. This is going to be how you write it correctly, safely, and some neat tricks you can do with it, and some gotchas around Dynamic SQL that you need to keep in mind when you’re writing it. So the performance one’s going to be next, I guess, because I don’t think I have a choice. I think that is just the forward arrow of time dictating to us when things will happen. But, man, there’s like a five second delay between when I say slide and this thing. slides. If you like this content, if you’re enjoying, I don’t know, maybe this series on store procedures, or maybe if you just like things around here generally, you have all sorts of ways available to you to keep this show going. You can sign up for a membership for as few as $4 a month. And you can say, good job, Erik. I appreciate you. You can like, you can comment, you can subscribe. Other ways to get some numbers increasing for the channel, always good to see upward trajectory with things. And if you want to ask me a question about SQL Server performance, or I don’t know, if anything else strikes your fancy, I guess you can ask. I can’t promise I’ll have a good answer, though. At this point, the entirety of my brain is wrapped around SQL Server performance stuff. So things outside of that, unless it’s about like, something in the gym.
I don’t know if I would have a terribly good answer for you. I could probably answer some questions about like, wine, scotch. Maybe some food stuff. But yeah, I don’t know. I’m pretty, I’m pretty dumb otherwise. That’s why they call me an E-core. Not a P-core. If you need help with your SQL Server. If you have reached the limits of your patience with some SQL Server performance issue, and you would like me to help you out, you can do that. And I can do all of this stuff. And more. And as always, my rates are reasonable. If you would like to get some training from me, wow, boy, wouldn’t that be nice. You can get all of mine for about 150 USD with that discount code.
Fully assembled down yonder in the video description as well. A SQL Saturday, New York City, May the 10th, 2025 with a performance pre-con by Andreas Walter on May the 9th. So that’s Friday. Saturday is the 10th. I’ll be there, of course. As an organizer, I won’t be speaking there, but I will be taking care of all sorts of things, wandering around the halls.
If I get a break, maybe I’ll sit at a table and just answer questions. I don’t know. Just don’t talk to me while I’m eating. I might bite you. Anyway, let’s talk about safe dynamic SQL use. Now, I spent a lot of time in my… Actually, let’s make sure I ran these things first. I don’t want to get caught flat-footed late and be like, oh, look at the dumb thing I did.
It happens enough in my life. I spent a lot of time talking about dynamic SQL and different reasons you would want to use it. In this video, we’re going to talk about some of the finer points of using it that make using it safer, easier, better, all that other stuff. I wish Microsoft would give us some sort of dynamic SQL data type or template thing where it would not require as much fuss to get dynamic SQL correct.
It would be just a far easier, far safer, far more approachable method of dynamically generating a string for execution. But, you know, instead we get fabric and ledger tables and big data clusters and such junk, such absolute junk, terrible products that who cares? All right, let’s not waste any more time on them. Anyway, first things first.
When you’re executing dynamic SQL, unless you need to do exec at a linked server, which I’m not going to be talking about here because, God, linked servers, you know… It’s funny to see how many questions people ask about linked servers because you’re just like… Would you give it up already? Would you just stop with the linked servers?
So unless you need to do exec at a linked server, you want to be using sp-execute-sql because that’s the only way that you can parameterize dynamic SQL safely. There are… In the performance section, we’ll talk about this stuff, but there are a few limited use cases where somewhat unsafe dynamic SQL would be the name of the game. In some cases, for use with very specific things that disagree with parameters, like filtered indexes.
But we’ll talk about that later. When you’re using sp-execute-sql, all of the arguments that you pass to… Well, two of the arguments that you pass to sp-execute-sql do need to be nvarkar.
So in this case, I have one incorrect, that one, and one correct, that one. And if I try to run this, we will get an error. And SQL Server will say, This procedure expects parameter statement of type n-text, n-char, n-var, n-car, n-var, n-char, n-var-char.
n-car, care, whatever. They’re not characters. They’re characters.
n-var-car, not char. They’re not characters either. It’s like vegetarians, right? If they were like vegans, they’d be vegetarians.
But they’re not. If we try to do this the wrong way here, we will get a different error, right? So here we have this one correct with n-var, n-var-car.
And this one we have incorrect with varkar. And if we try to run this one, of course, SQL Server will throw a different error and say, The procedure expects parameter params of type n-text, n-car, n-var-car.
So of course, neither of these work. But these are the rigorous demands of a very strongly typed language like SQL. I’m kidding.
So we get errors from that. But if we do everything correct and we have our strings set up correctly with these, then we will execute flawlessly. And this is how you want to do it when you’re writing it.
Besides, you never know when something Unicode-y or something outside of the standard character set might sneak in to one of the strings that you’re building. And when it does, I’ve seen all sorts of cases where database names, table names, something else ended up with a Unicode character in them.
And you will get just question marks. You will get one or more question marks depending on how many bytes your special character consumes. All right.
So that’s not good. You don’t want question marks showing up in there when you should have a valid character in there. The other thing is that when you are dealing with dynamic SQL, you always want to use quote name to protect your object names.
We’re going to talk about limitations of it, but quote name protects you from SQL injection via object name when just square brackets won’t. Now, square brackets do do something, right?
But they don’t do the full thing. Because quote name will actually double bracket stuff when it should, where just using square brackets won’t. So if I get rid of this table and then I run these two sections of code, the first one is…
Oh, boy. I did that all wonky, didn’t I? Look at that. Oh, Eric. Oh, you’re slipping. Slipping and tripping. Plus signs go at the end of the line, not at the beginning of the line. That’s just as bad as the leading comma.
So what I’m going to do is run this where I’m using the square brackets to enclose the string here. And then in the second one, I’m going to use quote name to enclose the string. And I think what’s interesting for both of these is that neither one of them actually does something, right?
Like they both throw an error, right? To not find store procedure print one. They both throw that error.
But in the case of the first one, the table actually does get dropped, right? Because when we try to… What do you call it?
When we try to select from it, we get invalid object name T down here, where that’s something we don’t get down here. So up in the results, we can see that we get an ID 2. And that’s from this second one, right?
That’s from… Jeez, I did not hit format on the script before I ran it. From the second batch where we inserted the value of 2 into it, right? We inserted a 1 up here.
For the first one, we didn’t get anything back. We just got an error saying invalid object name. And then we did this next one. And in this one, like quote name protected us from dropping the table in the dynamic SQL. So like quote name really does make your dynamic SQL safer all around.
So if you’re planning on accepting some combination, one kind of note up here. Sysname is the best data type to use generally for objects in SQL Server. That’ll keep you from messing a lot of stuff up.
But if you are planning on accepting fully qualified things like schema.object, database.schema.object, or database.schema.object, you do need slightly longer in VARCHAR strings because quote name has a limit. And we’ll talk about that in a minute.
Anyway, one reason why you need the bracket things in general are when SQL Server hits things that it can’t identify easily. So if I try to run this and create this clown table, SQL Server will say incorrect syntax near that. All right.
But if I say create table clown in brackets, I can do that. Where this gets interesting for dynamic SQL is that even if we use all the correct data types and we do everything the way we ought to, if we end up with this as a string, SQL Server will not be able to use that.
And we will get the same sort of error here with the incorrect syntax near this thing. Right. And that’s not a good time.
If we contrast that with this, where we use quote name on the table name, all of a sudden SQL Server will be able to figure that out just fine and select stuff from it. Right.
So using quote name there gives us that. In this case, you know, you could square bracket. You could just square bracket that, but our goal is to write the safest possible dynamic SQL, not to write just whatever the laziest, clumsiest dynamic SQL we can rethink of on the spot is. Now, one thing that is worth noting, though, is like, and this happens to me a lot in my store procedures, where I’m like, okay, I’ve got this identifier, right?
I’ve got a database server database schema table, whatever. And I stuck it in quote name. But now I want to go figure out if that object actually exists or not before I go try to do anything with it.
Is like none of the system views have object names quoted in them. Right. So like if you do something like this and you say, well, you know, I’m going to be safe and set my parameter or variable to the quoted version of something.
If you need to go look that thing up, you need to either like unquote it or like do like replace or just use parse name. Parse name is a handy built in function that is meant to do that. So like running these two things, what we get is for the one where we didn’t unquote stuff with parse name, we get nothing back.
For the one where we did unquote stuff with parse name, we get a valid result back. So if you’re going to like look at objects in here, you’re going to have, you’re going to want to do that anyway. Quote name, though, is, or we’ll talk about that in a second.
But you can, of course, use, you can, of course, like tell quote name anything to put in for what to use as the identifier. Right. You can like by default, it’s square brackets.
You can use quotes. You can use parentheses. You can use anything you want here as long as it’s like valid, I guess. But this is really only useful for dynamic SQL. Now, like if you have to embed one of these in a string and like you want to say like where like, you know, some object name equals something, you might want to use quote name to make, make your life easier with like the, like the single ticks and stuff.
Right. Because like if you just use that in straight, like, like a normal query, that’ll put, this will put double ticks around this. And there’s no object name called double tick, called tick clown tick.
Right. It’s just, it’s not there. Quote name does have a limitation, though. And that is 258 bytes. And keep in mind that the string, like the lengths that you see here are not characters, they are bytes.
So if we do this, we will get two strings back yelling, ah, at us. This one, you can see, does have the quote on it. I forget if this will get us to the very end.
It does. So we actually get both ends of the quote name. But if we say 129 here, then this will actually just return a null. Right.
So you can’t use quote name on very long strings. You can’t use quote name on anything longer than a single in VARCAR 128. You’ll notice that I have one, I have this defined as 129. It’s all about what you put in the string, though, right?
So if this were like in VARCAR max or something, like it would, it would return null if it were longer than 128 bytes and wouldn’t quote it. So quote name is kind of only useful for single parts of object identifiers. So like server name, database name, schema name, object name, whatever that object is, whether it’s a procedure, table, view, function, whatever.
So just, you know, be careful with quote name because you can have some unexpected disappearing strings in there. Another problem that I run into a lot with SQL Server that is difficult to reproduce reliably is when you’re concatenating dynamic strings together, a lot of the time what you’ll have, like what might happen is, and this is like seemingly completely random. Like there’s just some weird like implicit conversion that happens and your beautiful dynamic string gets truncated.
And this happens a lot when you’re just like putting some smaller portion of a, like tacking some smaller portion of a string onto your longer string. So what I end up having to do in a lot of my procedures is whenever I need to like, like add on something in here, I need to explicitly convert whatever I’m adding on to be another in VARCAR max. So I don’t like, I don’t get the string doesn’t concatenate surprise.
And I’m sitting there like, like trying, like printing it out and like, wait a minute, there’s part of the string missing. What, where did it go? Cause like, like I know print has limits on it and you know, you can run into, you can run into problems there. So, but if you’re like printing sub strings and you’re like, wait a minute, I’m printing this sub string and my string is still disappearing.
You most likely have to convert some like tacked on addition to the string with, with convert. I know that the concat function exists. And some of my procedures do run in versions of SQL Server where the concat function is available.
Like they’re only running that like quickie store. I just don’t use it because, you know, I distribute my scripts as like, as a whole, pretty much like you can get that. We can get like the install all file.
And I don’t want someone on like an older version of SQL Server that doesn’t, maybe doesn’t have concat where some of the store procedures are still valid and will still return results and give you information back. I don’t want those to like error out because of something in a different file. So another thing that’s important with dynamic SQL is formatting.
Most of the time I will, I will write as much of the query out as I can and format it and then paste that into my dynamic SQL string and do whatever other stuff I need to. So all of my dynamic SQL is formatted in exactly the way that I would format a normal query. And I am pretty happy with that because then I have a nice legible dynamic string that I can copy and paste out of here and make my life is a lot easier.
You know, side note on style stuff, whatever you’re writing dynamic SQL, when you can put a comment in the string that tells people what store procedure it comes from. Output is another very, very useful thing that you can do or rather output parameters or output values is a very useful thing that you can do with dynamic SQL. And in this case, you can actually use these things as input and output values.
So this is kind of a neat trick where I’m going to set I equal to zero. I’m going to set E equal to the max database ID. I got my string here, right?
So I got all this stuff lined up and then I’m going to pass. I have the same parameter in here. I and I. So what I’m doing is selecting the top one database ID where the database ID is greater than I. And then down here in a loop, I’m just going to say while I is less than E, well, like I is less than the max database ID, just keep outputting that and running stuff.
And what we can do there is actually just pass that in and out where we’re looking at database one, two, three, four, five, six, seven, eight, nine of nine. So you can use output to drive loops with dynamic SQL, which is pretty neat because then you don’t have to either keep rerunning syntax to find some value. And you don’t have to, in your loop, you don’t have to increment.
You don’t have to remember to increment anything here. It’s almost as cool as a cursor where you don’t have to remember to be like, oh, set I plus equals one or something to go find the next value. It’s especially helpful when you might not have contiguous values.
So like, let’s say I had database IDs one, three, five, seven, nine. I wouldn’t waste time looking for database IDs two, four, six, eight. So you can do neat stuff with output there.
But what I use output for a lot in the context of dynamic SQL, and here’s actually a good example of using quote name with the single ticks to make string quoting within the dynamic string easier. What I end up doing for it a lot is using it to validate the existence of other things and other databases. So what I’m doing here is I’m using the output stuff to figure out if the schema DBO exists in the master database.
And so if I run this, SQL Server will be like, does the output schema exist? Go run this query in the master database context where name equals, oops, where name equals DBO. And then it’ll say, hey, it looks like that schema does exist.
All right. But if I change this to like something stupid, like typing, if I change this to something stupid that obviously doesn’t exist and I go look for it, SQL Server will be, oh, I forgot to highlight the rest of that string.
That’s the important part, isn’t it? If I highlight this, we’ll get an error that says it looks like the schema barf doesn’t exist in that database. Now, like granted, like schema checking is one of those things where you could probably skip over checking to see if DBO exists. Right.
Like, like obviously it’s going to be there. Whether you have permission and access to it might be a different matter, but it’s there. Now, the last thing I want to talk about in this video is when you are accepting any object name, like even using quote name, you know, sometimes you want to be extra safe. Right.
And the way to be extra safe is to never concatenate or even using quote name, put those into whatever dynamic string you’re going to execute. So what I’ll do sometimes if I need to be extra, extra safe is I’ll accept the parameters for whatever in here and then I’ll declare safe local variables for those things here. Right.
And what I’ll do is I will only use the parameter values to look up safe values. Right. So like I’ll go to sys.databases and based on the database ID, like if this is if there’s if this is invalid, like you can’t execute anything in here. It’s not dynamic.
See, well, this isn’t going to execute like some malicious string, but this will just set the safe database name to the name that aligns with the database ID. So we ditto this in here with like the safe schema and table. We just don’t join sys.tables assist.schemas.
And sometimes it’s useful to put like a backup thing in here just in case like but, you know, you don’t obviously don’t have to do that. And then for columns, what I’ll do and I know some of these functions aren’t available in all the every version of SQL Server and every compat level. But this is just sort of a brevity here.
I’m going to use string ag and string split to do this. And what and what this will get you is like a safe list of columns based on whatever someone supplied in the column column names parameter. Right.
So the safe columns get assigned to a comma separated list there. And each one of those each one of those column names gets put gets put in quote name. Right. So that that’s all worked out.
Now, you want to like do some checking, be like, hey, if any of these come back. It’s null. Just say, you know, something was invalid and whatever. Now, one thing with the safe columns and I’ll show you this when we execute stuff.
But it’s kind of neat about the safe columns thing is if someone gets one column wrong, the store procedure won’t error out. It’ll just skip that column. So then, of course, this will be our dynamic SQL where we’re still using quote name on all this stuff just in case there’s anything weird in them.
But like I know we could have said that. We could have said we could have set some of this stuff earlier. We could have said like quote name, whatever, but it doesn’t matter so much.
Then we’ll set that stuff in there. But let’s just make sure this is created and everything is good with this. And then let’s walk through a couple executions of this.
So this with like doing this will return results. Right. This will be fine. Doing this, we get all the results back. If we put in invalid database name and we just put a Z on there, this will say, hey, invalid database.
And if we do the same thing to the schema name, it’ll say invalid table or schema. And if we can see the debas there, right, that’s the incorrect one. And then if we do that for album, let’s say that make that albums.
This will throw the invalid table, like invalid schema or table for albums. But here’s what I was talking about with the column list. And let’s say we just put a Z at the end here.
Now, actually, I’m going to run this once. Let’s make another copy of this. And let me just show you how this is different. If you run these two, notice the track ID column isn’t this one, but the track ID column just gets emitted from this one.
And that’s because when we look for stuff up here, like that track ID just doesn’t make the in clause for this. If we were to change this drastically and we were to do something like maybe only select one column and have it have a Z at the end, then we would get the column list thing here.
Now, if you were like really interested in making this extra like verbose and whatever, you could, of course, like, you know, compare the list of columns passed in to the list of safe columns or the list of columns that you find and be like, hey, this column was invalid.
But maybe look at that. I just didn’t do that here. Anyway, this is about as much about like safe and sound and good dynamic SQL uses I can fit into a video a reasonable length. It’s a lot to remember and a lot to think about.
But hopefully the more you do it, the easier and more intuitive that becomes. So anyway, I hope you enjoyed yourselves. I hope you learned something.
And I will see you in the next video where we will talk about dynamic SQL, like I said before, in the context of performance, which is generally what we care about. But at some point, we also care about tables not getting dropped and data not getting exfiltrated or vandalized and all that good stuff.
So anyway, we’ll do that. And I forget what’s after dynamic SQL, but that’s OK. It’s OK.
Once you know dynamic SQL, what more do you need, really? Anyway, thank you for watching.
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.
All About SQL Server Stored Procedures: Wrapper Stored Procedures
Video Summary
In this video, I delve into the world of wrapper stored procedures and how they can help improve performance in SQL Server. Specifically, we focus on transforming local variables into more performant parameters, preventing code from compiling when it’s not used in an if branch, and dealing with parameter sniffing issues by generating different query plans. While wrapper stored procedures offer some benefits, I also discuss their limitations, particularly the maintenance overhead they can introduce and how dynamic SQL might be a better solution for complex queries with many optional parameters. By walking through practical examples, I demonstrate how to use wrapper stored procedures effectively to address common performance problems in stored procedures that rely heavily on local variables.
Full Transcript
Erik Darling here with Darling Data, and in today’s video we are going to continue our stored procedure soliloquy, and we are going to talk about how to use wrapper stored procedures to improve performance. Now, there are a couple topics in here that I am not going to cover because I’m going to cover them when we start talking about temporary objects in stored procedures. particularly around unique naming for temp tables, pound sign temp tables in stored procedures, and also the sort of like creating a temp table in one procedure and then referencing it in another procedure. So we’re going to do that stuff. And in a different video, in this one, we are going to stay focused on how wrapper stored procedures can help you with certain performance issues, particularly ones around the old local variables in stored procedure code. So, if this thing will kindly progress to the next slide. Thank you. Slide, please. If you would like to support this grand content that I produce for you, you are free to click the little link in the video description that says join, and become a full-fledged paying member. I’m going to have a little surprise for paying members coming up there. A fun little thing to test out. And for as few as $4 a month, you can help me stay motivated to do these things.
I don’t mean to threaten you too much here. If you are just shy of $4, nothing good going for you. I don’t know. I don’t know what’s wrong with your life. You can do other things to support this channel like like and comment and subscribe. I’ve noticed that a few videos lately, which is rare for me, have gotten a thumbs down. So if you are planning on leaving a thumbs down for some reason, please do leave a little note as to what did not meet expectations. It is difficult for me to improve if all you do is say, boo, if there’s a good reason for it. Please do let me know what it is.
Even if it’s just me. Even if it’s just, I hate you. I hate you and I want you to die. You can say that. It’s fine. I have very thick skin. If you would like to ask a question that I will answer on my Office Hours episodes, that link is also available in the video description. If you click on that, you will be taken to a page with some information and a link to a form where you can plop your question in there.
I have a few episodes of those recorded and scheduled out since I answer five at a time. You’ve been kind enough to grace me with many questions in there for me to answer. If you need help with your SQL Server beyond what you think an anonymous question answered on YouTube can provide you with, I am available as a consultant with reasonable rates to do all of the above tasks and more.
Don’t feel limited by this list. There are many other things we can do here. If you would like some also reasonably priced SQL Server performance tuning training, I’ve got a hedge over 24 hours of that. And you can get all of that for about $150 US dollars.
And that is good for the rest of your life. It is not a subscription product. This link is also fully assembled for you down in the video description.
SQL Saturday, New York City. March 10th. May 10th. I don’t know why that always happens to me.
May the 10th of 2025. New York City. Microsoft offices. Times Square. Performance pre-con by Andreas Walter on May 9th. Just be there or be forever square and you’re holding something.
Peace. Anyway, let’s talk about wrapper store procedures. And I’m not talking about…
Actually, that would be a stupid joke. I’m not going to… I’m absolutely not going to make that joke. I’m actually somewhat appalled with myself for even considering making that joke. Anyway, wrapper store procedures are good for all sorts of things.
Like what I’m going to show you today. Transforming declared local variables into much more performant parameters. Preventing code from compiling when it is not used in an if branch.
Which would be a very handy thing in some cases. And also generating different query plans to deal with parameter sniffing. Which is a perfectly good and valid use case.
But it really only works if you are worried about a couple different… Maybe like one or two faulty parameters. Beyond that, the number of store procedures that you need to maintain to deal with that very quickly becomes unwieldy.
And you are much, much better off in the majority of these scenarios… Just using some well-formed dynamic SQL. There is some upside to this, of course, over dynamic SQL.
You know, all the typical stuff around security and permissions. And, you know, if you’re into that sort of thing… You might care very much about this.
I do not. I do not delve into security. I do not delve into permissions. I stay away from that stuff just as much as possible. Because it is incredibly dull.
And frustrating. And annoying. I have… I have… Just… I have enough grievances and annoyances with having to use authenticators for things. Where like…
Not like… Oh, I am never going to use an authenticator. Send me an SSMS. Like I use authenticators for a lot of stuff. It is aggravating because I have like five of them now. And some of them have really long lists of stuff.
And I am like… Well, I can never remember like which thing is in where. And then like… Like scrolling through this long list of crap. And then like… They all do like the yes… The confirmation screen differently.
Where it is like… Like the yes and no will be on different sides for different authenticators. And then like… Some of them just have really confusing logic. Like… Like yes, it is not me.
Or no, it is me. And like… Huh? Should… Which one lets me in? Just give me a green button and a red button. I do not need…
I do not need all this confusing wording. And my authenticator apps life is hard enough. Anyway. Anyway. The sort of downside of store procedure… Using store procedures or…
You know, for… Like… If you have like store procedures that are going to have to maintain duplicative logic. That’s where it kind of sucks just for that thing.
Because now it’s like if you change one, you have to change the other one. And if you have a bunch of them, you have to change a bunch of them. But there is a shared downside of… Well, I mean, not a downside.
Just a little bit of a caveat to either wrapper store procedures or dynamic SQL. Where the resource usage of the underlings, right? Like the inner store procedure or the inner execution of dynamic SQL.
Will all be attributed to the outer store procedure. So like… You might be looking at the plan cache or most likely query store. And you might see a store procedure pop up in there.
And you’re like… Wow. This thing… This really does all that? And then you look at the store procedure. And it calls like other store procedures. Or creates a bunch of dynamic SQL. And that… Like all of that…
Bubbles up to the parent that calls it. And so… You… Like… Like it just becomes like a little bit more… Strenuous to figure out… Like either which of the sub-store procedures.
Or which of the dynamic SQL executions… You know… Caused a problem. Granted, it’s a little bit easier to… Find other store procedure names. In either the plan cache or query store.
As long as their plan cache is sort of reliable. But… I think… You know… With dynamic SQL…
The additional sort of… Additional sort of pain with that is that… There is no parent object associated with it. It is completely headless and detached. Much like…
Microsoft’s implementation of the parameter sensitive plan optimization. Where like… Like there’s like… Like you don’t get like the… The calling procedure name with the plan variant. Which is pretty annoying.
Um… But you know… If performance is… Generally acceptable. This is somewhat less of a concern overall. Uh…
Oh… Hey… Zoom it. First… First wink of the day. Uh… But if performance is okay… Then you generally spend less time on this. Um… Of course the… You know… The classic…
Uh… Solution for dynamic SQL… Is to put a comment… In… The dynamic SQL block… With the store procedure name that calls it. So you can still search the text of stuff for a store procedure name. That’s just a little…
Uh… More CPU intensive than just looking for an object name. But the goal for us is of course better performance. It is not necessarily… Any of… Any of this stuff. So we’re gonna…
We’re gonna not talk about much more of that stuff. But this is kind of my point with… Wrapper store procedures. Right? Like… Like let’s say you… You know…
You do some stuff. And then based on that stuff… You go do some other stuff. Right? Now… Uh… Let’s just say… Let’s just pretend that these are store procedures. And let’s just pretend for a second…
That… Uh… You know… Uh… We… We maintain very similar logic… In these. And all of a sudden… If we need to add some exclusion… Or exception…
Or some other columns… Or some additional join logic… Or filtering logic… Or something… Uh… That… You know… We have to maintain that now across to it. It’s obviously a little bit more… Or work for you. And some more stuff to have to remember.
But… Again… Minor point. Uh… If you have a lot of if-else branches… Uh… You’ll have a lot more store procedures to dig around. Um…
Let’s see… Uh… Did it… Uh… Let’s see… Store procedures aren’t a very good use case for kitchen sink queries… That have a lot of optional parameters. Because again… The number of permutations and different… Combinations of stuff is not going to be fun…
For you to create all of those objects for. Dynamic SQL is the best… Uh… Best deal there. But… For this one… I’m just going to show you real quick…
Uh… How… Uh… Wrapper store procedures can be useful for… Uh… Fixing performance problems with… Uh… Store procedures that use local variables in them. Since that is sort of what led us to this point.
Uh… So we’ve got two indexes on the post table. Uh… One called P0. That is just on the owner user ID column. And I already created these because I didn’t want to make you wait when I did all that.
And one called P1. That is on parent ID, creation date, and last activity date. And includes post type ID. And uh…
What we’re going to do is pretend in here that either… Someone did something like this. Right? And said when parent ID is less than zero… Then set parent ID fix back to zero. Uh…
Or they were just like… They’re just one of those… Ha ha. No parameter sniffing. I’m going to… People. All right? That’s like… Not… Not the brightest bunch typically.
And then… Uh… We’ve got another store procedure down here. Where… Uh… And like we take the… The query that would have used this. Right?
Which is this thing. Uh… And we put that into an inner store procedure. And we have an outer store procedure that still does our little fixer upper here. But then executes the inner store procedure here with the parent ID fix stuff in it.
So… Uh… When you run this… Uh… You are going to of course…
Uh… Use a local variable. Uh… Parent ID is going to get replaced by the local variable in the where clause. And if we… Uh… If we run this…
We are going to be unhappy with the performance results. Uh… Not only are we going to use a… Well… Two things are going to happen. One… We’re going to get a real bad cardinality estimate on parent ID. And two…
Because we get a real bad cardinality estimate on parent ID. We are going to choose a less efficient index. And we are going to choose a less efficient query plan. Um…
See here… Uh… We choose the index P1. Remember this is the one that led with parent ID. So… Because we created an index that leads with parent ID. We have a full scan histogram on parent ID. But because we use a local variable.
SQL Server makes a real real bad guess on how many rows are going to come out of that. And because of that real bad guess. SQL Server cost this plan very very low. Right?
Estimated subtree cost of 0.0192738 query bucks. And we get a really bad serial plan out of this. If this plan went parallel. It would probably be a bit faster.
Because we would have more CPU doing more work here. But that’s not… That’s not really the point. SQL Server just didn’t even come close to a parallel plan on this one. There’s not even like a little like edging you could do to bring that one up.
With this one. This is the store… This is the version of the store procedure that is going to… Call the wrapper store procedure inside of it.
So even with the local variable thing that we do in the outer… Store procedure. Since that gets transmogrified into a parameter when we pass it to this inner store procedure. Right?
That is in the parameter list there. And that is a parameter there. SQL Server is going to do its… It’s like normal cardinality estimation process. It is not going to use that density vector guess that we get from local variables. And of course this will run much faster.
We got a little bit of a funny execution plan there. But not the end of the world. This one does go parallel. This one does seek into some stuff.
And we do… Do a pretty good job of getting a fast enough execution plan there. So…
Local variables… When it comes to performance… Tend to cause more problems than they solve. There is some room for testing in that. I’m happy for you to test things and figure out on your own if there is an appreciable difference with things.
Don’t just test one execution of the store procedure though. Test a bunch of them because you might find things get a little weird. If you’re having a parameter sniffing problem…
What you really want to do to… Like dig into a parameter sniffing problem… Is have two sets of parameters to test. One that creates the plan…
That you want shared by future executions. And then a set of parameters that has a far different distribution than the initial set. So you want the plan to get reused… So you can figure out if the query plan that you’re generating with that initial set is good and shareable amongst others.
If you’re going to test local variables… Don’t just test that. Test everything.
Test the first set. Test the second set. Don’t just test one set. Make sure that whatever parameters you’re testing the local variables with… You have a variety of values that you can put in there…
To make sure that across a large number of executions… You see a significant improvement. That is not just a one and done thing.
That is if you are facing a parameter sniffing issue… When your big idea to fix parameter sniffing… Is to remove parameter sniffing from the equation by using local variables… Then you need to seriously consider…
Finding a number of permutations that cause the parameter sniffing issue in the first place… And making sure that it is significantly better in both cases. You don’t just want to be one of those people who said…
Oh, it seemed to help. A number of phone calls I get on where I see dumb stuff in code… And someone says, it seemed to help. And we’re sitting there staring at some query that runs for like 30 seconds, a minute, more. Like, did it seem to help?
Did it… What did it help exactly? Did it finish a second faster? Like, tell me what it seemed to help. Because we’re on the phone now and it didn’t seem to help anything. We have reached an impasse with it seeming to help.
So, just be careful out there when you’re using them. Make sure that you are testing things thoroughly. And make sure that you see an actual improvement. Look at those actual execution plans.
Because that will tell you… That will give you more information than you just running the query and being like, Yeah, it seemed to help. Because someone like me will sit there and stare at you.
Stare into your soul until you admit you were wrong. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you in the next video where we will talk about some other store procedure stuff. Undoubtedly. Or maybe we’ll take a break from store procedures and talk about something else.
And then come back to store procedures. I haven’t… I cannot see into the future, my friends. I am anything but psychic. Anyway.
Thank you for watching. 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.
Hi Erik! I recently read a post stating that not de-fragmenting your indexes lead to more expensive plans, therefore ignoring index fragmentation Is a bad idea.” What do you think about this? This Is the post I’m referring to: https://sqlperformance.com/2017/12/sql-indexes/impact-fragmentation-plans
How can I get more out of SolarWinds DPA? People like it so much that I really don’t know what I’m missing. What is it great for?
When tables get large and the default sample rate inflates estimates do you generally recommend increasing the sample rate to lower the estimates or doing a full scan and disabling stats updates, or something else?
When viewing a long-running query in sp_whoisactive, how can I retrieve the parameter values of the query? I thought I could get them with @get_plans = 1 and then examining the ParameterCompiledValue in the execution plan, but that is unfortunately the param value for the cached plan that it is using, not the current param value that is causing it to run slow. I’m on SQL Server 2019 and have query store enabled. This would be a huge help, thanks!
If you could only have one watch from your current collection, what would it be? Also, what’s a grail watch that you think about a lot but can’t afford/justify/get ahold of? PS, if you can only answer one, please answer my serious SQL related question. Cheers
In this video, I dive into some interesting questions from the Darling Data community during our Office Hours session. We start by discussing a post from 2017 about index defragmentation and its impact on query plans. I share my thoughts on John Cahias’ perspective, emphasizing the importance of understanding different types of fragmentation and the specific context in which his advice was given. Moving on, we explore how to get more out of SolarWinds DPA, leading to a strong recommendation to switch to SQL Sentry instead due to its superior performance and reliability. The session then delves into statistical sampling rates for large tables, where I highlight the complexities involved and commend those who have successfully resolved such intricate issues. Finally, we tackle retrieving parameter values from long-running queries in SP:who is active, discussing limitations with SQL Server 2019 and suggesting alternative methods like extended events. Throughout the session, I share practical insights and tools that can help address these challenges effectively.
Full Transcript
Erik Darling here with Darling Data. And if you can’t tell by the big smile on my face that it is time for Office Hours, well, you haven’t watched enough Office Hours, or I haven’t recorded enough Office Hours, Hourses yet. But anyway, before we do Office Hours, let’s talk a little bit about my channel. If you would like to support it, you can do that. If not, totally fine. You can do other stuff, like like and comment and subscribe and ask questions on Office Hours. It’s a great deal. If you would like to ask me questions in exchange for actual money, and have me fix stuff in exchange for actual money, my rates are reasonable and we can make arrangements to do all of these things. It’s a wonderful setup being a consultant. If you would like to get some training from me in the long form, in the streaming video variety, not on YouTube, you want to feel real close and intimate with me, you can get all 24 hours of my performance tuning training content for about 150 US dollars. Good for the rest of your life. That link, that discount code again down there in the old video description. You can also catch me handing out lunches and making sure everyone’s happy at SQL Saturday New York City coming up May 10th 2025 at the Microsoft offices in Times Square. If you’ve never been there, I can’t recommend Times Square.
They got a Sbarro, they got a Sbarro, they got a Ruby Tuesday, they got, what’s the other one, I think they still have a Bubba Gump shrimp, shrimp, shrimp boat, house, something. You could also walk a few blocks away and get real food that’s good too. Whatever you’re into. Some people don’t have any real sense of food. So, delete anything. Probably explains a lot. But anyway, let’s answer some questions. Alright. So, the first one up here that we’re going to deal with is…
Hi, Eric. Hi. How’s it going? I recently read a post. You recently read a post from 2017. Good, good, good. Stating that not defragmenting your indexes lead to more expensive plans, therefore ignoring fragmentation is a bad idea. What do you think about this? Well, going by memory, because I don’t want to click on any links here.
Going by memory, that post was written by a very smart fella named John Cahias. And, you know, we’ve got nothing bad to say about John. He’s been a valuable contributor to SQL Server stuff for a very long time. Very smart guy. I hope one can’t say a bad thing about him. I hope he has all the great weekends money can buy.
And I remember that post. And I think you should probably read the comments on that post before you get too married to it. And I have no doubt in my mind that that post stemmed from an actual problem that Mr. Cahias ran into. Where I recall feeling, at the time I read it, that the post fell a little short.
Is in maybe framing the problem a little bit better. You know, I think from remembering like the tables, like the one was highly fragmented because of something with GUIDs and there had like the, the problem wasn’t logical fragmentation.
Like, like the type of like when you’re saying fragmentation and like the post didn’t make a good distinction with the type of fragmentation that was the problem. So like every index script that you would go and run to find fragmentation would still be looking for logical fragmentation that goes for all this stuff that goes for that stupid thing in the tiger tool box, toolkit, whatever. Unless you write a custom script that goes and looks for physical fragmentation, which requires a higher, like, or rather a higher level of detail when you are gathering fragmentation metrics on your indexes than finding logical fragmentation does.
Like when you’re finding logical fragmentation, you can use that, whatever that table valued function is with like limited. And you can find pages that are out of order. That’s not the kind of fragmentation that caused a problem here. The problem that got caused in this one was physical fragmentation.
That was there being a lot of empty space on data pages because of the fragmentation. There was also a thing where, um, I think that the, the two demo queries, uh, neither one of them had a where clause on them. They were just like two group, like it was just a group by of two like string columns from the different tables.
So, um, I don’t, I don’t know how much I’d like, like go with that is like, oh, you must defragment the indexes. Uh, like I’m sure that like that was an actual problem that, you know, Jonathan or someone at SQL, SQL skills ran into that. Jonathan blogged about, but, uh, there there’s, there’s sort of a lot of stuff in there that is very specific to the setup of that.
And not necessarily, I think you could use to take as general advice, uh, about how to like either decide if your queries were, um, having problems, like, or your query plans were changing because of this or not. The other thing that I remember about that post is that, um, the, the query that hit the table with fragmentation got a parallel execution plan and ran like three or four times as fast as the query that hit the, the, the non fragmented, uh, table. And, uh, because the one that hit the non fragmented query was considered cheaper by the optimizer and got a serial execution plan.
In my performance tuning life, the majority of the time where I have had a gripe about, uh, parallel plan choice has mostly been in the other direction. It’s mostly been, man, I really wish SQL Server would choose a parallel plan here. Why the beep is it chewing, choosing a, a serial plan here?
Uh, and me trying to figure out a, um, a supported way to, uh, get SQL Server to choose a parallel execution plan rather than a serial execution plan. Um, that, that there’s probably about a hundred to one ratio. There are times where I’m like, damn it, SQL Server.
Why, like, like, why aren’t you choosing a parallel plan? They’re very much, much smaller number of times have I been like, damn it, SQL Server. Why did you choose a parallel plan instead of a serial plan?
I want to, I want a serial plan for this. Um, of course, when, if you want a serial plan, it’s a whole lot easier to apply a max.1 hint than it is to apply a go parallel hint. Because setting, if you set max.8 to 8, that doesn’t force the query to go to max.8.
That just says you can go up to max.8 if you choose. Right? It is not min.Dop.
It is max.Dop. Uh, and the two ways that you can, uh, for, try to force a parallel plan with trace flag. 86.49 or the enable parallel plan preference, uh, option use hint. They’re both very specifically not supported by SQL Server.
And the fact they say, don’t use these in production. They’re not min. Because who knows? So, um, I’d be a little careful with that one. Uh, read, read, read, read the full post and the comments.
I don’t, not often you’ll hear someone on the internet say, oh, read the comments. But no, go ahead and read the comments. All right. That one is done.
Let’s go on to the next question. Question two of five. How can I get more out of SolarWinds DPA? People like it so much that I really don’t know what I’m missing. What is it great for?
Well, the best way to get the most out of SolarWinds DPA is to uninstall it and then call up SolarWinds and say, hey, I want to get SQL Sentry licenses instead of SolarWinds DPA licenses. Please, for the love of God, give me SQL Sentry licenses. I don’t want DPA anymore.
Uh, DPA is a trash heap of a, of a, of a monitoring tool. I can’t say enough bad things about it. Like every time I’ve tried to use it, it has been endless frustration and annoyance. Uh, avoid it at all costs.
So, there we go. And here we say, do, do, do, do. When tables get large and the default sample rate inflates estimates, do you generally recommend increasing the sample rate to lower the estimates or doing a full scan and disabling stats updates or something else? Well, boy, oh boy.
There’s, there’s, there’s a lot, there’s a lot to think about here, isn’t there? Um, if, if, I think if, if you’re able to, uh, sufficiently isolate the process, you’ve got a problem to, to, to this and you, you’ve come up with a solution.
I mean, what, what, what more do you want me to give you on this? You’ve already, you’ve figured it out already. So, but you’ve, you’ve, you’ve come to a, you’ve come, actually come to a very good point in your career. Congratulations.
Where you were able to look at a, a, a performance problem, uh, identify that the default, uh, auto, like stats update, whether it’s auto stats or like a manual stats update with the default sampling rate was causing a problem. Right. It was inflating estimates for some portion of queries that were hitting the table and it was giving you a bad execution plan.
So, you know what? Good job. Like, great. That’s fantastic.
I don’t like, I don’t have a general recommendation here because things like situations like this are so unique and, um, have so many moving parts that there’s not like a good general piece of advice here. When you’ve gotten to the point where this is, these are the types of issues that are hitting your workload because you’ve cleared up like many of the other, like simpler, more easy to identify things. Then you’ve got to, this is, this is the kind of stuff that you kind of have to figure out based on all the local factors that apply to you.
Um, I will say that I’ve been in situations where certainly I’ve, I’ve had to do full scan stats updates to, uh, prevent, um, cardinality estimation issues of the variety you’re talking about. I’ve been in situations where I’ve disabled auto stats updates because the auto stats updates that happened were not good. Um, on newer versions of SQL Server, you can do something a little bit cooler and you can actually, uh, preserve the, the stats update, uh, percentage.
So you can create statistics or update statistics and you can tell SQL Server every time you update these statistics, you have to use X percentage sampling rate up to a hundred. So you can like control that a little bit better now, but, uh, really congratulations. You’ve solved a weird, hard problem.
Um, but I don’t have general advice on this. I have very specific advice that I would figure out and give based on what’s happening with the server, but you’ve done that. There’s, there’s, I’m not, I’m not going to have anything better because you, my friend have figured out the problem.
Congratulations to you. All right, let’s answer this one. Uh, let’s see.
Ah, there we go. When viewing a long running query in SP who is active, how can I retrieve the parameter values of this query? Uh, so I’m going to assume you mean the runtime parameter values of this, because you’re saying, I thought I could get them with get plans equals one and then examining the parameter compile value in the execution plan. But unfortunately that is the pram value for the cash plan that it is using, not the current param value that is causing it to run slow.
I’m on SQL Server 2019 and have query store enabled. Uh, so you can’t do it with SP who is active unless you’re on SQL Server 2022 or one of the cloudy builds like SQL DB or managed instance. There’s a database scope configuration called force runtime parameter collection.
There’s a big note in the, in the docs for it that says, this is not for extended use. You use this for a limited time for troubleshooting. Uh, so like, don’t leave this on forever.
Cause it, that it’ll be a mess. So, uh, you could do it if you were on SQL Server 2022, but you’re on 2019. So there’s no joy for you. You would have to use extended events, uh, to capture that.
And you would have to figure out like, you know, um, like it’s, it’s a, I guess it’s sort of unclear to me if this is like a store procedure or like a, like an ORM query using SP executes equal or something. But, uh, if you use my store procedure, SP underscore human events, there is, uh, uh, a class of event you can use called where it’ll collect runtime query information. This isn’t going to tell you like for currently executing queries, like the query does have to finish for it to get logged to the extended event.
Uh, but, uh, that like extended events can capture the parameter runtime values. Uh, one of the things that my tool captures is my tool captures, sorry, uh, is the, um, the post execution show plan. So that would show you, uh, in the, in the plan XML, uh, what the parameter compile and runtime values were.
If you don’t, you can skip collecting that. And one of the, uh, and like some of the other extended events will show you the actual call with, um, uh, with the parameter values. Uh, it occurs to me while I’m answering this, that one other, one other thing you could try with SP who is active is that, that sometimes work.
It won’t always work depending on how parameterized things are. But if you’re looking at like a store procedure call where it’s like, you know, it’s calling the store procedure and you can, and like, like the application is passing, passing in literal values and not just passing in like another set of parameters. That like, like, like your, like, like the, the store procedure parameters equal.
So if it’s like, like store, like store procedure parameter equals literal value, you could do it. But if it’s like store procedure parameter equals some other parameter value from the ORM, you couldn’t do it. Uh, there’s, uh, another SP who is active parameter called get outer command.
And if you set that equal to one, you’ll get like the full thing that called the query you’re looking at. And you might be able to see the parameter values there. Uh, if it’s not there, then you have to go to extended events.
All right. Final question for this week’s episode of office hours is, wow, it’s not, not SQL Server related at all. It’s a very personal question.
Uh, if you could only have one watch from your current collection, what would it be? Well, this one, uh, this is the, this is, this was, this was my, my business is going well watch. So this was the one that I would, this is the one that I would keep.
I would, I do want to be like upfront. My watch collection is two watches. It is not an extensive collection. I do not have a wall of watches spinning on winders. Then I, and I have to like painstakingly choose which one is going to go best with my Adidas shirt for the day.
I have two. I have the, like a stainless steel watch that I wear to like the gym and the pool and the beach and other stuff where I don’t want to like mess up a gold watch. And then I have my gold watch, which I wear for everything else.
So I don’t have a lot. Uh, it’s not a huge collection. Uh, but the question here is that, I mean, is probably worth answering is also what’s a grail watch that you think a lot about, but can’t afford justify get ahold of. Uh, and then PS, if you can only answer one, please answer my serious SQL, SQL related question.
I don’t know what your SQL related question was. It doesn’t tie like users to questions in any way. So, uh, I hope I answered it, but, um, maybe it’s, maybe it’s one of the ones before, maybe it’s one of the ones after, but, uh, the watch that I would love to get if, if so.
So, it’s kind of a sad story because the person who I worked with to get my watches with was, uh, the, the watch world is weird, right? Like you have to like suck up to people and like make friends and do all sorts of like jump through all sorts of hoops to buy like, like nicer watches. Uh, unless you just go to like a gray market dealer and, um, like, like you just like, you know, spend whatever money because they don’t, they don’t care.
Right. Uh, but like the, like the, like no watch store really is, is very few watch stores are owned by the watch company. They’re all sort of like franchises. The like, so like, like a lot of places, it’ll be like a jewelry store or something that opens up boutiques for watches because, you know, that watches are part of that.
But when you want to sell the nicer watches, not secondhand or something, then you have to like, have like a branded boutique to sell them out of. So, um, the person who I buy my watches from currently, this, the, the jewelry store they work for was opening up a Patek boutique. And I was very excited because there was exactly one Patek that I wanted.
It was a Nautilus 5980, uh, full gold, the black face, it’s a gorgeous watch. Uh, and I was like, man, as soon, as soon as, as soon as that opens up, like I’m putting my name in for that. And then like three months before that, that boutique opened up, Patek discontinued making that watch.
So now you can only get it on the gray market. And now like, like the low end on the gray market, it’s like 180, 250,000 for the thing. And there’s ain’t no way that’s happened.
Like, I would have to win Powerball before I start thinking about that. You know, retail was like, like less than, like way less than half of that. Like, like, like if I, you’ve got it from the store, but you know, that, that opportunity is, has passed me by. So, um, yeah, anyway, that’s, that’s it for there.
Uh, I’m not going to talk anymore about that because it gets obnoxious pretty quickly. But anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something about, well, maybe about watches, if not about SQL Server. And, uh, I will see you for the next round of Office Hours questions. Uh, the next, the next, next five. Anyway, thank you for watching.
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.
