ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 参数探测(Parameter Sniffing)与影响计划重用的SET选项

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

原创 Linux操作系统 作者:bigholy 时间:2011-10-19 10:04:00 0 删除 编辑
备注:翻译不当,请指出或参考原文。
  
     在排查性能问题时经常被问到的一个有趣问题是:开发者说应用程序中的存储过程执行超时或花费很长时间执行,然而在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选项值如下:

true" ARITHABORT="false" 
CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true"
ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
image

在结束处可以找到使用的参数值:


Column="@pid" ParameterCompiledValue="(870)" />

同样地,查看第二个计划缓存的SET选项值:
true" ARITHABORT="true" 
CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true"
ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
image
参数值:

Column="@pid" ParameterCompiledValue="(898)" />

从上面的信息看出,ARITHABORT SET选项使用了不同的值,通过上面的图形计划可看出,第一个参数值为870的使用了“表扫描”,而第二使用了“索引查找/RID查询”。
 
既然分析了计划,可以重编译存储过程来强制优化以使应用程序马上使用较好的计划(注意:这并不是最终的方案)。
sp_recompile test

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

最后,提供以下脚本来显示特定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();
}
}
}
}.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-709334/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    83554