Signs You Need Dynamic SQL In Your SQL Server Queries
Thanks for watching!
Video Summary
In this video, I dive into some key signs that indicate your SQL queries might benefit from using dynamic SQL. Starting off big and moving to the specifics, I cover scenarios like when you frequently use local variables in your WHERE clauses or have conditional logic based on parameters. These situations often lead to suboptimal query plans due to how SQL Server handles cardinality estimates for local variables and if-logic during compilation. To illustrate these points, I walk through a few examples and show how adding the RECOMPILE hint can sometimes solve performance issues temporarily, but ultimately, dynamic SQL is the more reliable solution for long-term optimization. Additionally, I share some upcoming events where you can catch me in person, including Data Saturday Dallas and Pass Data Summit, as well as discuss other ways to support my channel through memberships or by simply liking, commenting, and subscribing—because every bit of engagement helps keep the content flowing!
Full Transcript
Erik Darling here with Darling Data, doing my Darling Data damnedest to keep you educated and entertained about SQL Server. Alright, that was pretty good. Nailed that. I think that’s a one taker. I should just cut the video off now. It’s not going to get better than that. In today’s video, we’re going to talk about some signs that you need Dynamic SQL. Now, there are of course signs beyond this, beyond the ones that I’m going to show you. that may become apparent in what you need to do with the query. Like, if you need to pass in like a schema, table, database, server name, something like that, for multiple different database. Let’s start, let’s start, let’s go big to small server, database, schema, table names. That’s a good use of Dynamic SQL. What we’re going to focus on in this video are some signs that for query performance, you are likely to need Dynamic SQL. Before we get into that, just a few casual reminders for the viewers out there at home. All of this content is free. You can freely watch it. If you feel strongly about supporting my channel, I have very, very low cost memberships for like four bucks a month. Eventually, I’m going to expand the offering and make it a little bit more enticing. But for now, getting a YouTube video like five days, five days out of the out of every week seems pretty good. I don’t know. We’ll see. We’ll see what comes up in the future. If you are unable to participate in a monetary exchange for whatever reason, maybe maybe you drank it all. I don’t know. I don’t know what goes on in your life.
I don’t know. Maybe you just bought a cool new car or something. Maybe the price of gas is killing you. I don’t know. But I promise you I will never cost $4 a gallon. If you’re unable to participate for whatever reason, other ways to make me feel all warm and fuzzy and fluffy inside are to like, comment and subscribe. Wonderful things that you can do for free. Make me feel good. If you are in need of SQL Server Consulting, these are things that I help people with on a daily basis. I can do other stuff too. But this is what I like the best. If you need something else, I don’t know. My rates are reasonable. We can discuss whatever you need.
If you are in need of SQL Server training, perhaps, because you just don’t get enough from these videos. I have dedicated focused performance tuning training that goes from beginner to intermediate to advanced. With the discount code spring cleaning, you can get the whole caboodle, the shebang, the enchilada. I don’t know. You can get the meat lovers for about $150. So that’s a pretty good way to spend $150 if you get $150 burning a hole in your pocket.
I will be live and in person at a couple events coming up. Friday, September 6th, I will be at Data Saturday Dallas doing a full-day pre-con. November 4th and 5th, I will be at Pass Data Summit doing double-team pre-cons with Kendra Little. So if you’re going to be in Dallas or you’re going to be in Seattle and you would like to see me, these are times you can do it.
If you’re not, I forgive you. We can still be friends, maybe. Now, let’s talk about these dynamic SQL worm signs. Now, these are all things that I end up using dynamic SQL to fix for performance problems with, client queries with, also demo queries, in case you haven’t noticed.
In case this is the first video you’ve seen on this channel, I do this in like a billion other videos. We’ll go through these things because they’re important. So one of the first signs that you probably need dynamic SQL is if you use a lot of local variables in your code and you end up using those local variables in where clauses.
I suppose you could also use them in join clauses. I don’t often see them there, but they would have a profound effect there as well. Local variables do not get treated the same as parameters or literal values.
SQL Server does not use the smart part of the histogram to give you cardinality estimates for local variables. It uses some dumb fuzzy math to figure that out. So one very good sign that you need to, if you have a query that’s slow and you’re using a local variable, the first thing to do to figure out if you’re having a problem there is put a recompile hint on it.
If it’s still slow, if you still get crappy plans from it, you have another problem. If the recompile hint fixes it, then you should invest in some dynamic SQL to get better cardinality estimates to have a properly performing query. Another very, very big sign that you might need dynamic SQL or that you probably need dynamic SQL or you need to hire me as a consultant to figure it out is if you have if logic that executes important queries based on the outcome of some parameter value.
Unfortunately, for you, not for me, for you, when SQL Server compiles query plans for some batch, whether that batch is some queries like this or in a store procedure, it compiles execution plans for all the queries, regardless of whether they will actually execute for that compilation or not. So you get cardinality estimation for that compilation, which might not make a whole lot of sense if you follow the if branch up there the first time and the if branch down there the second time.
Because the if branch down there is probably not going to get anything very good. Right. So this is another great sign that you might need dynamic SQL because if you change these raw SQL queries to dynamic SQL, they will only compile plans when they execute, not when they don’t execute.
So you can get much, much, much, much more reliable query performance if you use dynamic SQL here instead of just running whatever select. Another great sign that you need dynamic SQL is if you do stuff like this. Now, again, like I said in that first example, one way to test if this is your problem and whether dynamic SQL is going to be a good option for any of these things is to try them with the recompile hint.
The recompile hint fixes them. You can, of course, just use the recompile hint. I don’t care.
It might just be the easiest thing for you to do at the time. Stick option recompile at the end of the query. The problem solved. I don’t know. Maybe just call it there. You could do that. But not everyone can just recompile every query all the time.
Eventually, you hit some limit with that where you might be unhappy with the way SQL Server is spending its CPU time. So, again, if you test any of this stuff with a recompile hint and find a positive effect and you rewrite it as dynamic SQL, you get much better sort of long-term benefit from that. So, again, this is another good sign that you might need.
Oh, why did you do that to me? That you might need dynamic SQL is if you have this sort of conditional where clause logic in your queries where you’re saying, oh, is score greater than this parameter or is this parameter known? What can we ascertain from this?
Great use of dynamic SQL is to just build the where clause you want. Great use of dynamic SQL is to just execute the query you need when you need it. All right?
Good stuff there. Another one that I see quite often when I’m working with clients is some conditional join logic. So, you might see a bunch of joins out to a bunch of tables, but not every join will always be used. I cover this in a video about startup expression predicates where we resolve the majority of our issues with, again, say it with me.
Dynamic SQL. It’s great. Wonderful for this stuff because then you just join to the tables you need when you need them.
And you don’t have to deal with weird cardinality estimation issues if these are sometimes one and sometimes zero. And you reuse execution plans. And sometimes they’re good.
And sometimes they’re bad. That’s not exactly parameter sniffing. It’s, oh, you just being a jerk. Another thing, another sign that you might need dynamic SQL would look something like this.
It’s conditional existence check. If you’ve noticed a theme here, a lot of the times when you need dynamic SQL is when you are asking the optimizer to come up with a good execution plan based on some conditional logic at compile time. And this will bite you in the behind just about every single time.
This sort of decision making stuff does not make for a happy optimizer. I’m going to repeat something that I’ve said in a lot of videos. Again, if this is the first video that you’ve ever seen from me, this might blow your mind.
Anything that makes your job easier makes SQL Server’s job harder. So doing cute little things like this and some of the other conditional stuff and the local variable thing, you know, it’s a really nice shortcut for writing a query. But it’s just not going to perform well over time.
The bigger your data gets, the worse these problems get. The more of this stuff you have to clean up, the harder your job is. Because eventually you have to make SQL Server’s job easier so that your job will be easier. Otherwise, you’re just going to spend the rest of your life firefighting performance issues.
Well, maybe not the rest of your life. Depending on your employment contract or other local factors, you might get fired or go get a new job before you actually have to fix all this stuff. You might see things starting to get difficult and say, I’m out of here.
I need to go screw something else up from the ground floor, right? It might happen for you. But this is another case where, you know, you would want to write dynamic SQL to just tack this where exist clause on if check post is something that you want to do for the query.
If check post is not something you want to do for the query, you don’t need to put this anywhere near your query. It’s ridiculous. Who would do such a thing?
Now, we’ve talked about some of the most common signs that I see in client queries where dynamic SQL is a great way to fix a performance problem. Again, if you apply an option recompile hint to a query suffering from any sort of conditional logic or from any sort of local variable weirdness and your query suddenly speeds up, it’s probably a pretty good sign that you need to buckle down and rewrite some of that beautiful SSMS red text that builds strings dynamically and executes them based on precisely what the requirements of the query are and not just a, well, any old thing.
We don’t really know. We’ve got to be flexible. We’ve got to make sure everyone can do everything all at once.
Well, that’s not really good for your query plans and that’s not really good for your SQL Server performance. If you’re doing any of this stuff currently in your queries, test with the option recompile hint. If that helps, rewrite that as nice parameterized dynamic SQL.
If you don’t know how to do that, you can hire me to do it. I love dynamic SQL. It’s one of my favorite things in the world. Someday I will have a yacht named dynamic SQL.
It would be cool if I could get a private jet named dynamic SQL, but I sort of realized the limitations there. Maybe I’ll have a private jet named entity framework. That seems more feasible to me.
So I’ll have a yacht named dynamic SQL and a private jet named entity framework. And I still won’t be able to retire. Someone’s got to keep putting gas on those things, right?
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you too will learn to acknowledge the greatness, the grandiosity, the grandeur of dynamic SQL when it’s properly applied, constructed, and used in SQL Server. I’ve had great luck with using it over the course of my career.
One thing that people always ask about is, well, if I use dynamic SQL, will SQL Server reuse the query plans? Yes, when it should. Using sp-execute SQL for dynamic SQL gets you just about equivalent plan reuse as if you write a store procedure.
Store procedures, reuse execution plans. So does dynamic SQL when used with sp-execute SQL. If you just use exec some SQL thing, maybe, probably not.
But the other one, but sp-execute SQL, mwah! K-bet. Lots of plan reuse there. Because sp-execute SQL is a store procedure.
It’s executing a query, parameters. All right. Cool.
I’m exhausted. Okay. But I’m having a good time. So I just keep going. I just keep trucking. All right.
I think that’s enough for this one. I’m going to go think about what I want to record next. It might be something about is null and coalesce, and it might be something about in-memory table variables, and how they’re not that great either.
So who knows what’s going to happen? It’s going to be wild. It’s going to be crazy. Hopefully, hopefully no one spills lube on the floor again. 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.