参数嗅探_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的