SQL Server – Did you know about Trace Flag 4136 ?

Hi Friends,

Parameter Sniffing, in general, is a good thing and your queries can benefit from that. Here are some of my blogs posts that you can read to get some background before you proceed further:

https://www.sqlservergeeks.com/sql-server-using-optimize-for-query-hint/

https://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/8/forceseek-hint-in-sql-server-2008-part-i

https://www.sqlservergeeks.com/sql-server-forceseek-hint-in-sql-server-2008-part-ii-working-with-parameter-sniffing/

But sometimes your queries might be atypical, especially when there are too many ad hoc queries coming from your application. You could implement a number of solutions to fix parameter sniffing issues depending on the kind of problem you have. Some of these solutions are as follows:

1. Forcing parameterization in SQL Server at Database level

ALTER DATABASE <DATBASE NAME> SET PARAMETERIZATION FORCED

You can do this when you have too many ad hoc non parameterized queries coming from your application and each one creates its own plan. Forcing parameterization can help in better plan reuse.

2. Optimize for ad hoc workload

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE

You can do this when your system witnesses too many one-time ad hoc queries. This option is at server level. When turned on, SQL Server will not cache the execution plan for the ad hoc query the first time. Only a stub will be stored that will take much lesser memory compared to the full plan. When the same query comes in a 2nd time, SQL Server now stores the full plan as usual. This just helps in better utilization of plan cache memory.

3. You can use some hints like:

OPTIMIZE FOR – use this hint to instruct the optimizer to optimize the plan for a specific parameter values. Note that this hint might not be solving your atypical queries with variety of parameter values.

RECOMPILE – you can use this hint at SP level or at query level. When used, plans are never cached and new plans are created every time the query is executed based on the parameter values. Note that compilation is CPU intensive process.

OPTIMIZE FOR UNKNOWN – while the above hints actually tell SQL Server to consider parameter sniffing and benefit from it, OPTIMIZE FOR UNKNOWN does the opposite. When used, SQL Server will not optimize the query for any particular value and a default selectivity is going to be applied every time based on the number of records and the density factor.

So what about Trace Flag 4136 ?

While OPTIMIZE FOR UNKNOWN disables parameter sniffing at query level, trace flag 4136 disables parameter sniffing at instance level. Which means, no parameter sniffing for that instance – all queries/workloads for all databases get affected with this.

Use this flag and some of the hints I talked about as a last resort while deal with parameter sniffing issues. You can clearly see that there are multiple options and there is no one solution-fit all.

My friend, Karthik from Microsoft added: “Microsoft SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 Service Pack 1 (SP1) Cumulative Update 7 and Microsoft SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 9 introduce trace flag 4136”

In my next post, I will try to demo this trace flag with an example.