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.