参数Sniffing问题

 

SQL Server使用统计对象的histogram来估计谓词predicate的集势,并利用该信息来生成优化的执行计划。查询优化器通过检查

查询参数的值的方式来进行优化,这种模式也称之为“参数探查(parameter sniffing)”,其利在于:根据查询的当前参数值产生的执行计划可以提高应用程序的性能,我们也知道:计划缓存可以存储这些执行计划,以便相同查询再次执行时可以重用这些缓存的执行计划,这极大地节省了时间和CPU资源(查询优化器不需要再次编译)。

 

虽然查询优化器可以和计划缓存很好地协同工作,不过,偶尔也会发生一些性能问题。由于查询优化器可以为同一个查询产生多个执行计划,这主要取决于参数值、缓存,就该查询的一个实例而言(得益于better plan),仅重用这些计划的某一个可能是个性能问题,不过,使用显式的参数查询问题是已知问题,使用存储过程就是这样一个例子。下面我通过一些例子来介绍该问题并给出一些修复的建议。

 

首先,在AdventureWorks数据库中创建以下存储过程:

CREATE PROCEDURE test (@pid int)

AS

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

 

接着,开启显示执行计划,运行以下语句来执行存储过程:

EXEC test @pid = 897

 

由于查询优化器估计满足该参数的查询有一小部分记录,产生了以下的执行计划,该计划使用“索引查找”运算符,以便快速通过现有的非聚集索引来找到记录,使用“键查询”运算符,基于查询请求的其他列来对表进行搜索。

 

 

从上图可以看出,索引查找与键查询组合查询在本例中是不错的选择,原因是查询具有较高的选择性,下面用不同的ProductID再运行一次存储过程,为方便查看结果,开启SET STATISTICS ON语句来显示磁盘活动的IO数:

SET STATISTICS IO ON

go

EXEC test @pid = 870

go

IO统计:

(4688 row(s) affected)

Table 'SalesOrderDetail'. Scan count 1, logical reads 14377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

当第二次执行存储过程,SQL Server需要执行约14000次的逻辑读,而SalesOrderDetail表约有1240个页,显然其所需开销比扫描整个表的开销大10倍。那么,第二次的执行为何会如此慢?当SQL Server第二次执行时,它使用了第一次执行产生的执行计划,对于本例来说,对于第一个查询中使用的参数而产生的计划是优化的,但不适用于第二个查询。

 

现在清除计划缓存从内存中删除当前的执行计划,再次用先前的参数运行存储过程:

DBCC freeproccache

EXEC test @pid=870

 

由于计划缓存中没有可用的计划,SQL Server使用ProductID870进行优化,并为其生成一个优化的执行计划,新的计划则使用“聚集索引扫描”,如下图所示:

 

 

上面执行的I/O数如下:

(4688 row(s) affected)

Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

从执行的IO信息可以知道,这次的执行仅扫描了1241次逻辑读,相比于先前的14337个页来说要小得多。

 

当然,这并不是说使用存储过程有问题,需要知道有这样一个问题:当调用存储过程时使用不同的参数时,其性能会随着其参数不同而发生变化,针对此问题,有一些可用的方案供考虑。

 

Typical参数的优化

 

有一种可能情况:当某个查询在相同的执行计划中多次执行受益,极大地减小了优化的开销,对于这种情况,可以使用OPTIMIZE FOR 提示(此提示在SQL SERVER 2005引入)。对于多数可能的值来说,使用此提示可以生成优化的计划,只有使用典型的参数不使用最佳的计划。

 

假设,几乎所有我们的存储过程都使用索引查找和键查询运算,将会得到受益,你可以用以下的方式来重写存储过程:

alter PROCEDURE test (@pid int)

AS

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

OPTION (OPTIMIZE FOR (@pid = 897))

 

当第一次运行存储过程时,优化器会针对897进行优化,而不在乎在执行时指定的参数值,该计划则存储在计划缓存中,用于后续的存储过程调用。用以下值再次进行测试:

 

EXEC test @pid = 870

你可以通过其执计划查看详细的信息,如下图所示:

<ParameterList>

<ColumnReference Column="@pid" ParameterCompiledValue="(897)" ParameterRuntimeValue="(870)" />

</ParameterList>

使用此提示的好处是可以极大地控制哪一个计划存储在计划缓存,以备重用,在先前的例子中,你对两个执行计划中的哪一个存储在计划缓存中没有控制。

 

每执行时优化

 

若没有可用的参数或计划可用,你还可以对每一次的执行进行优化查询,但这样要付出一定的优化开销,使用RECOMPILE提示来优化。

ALTER PROCEDURE test (@pid int)

AS

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

OPTION (RECOMPILE)

 

再次运行存储过程:

 

EXEC test @pid = 897

 

<ParameterList>

<ColumnReference Column="@pid" ParameterCompiledValue="(897)" ParameterRuntimeValue="(897)" />

</ParameterList>

 

这次查询在执行时已经进行优化,根据当前的参数值来生成一个优化的查询。

 

 

在先前的旧版本SQL Server中,也采取一些其他的方法实现:使用“本地变量”来替换“参数探测”问题。

posted @ 2011-10-14 14:06  Yuejun Sun  阅读(...)  评论(...编辑  收藏