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.