参数探测（Parameter Sniffing）与影响计划重用的SET选项

上一篇 / 下一篇 2011-10-19 10:04:00 / 个人分类：SQL Server

原文：http://www.benjaminnevarez.com/2011/09/parameter-sniffing-and-plan-reuse-affecting-set-options/

备注：翻译不当，请指出或参考原文。

在排查性能问题时经常被问到的一个有趣问题是：开发者说应用程序中的存储过程执行超时或花费很长时间执行，然而在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复制一些数据。

CREATEDATABASETest
GO
USETest
GO
SELECT*INTOdbo.SalesOrderDetail
FROMAdventureWorks.Sales.SalesOrderDetail
GO
CREATENONCLUSTEREDINDEXIX_SalesOrderDetail_ProductID
ONdbo.SalesOrderDetail(ProductID)
GO
CREATEPROCEDUREtest (@pidint)
AS
SELECT*FROMdbo.SalesOrderDetail
WHEREProductID = @pid

接下来，我们使用两个不同的应用程序（.NET程序和Management Studio）来执行存储过程，对于测试来说，我们假定“表扫描”的计划是性能差的计划，而使用“索引查找/RID查询”的计划是优化的。

首先使用以下命令来清除计划缓存：

DBCCFREEPROCCACHE

接着，从命令行运行.NET应用程序，并提供参数值：870（注意：此应用程序调用先前创建的test存储过程）

C:\TestApp\test
Enter ProductID: 870

此时，我们可以通过运行以下脚本来观察计划缓存：

SELECTplan_handle, usecounts, pvt.set_options
FROM(
SELECTplan_handle, usecounts, epa.attribute, epa.value
FROMsys.dm_exec_cached_plans
OUTERAPPLY sys.dm_exec_plan_attributes(plan_handle)ASepa
WHEREcacheobjtype ='Compiled Plan')ASecpa
PIVOT (MAX(ecpa.value)FORecpa.attributeIN("set_options", "objectid"))ASpvt
wherepvt.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中使用类似下面的存储过程来排查问题：

EXECtest @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*fromsys.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>
<ColumnReferenceColumn="@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>
<ColumnReferenceColumn="@pid" ParameterCompiledValue="(898)" />
</ParameterList>

从上面的信息看出，ARITHABORT SET选项使用了不同的值，通过上面的图形计划可看出，第一个参数值为870的使用了“表扫描”，而第二使用了“索引查找/RID查询”。

既然分析了计划，可以重编译存储过程来强制优化以使应用程序马上使用较好的计划（注意：这并不是最终的方案）。

sp_recompile test

到目前为止，你已经了解了参数探测的问题，接下来该如何解决呢？可以参考先前的文章：参数探测问题和Optimize for Unknown工作原理，另外也可参考“禁用参数探测”，不过通常不建议使用。

最后，提供以下脚本来显示特定set_options值的信息：

declare@set_optionsint= 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#代码：

usingSystem;
usingSystem.Data;
usingSystem.Data.SqlClient;

classTest
{
staticvoidMain()
{
SqlConnection cnn =null;
SqlDataReader reader =null;

try
{
Console.Write("Enter ProductID: ");
stringpid = Console.ReadLine();

cnn =newSqlConnection("Data Source=(local);Initial Catalog=Test;
Integrated Security=SSPI");
SqlCommand cmd =newSqlCommand();
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)
{
throwe;
}
finally
{
if(cnn !=null)
{
if(cnn.State != ConnectionState.Closed)
cnn.Close();
}
}
}
}.