参数嗅探_SQL Server中SQL查询优化技术：参数嗅探-优快云博客

参数嗅探是SQL Server中一个关键的查询优化问题，可能导致性能下降。本文深入探讨了参数嗅探的概念，如何查找和解决这一问题，包括使用局部变量、RECOMPILE选项、动态SQL以及创建临时存储过程等方法。同时，文章强调了避免过度的代码路径和过多的参数，以及减少存储过程中的业务逻辑，以改善性能和计划重用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参数嗅探

描述 (Description)

Of the many ways in which query performance can go awry, few are as misunderstood as parameter sniffing. Search the internet for solutions to a plan reuse problem, and many suggestions will be misleading, incomplete, or just plain wrong.

在查询性能可能会出现问题的许多方法中，很少有像参数嗅探那样被误解的。在Internet上搜索计划重用问题的解决方案，许多建议将产生误导，不完整或完全错误。

This is an area where design, architecture, and understanding one’s own code are extremely important, and quick fixes should be saved as emergency last resorts.

在这个领域中，设计，体系结构和理解自己的代码非常重要，应将快速修复程序保存为紧急措施。

Understanding parameter sniffing requires comfort with plan reuse, the query plan cache, and parameterization. This topic is so important and has influenced me so much that I am devoting an entire article to it, in which we will define, discuss, and provide solutions to parameter sniffing challenges.

了解参数嗅探要求您对计划重用，查询计划缓存和参数化感到满意。这个主题是如此重要，并且对我的影响如此之大，以至于我在整篇文章中都投入了大量精力，其中我们将定义，讨论并提供参数嗅探挑战的解决方案。

审查执行计划重用 (Review of Execution Plan Reuse)

SQL query optimization is both a resource and time intensive process. An execution plan provides SQL Server with instructions on how to efficiently execute a query and must be available prior to execution and is the product of the query optimization process whenever a query is executed.

SQL查询优化既耗时又耗时。执行计划为SQL Server提供了有关如何有效执行查询的说明，执行计划必须在执行之前可用，并且是执行查询时查询优化过程的产物。

Because it takes significant resources to generate an execution plan, SQL Server caches plans in memory in the query plan cache for later use. If the same query is executed multiple times, then the cached plan can be reused over and over, without the need to generate a new plan. This saves time and resources, especially for common queries that are executed frequently.

由于要花费大量资源来生成执行计划，因此SQL Server将计划缓存在查询计划缓存中的内存中，以供以后使用。如果多次执行同一查询，则可以重复使用缓存的计划，而无需生成新计划。这样可以节省时间和资源，尤其是对于频繁执行的常见查询。

Execution plans are cached based on the exact text of the query. Any differences, even those as minor as a comment or capital letter, will result in a separate plan being generated and cached. Consider the following two queries:

根据查询的确切文本来缓存执行计划。任何差异，甚至是注释或大写字母之间的微小差异，都将导致生成并缓存单独的计划。考虑以下两个查询：

SELECT
	SalesOrderHeader.SalesOrderID,
	SalesOrderHeader.DueDate,
	SalesOrderHeader.ShipDate
FROM Sales.SalesOrderHeader
WHERE SalesOrderHeader.OrderDate = '2011-05-30 00:00:00.000';
 
SELECT
	SalesOrderHeader.SalesOrderID,
	SalesOrderHeader.DueDate,
	SalesOrderHeader.ShipDate
FROM Sales.SalesOrderHeader
WHERE SalesOrderHeader.OrderDate = '2011-05-31 00:00:00.000';

While the queries are very similar and will likely require the same execution plan, SQL Server will create a separate plan for each. This is because the filter is different, with the OrderDate being May 30^th in the first query and May 31^st in the second query. As a result, hard-coded literals in queries will result in different execution plans for each different value that is used in the query. If I ran the query above once for every day in the year 2011, then the result would be 365 queries and 365 different cached execution plans.

虽然查询非常相似，并且可能需要相同的执行计划，但是SQL Server将为每个查询创建单独的计划。这是因为过滤器不同， OrderDate在第一个查询中是5月30 ^日，在第二个查询中是5月31 ^日。结果，查询中的硬编码文字将为查询中使用的每个不同值产生不同的执行计划。如果我在2011年每天都在上面运行一次查询，那么结果将是365个查询和365个不同的缓存执行计划。

If the queries above are executed very often, then SQL Server will be forced to generate new plans frequently for all possible values of OrderDate. If OrderDate is a DATETIME and can (and will) have lots of distinct values, then we’ll see a very large number of execution plans getting created at a rapid pace.

如果上面的查询执行得非常频繁，则SQL Server将被迫频繁地为OrderDate的所有可能值生成新计划。如果OrderDate是一个DATETIME，并且可以（并且将具有）许多不同的值，那么我们将看到快速创建了大量执行计划。

The plan cache is stored in memory and its size is limited by available memory. Therefore, if excessive numbers of plans are generated over a short period of time, the plan cache could fill up. When this occurs, older plans are removed from cache in favor of newer ones. If memory pressure becomes significant, then the older plans being removed may end up being useful ones that we will need soon.

计划缓存存储在内存中，其大小受可用内存的限制。因此，如果在短时间内生成了过多的计划，则计划缓存可能会填满。发生这种情况时，会将较旧的计划从缓存中删除，而采用较新的计划。如果内存压力变得很大，那么被删除的旧计划可能最终会成为有用的计划，我们不久将需要它们。

参数化 (Parameterization)

The solution to memory pressure in the plan cache is parameterization. For our query above, the DATETIME literal can be replaced with a parameter:

计划缓存中内存压力的解决方案是参数化。对于上面的查询，可以将DATETIME文字替换为参数：

CREATE PROCEDURE dbo.get_order_date_metrics
	@order_date DATETIME
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT
		SalesOrderHeader.SalesOrderID,
		SalesOrderHeader.DueDate,
		SalesOrderHeader.ShipDate
	FROM Sales.SalesOrderHeader
	WHERE SalesOrderHeader.OrderDate = @order_date;
END

When executed for the first time, an execution plan will be generated for this stored procedure that uses the parameter @order_date. All subsequent executions will use the same execution plan, resulting in the need for only a single plan, even if the proc is executed millions of times per day.

首次执行时，将为此存储过程生成使用参数@order_date的执行计划。所有后续执行都将使用相同的执行计划，即使仅每天执行proc数百万次，也只需要一个计划。

Parameterization greatly reduces churn in the plan cache and speeds up query execution as we can often skip the expensive optimization process that is needed to generate an execution plan.

参数化极大地减少了计划缓存中的客户流失，并加快了查询的执行速度，因为我们经常可以跳过生成执行计划所需的昂贵的优化过程。

什么是参数嗅探 (What is Parameter Sniffing)

Plan reuse is an important part of how execution plans are managed. The process of optimizing a query and assigning a plan to it is one of the most CPU-intensive processes in SQL Server. Since it is also a time-sensitive process, slowing down is not an option.

计划重用是管理执行计划的重要组成部分。优化查询并为其分配计划的过程是SQL Server中最占用CPU的过程之一。由于它也是一个对时间敏感的过程，因此不能放慢速度。

This is a good feature and one that saves immense server resources. A query that executes a million times a day can now be optimized once and the plan reused 999,999 times for free. While this feature is almost always good, there are times it can cause unexpected performance problems. This primarily occurs when the set of parameters that the execution plan was optimized for ends up being drastically different than the parameters that are being passed in right now. Maybe the initial optimization called for an index seek, but the current parameters suggest a scan is better. Maybe a MERGE join made sense the first time, but NESTED LOOPS is the right way to go now.

这是一项好功能，可以节省大量服务器资源。现在每天可以优化一次执行一百万次的查询，该计划可免费重复使用999,999次。尽管此功能几乎总是很好，但有时可能会导致意外的性能问题。这主要是在执行计划针对其进行优化的一组参数与当前传递的参数完全不同时发生的。也许最初的优化需要索引查找，但是当前参数表明扫描效果更好。也许第一次合并就很有意义，但是嵌套循环是正确的选择。

The following is an example of parameter sniffing:

以下是参数嗅探的示例：

CREATE PROCEDURE dbo.get_order_metrics_by_sales_person
	@sales_person_id INT
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT
		SalesOrderHeader.SalesOrderID,
		SalesOrderHeader.DueDate,
		SalesOrderHeader.ShipDate
	FROM Sales.SalesOrderHeader
	WHERE ISNULL(SalesOrderHeader.SalesPersonID, 0) = @sales_person_id;
END

This stored procedure searches SalesOrderHeader based on the ID of the sales person, including a catch-all for NULL IDs. When we execute it for a specific sales person (285), we get the following IO and execution plan:

此存储过程根据销售人员的ID搜索SalesOrderHeader ，包括对NULL ID的全部捕获。当我们为特定的销售人员执行它时（285），我们得到以下IO和执行计划：

Table ‘SalesOrderHeader’. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'SalesOrderHeader'。扫描计数1，逻辑读105，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

We can see that SQL Server used a scan on a nonclustered index, as well as a key lookup to return the data we were looking for. If we were to clear the execution plan cache and rerun this for a parameter value of 0, then we would get a different plan:

我们可以看到SQL Server使用了对非聚集索引的扫描，以及用于返回我们要查找的数据的键查找。如果我们要清除执行计划缓存并针对参数值0重新运行它，那么我们将获得一个不同的计划：

Table ‘SalesOrderHeader’. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'SalesOrderHeader'。扫描计数1，逻辑读698，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

Because so many rows were being returned by the query, SQL Server found it more efficient to scan the table and return everything, rather than methodically seek through an index to return 95% of the table. In each of these examples, the execution plan chosen was the best plan for the parameter value passed in.

由于查询返回了这么多行，因此SQL Server发现扫描表并返回所有内容的效率更高，而不是有条不紊地查找索引以返回表的95％。在每个示例中，选择的执行计划都是传入参数值的最佳计划。

How will performance look if we were to execute the stored procedure for a parameter value of 285 and not clear the plan cache?

如果我们要为参数值285执行存储过程并且不清除计划缓存，性能会如何？

Table ‘SalesOrderHeader’. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'SalesOrderHeader'。扫描计数1，逻辑读698，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

The correct execution plan involved a scan of a nonclustered index with a key lookup, but since we reused our most recently generated execution plan, we got a clustered index scan instead. This plan cost us six times more reads, pulling significantly more data from storage than was needed to process the query and return our results.

正确的执行计划包括使用键查找对非聚集索引进行扫描，但是由于我们重复使用了最新生成的执行计划，因此我们获得了聚集索引扫描。该计划使我们的读取次数增加了六倍，与处理查询并返回我们的结果相比，从存储中提取的数据量明显增加。

The behavior above is a side-effect of plan reuse and is the poster-child for what this article is all about. For our purposes, parameter sniffing will be defined as undesired execution plan reuse.

上面的行为是计划重用的副作用，并且是本文所涉及的全部内容。就我们的目的而言，参数嗅探将被定义为不希望的执行计划重用。

查找和解决参数嗅探 (Finding and Resolving Parameter Sniffing)

How do we diagnose parameter sniffing? Once we know that performance is suboptimal, there are a handful of giveaways that help us understand when this is occurring:

我们如何诊断参数嗅探？一旦我们知道性能不佳，就会有一些赠品可以帮助我们了解何时发生这种情况：

A stored procedure executes efficiently sometimes, but inefficiently at other times.
存储过程有时会高效执行，但其他时候效率低下。
A good query begins performing poorly when no changes are made to database schema.
如果不对数据库架构进行任何更改，则良好的查询将开始表现不佳。
A stored procedure has many parameters and/or complex business logic enumerated within it.
存储过程具有许多枚举的参数和/或复杂的业务逻辑。
A stored procedure uses extensive branching logic.
存储过程使用扩展的分支逻辑。
Playing around with the TSQL appears to fix it temporarily.
玩TSQL似乎可以暂时修复它。
Hacks fix it temporarily
hacks暂时修复

Of the many areas of SQL Server where performance problems rear their head, few are handled as poorly as parameter sniffing. There often is not an obvious or clear fix, and as a result we implement hacks or poor choices to resolve the latency and allow us to move on with life as quickly as possible. An immense percentage of the content available online, in publications, and in presentations on this topic is misleading, and encourages the administrator to take shortcuts that do not truly fix a problem. There are definitive ways to resolve parameter sniffing, so let’s look at many of the possible solutions (and how effective they are).

在SQL Server的许多出现性能问题的领域中，很少有像参数嗅探那样糟糕的处理方法。通常没有明显或明确的修复程序，因此，我们实施了破解或错误的选择来解决延迟问题，并允许我们尽可能快地继续前进。在线，出版物和演示中有关此主题的大量可用内容具有误导性，并鼓励管理员使用无法真正解决问题的快捷方式。有确定的方法来解决参数嗅探，因此让我们看一下许多可能的解决方案（以及它们的有效性）。

I am not going to go into excruciating detail here. MSDN documents the use of different hints/mechanics well. Links are included at the end of the article to help with this, if needed.

我在这里不做详细介绍。 MSDN很好地记录了不同提示/机制的使用。如果需要，文章末尾包含一些链接来帮助您解决此问题。

在本地重新声明参数 (Redeclaring Parameters Locally)

Rating: It’s a trap!

评分： 这是一个陷阱！

This is a complete cop-out, plain and simple. Call it a cheat, a poor hack, or a bandage as that is all it is. Because the value of local variables is not known until runtime, the query optimizer needs to make a very rough estimate of row counts prior to execution. This estimate is all we get, and statistics on the index will not be effectively used to determine the best execution plan. This estimate will sometimes be good enough to resolve a parameter sniffing issue and give the illusion of a job well done.

这是一个完整而简单的解决方案。称其为作弊，可怜的骇客或绷带，仅此而已。因为直到运行时才知道局部变量的值，所以查询优化器需要在执行之前对行计数进行非常粗略的估计。我们仅能获得此估计，因此索引的统计信息将无法有效地用于确定最佳执行计划。这种估计有时将足以解决参数嗅探问题，并给人以做得很好的错觉。

The effect of using local variables is to hide the value from SQL Server. It’s essentially applying the hint “OPTIMIZE FOR UNKNOWN” to any query component that references them. The rough estimate that SQL Server uses to optimize the query and generate an execution plan will be right sometimes, and wrong other times. Typically the way this is implemented is as follows:

使用局部变量的作用是对SQL Server隐藏该值。实质上是将提示“ OPTIMIZE FOR UNKNOWN”应用于引用它们的任何查询组件。 SQL Server用于优化查询并生成执行计划的粗略估计有时是正确的，而其他时候则是错误的。通常，实现方法如下：

Performance problem is identified.
确定性能问题。
Parameter sniffing is determined to be the cause.
确定参数嗅探是原因。
Redeclaring parameters locally is a solution found on the internet.
在Internet上可以找到在本地重新声明参数的解决方案。
Try redeclaring parameters locally and the performance problem resolves itself.
尝试在本地重新声明参数，性能问题将自行解决。
Implement the fix permanently.
永久实施此修复程序。
3 months later, the problem resurfaces and the cause is less obvious.
3个月后，问题再次出现，原因不那么明显。

What we are really doing is fixing a problem temporarily and leaving behind a time bomb that will create problems in the future. The estimate by the optimizer may work adequately for now, but eventually will not be adequate and we’ll have resumed performance problems. This solution works because oftentimes a poor estimate performs better than badly times parameter sniffing, but only at that time. This is a game of chance in which a low probability event (parameter sniffing) is crossed with a high probability event (a poor estimate happening to be good enough) to generate a reasonable illusion of a fix.

我们真正要做的是暂时解决问题，并留下定时炸弹，这种炸弹将来会造成问题。优化程序的估计可能暂时可以正常运行，但最终将不足够，我们将恢复性能问题。该解决方案之所以有效，是因为通常情况下，差的估计比差的时间进行的参数嗅探要好，但仅在那时才如此。这是一种机会游戏，其中低概率事件（参数嗅探）与高概率事件（差的估计恰好足够好）相交以产生合理的假象幻觉。

To demo this behavior, we’ll redeclare a parameter locally in our stored procedure from earlier:

为了演示这种行为，我们将从先前的存储过程中本地重新声明一个参数：

IF EXISTS (SELECT * FROM sys.procedures WHERE procedures.name = 'get_order_metrics_by_sales_person')
BEGIN
	DROP PROCEDURE dbo.get_order_metrics_by_sales_person;
END
GO
 
CREATE PROCEDURE dbo.get_order_metrics_by_sales_person
	@sales_person_id INT
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @sales_person_id_local INT = @sales_person_id;
 
	SELECT
		SalesOrderHeader.SalesOrderID,
		SalesOrderHeader.DueDate,
		SalesOrderHeader.ShipDate
	FROM Sales.SalesOrderHeader
	WHERE SalesOrderHeader.SalesPersonID = @sales_person_id_local;
END

When we execute this for different values, we get the same plan each time. Clearing the proc cache has no effect either:

当我们针对不同的值执行此操作时，每次都会获得相同的计划。清除proc缓存无效：

EXEC dbo.get_order_metrics_by_sales_person @sales_person_id = 285;
DBCC FREEPROCCACHE
EXEC dbo.get_order_metrics_by_sales_person @sales_person_id = 0;
EXEC dbo.get_order_metrics_by_sales_person @sales_person_id = 285;
DBCC FREEPROCCACHE
EXEC dbo.get_order_metrics_by_sales_person @sales_person_id = 285;
EXEC dbo.get_order_metrics_by_sales_person @sales_person_id = 0;

For each execution, the result is:

对于每次执行，结果为：

Table ‘SalesOrderHeader’. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'SalesOrderHeader'。扫描计数1，逻辑读698，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

When we hover over the results, we can see that the estimated number of rows was 1748, but the actual rows returned by the query was 16. Seeing a huge disparity between actual and estimated rows is an immediate indication that something is wrong. While that could be indicative of stale statistics, seeing local variables in the query should be a warning sign that they are related. In this example, the local variable forced the same mediocre execution plan for all runs of the query, regardless of details. This may sometimes give an illusion of adequate performance, but will rarely do so for long.

当我们将鼠标悬停在结果上时，我们可以看到估计的行数为1748，但是查询返回的实际行数为16。看到实际行与估计行之间的巨大差异立即表明存在问题。虽然这可能表明统计信息已过时，但是在查询中看到局部变量应该是一个警告信号，表明它们是相关的。在此示例中，无论详细信息如何，局部变量都会为所有查询强制执行相同的中等执行计划。有时可能会给人以适当性能的假象，但很少能长期如此。

To summarize: declaring local variables, assigning parameter values to them, and using the local variables in subsequent queries is a very bad idea and we should never, ever do this! If a short-term hack is needed, there are far better ones to use than this 🙂

总结一下：声明局部变量，为它们分配参数值，并在后续查询中使用局部变量是一个非常糟糕的主意，我们永远都不要这样做！如果需要短期破解，有比这更好的使用🙂

选项（建议） (OPTION (RECOMPILE))

Rating: Potentially useful

评分： 潜在有用

When this query hint is applied, a new execution plan will be generated for the current parameter values supplied. This automatically curtails parameter sniffing as there will be no plan reuse when this hint is used. The cost of this hint are the resources required to generate a new execution plan. By creating a new plan with each execution, we will pay the price of the optimization process with each and every execution.

应用此查询提示后，将为提供的当前参数值生成一个新的执行计划。这将自动减少参数嗅探，因为使用此提示时不会重复使用计划。此提示的成本是生成新执行计划所需的资源。通过为每次执行创建一个新计划，我们将为每次执行付出优化过程的代价。

This option is also an easy way out and should not be blindly used. This hint is only useful on queries or stored procedures that execute infrequently as the cost to generate a new execution plan will not be incurred often. For important OLTP queries that are being executed all day long, this is a harmful option and would be best avoided as we would sacrifice valuable resources on an ongoing basis to avoid parameter sniffing.

此选项也是一种简便的方法，不应盲目使用。该提示仅对不经常执行的查询或存储过程有用，因为不会经常产生生成新执行计划的成本。对于整天执行的重要OLTP查询，这是一个有害的选择，最好避免这样做，因为我们会不断牺牲宝贵的资源来避免参数嗅探。

OPTION RECOMPILE works best on reporting queries, infrequent or edge-case queries, and in scenarios where all other optimization techniques have failed. For highly unpredictable workloads, it can be a reliable way to ensure that a good plan is generated with each execution, regardless of parameter values. Here is a quick example of OPTION (RECOMPILE) from above:

OPTION RECOMPILE最适合于报告查询，不频繁或极端情况查询，以及所有其他优化技术均失败的情况。对于高度不可预测的工作负载，这是确保每次执行都生成好的计划的可靠方法，而与参数值无关。这是上面的OPTION（RECOMPILE）的快速示例：

IF EXISTS (SELECT * FROM sys.procedures WHERE procedures.name = 'get_order_metrics_by_sales_person')
BEGIN
	DROP PROCEDURE dbo.get_order_metrics_by_sales_person;
END
GO
 
CREATE PROCEDURE dbo.get_order_metrics_by_sales_person
	@sales_person_id INT
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT
		SalesOrderHeader.SalesOrderID,
		SalesOrderHeader.DueDate,
		SalesOrderHeader.ShipDate
	FROM Sales.SalesOrderHeader
	WHERE SalesOrderHeader.SalesPersonID = @sales_person_id
	OPTION (RECOMPILE);
END
GO

The results of this change are that the stored procedure runs with an excellent execution plan each time:

更改的结果是每次存储过程都以出色的执行计划运行：

Table ‘SalesOrderHeader’. Scan count 1, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'SalesOrderHeader'。扫描计数1，逻辑读50，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

It is important to note that if this query hint is utilized and the query later begins to be used more often, you will want to consider removing the hint to prevent excessive resource consumption by the query optimizer as it constantly generates new plans. OPTION RECOMPILE is useful in a specific set of circumstances and should be applied carefully, only when needed, and only when the query is not executed often. To review, OPTION (RECOMPILE) is best used when:

重要的是要注意，如果利用了该查询提示并且以后开始更频繁地使用查询，则您将要考虑删除该提示，以防止查询优化器不断生成新计划而浪费过多的资源。 OPTION RECOMPILE在特定情况下很有用，只有在需要时且仅在不经常执行查询时才应谨慎使用。要进行审查，在以下情况下最好使用OPTION（RECOMPILE）：

A query is executed infrequently.
查询很少执行。
Unpredictable parameter values result in optimal execution plans that vary greatly with each execution.
不可预测的参数值会导致最佳执行计划，该计划随每次执行而变化很大。
Other optimization solutions were unavailable or unsuccessful.
其他优化解决方案不可用或不成功。

As with all hints, use it with caution, and only when absolutely needed.

与所有提示一样，请谨慎使用它，并且仅在绝对需要时使用。

动态SQL (Dynamic SQL)

Rating: Potentially useful

评分： 潜在有用

While dynamic SQL can be an extremely useful tool, this is a somewhat awkward place to use it. By wrapping a troublesome TSQL statement in dynamic SQL, we remove it from the scope of the stored procedure and another execution plan will be generated exclusively for the dynamic SQL. Since execution plans are generated for specific TSQL text, a dynamic SQL statement with any variations in text will generate a new plan.

虽然动态SQL可能是一个非常有用的工具，但使用它有点尴尬。通过将麻烦的TSQL语句包装在动态SQL中，我们将其从存储过程的范围中删除，并且将专门为动态SQL生成另一个执行计划。由于执行计划是针对特定的TSQL文本生成的，因此，带有任何文本变化的动态SQL语句将生成新的计划。

For all intents and purposes, using dynamic SQL to resolve parameter sniffing is very similar to using a RECOMPILE hint. We are going to generate more execution plans with greater granularity in an effort to sidestep the effects of parameter sniffing. All of the caveats of recompilation apply here as well. We do not want to generate excessive quantities of execution plans as the resource cost to do so will be high.

出于所有目的和目的，使用动态SQL来解析参数嗅探与使用RECOMPILE提示非常相似。我们将以更大的粒度生成更多的执行计划，以避开参数嗅探的影响。重新编译的所有注意事项也适用于此。我们不想生成过多的执行计划，因为这样做的资源成本很高。

One benefit of this solution is that we will not create a new plan with each execution, but only when the parameter values change. If the parameter values don’t change often, then we will be able to reuse plans frequently and avoid the heavy repeated costs of optimization.

此解决方案的一个好处是，我们不会在每次执行时都创建一个新计划，而只会在参数值更改时创建一个新计划。如果参数值不经常更改，那么我们将能够频繁地重用计划，并避免了重复的优化成本。

A downside to this solution is that it is confusing. To a developer, it is not immediately obvious why dynamic SQL was used, so additional documentation would be needed to explain its purpose. While using dynamic SQL can sometimes be a good solution, it is the sort that should be implemented very carefully and only when we are certain we have a complete grasp of the code and business logic involved. As with RECOMPILE, if the newly created dynamic SQL suddenly begins to be executed often, then the cost to generate new execution plans may become a burden on resource consumption. Lastly, remember to cleanse inputs and ensure that string values cannot be broken or modified by apostrophes, percent signs, brackets, or other special characters.

这种解决方案的缺点是令人困惑。对于开发人员而言，为什么立即使用动态SQL尚不清楚，因此需要其他文档来解释其目的。尽管有时使用动态SQL可能是一个很好的解决方案，但是应该非常仔细地实施这种排序，只有当我们确定我们对所涉及的代码和业务逻辑有了完全的了解时，才应谨慎执行。与RECOMPILE一样，如果新创建的动态SQL突然开始频繁执行，那么生成新执行计划的成本可能会成为资源消耗的负担。最后，请记住清理输入，并确保字符串值不能被撇号，百分号，方括号或其他特殊字符破坏或修改。

Here is an example of this usage:

这是此用法的示例：

IF EXISTS (SELECT * FROM sys.procedures WHERE procedures.name = 'get_order_metrics_by_sales_person')
BEGIN
	DROP PROCEDURE dbo.get_order_metrics_by_sales_person;
END
GO
 
CREATE PROCEDURE dbo.get_order_metrics_by_sales_person
	@sales_person_id INT
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @sql_command NVARCHAR(MAX);
 
	SELECT @sql_command = '
		SELECT
			SalesOrderHeader.SalesOrderID,
			SalesOrderHeader.DueDate,
			SalesOrderHeader.ShipDate
		FROM Sales.SalesOrderHeader
		WHERE SalesOrderHeader.SalesPersonID = ' + CAST(@sales_person_id AS VARCHAR(MAX)) + ';
	';
	EXEC sp_executesql @sql_command;
END
GO

Our results here are similar to using OPTION (RECOMPILE), as we will get good IO and execution plans generated each time. To review wrapping a TSQL statement in dynamic SQL and hard-coding parameters into that statement can be useful when:

我们的结果类似于使用OPTION（RECOMPILE），因为我们将获得良好的IO和每次生成的执行计划。在以下情况下，检查将TSQL语句包装在动态SQL中并将参数硬编码到该语句中可能很有用：

A query is executed infrequently OR parameter values are not very diverse.
很少执行查询，或者参数值不是很多样化。
Different parameter values result in wildly different execution plans.
不同的参数值会导致完全不同的执行计划。
Other optimization solutions were unavailable or unsuccessful.
其他优化解决方案不可用或不成功。
OPTION (RECOMPILE) resulted in too many recompilations.
OPTION（RECOMPILE）导致过多的重新编译。

优化 (OPTIMIZE FOR)

Rating: Potentially useful, if you really know your code!

评分：如果您确实了解自己的代码，可能会很有用！

When we utilize this hint, we explicitly tell the query optimizer what parameter value to optimize for. This should be used like a scalpel, and only when we have complete knowledge of and control over the code in question. To tell SQL Server that we should optimize a query for any specific value requires that we know that all values used will be similar to the one we choose.

当我们利用此提示时，我们明确地告诉查询优化器要优化的参数值。仅当我们完全了解并控制有问题的代码时，才应像手术刀一样使用它。为了告诉SQL Server我们应该针对任何特定值优化查询，要求我们知道所使用的所有值都将与我们选择的值相似。

This requires knowledge of both the business logic behind the poorly performing query and any of the TSQL in and around the query. It also requires that we can see the future with a high level of accuracy and know that parameter values will not shift in the future, resulting in our estimates being wrong.

这需要了解性能差的查询背后的业务逻辑以及查询中及其周围的任何TSQL。它还要求我们能够以较高的准确性看到未来，并且知道参数值不会在未来发生变化，从而导致我们的估计错误。

One excellent use of this query hint is to assign optimization values for local variables. This can allow you to curtail the rough estimates that would otherwise be used. As with parameters, you need to know what you are doing for this to be effective, but there is at least a higher probability of improvement when our starting point is “blind guess”.

该查询提示的一种出色用法是为局部变量分配优化值。这可以让您减少原本会使用的粗略估计。与参数一样，您需要知道要做什么才能有效，但是当我们的出发点是“盲目猜测”时，至少有较高的改进可能性。

Note that OPTIMIZE FOR UNKNOWN has the same effect as using a local variable. The result will typically behave as if a rough statistical estimate were used and will not always be adequate for efficient execution. Here’s how its usage looks:

注意，“优化未知”与使用局部变量具有相同的效果。结果通常表现得好像使用了粗略的统计估计，并且并不总是足够有效执行。它的用法如下：

IF EXISTS (SELECT * FROM sys.procedures WHERE procedures.name = 'get_order_metrics_by_sales_person')
BEGIN
	DROP PROCEDURE dbo.get_order_metrics_by_sales_person;
END
GO
 
CREATE PROCEDURE dbo.get_order_metrics_by_sales_person
	@sales_person_id INT
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT
		SalesOrderHeader.SalesOrderID,
		SalesOrderHeader.DueDate,
		SalesOrderHeader.ShipDate
	FROM Sales.SalesOrderHeader
	WHERE SalesOrderHeader.SalesPersonID = @sales_person_id
	OPTION (OPTIMIZE FOR (@sales_person_id = 285));
END

With this hint in place, all executions will utilize the same execution plan based on the parameter @sales_person_id having a value of 285. OPTIMIZE FOR is most useful in these scenarios:

有了此提示，所有执行将基于参数@sales_person_id（值为285）使用相同的执行计划。在以下情况下，OPTIMIZE FOR最有用：

A query executes very similarly all the time.
查询总是很相似地执行。
We know our code very well and understand the performance of it thoroughly.
我们非常了解我们的代码，并且彻底了解了代码的性能。
Row counts processed and returned are consistently similar.
处理和返回的行计数始终相似。
We have a high level of confidence that these facts will not change in the future.
我们对这些事实将来不会改变抱有高度信心。

OPTIMIZE FOR can be a useful way to control variables and parameters to ensure optimal performance, but it requires knowledge and confidence in how a query or stored procedure operates so that we do not introduce a future performance problem when things change. As with all hints, use it with caution, and only when absolutely needed.

OPTIMIZE FOR是控制变量和参数以确保最佳性能的有用方法，但是它要求对查询或存储过程的运行方式有一定的了解和信心，以便在事情发生变化时不会引入将来的性能问题。与所有提示一样，请谨慎使用它，并且仅在绝对需要时使用。

创建一个临时存储过程 (Create a Temporary Stored Procedure)

Rating: Potentially useful, in very specific scenarios

评分： 在非常特定的情况下可能有用

One creative approach towards parameter sniffing is to create a temporary stored procedure that encapsulates the unstable database logic. The temporary proc can be executed as needed and dropped when complete. This isolates execution patterns and limits the lifespan of its execution plan in the cache.

一种用于参数嗅探的创新方法是创建一个临时存储过程，该过程封装了不稳定的数据库逻辑。临时proc可以根据需要执行，完成后将其删除。这样可以隔离执行模式并限制其执行计划在缓存中的寿命。

A temporary stored procedure can be created and dropped similarly to a standard stored procedure, though it will persist throughout a session, even if a batch or scope is ended:

可以创建和删除临时存储过程，类似于标准存储过程，尽管临时存储过程将在整个会话中持续存在，即使批次或作用域已结束：

CREATE PROCEDURE #get_order_metrics_by_sales_person
	@sales_person_id INT
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT
		SalesOrderHeader.SalesOrderID,
		SalesOrderHeader.DueDate,
		SalesOrderHeader.ShipDate
	FROM Sales.SalesOrderHeader
	WHERE SalesOrderHeader.SalesPersonID = @sales_person_id
	OPTION (OPTIMIZE FOR (@sales_person_id = 285));
END
GO
 
EXEC #get_order_metrics_by_sales_person @sales_person_id = 285;
EXEC #get_order_metrics_by_sales_person @sales_person_id = 0;
GO
DROP PROCEDURE #get_order_metrics_by_sales_person;
GO

When executed, the performance will mirror a newly created stored procedure, with no extraneous history to draw on for an execution plan. This is great for short-term tasks, temporary needs, releases, or scenarios in which data patterns change on a mid-term basis and can be controlled. Temporary stored procs can be declared as global as well by using “##” in front of the name, instead of “#”. This is ill-advised for the same reason that global temporary tables are discouraged, as they possess no security, and maintainability becomes a hassle across many databases or the entire server.

执行后，性能将镜像新创建的存储过程，没有多余的历史可用于执行计划。这对于短期任务，临时需求，发布或其中数据模式在中期基础上可以控制的场景非常有用。也可以通过在名称前面使用“ ##”而不是“＃”将临时存储的procs声明为全局变量。不建议这样做的原因是，不鼓励使用全局临时表，因为它们不具有安全性，并且可维护性在许多数据库或整个服务器上变得很麻烦。

The benefits of temporary stored procedures are:

临时存储过程的好处是：

Can control stored proc and plan existence easily.
可以轻松控制存储的过程并计划存在。
Facilitates accurate execution plans for data that is consistent in the short term, but varies long-term.
为短期内一致但长期变化的数据提供准确的执行计划。
Documents the need/existence for temporary business logic.
记录临时业务逻辑的需求/存在。

This is a little-known feature and few take advantage of it, but it can provide a useful way to guarantee good execution plans without the need to hack apart code too much in doing so.

这是一个鲜为人知的功能，很少有人利用它，但是它可以提供一种有用的方法来保证良好的执行计划，而无需过多地破解代码。

禁用参数嗅探（跟踪标志4136） (Disable Parameter Sniffing (Trace Flag 4136))

Rating: Occasionally useful, but typically a bad idea!

评分： 有时有用，但通常是个坏主意！

This trace flag disables plan reuse, and therefore stops parameter sniffing. It may be implemented on a server-wide basis or as a query hint option. The result is similar to adding OPTIMIZE FOR UNKNOWN to any query affected. Specific query hints override this, though, such as OPTIN (RECOMPILE) or OPTIMIZE FOR.

此跟踪标志禁用计划重用，因此停止参数嗅探。它可以在服务器范围内实现，也可以作为查询提示选项来实现。结果类似于将OPTIMIZE FOR UNKNOWN添加到任何受影响的查询中。但是，特定的查询提示会覆盖此内容，例如OPTIN（RECOMPILE）或OPTIMIZE FOR。

Like query hints, trace flags should be applied with extreme caution. Adjusting the optimizer’s behavior is rarely a good thing and will typically cause more harm than good. OPTIMIZE FOR UNKNOWN, like using local variables, will result in generic execution plans that do not use accurate statistics to make their decisions.

与查询提示一样，跟踪标志应格外小心。调整优化程序的行为很少是一件好事，通常会带来弊大于利的后果。优化未知，就像使用局部变量一样，将导致通用执行计划不使用准确的统计信息来做出决策。

Making rough estimates with limited data is already dangerous. Applying that tactic to an entire server or set of queries is likely to be more dangerous. This trace flag can be useful when:

用有限的数据进行粗略估计已经很危险。将该策略应用于整个服务器或一组查询可能更危险。在以下情况下，此跟踪标志很有用：

Your SQL Server has a unique use-case that you fully understand.
您SQL Server具有您完全理解的独特用例。
Plan reuse is undesired server-wide.
计划重用在服务器范围内是不希望的。
Usage patterns will not change in the foreseeable future.
使用模式在可预见的将来不会改变。

While there are a few legitimate uses of this trace flag in SQL Server, parameter sniffing is not the problem we want to try and solve with it. It is highly unlikely that this will provide a quality, long-term solution to a parameter-related optimization problem.

尽管在SQL Server中有一些合理的使用此跟踪标志的方法，但参数嗅探不是我们想要尝试解决的问题。这极不可能为与参数有关的优化问题提供高质量的长期解决方案。

改善业务逻辑 (Improve Business Logic)

Rating: Very, very good!

评分： 非常非常好！

Suboptimal parameter sniffing is often seen as an anomaly. The optimizer makes bad choices or solar flares somehow intersect with your query’s execution or some other bad thing happens that warrants quick & reckless actions on our part. More often than not, though, parameter sniffing is the result of how we wrote a stored procedure, and not bad luck. Here are a few common query patterns that can increase the chances of performance problems caused by parameter sniffing:

次佳参数嗅探通常被视为异常。优化器做出错误的选择或太阳耀斑以某种方式与您的查询的执行相交，或者发生其他一些不好的事情，这需要我们做出快速而鲁ck的动作。但是，参数嗅探通常是我们编写存储过程的结果的结果，而不是运气不好。以下是一些常见的查询模式，它们可能会增加由参数嗅探引起的性能问题的可能性：

太多的代码路径 (Too Many Code Paths)

When we add code branching logic to procedural TSQL, such as by using IF…THEN…ELSE, GOTO, or CASE statements, we create code paths that are not always followed, except when specific conditions are met.

当我们向过程性TSQL添加代码分支逻辑时（例如，使用IF…THEN…ELSE，GOTO或CASE语句），我们会创建并非总是遵循的代码路径，除非满足特定条件。

Since an execution plan is generated ahead of time, prior to knowing which code path will be chosen, it needs to guess as to what the most probable and optimal execution plan will be, regardless of how conditionals are met.

由于执行计划是提前生成的，因此在知道选择哪个代码路径之前，无论是否满足条件，它都必须猜测什么是最可能和最佳的执行计划。

Code paths are sometimes implemented using “switch” parameters that indicate a specific type of report or request to be made. Switch parameters may indicate if a report should return detailed data or summary data. They may determine which type of entity to process. They may decide what style of data to return at the end. Regardless of form, these parameters contribute heavily to poor parameter sniffing as the execution plan will not change when parameters do change. Use switch parameters cautiously, knowing that if many different values are passed in frequently, the execution plan will not change.

有时使用指示特定类型的报告或要进行的“切换”参数来实现代码路径。开关参数可以指示报告是否应返回详细数据或摘要数据。他们可以确定要处理的实体类型。他们可以决定最后返回哪种数据样式。无论采用何种形式，这些参数都会严重影响参数的嗅探效果，因为当参数更改时执行计划不会更改。请谨慎使用开关参数，因为如果频繁传入许多不同的值，执行计划将不会更改。

This is in no way to suggest that conditional code is bad, but that a stored procedure with too many code paths will be more susceptible to suboptimal parameter sniffing, especially if those code paths are vastly different in content and purpose. Here are a few suggestions for reducing the impact of this problem:

这绝不是暗示条件代码是错误的，而是具有太多代码路径的存储过程将更容易受到次优参数嗅探的影响，尤其是在那些代码路径的内容和用途截然不同的情况下。以下是一些减少此问题影响的建议：

Consider breaking out large conditional sections of TSQL into new stored procedures. A large block of important code may very well be more appropriate as its own stored procedure, especially if it can be reused elsewhere.
考虑将TSQL的较大的条件部分分解为新的存储过程。一大堆重要代码很可能更适合作为其自己的存储过程，尤其是如果可以在其他地方重用它的话。
Move business branching logic into code. Instead of making a stored procedure decide what data to return or how to return it, have the application decide and let SQL Server manage what it does best: reading and writing of data! The purpose of a database is to store and retrieve data, not to make important business decisions or beautify the data.
将业务分支逻辑移到代码中。让应用程序决定并让SQL Server管理最擅长的是：读取和写入数据，而不是让存储过程决定要返回什么数据或如何返回数据。数据库的目的是存储和检索数据，而不是做出重要的业务决策或美化数据。
Avoid unnecessary conditionals, especially GOTO. This causes execution to jump around and is not only confusing for developers to understand, but makes optimization challenging for SQL Server.
避免不必要的条件，尤其是GOTO。这会导致执行跳来跳去，不仅使开发人员难以理解，而且使SQL Server的优化面临挑战。

参数太多 (Too Many Parameters)

Each parameter adds another level of complexity to the job of the query optimizer. Similar to how a query becomes more complex with each table added, a stored procedure will become more challenging to optimize a plan for with each parameter that is added.

每个参数给查询优化器的工作增加了另一层次的复杂性。与如何在添加每个表时使查询变得更复杂相似，对于为添加了每个参数来优化计划而言，存储过程将变得更具挑战性。

An execution plan will be created for the first set of parameters and reused for all subsequent sets, even if the values change significantly. Like when too many code paths exist in a stored procedure, it becomes challenging to pick a good execution plan will also happen to be good for all possible future executions.

即使值发生了显着变化，也将为第一组参数创建一个执行计划，并将其用于所有后续组。就像存储过程中存在太多代码路径一样，选择一个好的执行计划也将对将来所有可能的执行都有利，这具有挑战性。

A stored procedure with ten or twenty or thirty parameters may be trying to accomplish too many things at once. Consider splitting the proc into smaller, more portable ones. Also, consider removing parameters that are not necessary. Sometimes a parameter will always have the same value, not get used, or have its value overridden later in the proc. Sometimes the logic imposed by a specific parameter is no longer needed and it can be removed with no negative impact.

具有十，二十或三十个参数的存储过程可能试图一次完成太多事情。考虑将proc分成更小巧，更便于携带的proc。另外，请考虑删除不必要的参数。有时，参数将始终具有相同的值，不会被使用，或者稍后在proc中覆盖其值。有时不再需要由特定参数强加的逻辑，并且可以删除它而不会带来负面影响。

Reducing the number of parameters in a stored procedure will automatically reduce the potential for parameter sniffing to become problematic. It may not always be an easy solution, but it’s the simplest way to solve this problem without having to resort to hacks or trickery.

减少存储过程中参数的数量将自动减少参数嗅探成为问题的可能性。它可能并不总是一个简单的解决方案，但是它是解决此问题的最简单方法，而不必诉诸于黑客或骗局。

存储过程过大。又名：数据库中的业务逻辑太多 (Overly Large Stored Procedure. AKA: Too Much Business Logic in the Database)

Even if the parameter list is short, a very long stored procedure will have more decisions to make and more potential for an execution plan to not be the one-size-fits-all solution. If you’re running into a performance problem on line 16,359, you may want to consider dividing up the stored procedure into smaller ones. Alternatively, a rewrite that reduces the amount of necessary code can also help.

即使参数列表很短，一个很长的存储过程也将有更多的决策要执行，并且执行计划可能不是千篇一律的解决方案。如果您在第16359行遇到性能问题，则可能需要考虑将存储过程分成较小的过程。另外，减少必要代码量的重写也可能会有所帮助。

Oftentimes new features in SQL Server allow for code to be written more succinctly. For example, MERGE, OUTPUT, or common-table expressions can take long and complex TSQL statements and make them shorter and simpler.

通常，SQL Server中的新功能使代码编写更加简洁。例如，MERGE，OUTPUT或公共表表达式可以使用长而复杂的TSQL语句，并使它们更短，更简单。

If a stored proc is not long due to having many code paths or too many parameters, it may be due to using the database as a presentation tool. SQL Server, like all RDBMS, is optimized for the quick storage and retrieval of data. Formatting, layout, and other presentation considerations can be made in SQL Server, but it simply isn’t what it is best at. While the query optimizer generally has no trouble managing queries that adjust formatting, color, and layout as they are relatively simple in nature, we still incrementally add more complexity to a stored proc when we let it manage these aspects of data presentation.

如果存储的proc由于包含许多代码路径或太多参数而时间不长，则可能是由于使用数据库作为表示工具而导致的。与所有RDBMS一样，SQL Server已针对快速存储和检索数据进行了优化。可以在SQL Server中进行格式，布局和其他表示方面的考虑，但这并不是最擅长的。虽然查询优化器本质上相对简单，但是管理调整格式，颜色和布局的查询通常不会遇到麻烦，但是当我们让它管理数据表示的这些方面时，我们仍然会为存储的proc逐渐增加更多的复杂性。

Another reason why a stored procedure can become too long is because it contains too much business logic. Decision-making, presentation, and branching all are costly and difficult to optimize a universal execution plan for. Reporting applications are excellent at managing parameters and decision-making processes. Application code is built for branching, looping, and making complex decisions. Pushing business logic from stored procedures, functions, views, and triggers into applications will greatly simplify database schema and improve performance.

存储过程可能变得太长的另一个原因是，它包含太多的业务逻辑。决策，演示和分支都非常昂贵，并且难以为其优化通用执行计划。报告应用程序在管理参数和决策过程方面非常出色。应用程序代码是为分支，循环和做出复杂决策而构建的。将业务逻辑从存储过程，函数，视图和触发器推送到应用程序中，将大大简化数据库架构并提高性能。

Reducing the size of a stored procedure will improve the chances that the execution plan generated for it is more likely to be good for all possible parameter values. Removing code paths and parameters helps with this, as does removing presentation-layer decisions that are made within the proc.

减小存储过程的大小将提高为该过程生成的执行计划更有可能适合所有可能的参数值的机会。删除代码路径和参数将对此有所帮助，删除proc中所做的表示层决策也是如此。

结论 (Conclusion)

To wrap up our discussion of parameter sniffing, it is important to be reminded that this is a feature and not a bug. We should not be automatically seeking workarounds, hacks, or cheats to make the problem go away. Many quick fixes exist that will resolve a problem for now and allow us to move on to other priorities. Before adding query hints, trace flags, or otherwise hobbling the query optimizer, consider every alternate way to improve performance. Local variables, dynamic SQL, RECOMPILE, and OPTIMIZE for are too often cited as the best solutions, when in fact they are typically misused.

要结束对参数嗅探的讨论，必须提醒您，这是功能而非错误，这一点很重要。我们不应该自动寻求解决方法，破解或作弊手段以使问题消失。存在许多快速解决方案，这些解决方案现在可以解决问题，并允许我们继续进行其他工作。在添加查询提示，跟踪标志或使查询优化器陷入困境之前，请考虑所有其他提高性能的方法。局部变量，动态SQL，RECOMPILE和OPTIMIZE经常被引用为最佳解决方案，而实际上它们通常被滥用。

When a performance problem due to parameter sniffing occurs frequently, it is more likely the result of design and architecture decisions than a query optimizer quirk. When implementing hints, trace flags, or other targeted solutions, be sure they are durable enough to withstand the test of time. A good fix now that breaks performance in 6 months is not a very good fix. If special cases for parameters exist, consider handling them separately by breaking a large stored procedure into smaller ones, removing parameters, or reducing the implementation of business logic in the database.

当由于参数嗅探导致的性能问题频繁发生时，与查询优化器的怪癖相比，更有可能是设计和体系结构决策的结果。在实现提示，跟踪标志或其他目标解决方案时，请确保它们足够耐用以承受时间的考验。现在，一个好的修复程序会在6个月内使性能下降，这不是一个很好的修复程序。如果存在参数的特殊情况，请考虑通过将大型存储过程分解为较小的存储过程，删除参数或减少数据库中业务逻辑的实现来分别处理它们。

By taking the time to more fully analyze a parameter sniffing problem, we can improve overall database performance and the scalability of an application. Instead of creating hacks to solve problems quickly, we can make code more durable at little additional cost to developers. The result will be saved time and resources in the long run, and an application that performs more consistently.

通过花时间更全面地分析参数嗅探问题，我们可以提高数据库的整体性能和应用程序的可伸缩性。无需创建黑客来快速解决问题，我们可以使代码更持久，而对开发人员却几乎没有增加成本。从长远来看，结果将节省时间和资源，并使应用程序的性能更加稳定。