While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled?
This is my favorite kind of question because the answer is simple: I don’t know.
Parameter Sniffing
For those who don’t know, parameter sniffing is when SQL Server uses the precise values passed into a query as a parameter (this means stored procedures or prepared statements) to generate an execution plan from the statistics using the value from the parameter. Most of the time, parameter sniffing is either helping you, or is not hurting you. Sometimes, parameter sniffing turns bad and hurts you quite severely.
Usually, but not always, this is because you either have severely skewed data (some data is very different than the rest, lots of rows versus fewer or vice versa), or your statistics are out of date. The real issue is deciding how best to resolve the issue if you are dealing with bad parameter sniffing.
OPTIMIZE FOR
There are a bunch of ways to deal with parameter sniffing when it goes bad. All of them revolve around controlling what type of execution plan gets generated. The three most common methods for resolving bad parameter sniffing are, plan forcing using Query Store (currently available in Azure SQL Database and SQL Server 2016, I have written extensively on this topic), using a RECOMPILE hint to get a new plan every time, or, using the OPTIMIZE FOR hint to get a plan based on a specific value or on the average of values.
Using OPTIMIZE FOR is pretty straight forward. Here’s an example query that uses the OPTIMIZE FOR hint to force the optimizer to choose a particular execution plan:
CREATE PROC dbo.AddressByCity @City NVARCHAR(30)
AS
SELECT a.AddressID,
a.AddressLine1,
a.AddressLine2,
a.City,
sp.Name AS StateProvinceName,
a.PostalCode
FROM Person.Address AS a
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE a.City = @City
OPTION(OPTIMIZE FOR(@City='Mentor'));
Any time this stored procedure is called and that query gets compiled, or even if the statement gets recompiled, it will use the value of ‘Mentor’ to look at the statistics and determine how many rows are likely to be returned. This lets me take control away from the parameter sniffing process.
Turning Off Parameter Sniffing Entirely
We can just completely eliminate parameter sniffing. There’s a traceflag that we can set:
DBCC TRACEON (4136,-1);
This doesn’t require a server reboot or anything. It’ll just stop using parameter sniffing… on the whole server. I strongly advocate against using this without very extensive testing to confirm that you’re not benefitting from parameter sniffing.
We can remove the hint from the query above. Let’s then turn off parameter sniffing. If I capture the execution plan and look at the SELECT properties, that’s where I would normally see the Compile Time values for parameters. However, as you can see here, I don’t have a Compile Time value, just a Run Time value:
What Happens To The OPTIMIZE FOR Hint
Let’s recompile the stored procedure using the OPTIMIZE FOR hint so that we can see what happens. Then, I’m going to call the procedure, but I’m going to pass a value that would result in a different execution plan:
EXEC dbo.AddressByCity @City = N'London';
Now, let’s take a look at the properties:
You can see that I have both a compile time value, and a run time value.
Conclusion
Just because I have parameter sniffing disabled, the query hints do not stop working. If I used OPTIMIZE FOR UNKNOWN instead of OPTIMIZE FOR a value, the results would be the same as when I disable parameter sniffing (the optimizer just uses an average instead of specific values). However, the other hint still pushes the optimizer to use a particular value, effectively pushing it to still do parameter sniffing even though parameter sniffing has been disabled. One other point, I also tested using OPTIMIZE FOR through a Plan Guide even though parameter sniffing was disabled. This worked fine too.
This is exactly what I’m looking at now where we have serious data skew between large/small clients. Unfortunately it’s all ORM generated sql. We are looking at calling procs or adding optimize hints but it will take time.
The immediate plan is ( other than clearing the entire plan cache twice a week ), in the short run we’re planning to put everything on SSD drives, although I’ve read about deadlock issues with that.
But I’m also looking at running some of the worst report queries manually, or in an agent job, with option(recompile) and hoping it “sticks” for a while.
Also filtered stats. Plan guides I can’t get to work — the sql is just so long and complex with numerous joins etc.
Lastly, we turned on forced parameterization years ago but I think now that since 99.9% of our app sql is parameterized, that should be turned off.
Hello,
Yeah, this blog post is pretty targeted. It’s not addressing bad parameter sniffing in the larger sense. I’m only talking about what happens to the query hints meant to address bad parameter sniffing when you choose to take the added step of disabling it through the traceflag.
I wrote an entire chapter on parameter sniffing in my query tuning book and another in the MVP Deep Dives book. It’s a big topic that a blog post just won’t cover adequately.
If you have major data skew, frequently the best bet is to recompile your queries (assuming you can afford that, recompile is not free and depending on your system and the queries there, that could be very costly). That way you get the best plan for each distinct data set. However, it may require some rework on the ORM side of things.
As to SSDs, on the one hand, they’re pretty amazing. On the other hand, yeah, they can expose other issues. It’s kind of odd to say “Hey, it makes my code faster, and that’s a problem” but, it can be an issue. Usually though, the issues it exposes are issues that need to be addressed anyway. Also, assuming you pick the right place to put the SSD, you do get some pretty major performance benefits.
Plan guides frequently don’t work because of either very complex code, or, more frequently, the ad hoc nature of lots of code. They’re not my preferred method of dealing with it.
If you’re on Azure SQL Database or you’re on SQL Server 2016, a very good option for dealing with bad parameter sniffing is the ability to force a plan using the Query Store. I have a bunch of different blog posts on that topic:
https://www.scarydba.com/tag/query-store/
If you don’t have evidence that forced parameterization is hurting, I’d leave it enabled until you do gather that data. I wouldn’t suggest enabling or disabling anything without measuring performance and finding evidence that something is hurting you before you make the change (one exception to that, cost threshold for parallelism, the default value of 5 is ridiculous and should be changed).
I would advocate pretty strongly to target queries for removal from cache rather than clearing the whole thing on a regular basis. You can use FREEPROCCACHE with a plan handle to remove a single plan. I wrote about this here:
https://www.scarydba.com/2015/08/24/targeted-plan-cache-removal/
I hope any of this is helpful.
Thanks. Yes I’ve used the targeted “remove a plan(s) from cache” approach, but sometimes when the help desk calls I don’t have time for that.
I see this book on Amazon — SQL Server Query Performance Tuning by Grant Fritchey I’m guessing most of these topics would be covered there.
We’re still on sql 2012, not Azure.
The SSDs “leading to deadlocks” came up in a Sql Server Central discussion. Unfortunately I don’t think we’re going to be able to do prod-like load testing before the switch over.
http://www.sqlservercentral.com/Forums/Topic1841628-2799-3.aspx#bm1842143
Yep, that’s the book, 4th edition. Has a chapter on parameter sniffing specifically. Although, to your pet peeve, many of the examples are shorter.
Yeah, that discussion is pretty much what I said. The disk contention caused by slow disk can mask existing issues and then the faster performance unmasks the code problems, which were there. It’s not that the SSDs lead to problems. They just eliminate an existing issue which then shows another issue. It’s not in any way automatic. In fact, most people don’t hit an issue at all.
Got the book boss says I can expense it….as a side note it just dawned on me that many years ago we named our Second Son Grant ..I was recalling an old fraternity buddy at the time.. since my wife is originally from Southern Virginia I was a little concerned about that name 🙂
Ha! Well, my family were such strong Union supporters during the war that there was a poem written about one relative (Barbara Fritchie) and another was named Ulysses Grant. His son was Grant Ulysses. His son was Herbert Grant. They skipped a generation (weird set of deaths & marriages) and then my mom named me after her dad. So, yeah, the Virginia people might not care for me. Ha!
Oh, and my son is Tristram Grant. I decided to keep it going.
Oh, and if you hit issues with the book, you know where to go with questions.