SQL Server OPTION (OPTIMIZE FOR UNKNOWN) 测试总结

关于 SQL Server 的查询提示 OPTION (OPTIMIZE FOR UNKNOWN) ，它是解决参数嗅探的方法之一。 而且对应的 SQL 语句会缓存，不用每次都重编译。关键在于它的执行计划的准确度问题， 最近在优化的时候，和同事对于这个查询提示（ Query Hint） 有一点分歧，遂动手实验验证、总结了一些东西。

关于提示 OPTION (OPTIMIZE FOR UNKNOWN) ，它会利用统计数据和标准算法生成一个折中、稳定的执行计划，但是它是无法利用直方图（ histogram ）信息来生成执行计划。官方文档的介绍如下：

OPTIMIZE FOR 编译和优化查询时提示查询优化器对本地变量使用特定值。仅在查询优化期间使用该值，在查询执行期间不使用该值。

UNKNOWN

指定查询优化器在查询优化期间使用统计数据而不是初始值来确定局部变量的值。 OPTIMIZE FOR 可以抵消优化器的默认参数检测行为，也可在创建计划指南时使用

OPTIMIZE FOR UNKNOWN

指示查询优化器在查询已经过编译和优化时为所有局部变量使用统计数据而不是初始值，包括使用强制参数化创建的参数。有关强制参数化的详细信息，请参阅 强制参数化 。

如果在同一查询提示中使用 OPTIMIZE FOR @variable\_name = literal_constant 和 OPTIMIZE FOR UNKNOWN ，则查询优化器将对特定的值使用指定的 literal_constant ，而对其余变量使用 UNKNOWN 。这些值仅用于查询优化期间，而不会用于查询执行期间 。

OPTIMIZE FOR UNKNOWN 是否会用直方图数据呢？ 不会， OPTIMIZE FOR UNKNOWN 只会用简单的统计数据。我们以 how-optimize-for-unknown-works 这篇博客中的例子来演示一下， 下面测试环境为 SQL Server 2014 ，数据库为 AdventureWorks2014

CREATE PROCEDURE test (@pid int)
AS
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = @pid OPTION (OPTIMIZE FOR UNKNOWN);

为了消除统计信息不准确会干扰测试结果，我们手工更新一下统计信息。

UPDATE STATISTICS [Sales] . [SalesOrderDetail] WITH FULLSCAN ;

我们在 SSMS 里面点击 “ 包含实际执行计划 ” 选项，然后测试执行该存储过程，如下截图所示： 执行计划居然走聚集索引扫描

EXEC test @pid = 709

Filter 里面过滤的记录为 456.079 ，而实际上 ProductID=709 的记录有 188 条，那么优化器是怎么估计判断记录数为 456.709 的呢？

其实优化器是这样来估计的：它使用 ProductID 列的密度 (Density)* Rows 来计算的

SELECT 0.003759399 * 121317 ~= 456.079008483 ~= 456.079

而 ProductID 列的密度 (Density) 的计算是这样来的 :

ProductID 的值有 266 个，可以用下面 SQL 获取 ProductID 的值个数

SELECT COUNT( DISTINCT ProductID) FROM   Sales . SalesOrderDetail

SELECT 1.0 / 266   ~=   0.003759

然后你可以使用任意不同的参数测试，例如 707 、 712......, 你会发现使用查询提示 OPTION (OPTIMIZE FOR UNKNOWN) 后，优化器会总是使用相同的执行计划。也就是说这个查询提示生成的执行计划是一个 “ 折中的执行计划 ” ，对于数据分布倾斜的比较厉害（数据分布极度不均衡）的情况下，

是极度不建议使用查询提示 OPTION (OPTIMIZE FOR UNKNOWN) 的。

本人曾经一度对是使用 OPTION （ RECOMPILE ）还是 OPTION (OPTIMIZE FOR UNKNOWN) 感到困惑和极度难以取舍，后面总结了一下：

1 ：执行不频繁的存储过程，使用 OPTION （ RECOMPILE ）要优先与 OPTION (OPTIMIZE FOR UNKNOWN)

2 ：执行频繁的存储过程，使用 OPTION (OPTIMIZE FOR UNKNOWN) 要由于 OPTION （ RECOMPILE ）

3 ：数据分布倾斜的厉害的情况下，优先使用 OPTION （ RECOMPILE ）

    4: 使用 OPTION (OPTIMIZE FOR UNKNOWN) 会生成一个稳定、统一的执行计划，如果这个执行计划的效率基本能满足用户需求，那么优先使用 OPTION (OPTIMIZE FOR UNKNOWN)

参考资料：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms181714(v=sql.100 )

http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/

https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/