参数嗅探_SQL Server 2016参数嗅探

参数嗅探

SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.

SQL Server总是尝试在首次执行存储过程时为每个存储过程生成最优化的执行计划。 SQL Server引擎在编译存储过程（第一次执行）时会查看存储过程传递的参数值，以便创建包括参数的最佳计划，并将该计划保留在计划缓存中以备将来使用。 此参数分析过程称为“ 参数嗅探” 。

New calls for the same stored procedure will be faster as it will not be compiled again; the SQL Server Query Optimizer will use the same execution plan for each execution with the same parameters values, which is optimized for these values, and any call for this stored procedure in the same way, which may or may not be optimal for the new values.

对同一存储过程的新调用将更快，因为不会再次对其进行编译。 SQL Server查询优化器将为具有相同参数值的每次执行使用相同的执行计划，该计划针对这些值进行了优化，并且对存储过程的任何调用均以相同的方式进行，这对于新值可能是最佳的，也可能不是最佳的。

Parameter Sniffing is useful for reusing the same execution plan for the same query with the same parameters values, as the initial compiling process can be expensive. But it may lead to using a less efficient execution plan to execute all queries with the same shape, which will cause performance degradation. The optimal plan for a specific set of parameters may not be suitable for other parameters, you may pass a parameter value that returns one record, where another value could return hundreds, thousands or millions of records, and the plan that handles one record may not serve the value that returns thousands of records with the same performance.

由于初始编译过程可能会很昂贵，因此参数嗅探对于具有相同参数值的相同查询重用相同的执行计划很有用。 但这可能导致使用效率较低的执行计划来执行所有具有相同形状的查询，这将导致性能下降。 特定参数集的最佳计划可能不适用于其他参数，您可以传递一个返回一个记录的参数值，而另一个值可能返回数百，数千或数百万个记录，而处理一个记录的计划可能不提供返回具有相同性能的数千条记录的值。

In most cases, the data distribution within the database is homogeneous, which is where parameter sniffing is helpful, but in some cases, with non-homogeneous distribution, parameter sniffing can be a problem. So that, to decide if you will or will not use parameter sniffing within your environment, you need to investigate your workload. If most of your workload consists of stored procedures calls, you can take benefits from the parameter sniffing, but if you have a lot of ad-hoc queries running within your workload, then it is better not to use the parameter sniffing.

在大多数情况下，数据库中的数据分布是同质的，这有助于参数嗅探，但是在某些情况下，如果分布不均匀，则参数嗅探可能会成为问题。 因此，要确定您是否将在环境中使用参数嗅探，您需要调查工作量。 如果您的大部分工作量都由存储过程调用组成，则可以从参数嗅探中受益，但是如果您的工作负载中有很多临时查询在运行，那么最好不要使用参数嗅探。

Parameter sniffing is enabled by default in SQL Server, you can disable it by turning on the Trace Flag 4136 at the instance level, which will affects all databases hosted in the same instance that may not be acceptable for instance with many databases serving different workload types. SQL Server 2016 introduces the use of Database Scoped Configuration which allows us to configure the parameter sniffing at the database level, rather than configure it only at the instance level. We will go through many ways to configure the parameter sniffing within the demo in this article.

默认情况下，SQL Server中启用了参数嗅探功能，您可以通过在实例级别打开跟踪标志4136来禁用它，这将影响同一实例中托管的所有数据库，对于许多服务于不同工作负载类型的数据库而言，这可能是不可接受的。 SQL Server 2016引入了数据库范围配置的使用，这使我们能够在数据库级别配置参数嗅探，而不是仅在实例级别进行配置。 在本文的演示中，我们将通过多种方式配置参数嗅探。

Let’s start our demo to see how parameter sniffing works and how to overcome the issue when it negatively affects stored procedure performance. First we will create two new tables; the Employees table with the employees’ information and the Employee_Departement table that contains the list of company departments, where the Employee table has the foreign key EmpDepID referenced to the DepID from the Employee_Departement table as below:

让我们开始演示，以了解参数嗅探如何工作以及如何在参数嗅探对存储过程性能产生负面影响时解决该问题。 首先，我们将创建两个新表； 雇员表以及雇员信息以及包含公司部门列表的Employee_Departement表，其中Employee表具有从Employee_Departement表引用到DepID的外键EmpDepID，如下所示：

 
USE SQLShackDemo 
GO
CREATE TABLE Employees
  (
   EmpID INT NOT NULL ,
   EmpName VARCHAR(50) NOT NULL ,
   EmpAddress VARCHAR(50) NOT NULL ,
   EmpDEPID int NOT NULL ,
   EmpBirthDay DATETIME ,
   PRIMARY KEY CLUSTERED ( EmpID )
  )
GO
 
CREATE TABLE Employee_Department
  (
   DepID INT NOT NULL ,
   DepName VARCHAR(50) NOT NULL ,
   PRIMARY KEY CLUSTERED ( DepID )
  )
  GO
 
CREATE INDEX IX_Employees_EmpDEPID 
ON Employees(EmpDEPID)
GO
USE [SQLShackDemo]
GO
 
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_EmpDep] FOREIGN KEY([EmpDEPID])
REFERENCES [dbo].[Employee_Department] ([DepID])
GO
 
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_EmpDep]
GO
 

Once the tables are created successfully, we will fill these two tables with test data using ApexSQL Generate test data generation tool, as follows:

成功创建表之后，我们将使用ApexSQL Generate测试数据生成工具将这两个表填充测试数据，如下所示：

We will create a simple stored procedure that reads from the two tables and take the department name as an input parameter:

我们将创建一个简单的存储过程，该存储过程从两个表中读取并以部门名称作为输入参数：

 
CREATE PROCEDURE EmpPerDEP 
@DepName   VARCHAR(50) 
 
AS
SELECT *
  FROM [SQLShackDemo].[dbo].[Employees] EMP
  JOIN [SQLShackDemo].[dbo].[Employee_Department] ED
  ON EMP.EmpDEPID=ED.DepID
  WHERE ED.DepName =@DepName
  GO
 

If we use the created stored procedure to search for the Sales department:

如果我们使用创建的存储过程来搜索销售部门：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
 

The optimal execution plan for this parameter value will be like:

该参数值的最佳执行计划如下：

Again, if we clear the plan cache and use the stored procedure to search for the HR department:

同样，如果我们清除计划缓存并使用存储的过程搜索人力资源部门：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
 

The optimal execution plan for that parameter value will be like:

该参数值的最佳执行计划如下：

As mentioned previously, the parameter sniffing is enabled by default, and we can makes sure that it is enabled on the SQLShackDemo database by querying the Parameter Sniffing Database Scoped Configuration option:

如前所述，默认情况下将启用参数嗅探，并且可以通过查询“参数嗅探数据库范围配置”选项来确保在SQLShackDemo数据库上启用了参数嗅探：

 
USE SQLShackDemo
GO
SELECT name, value FROM sys.database_scoped_configurations where name= 'PARAMETER_SNIFFING'
 

Which will show us that this option is enabled:

这将向我们显示该选项已启用：

If we clear the plan cache and run the two stored procedure calls together, first search for the HR then search for the Sales departments:

如果我们清除计划缓存并一起运行两个存储过程调用，则首先搜索HR，然后搜索销售部门：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
exec EmpPerDEP 'Sales'
GO
 

Parameter sniffing will create an optimal execution plan for the first HR search and use it in all ways in the second call for the Sales department as in the below execution plans comparison:

参数嗅探将为第一个HR搜索创建最佳执行计划，并在第二次致电销售部门时以各种方式使用它，如以下执行计划比较所示：

Again, if we clear the plan cache and run the two stored procedure calls together, opposite to the previous order, where we will search for the Sales department first then search for the HR departments:

同样，如果我们清除计划缓存并与之前的订单相反运行两个存储过程调用，则将首先搜索销售部门，然后搜索人力资源部门：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO
 

Parameter sniffing will create an optimal execution plan for the first Sales search and force its usage in the second call for the HR department as in the below execution plans comparison:

参数嗅探将为第一个Sales搜索创建最佳执行计划，并在第二次致电HR部门时强制其使用，如以下执行计划比较所示：

The previous results show us how parameter sniffing will use the first created execution plan in all stored procedure calls with same or different parameters’ values. This plan may or may not be optimal for all values as we mentioned in this article, which may cause performance issues.

先前的结果向我们展示了参数嗅探将如何在具有相同或不同参数值的所有存储过程调用中使用第一个创建的执行计划。 正如我们在本文中提到的那样，此计划可能对所有值都不是最佳选择，这可能会导致性能问题。

To overcome parameter sniffing performance issue that could occur due to forcing the same plan usage for all stored procedures parameters values we can use the WITH RECOMPLIE option in the stored procedure definition, which will force the stored procedure compilation at each execution, creating a new execution plan for each parameter value. The previous stored procedure can be modified to recompile at each run as follows:

为了克服由于强制所有存储过程参数值使用相同的计划而可能发生的参数嗅探性能问题，我们可以在存储过程定义中使用WITH RECOMPLIE选项，这将在每次执行时强制执行存储过程编译，从而创建新的执行为每个参数值计划。 可以将先前的存储过程修改为在每次运行时重新编译，如下所示：

 
ALTER PROCEDURE EmpPerDEP 
@DepName   VARCHAR(50) 
WITH RECOMPILE
AS
SELECT *
  FROM [SQLShackDemo].[dbo].[Employees] EMP
  JOIN [SQLShackDemo].[dbo].[Employee_Department] ED
  ON EMP.EmpDEPID=ED.DepID
  WHERE ED.DepName =@DepName
  GO
 

If we clear the plan cache and run the two stored procedure previous calls:

如果我们清除计划缓存并运行先前调用的两个存储过程：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO
 

A separate execution plan will be created for each call which is suitable for each parameter value as below:

将为每个调用创建一个适合每个参数值的单独的执行计划，如下所示：

Using WITH RECOMPILE statement is the simplest solution for parameter sniffing performance issues, but you need to take into consideration that trecompilation process is expensive, as it will increase the CPU consumption if this stored procedure is called very frequently.

使用WITH RECOMPILE语句是解决参数嗅探性能问题的最简单解决方案，但是您需要考虑到重新编译过程很昂贵，因为如果非常频繁地调用此存储过程，它将增加CPU消耗。

If the stored procedure contains more than one query, and you know that the parameter sniffing will cause performance issue only in one query, you can use the OPTION (RECOMPILE) query hint to recompile this part of the stored procedure only. The previous stored procedure can be modified to recompile only a specific query as below:

如果存储过程包含多个查询，并且您知道参数嗅探只会在一个查询中引起性能问题，则可以使用OPTION（RECOMPILE）查询提示仅重新编译存储过程的这一部分。 可以将以前的存储过程修改为仅重新编译特定的查询，如下所示：

 
ALTER PROCEDURE EmpPerDEP 
@DepName   VARCHAR(50) 
AS
SELECT *
  FROM [SQLShackDemo].[dbo].[Employees] EMP
  JOIN [SQLShackDemo].[dbo].[Employee_Department] ED
  ON EMP.EmpDEPID=ED.DepID
  WHERE ED.DepName =@DepName
  OPTION(RECOMPILE)
  GO
 

As our stored procedure has only one query in it, the same previous result will be found in the following execution plan after executing the same two queries:

由于我们的存储过程中只有一个查询，因此在执行相同的两个查询后，在以下执行计划中会找到相同的先前结果：

Another method that can be used to resolve the parameter sniffing performance issue is using the OPTION (OPTIMIZE FOR (@VARIABLE=VALUE)) query hint, that will generate an optimized plan that is generated when using a specific parameter value. In other words, if you know that using a specific value for that parameter will generate the best plan, you can specify that value in the query hint as a reference for the stored procedure optimization. If you are not sure which value will generate the best performance, you can use the OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN)) query hint which will help in optimizing a plan in between, that may be suitable for all parameters. For me, you need to be aware when using it as it may generate a plan that may harm system performance. The previous stored procedure can be modified as below to use the OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN)) query hint as below:

可以用来解决参数嗅探性能问题的另一种方法是使用OPTION（OPTIMIZE FOR（@ VARIABLE = VALUE））查询提示，该提示将生成优化计划，该计划在使用特定参数值时生成。 换句话说，如果您知道对该参数使用特定值将生成最佳计划，则可以在查询提示中指定该值作为存储过程优化的参考。 如果不确定哪个值将产生最佳性能，则可以使用OPTION（OPTIMIZE FOR（@VARIABLE UNKNOWN））查询提示，这将有助于优化介于两者之间的计划，该计划可能适用于所有参数。 对我来说，您需要了解使用它的时间，因为它可能会产生可能损害系统性能的计划。 可以如下修改先前的存储过程，以使用OPTION（OPTIMIZE FOR（@VARIABLE UNKNOWN））查询提示，如下所示：

 
ALTER PROCEDURE EmpPerDEP 
@DepName   VARCHAR(50) 
AS
SELECT *
  FROM [SQLShackDemo].[dbo].[Employees] EMP
  JOIN [SQLShackDemo].[dbo].[Employee_Department] ED
  ON EMP.EmpDEPID=ED.DepID
  WHERE ED.DepName =@DepName
 OPTION(OPTIMIZE FOR UNKNOWN )
  GO
 

Calling the stored procedure twice again:

再次调用存储过程两次：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO
 

The in between execution plan that could be optimal for both executions will be like:

两次执行之间可能最佳的执行计划如下：

In SQL Server 2016, Database Scoped Configurations are introduced, which allows us to configure these configurations at the database level. One of these options is the Parameter_Sniffing which we can easily disable it at the database level, enabling us to configure each database with its own workload type with the suitable configurations.

在SQL Server 2016中，引入了数据库范围的配置，这使我们能够在数据库级别配置这些配置。 这些选项之一是Parameter_Sniffing ，我们可以在数据库级别轻松禁用它，从而使我们能够使用适当的配置为每个数据库配置自己的工作负载类型。

The Parameter_Sniffing option can be disabled using the below ALTER DATABASE statement:

可以使用下面的ALTER DATABASE语句禁用Parameter_Sniffing选项：

 
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
 

Or simply from the Options tab of the Database Properties window, by changing the Parameter_Sniffing value to OFF as follows:

或仅从“数据库属性”窗口的“选项”选项卡中，通过将Parameter_Sniffing值更改为OFF，如下所示：

If we execute the same previous stored procedure calls again after disabling the parameter sniffing:

如果我们在禁用参数嗅探之后再次执行相同的先前存储过程调用：

 
DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
exec EmpPerDEP 'Sales'
GO
 

The SQL Server Query Optimizer will generate the optimal query that will fit all parameters, which is like the below execution plans in our case:

SQL Server查询优化器将生成适合所有参数的最佳查询，这与本例中的以下执行计划类似：

The same result will be found if you reverse the two queries.

如果您反转两个查询，将会发现相同的结果。

结论 (Conclusion)

Parameter Sniffing is the process of looking to the first passed parameters values when compiling the stored procedure in order to create an optimal execution plan that fits these parameters values and use it for all values. But the generated execution plan may not be optimal for all the parameter’s values, leading to performance problems in some cases. You should understand your workload well in order to decide if you will keep the parameter sniffing enabled or disable it using one of the query hints mentioned in this article, or from the SQL Server 2016 Database Scoped Configurations. In some cases you need also to rewrite your query to have the best execution plan and the optimum performance. It is better to simulate you workload in a test environment to study its performance and decide how to tune it and what is the suitable way to do that.

参数嗅探是在编译存储过程时查找第一个传递的参数值的过程，以创建适合这些参数值并将其用于所有值的最佳执行计划。 但是，生成的执行计划可能并非对于所有参数值都是最佳的，从而在某些情况下导致性能问题。 您应该很好地了解您的工作负载，以便决定是使用本文中提到的查询提示之一还是使用SQL Server 2016数据库范围的配置来启用或禁用参数嗅探。 在某些情况下，您还需要重写查询以具有最佳执行计划和最佳性能。 最好在测试环境中模拟您的工作负载，以研究其性能并决定如何对其进行调整以及执行此操作的合适方法。

翻译自: https://www.sqlshack.com/sql-server-2016-parameter-sniffing/

参数嗅探