Over a million developers have joined DZone.

OPTIMIZE FOR Hints When Parameter Sniffing Is Turned Off

DZone's Guide to

OPTIMIZE FOR Hints When Parameter Sniffing Is Turned Off

If you don't find parameter sniffing useful, you can disable it. You might worry about your hints, but it turns out that it doesn't affect them much at all.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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 at least 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.


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)
   SELECT a.AddressID,
      sp.Name AS StateProvinceName,
   FROM Person.Address AS a
   JOIN Person.StateProvince AS sp
      ON a.StateProvinceID = sp.StateProvinceID
   WHERE a.City = @City 

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.


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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

sql server ,optimize ,database ,parameter sniffing

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}