Category: Execution Plans

Enjoy!

---

Thanks for watching!

Video Summary

In this video, I delve into the world of parameter sniffing and explore how dynamic SQL can be a powerful tool in addressing it. I start by explaining what dynamic SQL is—essentially, it’s a string that you build into a query to execute—and highlight its flexibility and usefulness in various scenarios, such as making decisions based on user input or analyzing SQL Server configurations. However, I also emphasize the potential pitfalls of using dynamic SQL without proper caution, particularly when concatenating user inputs directly into your queries, which can lead to security vulnerabilities like SQL injection. To combat these issues, I walk through how to safely use dynamic SQL and introduce `sp_executesql` with parameters, ensuring that the parameter values are passed securely and reducing the risk of malicious code execution. Throughout the video, I provide practical examples and insights into when recompile hints might not be the best solution, especially in scenarios where queries execute frequently or take a long time to compile. By the end, you’ll have a clearer understanding of how dynamic SQL can help mitigate parameter sniffing issues while maintaining security and performance.

Full Transcript

Hello and welcome to defeating parameter sniffing with Dynamic SQL. I’ll be your host, I’m Erik Darling. If you want to get in touch with me about anything related to this talk or SQL Server or, I don’t know, just ask me how my day was, you can get in touch with me via any of these things. I have a website, I have an email address, and I have a Twitter handle I’m a triple threat as far as contact methods go. If you want to download the demo database and demo scripts to play around with on your own, you can go to these bit.ly links, but keep in mind they are case sensitive. And if you type them in incorrectly, I cannot be held personally responsible for wherever your browser ends up taking you because there’s no longer my links. So mind your S’s and D’s in those links right there. Now, of course, I say all that because I’m not going to use any of these things. Because this talk is given under the Creative Commons license, meaning you can do all sorts of stuff with it, and you can present it, and you can do all that as long as you attribute and you don’t change the licensing on that. But of course, I don’t have any, I’m not cool enough or rich enough to have any lawyers. So I don’t know, I’ll just have to beat you up next time I see you if you if you do any of these things, if you do any of these things dirty. So the reason why I want to give this talk is, of course, because parameter sniffing can be a really, really tough problem to solve. But the first thing we should clear up is that parameter sniffing isn’t always a problem. It gets as a very, very bad reputation, because when it is a problem, it is nuts. But it is quite often happening on many SQL servers all across the globe. And no one is complaining, it might even be happening in outer space. I don’t know exactly how far reaching Microsoft is, they might have like stuck SQL Server on like an asteroid or something and just sent it barreling into space. I don’t know.

But I’m sure that it’s out there. Parameter sniffing away. Now, most of the time, this is a good thing because SQL Server thinks up a query plan and uses it and then keeps using it. And no one’s any the wiser. No one’s complaining performance is okay. And everything is nice and stable in your server. And isn’t that wonderful? I mean, I think it’s wonderful. I think it’s wonderful. Sort of kind of makes me like the Maytag man where I’m like, you know, sitting there waiting for a phone call. Someone’s like, hey, we’re a performance problem. And then there’s no performance problem, because everything’s fine. And then I get sad and lonely. But there are of course, lots of other ways that you can fix parameter sniffing other than using dynamic SQL. But this is a kind of a fun, interesting method that I’ve used a lot with clients and that I want to share with the world. Because deep down inside, I’m a nice person and I do care about sharing with the world.

But before we get into exactly how to fix dynamic SQL, or how to fix parameter sniffing with dynamic SQL, I need to make sure everyone understands what dynamic SQL is, and how to use it safely. What a parameter is, and how they can cause performance problems. What parameter sniffing looks like, and how you can observe it happening. And of course, what information you need to reproduce it. All good things to know.

And of course, I want to teach you these things, because I want you to understand what is constantly happening on your SQL Server, when it’s good, and when it’s bad. So the first question that we’re going to answer today is, what is dynamic SQL? This is a good question, isn’t it? What is that dynamic SQL stuff? What is that wall of red text that I struggle with? Incorrect. Syntax near. Single tick. And of course, dynamic SQL is a string that you build into a query to execute. That is the most basic definition I can think of for dynamic SQL.

And of course, dynamic SQL is a very flexible coding paradigm with all sorts of cool stuff you can do with it. You can make decisions based on user input, the state of data, or where the query is executing. You might take a table name as input. You might see if a user has permission to go do something. If you are the type of crazy person who writes scripts that analyze SQL Server, well, you might need to make some decisions about which DMBs you touch and which column names you go and select from, depending on which version and edition of SQL Server you’re on.

You might need to figure out if objects exist before you run a query or do something. Like, let’s say you have a server where you have a database per client or something, right? And you want to add an index to a table, but you already added it to some tables and others. You go through every database and figure out if that index exists before you go and create it. All sorts of good stuff you can do with dynamic SQL.

Another great use for dynamic SQL is figuring out which search arguments you want to have in your where clause based on what people are searching for. Now, the problem with dynamic SQL, or one of the problems, one of the many myriad problems with dynamic SQL is that people write it in an unsafe way. Now, the easiest, for instance, to look at is when people use just exec to execute strings willy nilly. Now, of course, this isn’t always a problem.

You might ask for user input like this, but then not actually use that user input in the string that you execute. You might have a hard-coded string that you execute based on user input, and you are not going to get SQL injected when you do this, because there is no untrustworthy user ickiness drifting into your queries to run. We just get a hard-coded string that executes.

Someone would have to go to a pretty great lengths to, like, get into your code and change that hard-coded string to something malicious and then, you know, do stuff. So I’m not saying it’s impossible, but it’s certainly difficult. Now, if you look at the execution plan, of course, we’re just going to have selected from the votes table, because that is what our user instructed us to do.

Of course, the problem with exec is not when you do something like this. The problem with exec is when you do something like this, where you concatenate a value into a string, right? So you either use concat or the plus signs or whatever else, and this is where users can do all sorts of icky, nasty, ugly stuff.

Now, I hate strings in databases. I realize why they’re there, and they might have to be there for certain things. But let’s say that we have this column, which is an Envarkar 250, where every time someone asks a question in the Stack Overflow database, what happens?

They need to have a title for that question so people know what they’re getting into when they click the link. And with 250 characters available, you sure can fit a lot of nonsense into a string. You can fit, like, this entire Union All statement.

And I know what you’re thinking. You know, little Bobby tables. Go drop all our tables. I hate little Bobby tables. I hate that cartoon. Because it really does sort of distract people from what is often the intent of SQL injection, which is not to just inconvenience a DBA somewhere by dropping a table or a database or something.

It is to steal information. And if we look at what happens with the result of this query, we get a bunch of valid search results back, which match exactly what we are looking for. But we also get back a list of tables from the Stack Overflow database.

And this is probably not what we wanted users getting back. I mean, this whole list of tables here. So that’s not good. And if you look over in the Messages tab, we will see an issue.

We will see that we did search for where a title was like anything. But then we also concatenated this whole string on. And we got results back from a system table.

Now, it’s very easy, I think, to, in terms of, like, security, maybe disallow an application user from being able to drop a table or drop a database or do something crazy. But the more and more that I work with SQL Server and different applications, the more I see applications doing kind of crazy administrative stuff. Like, they might be creating indexes.

They might be creating databases. They might be creating schema. They might be creating agent jobs, taking backups, like, creating, like, security certificates, doing all sorts of things. You need a pretty elevated privilege to go out and do. Now, if you want to spend years of your life doing every grant and revoke to make that work out without, you know, someone being able to, say, select from sys.tables, well, that’s great for you.

But I think you’re going to have a really hard time with that. The other thing that’s difficult is that a lot of applications, when they do stuff like upgrades or whatever, they might need to hit those dynamic management views to see if certain things exist or not. So you’re going to have an even tougher time because no one out there in the world is writing their applications to use very specific logins for very specific tasks.

So until that happens, we’re going to have a tough time with SQL injection and Dynamic SQL. Now, of course, you could use sp-execute SQL to buy yourself a little bit of security here. But when we use sp-execute SQL, we still need to use parameters.

So in this case, we’re still being dum-dums and concatenating all this stuff into a string. And even though we’re using sp-execute SQL, we are afforded no protection here. If I run this the exact same way that I ran the last query, we are going to get the exact same results back where we get all of the tables from sys.tables.

And we are going to get, of course, that whole union all put into the select list, right? All that stuff is still in there. We did not save the day.

In order to make Dynamic SQL as safe as can be, we need to write our Dynamic SQL so that we are not concatenating the parameter into the string. The parameter has to be part of the string. See, the title is in red here, which means it’s inside of the Dynamic SQL.

The parameter is coming from inside of the Dynamic SQL. It’s terrifying. And, of course, when we do that, we need to give sp-execute SQL a little information about the parameter that exists inside of it. And we, of course, need to set that parameter to a value.

And when I run the Dynamic SQL like this, what we get back is something totally different. We get back no search results because there is no post in the post table. There is no question in the post table that has a title of UnionAllSelectYibidaBibidaBibida.

It’s just not in there. And if we go look at the Messages tab, we no longer have that whole big UnionAll as part of our string. We just have a single parameter in here.

So SQL Server went and searched for the contents of the parameter rather than taking that parameter and concatenating it into the string and getting a whole bunch of malicious code on top of it. I believe that dirty payload or something. So that is when dynamic SQL can be unsafe and backfire.

But dynamic SQL is often the only tool that you can use to fix some performance issues. Now, if you write queries like this, you should feel bad about yourself and the way that you write queries. It should feel terrible.

It should hire me to help you fix them. But I get it. If you do this stuff and you stick a recompile hint on there, everything will go swimmingly. The problem becomes if queries execute frequently or if they take a long time to compile.

If they’re very complicated queries, they might take a very long time to compile an execution plan. And these can be times when a recompile hint works against you. The other kind of bummer about the recompile hint is that, I mean, it’s a little bit less of a bummer if you have query store turned on or if you have a monitoring tool.

But a kind of unfortunate side effect of recompile is that you do not have any sort of long forensic history in the plan cache about how many times a query executed, if there’s a lot of variation in it and stuff like that. So there is some stuff missing. There’s stuff that recompile takes away too.

There’s stuff that recompile can mess with. Now, let’s look at an example of recompile and the problem that it fixes. Now, I have two indexes on the POST table.

I have an index called 1z and an index called 3z. It would have been 1z, 2z, but 2z was on a different table. So we’ll get to that.

We’ll get to 2z in a bit. But 1z is on owner user ID, score, and creation date. Keep that in mind. Owner user ID is the leading column here. And on 3z, parent ID is the leading column, and owner user ID is the second column.

Now, what I’m going to do is run three queries using slightly different techniques to figure out nullability of parameters. We’ll do this one with an OR clause. We’ll do this one with ISNULL, and we’ll do this one with COALESSE.

Now, these don’t have recompile hints on them, so things are going to be a little bit awkward for all of these queries. And if we look at the execution plans, something kind of funny happens. This first one, even though it gets a good cardinality estimate for how many rows are going to come out of the index scan, we use the index that starts with parent ID instead of the index that starts with owner user ID.

And, of course, we have to scan that index because owner user ID is not the leading column. And even more troublesome is that we need to do, in the key lookup, we need to evaluate the predicate on creation date. So that’s very, very weird.

I don’t even have an explanation for this. SQL Server just got cracked out on me. I started drinking heavily. But the other two queries sort of have different performance issues. Even though they use the right index, if you look very, very closely, whether we use ISNULL or COALESSE, we get the same bad estimate across the board.

And notice that we still scan the nonclustered index, even though it reads with owner user ID. We still have to scan it. And if we look at the predicate that gets applied here, it is gigantic.

It is unfriendly. It is not friendly to being able to seek into the index because SQL Server on the fly has to figure out if a parameter is null or if it’s not null. And then it also has to figure out a plan that’s safe for any particular parameter being null or not.

We might not be able to seek to nulls in a not nullable column if the parameter we pass in is null. And that we could just seek the entire index. Wouldn’t that be unfortunate?

If we look at the predicates for COALESSE, they’re even more unfortunate. We get these gigantic case expressions. I mean, not that this query does significantly better or worse, but it’s just aggravating to look at. I just don’t like looking at it.

So using any of these methods, we get incorrect index usage. We get bad estimates. And we maybe get like just not maybe not like the best possible execution plan for our query. Recompile will help, of course.

Right. So if we run those same three queries with recompile hints, what’s going to happen is we’re going to get very different execution plans. And regardless of which method we choose, we are going to get accurate guesses. Right.

So now we’re able to seek into our indexes. We get a good guess. We use the correct index in this first one. Yippee-ki-yay. I’m not going to finish that thought probably. But then if we look at these two, not only do we use the correct index and seek, despite those crazy functions being in there, but look what happens. We get good guesses too.

Right. So we get spot on with those. And of course, the index seek just turns into a simple case of seeking to a literal value here and applying a predicate on this value here. So that’s pretty good.

Recompile is pretty awesome for these situations. Right. I’m totally fine. Again, not anti-recompile. I’m totally fine with you using it. But I do want you to be careful with it.

Use it judiciously. Use it when you know it’s a good idea. Use it when it’s safe. Now let’s ask ourselves a related question. Let’s ask ourselves just what a parameter is.

Good question to ask. What’s a parameter? What’s going on in there? What’s the frequency, Mr. Parameter? So there are different things in SQL Server that have parameters as part of their definition. So you can create store procedures with parameters.

That’s pretty obvious. You can create functions that accept parameters. And you can pass parameters into Dynamic SQL.

Like so. Now, parameters are not the same as declared variables. I have a whole long blog post about that at my website here.

So if you want to go look at that, you can. Sometimes it’s just easier to search Erik Darling data for local variables. And you’ll come to that post rather than try to memorize that whole URL.

But you’re smart. I’m sure you could figure that out. But anyway, if you declare a variable like this. And then use it in a query like this. Like specific.

Pretty specifically in a where clause. It is not the same as if you use a parameter. It is a much different thing. However, you can magically turn variables into parameters. By passing them to store procedures or functions.

Or passing them into Dynamic SQL. You can transmogrify them into. To magical, wonderful parameters. And have them not be variables anymore.

And this distinction is important. Because local variables do not usually get you good guesses for cardinality estimates. Right?

So what I’m going to do is declare these vote type ID variables. And set them equal to for here and to here. And I’m going to run both of these queries. And we’re going to admire the devastation.

I mean the first one is fine because it’s 733 rows. If your queries have problems counting 733 rows, you have a very different problem than parameter sniffing. You might just want to turn that server off because it’s terrible.

Anyway. Or you could call me for help too. I’m not going to complain either way. But what happens here is we get the same sort of stock guess regardless of how many rows are actually going to come out of the index seek here.

So SQL Server just uses the same cardinality estimation process. And it’s not even actually a cardinality estimate. Cardinality estimates are like math equations.

This is just a magic number guess. So that’s fun for you. Right? Cool. Anyway. In a perfect world, those would get correct-ish estimates.

Right? We would just maybe get the same behavior as using a parameter there. But that’s not what we get. And of course, if we start treating that variable like a parameter, well, this is where some trouble might start. So what I’m going to do is use dynamic SQL and pass a parameter to it for vote type ID.

The first time around, we’re going to use vote type ID 4, which only has 733 rows. And the second time around, we’re going to use vote type ID 2, which has something like 37 million rows. And if I run these two queries, we’re going to see where the problem with parameters starts to kick in.

Right? So even though we return two very different counts here, the execution plans, well, the execution plan, rather, gets reused. All right?

We get a good guess for 733 rows for vote type ID 4. But we get a very bad guess for vote type ID 2, which returns a count of 37 million rows. This query ends up taking nearly five seconds to run versus the zero seconds this one takes.

Yee. That’s no good. Of course, if we run those in reverse order, something different happens that works out mostly in our favor. All right?

Both of these finish relatively quickly. All right? But the execution plans are different now. These go parallel. All right? So we get a good guess here. And SQL Server says, this looks like it’s going to be an expensive date. I’m going to go parallel.

I’m going to have my little racing stripes on all these things. All right? So SQL Server’s like, yes, more CPUs, please. But now this query down here, which returns very few rows, also does the same thing because it reuses the guess for finding a large amount of data. Now, maybe that’s okay.

Maybe that’s not a big deal. Maybe that’s just the performance improvement that we need across the board. I’m not going to argue with that.

I’m not going to tell you it’s bad. But I am going to caution you a little bit because when queries go parallel, they use more CPU. They reserve more threads to use. And if some knucklehead admin comes along and doesn’t understand parallelism or a CX packet or weight stats, they might come look at your server and they might say, ah, I found this wonderful script on the internet.

It will tell me about the weights that I have and all their percentages. And they might run that script and might say, wow, this server has 99% CX packet weights. We should set max stop to one.

And then what happens? You have a lot of slow serial queries that could benefit from parallelism because we engaged parallelism perhaps when it was inappropriate. So that’s something to just be a little careful of.

If you’re going to tune your queries to go parallel constantly, you’re going to need to tune your admins to ignore those CX packet and CX consumer weights. CX consumer is on the newer SQL Server stuff, but it’s a bit of a digression that we don’t need to get into. Of course, there are very, very good reasons to parameterize.

We just saw a case where, you know, SQL Server using different execution plans got very different performance profiles of those queries. Right? There were some changes in the queries that might not have existed if we used just a plan based on whatever parameter we passed in.

Right? Not reusing plans. But there are very, very good reasons to parameterize. Now, if you write dynamic SQL that looks like this, it, of course, will not be parameterized.

And if you run queries that look like this for different values, SQL Server is not going to reuse execution plans. SQL Server is going to regard these queries with a great amount of distrust. And we are going to get different query plans based on what values get passed in.

Now, that all finished relatively quickly, which is by design. I’m pretty good at this stuff right now because I don’t want to sit here for a long time while queries run. That’s no fun for anybody.

But let’s look at what happened. We have all these literal values. Right? And for each one of these literal values, SQL Server is going to think up its very own special execution plan. And if you look through the list of execution plans, there are going to be three main strategies that we see.

Some queries are going to choose a key lookup based on how many rows they think are going to come out of this index seek. Some are going to skip that key lookup situation. And they’re just going to scan the clustered index and then go into a stream aggregate.

And even still, some others are going to scan the clustered index and go into a hash match aggregate. This is something that is a new sort of perk of SQL Server 2019’s batch mode on rowstore. It used to be that you could only have a stream aggregate for a scalar aggregate.

Now you can also have a hash aggregate for a scalar aggregate. So fun stuff there. Thanks, 2019, for giving me a third query plan to tell people about, I suppose. Now, if we look at the plan cache, and I highly, highly recommend if you are going to go looking at the plan cache, you use SP Blitz cache to do it.

But I also highly, highly recommend turning query plans off. Do not collect query plans for SP Blitz cache or else it will run for a very long time and you will question my sanity as a presenter and a performance tuner. But if you run this with query plans turned off, we’re going to get back to the top 10 statements that executed in here.

And if we look at the query text for them that got stored when the plans were generated, we can revalidate the fact that every single one of these literal value queries got a brand new execution plan. And we can even see, if we look over here, that all of them executed exactly once because SQL Server did not trust them to be the same query.

Bummer, right? Of course, if we parameterize like this, say between start date and, well, I’m going to use between. Aaron Bertrand might yell at me later.

I don’t care. He’s Canadian. I don’t know. Maybe he’s too polite to yell at me. Who knows? But if we turn query plans back on, because now we need them again, and we run this, we’re going to get all 11 of those queries back.

But all 11 of the queries that print out are going to look like this. We have these parameters in there rather than those literal values. All of the execution plans in this case will be the same, which is really to be expected when we reuse plans.

And if we look in SP Blitzcache, of course, turning query plans off, what are we going to get? One example of the query text with the parameters in it. Here, start date and adding 11 days to the start date.

And we will see that we got 14 executions of that query. So plan reuse, if you want it, you’re going to have to parameterize for it. So parameterization can be a very, very good technique.

You know, it’s like the opposite sort of opposite end of the spectrum, really. It’s like if you have using recompile hints everywhere, you know, you’re going to have a bunch of queries that just show one execution and not a lot going on. If you don’t parameterize queries, you’re going to see a whole bunch of the same query saying one execution and maybe using slightly different execution plans.

And then if you parameterize, you’re going to see all of the executions for a query since it’s parameterized. Now, it can be a real drawback to not parameterize because if you have a bunch of single-use statements and query plans, you might have a hard time with your plan cache because if you wanted to figure out, like, just how much this query is running, you need to find different ways to identify that query and then, like, add that up and tally things up by, like, a query hash or a query plan hash or something.

And if you have optimized or ad hoc workloads turned on, then what is that? Fix it, not fix it, and makes your job harder because all of those single-use plans just end up with a stub and you really don’t have a lot of feedback about that stub.

So that’s no good at all. So let’s recap what we know so far. We know that we can write Dynamic SQL to produce different queries situationally.

We know that we have to write it in a safe way to prevent people from stealing data or otherwise defacing our databases. We know that parameters and variables are treated much differently by the optimizer. We know that parameters encourage plan reuse.

And we know that that can be great if you have a good enough execution plan for everyone and not so great if your data has a lot of skew in it. Or if you use parameters to search for different volumes of data. So, like, rather than just, like, an equality which might get hurt by there being skew in the data, you know, you might have one query that searches for, like, everything that’s over a dollar and then another query that searches for everything that’s over, like, a million dollars.

And clearly, the one dollar query is going to return a lot of results and the over a million dollar query is not going to return as many results. So you can also see disparity with range-type queries, too. Now, the first thing you want to do if you suspect parameter sniffing is rule a couple things out.

There are a couple things that happen in SQL Server that always, always, always get confused for parameter sniffing. The first one is resource contention. If you need to figure out resource contention, you can grab my script, SP Pressure Detector.

It’s available on my website. You don’t have to memorize this whole URL. That’s in there for the sake of people who download the script and click on stuff. You can also go to my site.

There’s a little tab up top that says scripts. And if you hover your beautiful, cute little mouse over that, it’ll give you the option to which scripts you want to look at. The second thing you have to rule out is blocking.

And I think just about the best tool out there for that is SP WhoisActive. You can go to a very easy-to-memorize website to get that and troubleshoot blocking. And we’ll look at how to use SP WhoisActive to evaluate parameter sniffing.

But first, let’s talk a little bit about Query Store because Query Store is very cool. The plan cache, it’s a whole lot harder to track down parameter sniffing issues with the plan cache. Mostly because what you get back in the plan cache is just the compiled parameter value for a query.

You don’t get the runtime value. Now, the reason why that stinks is because if you want to reproduce a parameter sniffing situation, you need some things.

You need the query plan. You need the text of the query. You need the indexes available. And you need the parameters that were used to both compile and run whatever query we’re looking at. Now, you don’t get the runtime values in Query Store.

But you do get a couple interesting views where you can look at regressed queries and you can look at queries with a very high variance in resource usage. So you can look at that by a whole bunch of different things, CPU, res, writes, duration, all that stuff.

So let’s look at how SP WhoisActive can help us evaluate a parameter sniffing scenario. So the first thing I want to do is walk you through these parameters that I’m going to be using because they’re very, very important to how we troubleshoot the problem.

GetFullInnerText will tell us which query is currently executing. You’re probably pretty used to seeing that output already. GetOuterCommand will tell us if that query was called in a store procedure or some other larger batch of queries.

GetPlans will go and fetch us the execution plan. And GetAverageTime will go out and look at the plan cache and look at how long a query normally runs, how long a query runs for on average so we can compare the current runtime to the current average.

Now what we’re going to be looking for when we run SP WhoisActive like this is of course queries running for a longer than average amount of time. We’re going to look at the outer command to see if we have a store procedure that’s getting hit by parameter sniffing.

If not, then we look at the inner text and see if we just have a regular ad hoc query, perhaps something generated by Dynamic SQL that’s having problems. And then we’re also going to get the query plan and runtime values for parameters.

So we’re going to look at the execution plan and along with all the other goodies that we get, we’re also going to do something that we should always be doing when we are evaluating execution plans.

We’re going to go into the properties where all of the Pro Tools live. That’s where all the real SQL Server professionals go and look at stuff. I mean, it’s where I go look at stuff, so I assume everyone else does it. I could be wrong.

They might have much smarter things that they do. Crap. Now I feel very insecure. Might need to start drinking. So if we want to reproduce a parameter sniffing situation, we need to run the procedure first with the compile time value and then again with the runtime value.

Now what I have is a store procedure that I call take a chance. And what take a chance does is does some randomization of a number. And depending on what that number is, we are either going to set parent ID to zero or use some other modulus mapped out number in here.

Now the reason we’re doing that is because in the post table, there are around about 6 million or so rows that match a parent ID of zero. But then every other row in the table has a very, very small number of rows associated with it.

So sometimes this is going to run and use a small number of row plan. And then it’s going to hit parent ID zero and run for a lot longer. Other times we might start with parent ID zero and have some other weird juggling back and forth.

But let’s go create this. And we’re going to use, I think, a great tool for this called RML Utilities. It’s distributed by Microsoft.

If you might be easier to search for RML Utilities and go download it that way, then try to memorize this whole insane link. I still can’t memorize it and I go to it frequently. It’s strange, right?

But anyway, I mean, I say Microsoft distributes it. They haven’t done work on it in a very, very long time. So maybe I just said they used to distribute it and now it’s just sort of floating out there in the either. But we’re going to run, make extra double careful extra sure that I actually copied that string.

And we’re going to run store procedure, take a chance, 10 cycles and 100 threads per cycle. So that’s good. That’s all running.

And let’s go and run SP who is active. I should have put that in a new window when I told myself to. And run that. And what we’re going to have, right? I can probably kill this off now so that I don’t set my CPUs on fire.

What we’re going to have here is the output that I was just telling you about. Isn’t that wonderful? So the first couple columns are going to be pretty indicative of the problem of parameter sniffing.

They are currently executing for around 20 seconds. But on average, they run for around 0 seconds. So obviously that’s a problem.

If a query is normally running in 0 seconds and it’s now currently running for 20 seconds, we have an issue. And up here, we can see the text of the query that’s currently running. We can see the command that called the query.

In this case, our store procedure. And if we come way over here, we will have our execution plan. Our beautiful, wonderful, lovely execution plan where things are maybe not looking so hot. Because we have this query that’s just doing a whole lot more work than it should.

We have some pretty bad guesses up here. I’m sure 6 was a good guess when this first ran, but now 6 is not so great of a guess. And if we go look at the properties of the select operator, what we’re going to see is a parameter list.

And more importantly, what we’re going to see is the value that the query plan was compiled for and the value that the query is currently executing with. So starting with 34 and ending up with 0, what does that get us? A bad execution plan for when we need to return 6 million rows, but probably a great execution plan when we only need to return 6 rows.

So that’s fun and interesting. Thank you, spwhoisactive, and thank you, AdamMechanic, for writing that. Now, that’s cool.

That’s great. We’ve learned a few things about parameter sniffing. But now we should probably learn about how to fix parameter sniffing. That’s what we’re here for.

And we’re going to learn how we can do that with our good friend, DynamicSQL. But now we have to put all the things that we’ve learned together. So we know that we have parameters that we can use to make decisions when we build a query to execute.

Right? And we know that DynamicSQL is capable of building different strings based on that. So why don’t we use DynamicSQL to build different strings based on what we know about our data, about the parameters being passed in?

Now, first, we need to understand where skew lives, of course. Is it within equality predicates? Do we have some outliers in our data that have a lot of rows associated with them?

Do we have a problem with ranges? Do we sometimes search for a small range of data and sometimes search for a very large range of data? There’s some tug of war going on with that.

And, of course, we should also take some time to evaluate our queries before we go digging in and blaming parameter sniffing. Because we might be doing a whole lot of things that are messing up query performance that have nothing to do with the parameters that we’re using. Or rather, they are maybe equally at fault as the parameters that we’re using.

So hopefully everyone has a safe place to go and reproduce these issues. If not, well, I mean, I don’t know what to tell you. I’ve got a laptop.

So first, let’s look at some skewed data in the Stack Overflow database. Now, if we look at the vote type ID frequencies in the votes table, we’re going to see some pretty big disparities in the data volume present here. If we sort of draw some lines around small, medium, and large ranges of values, even within these ranges, there’s some pretty big disparities.

We’re going from like 37 million to 3.7 million. It’s a pretty big drop. There’s not much of a drop between 3.7 million and 2 million or even 1.2 million. But that’s a huge drop.

And then if we look at down here below, it’s a pretty big drop even after that where we go way down to a very small number of rows per group. This is skew. So when people tell you to index for selectivity, you should say, okay, well, what’s selective?

Because this doesn’t look very selective to me. This stuff maybe looks a little bit selective. And of course, this stuff maybe a little bit more selective.

And this stuff probably, well, I mean, not highly selective, but a lot more selective than we’re seeing for like this. So we have these procedures right now. Or rather, we have these indexes for our procedure right now.

So we have 1sie and 2sie. We know 1sie from earlier on the post table. And now we finally get to meet 2sie on the votes table, which is on vote type ID and creation date. Good stuff.

And we have this procedure, which is going to select some stuff from votes, join to posts, join to users. And the only parameter that we’re filtering on is vote type ID. And we’re doing that, of course, because vote type ID causes all sorts of problems.

Now, there are a whole bunch of different regressions if we run this store procedure in different orders. It’s not very interesting to do all of them. It wouldn’t like show you all of them because it just, trust me, it’s not that much fun.

It’s not that interesting. But if we, say, run it for plan 7, or rather vote type ID 7, this will finish very quickly for 7. It’s about 40 milliseconds.

Good stuff there. But if we reuse 7’s execution plan for 1, things go a little bit less well for our query. Things slow down rather significantly for our query.

Not in a way that we’re going to be happy with. Of course, we’re never happy when queries slow down. Unless we just want to, like, go take lunch and say, ah, this query’s going to take a half hour.

Let’s start it running and go start drinking. When we look at the execution plan now, this runs for around 11 seconds, 11 and a half seconds. And that’s not good.

From 40 milliseconds to 11 and a half seconds. And, of course, we’re only returning a top 200. But, again, this is a data volume issue. And starting way over here, the amount of data that we have to process is not a friendly amount for the query plan that we’ve chosen. All sorts of bad things happen because of this.

We end up taking about nine and a half seconds just getting up to this nested loops join. There’s all sorts of yucky stuff happening in this query. So that’s not good.

Now, of course, we could fix this with recompile. If we just recompile our store procedure, and if we actually hit the right button, we hit F5 instead of F4, we will recompile the store procedure. And if we were to run this, say, first for one, we would get a much different execution plan, especially if we’re on SQL Server 2019.

I’m going to get all this crazy adaptive join-y stuff. Look at this craziness happening in here. Woo-hoo!

Thanks, SQL Server. And the important thing, though, is that this query finishes in 1.8 seconds. But if we rerun this now for 7, 7 faces a little bit of a regression using that bigger plan.

Remember, this used to finish in about 40 milliseconds. Now it takes just under a second for 7 to do all the stuff that it has to do. So that’s not good, right?

Going from big to small and small to big, we’re not able to very effectively share plans from either side of that. Now, if we run this in slightly different order, right? If we look at, say, plan 7.

Actually, no, we’ll do it. We’ll look at plan 9 because plan 9 is interesting. Plan 9 uses the big plan, right? It doesn’t take quite as long as when we use vote type ID 1, but watch what happens if we use plan 7 for plan 9.

All right? That finishes in about 40 milliseconds, and now plan 9 finishes very quickly, too. All right?

So it’s interesting that if we were to use recompile here, vote type ID 9 would actually get a worse execution plan made specifically, tailored exactly for it. All right?

So recompile is not only obfuscating for the plan cache, but recompile can actually make some queries worse. Because when you see here, the SQL Server made a good guess about plan 9, and it came with an execution plan for it, but plan 9 actually did better with the plan for vote type ID 7.

So that’s a very, very interesting thing to think about now, is that we might have to test some queries with different execution plans in order to figure this stuff out. So our first option is to trick the optimizer into building a different execution plan by sticking some useless logic into our WHERE clause.

Let me show you what that means. First, we’re going to clear out the plan cache, because we’re allowed to do that. And the second thing is we’re going to turn this into the safe kind of dynamic SQL.

Yes, yes. Very safe. The next thing that we’re going to do is add some of this useless, meaningless logic to it.

So if the vote type ID is one that has to process a large volume of data, then we’re going to say, add where, or add and 1 equals select 1 to the WHERE clause. If we are processing a small amount of data, we’re going to add and 2 equals select 2 to the WHERE clause.

Now, the one thing that I do, one sort of side note that I do want to point out here, is that whenever you write dynamic SQL that’s going to be generated by a store procedure, it is common, common courtesy, to add a comment to the query that gets built in the dynamic SQL to tell people where it originated from.

So please, if you’re going to write dynamic SQL, do this, so that when some handsome, young, earnest consultant comes in and wants to start working with your queries that generate dynamic SQL, they will know where to find them in the store procedures.

They’ll know which store procedure to go and look at. I thank you for that. So now let’s run this store procedure for a couple different values. So if we run this for 7 and 1, which are two plans that we experimented with earlier, we’re going to get back two different execution plans.

For vote type ID 7, we get back the fast 40 millisecond plan. And for vote type ID 1, we get back the fast big, well, I mean, I mean, let’s say fast, but 1.8 seconds, right?

It’s faster than 11 seconds. Maybe there’s some query tuning we could do here. After all, SQL Server is telling us in this fabulous green text that we need an index. Hearts racing.

So because we told SQL Server to build different strings based on what got passed in, SQL Server came up with different execution plans for them. There’s 2 equals select 2, and there’s 1 equals select 1.

And because SQL Server built slightly different strings, it built two different execution plans. But we would actually be able to reuse execution plans within all of these. So these will all get reused when we process bigger, small amounts of data.

I understand that it’s a little bit tough sometimes to put hard-coded values like this into your query plan. Sometimes you might have to run a count query and make some runtime decision about what gets returned by that count query to figure out if you want a big plan or a little plan.

And, you know, that is a little bit trickier, but it is something you can do. For me, though, you know, I like using the votes table, and I like using that vote type ID column, and I have no problem hard-coding this.

Now, another similar option to that, now, is to use an optimize for a specific value hint. Now, I’m being very specific. We are not optimizing for unknown here, because this would probably not solve the problem that we want.

It usually introduces many other problems. When people say that they fixed parameter sniffing by using optimize for unknown or declaring a local variable, well, I mean, they’re sort of right.

They did get rid of parameter sniffing, but they usually introduced some other weird plan issues along the way, because you get that wonky estimate, that density vector estimate, when you use unknown or a local variable.

But the optimizing for a specific value will work the same as the, you know, whatever equals select whatever. You will get plan reuse for each optimize for, and the only sort of warning here is that it’s probably not as safe as the one equals select one, two equals select two method if you need to replace string values in there.

So just sort of an example of what that looks like would be to stick an option optimize for hint at the end of your query. And sort of a funny tokenized looking value here.

And then you could just, in the dynamic SQL, replace that tokenized string with a different number, or with a different optimize for value. So that’s another way that you can do it.

And I’m not going to go and run that, because just about the same thing happens. But you get what you get, and you better not cry about it. So those are both fine techniques if you’ve got a pretty manageable number of values to deal with, and their overall distribution will be stable.

Right? So what I mean by that is in the votes table, like, we might have, you know, we might add 10 years of data to it, but all the most common votes would still be the big values, and all of the least common votes would still be the small values.

Right? We’d still, like, keep, we would still maintain the overall distribution of data. Just maybe the numbers would get higher, but they would still be, like, you know, relative to whatever they started at.

Equality predicates does make this easier, of course, because we can figure out if there’s skew for an equality. If there’s, like, an outlier value or set of values, we can always figure out if there’s skew there.

What’s a little bit trickier is if we have a range of values that we’re evaluating. So what I did for this is I wrote a function, and I know you’re going to, oh, no, not a function, Eric, not a function.

Please, no function. But this is the good kind of function. This is a good which. This is the kind of function that returns a table. This is the inline table-valued function that you’ve heard so much about. And what this is going to do is take a parameter, called procid.

This will make more sense in a minute. And it’s going to go out into the plan cache, and it’s going to search the plan cache for where the object ID equals the proc ID that we pass in. And we are going to get back all of the information about parameters from that query.

Good stuff. And what we’re going to do with that information is use that to help us make decisions. Now, a sort of simple demonstration of exactly how that function works within a store procedure.

This won’t work in an ad hoc query, unfortunately. But what we can do is take this parameter value, use it to find some data here, and then use the plan cache to go and get the parameter values for our store procedure.

The only thing is that this only works the first time, or rather, this only works after the first time that you execute it. So if we run this query once, we’re going to get back nothing about parameters.

But if we run this query a second time, we will get back information here. Let me say SQL Server.

What happened? Last time we executed this, or rather, the time we compiled a plan for this, we executed it with the value 8 for param 1. So good stuff there.

Now, this is what we need it for. We need it for situations where we are looking for some kind of range. So start date and end date is a pretty common one.

And here are where clause, where again, Aaron Bertrand is probably going to yell at me for using between with dates. But again, Canadians, right? What are you going to do with them?

Now, if we run this for one day in 2013, we will get back this execution plan, which finishes relatively quickly, 425 milliseconds.

But then if we go and reuse that for looking at a year of data, what’s SQL Server going to tell us? I don’t like you.

SQL Server is going to yell at us, scream at us, kick, drag its feet, poke our eyes. I don’t know. Who knows what else? But the execution plan for this, this takes about six and a half seconds. It’s a pretty painful degradation in performance.

All right? Not a good time there. And now let’s look at how we can use that function and a similar technique that we saw before to get around parameter sniffing issue.

So what we’re going to have to do is a little bit of work in order to solve a pretty big problem. We’re going to need a couple placeholder parameters to hold a compile start date and end date.

And then we’re going to go out to the plan cache once and dump all our parameter information into a temp table. And we’re going to do that because it’s better than making two trips out to the plan cache.

All right? So we’re going to set compile start date and compile end date from the previous compilations of the query here.

And then, so kind of a funny thing that I discovered while I was writing this is that if I don’t use the parameters in some way in the store procedure, they don’t actually get cached with the store procedure.

They will get cached with the dynamic SQL, but that’s far less helpful because dynamic SQL executes in a different context from the store procedure. And that’s why we need to do stuff like put the store procedure name in here.

Otherwise, it won’t be associated with it in any way. We completely detach. It’s like a headless, it’s like a procedureless code, I guess, if you wanted to use a cool hip term. Serverless is still cool, right? Could still talk about that.

But then, depending on some stuff, we will decide whether or not we want to put a recompile hint on the end of our query. So the sort of situationally appropriate circumstances that I wanted to look at are if the date diff between the current start date and end date is greater than 3, and the date diff between the previously compiled start and end date is less than 2, then we’re going to add a recompile hint.

And then sort of the inverse of that, where if the current start date and end date is less than 2, and the previous start date and end date is greater than 3, then we’ll use a recompile hint.

Otherwise, we will just add a semicolon to the end of our query so Itzik doesn’t come and yell at us. He stares at me in my sleep sometimes.

It’s very uncomfortable. The big fella is an imposing fella. Scares the dickens out of you at night. Anyway, he’s really nice.

He doesn’t actually come stare at me at night. Not that I’d be opposed to that. Maybe not because I left the semicolon off. But anyway, let’s clear the proc cache, get rid of you, and let’s run this.

Now we’re going to execute the first version twice and then the second version. And we’re going to look at both the execution plans and the query text that gets returned here. So for the first two executions, we do something very normal.

We just execute our query. No recompile hint. All right. And then for the third one, where our range changed, we do put a recompile hint on.

Now, for the life in New York, it’s always a party. Anyway, now let’s run the inverse fact. Actually, let’s look at execution plans.

I got all thrown off by the musical accompaniment. If we look at either of the first two executions, we’re going to see that 428 millisecond plan again. And if we look at the third execution, we’ll get a different execution plan that finishes in about three and a half seconds.

So that’s about three seconds better than the ineffective plan that we would have reused here, which is good, right? Three seconds faster. That’s probably a good thing. Now let’s run that in the inverse order.

First two executions, what are they going to be? The big plan. It’s more effective for processing a large volume of data. All right.

That big one takes three and a half seconds. And then on the third iteration, we use the other plan that takes about half a second. All very, very good. Good stuff there.

And if you look over at the messages tab, we’ll see the same thing as last time where the first two queries execute without recompile hints. And then the third iteration executes with our recompile hint. So we have a very, very happy time there.

So anyway, wipe the sweat off. What we learned during this session is that dynamic SQL is a string that you can build into a query to execute.

In order to use it safely, we need to parameterize, and we need to use SP execute SQL. A parameter is something that you can pass to a procedure, a function, or to dynamic SQL.

And it is not a local variable. Very important. Parameters can cause performance problems when execution plans get reused for highly skewed amounts of data.

All right. So data volume. And parameter sniffing, when we look at it, it looks like the query got a lot slower for absolutely no apparent reason.

But remember, we do need to check and see if there are resource contention issues or blocking issues before we go and firmly say we are facing a parameter sniffing problem. If you want to easily detect parameter sniffing while it’s happening, SP who is active is very, very helpful.

Remember all the parameters that I gave you for that, though, the inner text, outer command, query plan, and average time. And then in order to reproduce parameter sniffing, the four things that we need follow the Q-tip acronym, the query plan, the text of the query, the indexes available, and the parameters used to compile and run the query.

And then if we want to fix it with dynamic SQL, what we need to do is isolate skewed values or detect incompatible ranges, and that will help us figure out if we need to generate a different string or something along the way and run that.

So again, thank you for having me. Again, I’m Erik Darling with Erik Darling Data. You can reach me at any of these methods, and you can also get the scripts and database at these links.

Thanks for joining me. If you have any questions, you can ask them wherever in the chat window is. I’m not sure. They don’t tell me anything ahead of time. But anyway, if you don’t have any questions, go start drinking.

I know that’s what I’m going to do. Thanks again. 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.