参数探测（Parameter Sniffing）与影响计划重用的SET选项
备注：翻译不当，请指出或参考原文。
在排查性能问题时经常被问到的一个有趣问题是：开发者说应用程序中的存储过程执行超时或花费很长时间执行，然而在Management Studio环境中执行速度很快，即便相同参数也如此。虽然对于此类问题发生的原因有多种，包括锁定，最常见的一种是采用组合参数而进行优化的BAD执行计划，也有可能会误导你运行sp_recompile来执行强制优化以使应用程序继续运行，这显然并不能真正修复问题，问题有可能还会发生。你也看到过诸如“更新统计”、“重建索引”等操作的方法来修复突如其来的问题。不过，这些方法只是暂时的方法；明显地最佳的方法是通过导致此类问题的BAD执行计划进行深入的分析，以便提供更好的解决方案，在本篇，我会向你介绍如何实现的方法。
首先，需要引入一点背景知识，记住：通常查询优化器是开销比较大的运算，为了避免优化开销，计划缓存会尽可能使内存中的执行计划重用；这样一来，存储过程执行数千次，仅需要一次优化，不过，若采用相同存储过程和不同SET选项的连接在执行时，有可能产生新的执行计划，而并非采用已缓存的执行计划。以下列出了一些影响执行计划重用的SET选项：
ANSI_NULL_DFLT_OFF
ANSI_NULL_DFLT_ON
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
DATEFIRST
DATEFORMAT
FORCEPLAN
LANGUAGE
NO_BROWSETABLE
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER
不过，不同的管理工具或开发工具，像Management Studio、ADO.NET、sqlcmd，这些默认都采用了不同的SET选项，像上面提到的选项中，最常引起问题的一个就是：ARITHABORT,在ADO.NET中，其状态是OFF，在Management Studio中，其状态是ON，因此，Managemnet Studio和web前端应用程序采用了不同的缓存计划。
现在让我们一起来看一下如何在实际问题验证“参数探测”的问题，如何析取执行计划来分析优化时采用的参数及SET选项，下面我们创建一个TEST的测试数据库，从AdventureWorks复制一些数据。
CREATE DATABASE Test
GO
USE Test
GO
SELECT * INTO dbo.SalesOrderDetail
FROM AdventureWorks.Sales.SalesOrderDetail
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID
ON dbo.SalesOrderDetail(ProductID)
GO
CREATE PROCEDURE test (@pid int)
AS
SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = @pid
接下来，我们使用两个不同的应用程序（.NET程序和Management Studio）来执行存储过程，对于测试来说，我们假定“表扫描”的计划是性能差的计划，而使用“索引查找/RID查询”的计划是优化的。
首先使用以下命令来清除计划缓存：
DBCC FREEPROCCACHE
接着，从命令行运行.NET应用程序，并提供参数值：870（注意：此应用程序调用先前创建的test存储过程）
C:\TestApp\test
Enter ProductID: 870
此时，我们可以通过运行以下脚本来观察计划缓存：
SELECT plan_handle, usecounts, pvt.set_options
FROM (
SELECT plan_handle, usecounts, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
where pvt.objectid = object_id('dbo.test')
执行的结果如下所示：
plan_handle usecounts set_options
0x0500110020C96C7EB8407115000000000000000000000000 1 251
从上面的输出结果可以看出，计划缓存中存在一条执行计划，根据usecounts列可以知道，该计划使用了1次，set_options值为251,此属性也可以使用sys.dm_exec_plan_attributes DMF来获得。由于存储过程是第一次执行，这里采用参数值为870，这种情况下，使用了“表扫描”的方式为其创建了执行计划。现在使用返回较少记录的参数运行.NET应用程序：
C:\TestApp\test
Enter ProductID: 898
如果执行先前的查看计划缓存的查询，会注意到，缓存的计划使用了2次，显然，对于第二个参数并未进行优化。
plan_handle usecounts set_options
0x0500110020C96C7EB8407115000000000000000000000000 2 251
此时，开发人员可能尝试在Management Studio中使用类似下面的存储过程来排查问题：
EXEC test @pid = 898
现在，开发人员惊奇地发现SQL Server返回了一个较好的执行计划，并助查询执行很快，再次运行先前查看计划缓存的查询，如下所示：
plan_handle usecounts set_options
0x0500110020C96C7EB840A210000000000000000000000000 1 4347
0x0500110020C96C7EB8407115000000000000000000000000 2 251
这次你发现，对于在Management Studio中执行的查询，生成了一条新的执行计划，并且采用了不同的set_options。
你可能会问，接下来怎么做？现在需要查究计划，并研究优化时使用的set选项和参数值，使用set_option值为251的计划缓存的plan_handle来运行如下查询：
select * from sys.dm_exec_query_plan(0x0500110020C96C7EB8407115000000000000000000000000)
在计划的开始处，可以找到SET选项值如下：
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false"
CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true"
ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
在结束处可以找到使用的参数值：
<ParameterList>
<ColumnReference Column="@pid" ParameterCompiledValue="(870)" />
</ParameterList>
同样地，查看第二个计划缓存的SET选项值：
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true"
ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
参数值：
<ParameterList>
<ColumnReference Column="@pid" ParameterCompiledValue="(898)" />
</ParameterList>
从上面的信息看出，ARITHABORT SET选项使用了不同的值，通过上面的图形计划可看出，第一个参数值为870的使用了“表扫描”，而第二使用了“索引查找/RID查询”。
既然分析了计划，可以重编译存储过程来强制优化以使应用程序马上使用较好的计划（注意：这并不是最终的方案）。
sp_recompile test
最后，提供以下脚本来显示特定set_options值的信息：
declare @set_options int = 251
if ((1 & @set_options) = 1) print 'ANSI_PADDING'
if ((4 & @set_options) = 4) print 'FORCEPLAN'
if ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL'
if ((16 & @set_options) = 16) print 'ANSI_WARNINGS'
if ((32 & @set_options) = 32) print 'ANSI_NULLS'
if ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER'
if ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON'
if ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF'
if ((512 & @set_options) = 512) print 'NoBrowseTable'
if ((4096 & @set_options) = 4096) print 'ARITH_ABORT'
if ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT'
if ((16384 & @set_options) = 16384) print 'DATEFIRST'
if ((32768 & @set_options) = 32768) print 'DATEFORMAT'
if ((65536 & @set_options) = 65536) print 'LanguageID'
C#代码：
using System;
using System.Data;
using System.Data.SqlClient;
class Test
{
static void Main()
{
SqlConnection cnn = null;
SqlDataReader reader = null;
try
{
Console.Write("Enter ProductID: ");
string pid = Console.ReadLine();
cnn = new SqlConnection("Data Source=(local);Initial Catalog=Test;
Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "dbo.test";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pid", SqlDbType.Int).Value = pid;
cnn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[0]);
}
return;
}
catch (Exception e)
{
throw e;
}
finally
{
if (cnn != null)
{
if (cnn.State != ConnectionState.Closed)
cnn.Close();
}
}
}
}.
