When we write our queries to the database, SQL Server has to go and figure out the best way to go and bring back the data you asked for. A query plan is created to help SQL Server remember how to get the data for this query. It takes time to make these queries, so the database wants to limit the number of times it has to create the plans so it will try to reuse the plan as much as possible.

Our topic for today’s episode is query plan reuse and the pros and cons with this concept. We will also touch on the concept of parameter sniffing–a technique SQL Server uses to try and figure out the best values to use for the execution plan with the hopes the plan will help the most queries. Special thanks to James Youkhanis for the suggestion.

 Episode Quote

“The concept behind this is it’s there to make things a little bit faster by reusing cache plans.”

“Parameter sniffing is a good thing because without it SQL Server wouldn’t be able to optimize your plan for any kind of parameter. But occasionally it goes wrong.”

“I think it kind of comes down again to kind of knowing your system and understanding the problem”

“Optimized for adhoc workloads is one of those parameters that we most of the time will recommend people turn on”

Listen to Learn

4:53  SQL Server in the News
5:00  Ola Hallengren scripts now on GitHub
6:45 What is plan cache?
7:48 Description of T-SQL and its execution plan
10:15  Scenario in regards to statistics and indexes, and data types
11:30  One-time use query plan cache
12:22  SQL Server and the importance of memory
12:50  A specific problem with one-time use query
12:55 Parameterization
17:30  Parameter sniffing
20:25  Stored procedure and plan cache, parameter sniffing issues
23:55  Options to solve parameter sniffing issues, recompiling
27:28  Controlling plan cache size
28:10  Plan cache and flash array
29:27  Idea of ad-hoc workloads
32:30  Needs parameter reports and examples
38:15  One-time use query reports
38:50  Instance level memory report
39:40  More about hints, recompiling and plan guides

Carlos: So companeros, welcome to Episode 103. Thanks for tuning again to another great episode.

Steve: Yeah, Episode 103. Wow, so this episode is on cache plans and plan reuse which is one of my favorite topics in SQL Server. I know I’ve had a lot of lively debates over the years around this.

Carlos: Yeah, that’s right. I think it’s one of those things where from a performance perspective so indexing and some of the other objects and then you got to figure out to the internals of how SQL Server works. Yeah, it can a bit confusing a little.

Steve: And this topic came to us from James and he suggested that we talk about plan cache and plan reuse. I want to thank him for that suggestion.

Carlos: Yeah, we apologize. He was suggesting, gosh it’s been, I’m embarrassed to say how long have it’s been but it was during a time when we had a slew of interviews kind of lined up. It was kind of push to the back there but we’re glad to finally circle back around to it. We have a couple of shout outs this episode.

Steve: Yes, so one shout out came from sqlgambo on Twitter and this was in regard to a post that I had tweeted about Database Health Monitor, and he came back and said why I am building these tools of mine. I guess he hadn’t seen Database Health Monitor and then he and I actually chatted through private messages on Twitter for quite a bit and learn some stuff about what he is working on. And learn that he really likes with Database Health Monitor, so yeah, good to connect there.

Carlos: Yeah, very cool and it’s kind of interesting. We were talking before we started recording here about the history of database health monitor and how it kind of started from SQL Server reporting services reports. I was there, I was building my own home grown and came across Database Health Monitor. Lots of other tools out there, that’s one of the very nice things about the community and making those things available.

Steve: Yup, definitely.

Carlos: So Companero Conference coming up again on October – October 4th and 15th. Interestingly enough I was just at Nashville for a health conference this week trying to make some connections with some hospitals, and one of the things that stood up to me was the importance of unstructured time. So in that conference they were trying to because it had a mix of panels and speakers, and then vendor type sessions which weren’t horrible but they are still vendor sessions. So they were just trying to get through them. They kind of plow through it and they had this conference in a day and a half, a little bit more. I didn’t felt we had enough time just to talk, like let’s understand what it is. Are you being affected by this problem to be able to keep the conversation going? Anyways, so I thought give me some perspective on this idea of creating some structured content from over a session or from a panel. But they aren’t giving the unstructured time for people just to be able to talk, connect, right? Where are there similarities and commonalities? What I might want to pick up with this person after the conference. What conversations do I want to maybe talk about tomorrow or things like that? And so I found that again kind of single track conference, other way we’re going to do ours that I would have enjoyed a little bit more unstructured time with some folks.

Steve: Interesting. Yeah, I think that that unstructured time can be more valuable relationship building time that you have at a conference. And I think going to a conference and just getting the material, I mean, really you could do a lot of that on YouTube. But going there and making those contacts with people and being able to have time and talk about what it is you’re doing or when we do a session on performance tuning to have time to talk afterwards about issues you’ve ran into or problems you’re trying to work on can be incredibly valuable.

Carlos: I agree. I am looking forward to our conference and putting that on. We hope Companeros will join us October 4th and 5th in Norfolk, Virginia. You could take a look at companeroconfernce.com and we’ll make sure we have the link on the show notes page again as well.

Steve: And now on to SQL Server in the News.

Carlos: This has been up for a couple of weeks now but thought it was interesting. Many of you may be using Ola Hallengren scripts. He has decided to put them out on GitHub, kind of made them available. Obviously they are free to download but the now the difference being that you can actually suggest changes to his code and I know that a couple of people have done that already so it would be interesting to see what happens with those scripts as a result. Kind of going back to our community episode we talk a little bit about this, so it would be interesting to see what happens.

Steve: You know, we didn’t really talk much about Ola scripts on the community episode because it wasn’t really a community contribution project. It was sort of something he has built over time but now that it’s out on GitHub maybe it will become something more amazing than it is already based on community contribution.

Carlos: I believe it came out on GitHub after our episode so I wonder if we are not influencing people out there, Steve.

Steve: Yeah, who knows.

Carlos: Can we take credit for that, you know, SQL community you can thank us for Ola putting his stuff on GitHub.

Steve: Whether we were the cause or not.

Carlos: Yeah, sorry, butterfly effect or something. Ok, so today’s episode can be found at sqldatapartners.com/plancache.

Steve: Or at sqldatapartners.com/103 for our episode number.

Carlos: So again, ultimately what we are talking about is execution plans, plan cache, and plan reuse. So first, I guess let me back up and kind of from the 10,000 level view, what is it that we talk about when we are talking about plan cache.

Steve: Well from the high level it’s basically the plan cache is a piece of memory or junk of memory in SQL Server that keeps track of every query plan as it gets compiled. And if your queries are written in a way that it can reuse the plan it can then grab and reuse one of those existing plans rather than having to recompile that every time. And the concept behind this is it’s there to make things a little bit faster by reusing cache plans.

Carlos: Right, so allow me to go back up just a slight bit higher and take the idea that we use T-SQL to tell the database what it is that we want out of the database. So T-SQL has been described as the language of what is it that we want but it doesn’t tell the database how to go get it. So the database has to decide how best to do that and when the query comes in like as you mentioned, it’s going through a process to decide, “Ok, well this is what you want. How do I need to go and get that data?” And so as a result it’s going through a look and say, “Ok, well I think this is the best way and I want to create a plan.” I want to create an execution plan or a way to go and get this so that way every time you want it I will know how to go and get it. There could be many different ways almost like kind of going from Point A to Point B, lots of different ways to get there. And it has to figure out which way is the best or at least which is its going to use on a regular basis.

Steve: Right, and it can be a very expensive step in the process that compiling and figuring out how it’s going to go about getting the data.

Carlos: Well, it’s interesting. So they mentioned expensive and I guess kind of this but there is a cap, a couple of milliseconds, which all of a sudden I can’t remember how many it is, that it will what that is. And I thought, well gosh milliseconds isn’t sound all that long of a time but I think it’s all a matter of how busy your server is and then how many executions are coming to SQL Server as well.

Steve: Yup. And I guess to put it in perspective with milliseconds there. I mean last week I was working on a query with a client where we were trying to increase or decrease the run time from about 800 millisecond down to about 150 milliseconds. And milliseconds could make a big difference there, and this was a query that was being run continuously like through website traffic, web service
traffic and all kinds of things. It was being hit quite often so a difference between 150 milliseconds and 800 milliseconds meant a lot to the performance of the system.

Carlos: Sure, that’s one of the great point, right, is the frequency not just of all of the queries but of that specific query, because if it had to recompile every single time and you’re adding milliseconds on there then you’re just kind of piling everything back up and it’s going to go and redo a lot of that work every single time.

Steve: Yup, and the work that it’s doing there is it’s going out and it’s looking at statistics and what indexes are available, and what data are you looking for, and what are you filtering on. And it puts all those things together to figure out what is the best way that a SQL Server engine can you go and get your result set for you. And if it wasn’t so smart it will just say, I want to go out and I’m going to do a full table scan on every table you’re looking at and give you your results brought back together on your JOINS. But that just wouldn’t cut it in today’s world. Maybe databases 20, 30 years ago might have done that but today there is so much going on with indexes and big tables and different ways to get the data. There is a lot of options there to look at. I mean if you got a table with 30 indexes on it versus a table with 2 indexes there might be more work that has to happen there when it’s figuring out what is the best plan to use.

Carlos: Sure, and then we kind of get into data types, right? That plays a role as well. There a lot of things that it has to look at and consider.

Steve: Yup, so what happens after that plan gets compiled is it gets put into this memory location called the plan cache and those plans are kept around with the hope, SQL Server hoping, that they will be reused so it doesn’t have to do that work again but sometimes they never get reused. What you end up with is you end up with what could be called the one-time use query plan cache where if things are changing in the query and they are not identical you end up with all this one time use queries in the plan cache that can kind of clog things up and sometimes push other things out of the plan cache that would be useful to be reuse.

Carlos: Like you mentioned, going back to that idea, so the plan cache is a space of memory where your data has to store as well, SQL Server read everything from memory, right? So has to be able to read that stuff there and so if you’re not using it that mean that there are, memory is kind of a vital thing and a finite thing as well, that you are using those resources in a way that’s not helping your SQL Server go faster.

Steve: Right, so think of it this way. Let’s say we had a table called ‘podcast’. And in there we have the list of all of the podcasts that we know about and in there is a column called ‘host’. And you just said, SELECT * from podcasts WHERE host = ‘Carlos’ If you run that query it’s going to create, I mean by the first time you run it it’s going to create a cache plan and then if I come along and run that same query a moment or two later or a few minutes later and I run the exact same query SELECT * from podcasts WHERE host = ‘Carlos’ It’s going to not have to recompile that. It’s just going to use that already compiled plan and it’s going to save some time there. But then if we change it up and Carlos runs the query to say SELECT * from podcasts WHERE host = ‘Carlos’ and I say SELECT * from podcast WHERE host = ‘Steve’ that’s going to be two different plans because the queries are different. By different if you just look at the text of that entire query and if it’s not exactly identical meaning turns in the same place, space and the layout is exactly the same and the difference is we’ve changed the WHERE filter to say Carlos or Steve, that’s shows up two different plans in the cache. Now imagine if this was like, SELECT * from customers WHERE customer_name = ‘Fred’. Or customer_name = ‘Mary’, or customer_name = any of the 10 million customers you have in your website. You could end up with many many of these one-time use queries that may not or maybe they get used once or twice or three times while that customer is there but they end up chewing a whole lot of memory, and the way you get around that is you use parameterization. And the way that works is instead of saying, SELECT * from podcasts WHERE host = ‘Carlos’ you say SELECT * from podcast WHERE host =, a parameter. And that parameter is there and when that query gets run, whether you are running it through code you’ve written, website code, or reporting services or wherever. Instead of passing through the text string of Carlos or Steve as the parameter it passes through a separate parameter that says compile this without knowing what the parameter is necessarily. Just compile it and then we’ll fix up that parameter after the plan has been compiled.

Carlos: Then one of the ways that it goes and figures out but maybe parameter it should use as a default, is it well look at those statistics to say, “Ok well, I see Carlos is in here 100 times, Steve is in here 10 times.” Like, “Huh, Ok, I see there is a tilt towards Carlos so I’m going to assume that more people are going to query Carlos than Steve.” So I’m going to make some decisions potentially based on that distribution. This can be a good thing or this can be a bad thing.

Steve: Yup. An example of that, think of it as if you were looking up for customers by zip code. And now imagine that you are in a small town running a business and maybe that small town has one zip code, maybe two zip codes, and most of your customers are local so most of the time when you’re looking up customers you’re looking them up based off of those one or two local zip codes. Well, then you get a customer that is somewhere on the other side of the country in a different zip code. It might be that 99% of all of your customer are in one or two zip codes but then you have this 1% that are in other zip codes. What can happen with that is that the plan can assume that most of the time that’s being run with something that requires more work because it has to scan more customers based off that zip code distribution but then when you run it for that one customer without one zip code that doesn’t match your local zip code. It could get to that customer with less work but it doesn’t because it goes through the normal path of that pre-compiled plan to find the information there. There is a term for that and it’s called parameter sniffing. Where when a plan gets compiled the first time it gets compiled it looks what are the parameters that are being passed in and it figures out a good plan that’s going to work for those parameters.

Carlos: Based on everything that I know as of the data that’s all in these tables what is the highest probability of what’s going to come in and let me make it easiest for kind of the 80-20 rule if you will. That’s where I’m going to go and try to get that.

Steve: Yup, so then let’s say you have that customer example by zip code and you are looking it up by zip code and the very first time or when the plan gets compiled you use one of these odd zip codes that’s not a very common zip code in your system. It may then look at it and say, “Ok, there is only a very small percentage of the rows in our result set that use that zip code so what we’re going to do is we’re going to do an index seek right to that zip code location.”

Carlos: An anomaly in that sense. Like, “Oh, all the data must become an anomaly.”

Steve: Yes, but then if it was the first time you compile that plan and it was one of the common zip codes it may look at that in an example of instead doing a seek which we go right much quicker to where you’re going for the smaller set of data. It may say, well a majority of the rows in this table are in this zip code so instead of doing an index seek we may do an index scan or even a table scan because that’s going to be the most optimal way to bring back everything that you are looking for based off of that initial parameters. So what you end up with is that when that plan gets compiled and that’s compiled the first time you run it or if it gets pushed out of memory or somebody flags it to be recompiled that the next time it is run it gets recompiled. But if that time that it gets recompiled if you’ve got good parameters that represent a regular data set in your system you get really good performance out of your query. But then if it happens to get recompiled with one of those unusual parameters that causes your plan to do something different then what is optimal most of the time you could end up with a really inefficient plan, that ends up bogging down the system or really hurting the overall performance.

Carlos: Sure, and what gets a little bit weird and you may think I guess why would this affect me, this should be a problem. If you’ve ever seen scenarios where one time query runs pretty fast and then all of a sudden it doesn’t and then maybe later in the afternoon it runs again fast. That’s a common symptom if you will.

Steve: Yup. And I guess with that the common scenario that I see as a freelance consultant as Carlos and I are. I see that you will be working with a client and they’ll come and they’ll say, “Things have really slowed down. We haven’t changed anything on the database and something has really slowed down on this one specific process or job.” Or whatever it is that’s calling into to use this query. And then you’ll go and look at it and you’ll find here is the stored procedure that’s the root cause of the problem. You’ll copy it out. You’ll copy and paste it and put it into your Management Studio and change it around so you can run it, maybe not as a stored procedure but in line. And you run it and everything runs great because with the changes you’ve made to go run that in line it gets a different plan. And then you look and you think when you look at weight statistics and something like and you can see that like at noon today that’s when things just tipped and it went bad; and prior to that everything was running great. So what could have happen to cause that and what often happens is that you have a stored procedure that gets pushed out of the plan cache for some reason. The next time it is ran it is run with this unusual parameters sets which causes it to get a bad plan and then every other call into that starts using that bad plan. Or it was good plan for that one parameter but it’s a plan for the other parameters. And I’ve seen that take a stored procedure that normally runs in under a second and cause it to run like 7-8 minutes when it’s something more complex. Then everyone they hear that and they grip and say, “Oh, the database is horrible and everything broken.” And this is all really because of the parameters sniffing and parameter sniffing is a good thing because without it SQL Server wouldn’t be able to optimize your plan for any kind of parameter. But occasionally it goes wrong and you end up with the wrong plan based off of plan reuse on that stored procedure.

Carlos: And you may be thinking how big of a problem could this be. Now, I won’t say it’s the only reason but the new feature of the query store feature is basically trying to solve this very problem. And so it’s big enough of a problem that Microsoft has built a tool to help you combat it. And I think it’s one of those things that I think a lot of, at least I can remember going back and being frustrated and I think a lot of times it probably had to do with parameter sniffing.

Steve: Yup, and most of the time I ran into parameter sniffing issues it starts with customer service department who’s working with customers getting lots and lots of complaints that something has gone wrong or the system is down and not responsive. And then it leads to then eventually jumping into the database and finding, ok here is the problem, and then there is a lot of different things you can do to help mitigate the issue with that point. I mean one of them is people will put the option recompile on a query or the recompile hints on a stored procedure and that can cause more problems or different problems.

Carlos: Well, let me explain that. Let’s just go through that just for a little bit. So now I guess what we’re saying is to mitigate that you can tell as it goes through the process of actually creating the plan you can get, there is some we call hints, because a stronger word is plan guide but that is slight different. We’ll touch that in a minute but you can tell SQL Server, “Hey, SQL Server you should do it my way.” Or in the example of the recompile you’re saying, “Hey, every time you run this I want you to recompile.” I don’t want you to use the plan you have available to you. I want you to throw that plan away every time and figure out how to do it again. And so that’s an option that you have but there are risks if you will associated with that and you want to be careful about how you go about or when you go about implementing that option.

Steve: Yup, and one of the knee jerk reactions that I often see when I explain to people that, “Well, here is what happened. You got a parameter sniffing issue. The query got a bad plan, or the stored procedure got a bad plan. Here is what we did to fix it. We force it to recompile or we change the query around.” They often think, “Well, can we just force all of our stored procedures to recompile so we’ll never have this issue again.”

Carlos: Yeah, exactly. How do I eliminate the problem from never happening again.

Steve: Yup. And then the answer to that is, well you could but however by doing that you would eliminate any of the benefit that you get from the plan cache and be able to reuse plans. And depending on the system and the performance there, that could make things far worst over time depending on the load. So there are things you can do. You can go in and perhaps change the queries or change how things are working in the stored procedure or maybe have a stored procedure that calls another stored procedure that does something different. I mean there is a lot of different, there are probably 20 different ways you could go to figure out how to do this right. I guess we don’t have time to go through all of those now but sort of the knee jerk reaction is just make it so that everyone recompiles and that’s not a good thing to do.

Carlos: Well at least by default. I mean that may be an option that you pursue but don’t make that your first choice.

Steve: Oh yeah. And I’ve certainly used that for a single stored procedure or a couple of problematic stored procedures. Used the recompile option on them and every time they are run they get recompiled. And it’s just because of how they’re written and they are in positions where they could be rewritten but the overhead of recompiling those is cheaper than the time it would take to go rewrite those.

Carlos: Right, but I think it kind of comes down again to kind of knowing your system and understanding what the, so you understand the problem, “I believe I have a parameter sniffing issue.” What do I know about this either query, procedure, view, whatever it is and do I know any history about it? Can I go find some of that out to then understand what makes the most sense?

Steve: Yup. And we could probably go for hours on parameter sniffing but let’s shift back a little bit to sort of the generic plan cache topic now. So one of the things that often comes up with the plan cache is people say, “Well, how do I control the size of the plan cache?” And, you can’t. It’s something that’s dynamically sized by the SQL Server internally and it depends a lot on the server load and what memories are available. One way to control it is just put more memory one the server but that’s not a really good answer.

Carlos: Well, another feature that they added, I’m forgetting the edition, I want to say was it 2014? It seems like it was older than 2016 but maybe I’m remembering wrong. And that is when they added the ability to add flash, so if you have flash on the SQL Server you could actually expand the plan cache to use that flash array to give you more space when you have an issue like this. So to kind of indicate the gravity of problem Microsoft is putting solutions out there around the plan cache and its size. Even if they are not giving you the controls of like in Oracle to say this is what it should be.

Steve: Right. So the best way I’ve found to deal with the plan cache if you’ve got stuff that is getting pushed out or a lot on one-time use queries in there and things like that, it is to better understand what’s in there and then it might be and I’ve worked on systems that I have tens of thousands of different queries run against them and then it turns out there is a dozen queries that are really the big offenders in hogging up the plan cache was one-time use queries. And you can go in and work and optimized those dozen queries to use parameters or do whatever needs to be done there. And oftentimes with a small amount of work you can have a really big impact on the plan cache there.

Carlos: This is where the setting, is that the right word, for optimized for adhoc workloads comes in. So this idea of this adhoc is that, “Hey, I have a one-time use query. I have a whole bunch of those. What I’m going to do is instead capturing or keeping the full blown execution plan is I’m going to just keep a little stub. The first time it gets run and then when it get run the second time then I’ll keep the whole thing and make use of the being able to run it more frequently.

Steve: Yup and that optimized for adhoc workloads is one of those parameters that we most of the time will recommend people turn on.

Carlos: Yeah. I know we’ve talked about it before. I only ever heard of one person complaining about it which we actually talked about, like it was in Episode 99. Mindy brought it up, you were in the panel in Baltimore. I remember Wayne Sheffield talking that he’d seen some CPU spike but I think. Again, obviously you have to test in your environment, right? But it seems like it’s almost one of those standard features that you can enable now.

Steve: Yup, and that’s why I said we almost always recommend it. Not always but almost always. So then I guess, I mean as far as understanding what’s going on with your plan cache, and I know we talked about Database Health Monitor a little bit earlier but in the very beginning when I first created Database Health Monitor some of the very first reports that I built were around understanding the plan cache because I was working on an environment where it wasn’t well understood and I needed a way to built a show what’s going on with the plan cache.

Carlos: Sure. And I think at least in my first interactions with that are giving you the top queries because it will keep some statistics about the plans and their executions. You can go and start to interrogate that a little bit. Generally, from a performance perspective that was the first time I remember kind of going and taking a look. It’s like, well what are the top plans by CPU, or by memory or just how long it ran, something like that.

Steve: Yup. And there are four reports in there that I usually look at that are right around understanding the plan cache, and one of them is just called the plan cache report and it’s a pre database report. And what it will do is it will show you the first 500 largest plans in the plan cache and it will show you how big they are. So you can go and see, “Oh wow, we’ve got 12 plans that are very similar that are taking up 30K each. And you do the Math and you add it all up and realized, wow some of this add up real quick to taking up a lot of your cache. Another one that’s really handy is the needs parameters reports. And what it does it goes through it and analyzes the queries that are in the plan cache and it looks for things that could be parameterized and then it groups all of those together. So if you had 1,000 queries let’s say customer name in them that was hard coded in the query, it will go through and say that by fixing this one query it would reduce your plan cache from a thousand instances of that same or similar plan to be one reusable instance.

Carlos: Now let me ask you a question on that because I guess this is where I’m drawing a blank here, it was a gap, right because I thought that even. So we talked a little bit about stored procedures versus adhocs so views or inline queries. But I thought even though I was in line query and I’ve been written with like framework or something. If the SQL Server gets that query it’s still going to try parameterized it. Even those on store procedure I guess is what I’m saying. Obviously, the very reasons why I would have that but in that scenario what do you then go about doing to solve for them?

Steve: Well, take the example earlier where we’re querying the podcast tables, SELECT * from podcasts WHERE host = ‘Carlos’ or host = ‘Steve’. If you are running that code and it’s actually running that exact query hard coded from whatever application it is. But that’s what’s ending up in the plan. It’s hard coded with Carlos or Steve in there. That is taking up just for those two queries, two or sometimes four, plan cache entries. And let me just clarify that when I say it’s two, that’s the obvious, one for the query that is looking for Carlos one for the ones looking for Steve. But sometimes you will get a parallel and a non parallel version of that query in there so sometimes a single query will have two different plans in the cache. But to go back to what you’re looking for there. If the application is passing through hard coded strings like that each one that it’s passing through will get a different plan, so then that’s really all the needs parameters report does is it goes and finds the items in the plan cache that are very similar with everything besides the parameters.

Carlos: So I guess let me ask the question this way then.

Steve: I don’t think I answered that, did I?

Carlos: I think you did answer it. I think I asked the question wrong.

Steve: Ok.

Carlos: So I am misunderstanding and SQL Server will not in every instance try to parameterize your adhoc queries.

Steve: Yes, that is correct. And the way to tell that is to look at what’s in the plan cache. And if what’s in the plan cache contains those hard coded values for a name for instance, then they haven’t been parameterized. Or the other way to look at it is if you run it with two different parameter or two different names in that value do you get two copies of that in your plan cache? And if it is then it is not parameterizing it.

Carlos: Ok.

Steve: Now with that, I guess the thing I like to do is find the ones that are the most commonly ones that need to be parameterized. This only works if you have access to the code because if you’re running an off the shelf application where you can’t change any of the code you might not be able to do this. But if you are a development organization and you’re building an application, if you can go in and find that these are the queries that end up using the most out of the plan cache. They have big plans and they are called thousands of times and then you can figure out which one needs parameterization and go parameterized a couple of those you can often times have a big impact on the amount of those one-time or low use plans that are in the cache.

Carlos: Again, to connect the dots here. There is where we’re actually going to the code and instead of using that hard coding you are going to use like sp_execute or changing the way that you are making that call to the database.

Steve: Right, and I mean for instance if you are working in pretty much any programming language that I’ve ever seen or work with that allows parameterization, you usually pass through some kind of a parameter value like a variable name in place of what were you would be filtering on that hard coded string, and then in the code you say, here is the query patch it up with these parameters and then execute it.

Carlos: So the developers have to make one more step in putting that dynamic, I’m assuming it’s a dynamic query linking it all together. Before they send it to the database they need to make one more step to help us out of it.

Steve: Then we can have a whole other conversation for probably an hour for parameterization and the impacts that it has on preventing SQL injection or helping prevent SQL injection. I mean there is another benefits to parameterization just besides the performance. Maybe we’ll save that for another time. So another report, there are a couple of others that I look at is the one-time use query report. Did I already mentioned that one?

Carlos: I think we may have touched, we talked about it but this is just to show us how many queries have just been executed in one time.

Steve: Yup, and that’s a handy way to see how many of these are there. And if you look at your database and there’s 2 or 3 or a couple of dozen, you probably don’t have to worry about it. But if you find out that there are thousands of them there then maybe it’s something you need to look into.

Carlos: And then that’s where that optimized adhoc workload comes in.

Steve: And then the other report in Database Health Monitor that I really like to use and understand is the instance level memory report where you can go in and see how much memory is being used by each database, but it also shows you how much memory is being used by the plan cache. And it’s interesting, on some server the plan cache might be using more memory than some of your databases are. I mean it depends on the size of your database and performance and things, and the load, but it’s just good to understand how big it is. And I guess I said earlier you can’t really control the size of it but you can control the size of it by reducing the amount of one time used queries either through optimized adhoc workloads or by adding parameters.

Carlos: Influencing what’s in there.

Steve: Yup, yup.

Carlos: So another thing we will touch on, we talked about it earlier, as we talk about the ability to manipulate a little of this, right? So through using hints, recompiling, the other one is plan guides but we also want to say. Again, take it for what it’s worth but in my years as a database administrator I’ve only seen hints and guides used in two instances. And so I think sometimes particularly on the forms we kind of see that get rushed, again this idea of, “I want to make sure it never happens again so I’m going to put this very extreme process in place.” When maybe testing it out a little bit would be a better approach.

Steve: Yup. And I think in my experience I’ve seen that hints are used quite often but I’ve see that plan guides are used very infrequently. I just want to take a second to jump back to a previous podcasts where I talked about one of my things I would change around SQL Server I think was the term hints on plan guides. And that hints aren’t really hints, they are really commands. And plan guides aren’t really guides, they are commands that’s say, “You will perform this way.”

Carlos: Yeah, exactly.

Steve: I look at hints and plan guides oftentimes the first thing I’ll do on performance tuning is pull the hint out and see how it performs without it, and oftentimes things improve. But I think that they are kind of emergency band-aid type response that when you’re out of other options it maybe something to consider as a short term solution.

Carlos: Sure, and I don’t mean to say they shouldn’t be use. When they are appropriate they are appropriate. But I think, again kind of the whole rebooting the server, right? Like, “Oh it’s slow, let’s reboot it.” Stepping away from the knee jerk reaction. There are going to be instances where they are called for and where you’re going to be the hero for implementing them.

Steve: Yup, and I think plan guides are amazing, they are extremely awesome but they are extremely dangerous and I don’t want anyone to think of this podcast what we are talking about here is recommendation to say go try out plan guides for your performance tuning. If you’re hearing that you should translate to go learn all you can about plan guides before you ever try it because there are some negatives in there. If you apply a plan guide it may cause trouble when you try and recompile a stored procedure that’s being utilized or the plan guide is associated with.

Carlos: And again kind of going back to query store, that’s the flip side or other angle is going to help you understand is, “Hey, this query is running with a plan guide or a hint, just so you know.” It has been difficult. It’s something you really have to spend a time with so again, the knuckle dragging Neanderthal that I am to kind of understand, ok what’s going on here? How are these plans changing over time? So it does take some practice and just hanging in there. So you will get a little bit frustrated but hang in there and eventually it will come.

Steve: Yup. So I think just kind of a quick recap overall so basically the SQL Server plan cache is where all the compiled query plans are stored. They get compiled at the first time they are used or when someone indicates that it needs to be recompiled. And it’s kind of sized dynamically by SQL Server. You don’t really have a lot of control over that and there are some things you can do to adjust that like optimized for adhoc workloads and parameterization. And I think hints and plan guides can oftentimes cause trouble but they are kind of a last ditch attempt to try and fix queries.

Carlos: So again, ultimately we would like your feedback on this and one of the areas and kind of talking, reviewing this topic again is we would like to try to make Database Health Monitor better so we’re listening to your feedback. We’d love for you to take a peek at those reports. We’ll make sure that we put them on the show notes page and list them there. We’d like to get some feedback, so as you use them what do you like about them? What else do you want to see? How do you use them? Is there some other tool that you’re using to look at the plan cache. We would be interested in hearing from you about that and you can use any of the options of social media to leave us a comment or a thought there.

Steve: Or you can leave on the podcast show notes page as well.

Carlos: That’s right. So our episode URL today is sqldatapartners.com/plancache. Again thanks again for tuning in to this episode. If you want to connect with us on LinnkedIn I am @carloslchacon.

Steve: Or you can find me on LinkedIn @stevestedman.