SQL Server 2016 - Parameter Sniffing can be controlled at database level

SQL Server uses Parameter Sniffing for creating the best plan for your query (or stored procedure) that sniffs values passed for parameters and generates the plan accordingly. In most of the cases, this is the best for most queries and stored procedures unless the values for parameters are vary with each execution. There was no easy way of enabling and disabling this with previous versions but trace flags 4136 allows you to disable Parameter Sniffing at server level. However the question is, should we disable it, or why should we disable it? 

Let's try to understand this before looking at the setting given with SQL Server 2016. Have a look on the following code;

USE AdventureWorks2016CTP3
GO
SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID = 1
SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID = 1000

SQL Server generates two different plans for these two queries because they are ad-hoc statements and two different values are passed to SalesOrderDetailID. The plans were generated based on the values passed, basically using Parameter Sniffing. That is why we see two different plans for the same query.

With stored procedures, this is bit different. The plan for the procedure is created with its initial execution, based on the values passed for parameters and it stores the plan in the cache. SQL Server does not sniff parameter values with subsequent executions for generating the plan again unless it cannot find the cached plan. This speeds up the execution because SQL Server does not need to spend time and use resources for generating the plan again and again. But, remember, it uses the same plan for all type of values passed, it may not be the optimal plan for all values.

CREATE PROC GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

EXEC GetOrderDetails 1
EXEC GetOrderDetails 1000

Okay, now why we should disable this behavior? If we disable this, SQL Server uses average distribution statistics for generating the plan which is not best for almost all implementation. If you really need a plan that does not depend on values passed and you need sort of average type plan for all executions, then you will benefit disabling Parameter Sniffing.

Read more on this with my article: Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

As I mentioned above, the trace flag can be used for disabling but it applies to all databases. With SQL Server 2016, whether you need it or not, this setting is available at database level and can be enabled/disabled anytime.

This is how it display with database properties.

Let's disable this for AdventureWorks database and see how it works.

Let's clear the cache and execute the procedure again and see.

-- Clearing the cache
DBCC FREEPROCCACHE;

EXEC GetOrderDetails 1;
EXEC GetOrderDetails 1000;

As you see, a plan has been generated without considering values passed and it is being reused. If you think that your codes benefit with this, then this new option can be applied to your database, otherwise, keeping the default is the best.