人本善良

导航

mssqlservers数据嗅探

SQL Server - 最佳实践 - 参数嗅探问题 转。

 

文章来自：https://yq.aliyun.com/articles/61767

 

先说我的问题，最近某个存储过程，暂定名字：sp_a 总是执行超时，sp_a带有一个参数，暂定名为 para1 varchar(50)，刚开始以为 是 sp_a 的语句优化得不够好，毕竟当时写的时候都是能用就成。

然后啪啪啪优化存储过程，写完了一测试，速度蛮快的，秒出结果。然后上线之。。。我写存储过程的时候，是直接定义一个参数，然后在里面写代码，没有创建存储过程去执行存储过程，大概意思是在查询分析器里面像下面这样写，写完了F5测试，速度快就感觉可以。

declare @para1 varchar(50)

set @para1='11111'

select * from xxxx

left join 

inner join 

 

 

 

上线后因为这个功能是很不常用没注意，今天用到了，还是经常性超时。我第一反映是，上次优化的不够吗？然后就去在sql 查询分析器里面用  sp_a ‘11111’ 这样去执行，结果很久不出结果。

然后我就觉得奇怪，就去把 sp_a 的代码全部扣出来，然后写死一个参数去执行这个sql段，结果秒出。

感觉问题好奇怪，明明语句效率不会低，但是存储过程却不出结果。

去dba的一个交流群丢出问题，别人给我的方向是参数嗅探这块，但是按这篇文章各种操作后，还是一样没有用，我就差没重启数据库服务器了

按道理来说，如果是参数嗅探，它老早缓存了执行计划的话，我新建了一个存储过程名为 sp_aNew 参数和代码一样，应该不会卡才对，结果还是卡了。。。。

 

然后我按我最笨的办法，一步步修改存储过程，把里面的left join inner join 等操作一个个去掉，发现去到两个 left join 的时候就秒出结果了，那么问题就出在了这两个jeft join 

然后那两个left join 的作用是 判断数据是否存在于某个表，所以我改为i了 where not exists 也可以有类似效果，然后存储过程就秒出结果了。。。。

神奇的还在后头，后来我想要复现一下，把代码恢复到原来的，奶奶的竟然不会了。。。。。

所以疑问一堆：一，假设是我的存储过程代码效率 不够，那么为什么我写死参数后直接执行秒出结果？

二，假设是参数嗅探，为什么新建一个存储过程后，还是依然超时？不是说参数嗅探是因为这个存储过程的执行计划被缓存吗？那我新建过一个按道理不会才对啊。。。。。

三、我改完了那个 where 条件后，感觉应该是重建了执行计划，为什么我新的存储过程也不会卡了。。。。也就是说 sp_a存储过程和 sp_aNew 这个存储过程有关联吗？

感觉是不是它不针对存储过程名，而是针对表？

有知道的人，希望回复我，解答一下我的疑惑，谢谢。

问题虽然暂时解决，但是这个问题没有得到合理的解释，总感觉悬着，先记录一下，万一以后碰到类似的还能先找到临时的解决办法。。。。。

 

 

 

 

 

title: SQL Server - 最佳实践 - 参数嗅探问题

author: 风移

摘要

MSSQL Server参数嗅探既是一个涉及知识面非常广泛，又是一个比较难于解决的课题，即使对于数据库老手也是一个比较头痛的问题。这篇文章从参数嗅探是什么，如何产生，表象是什么，会带来哪些问题，如何解决这五个方面来探讨参数嗅探的来龙去脉，期望能够将SQL Server参数嗅探问题理清楚，道明白。

什么参数嗅探

当SQL Server第一次执查询语句或存储过程（或者查询语句与存储过程被强制重新编译）的时候，SQL Server会有一个进程来评估传入的参数，并根据传入的参数生成对应的执行计划缓存，然后参数的值会伴随查询语句或存储过程执行计划一并保存在执行计划缓存里。这个评估的过程就叫着参数嗅探。

参数嗅探是如何产生的

SQL Server对查询语句编译和缓存机制是SQL语句执行过程中非常重要的环节，也是SQLOS内存管理非常重要的一环。理由是SQL Server对查询语句编译过程是非常消耗系统性能，代价昂贵的。因为它需要从成百上千条执行路径中选择一条最优的执行计划方案。所以，查询语句可以重用执行计划的缓存，避免重复编译，以此来节约系统开销。这种编译查询语句，选择最优执行方案，缓存执行计划的机制就是参数嗅探问题产生的理论基础。

参数嗅探的表象

以上是比较枯燥的理论解释，这里我们来看看两个实际的例子。在此，我们以AdventureWorks2008R2数据库中的Sales.SalesOrderDetail表做为我们测试的数据源，我们挑选其中三个典型的产品，ProductID分别为897，945和870，分别对应的订单总数为2，257和4688。
挑选的方法如下：

use AdventureWorks2008R2
GO

;WITH DATA
AS(
select ProductID, COUNT(1) as order_count, rowid = ROW_NUMBER() OVER(ORDER BY COUNT(1) asc)
from Sales.SalesOrderDetail with(nolock)
group by ProductID
)
SELECT *
FROM DATA
WHERE rowid in (1, 266, 133)

得到如下结果：
01__Product

查询语句的参数嗅探表象

接下来，我们看三个非常相似的查询语句（仅传入的参数值不同）的执行计划有什么差异。
三个查询语句：

use AdventureWorks2008R2
GO
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail WITH(NOLOCK)
WHERE ProductID = 897;

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail WITH(NOLOCK)
WHERE ProductID = 945;

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail WITH(NOLOCK)
WHERE ProductID = 870;

分别的执行计划：
02__
从这个执行计划对比来看，ProductID为945和897的两条语句执行计划一致，因为满足条件的记录数非常少，分别为257条和2条，所以SQL Server均选择走最优执行计划Index Seek + Key Lookup。但是与ProductID为870的查询语句执行计划完全不同，这条语句SQL Server选择走的是Clustered Index Scan，几乎等价于Table Scan的性能消耗。这是因为，SQL Server认为满足条件ProductID = 870的记录数太多，达到了4688条记录，与其走Index Seek + Key Lookup，还不如走Clustered Index Scan顺序IO的效率高。从这里可以看出，SQL Server会因为传入参数值的不同而选择走不同的执行计划，执行效率也大不相同。确切的说，这个就是属于查询语句的参数嗅探问题范畴。

存储过程的参数嗅探表象

上一小节，我们看了查询语句的参数嗅探表象，这一小节我们来看看存储过程参数嗅探的表象又是如何的呢？
首先，我们创建如下存储过程:

USE AdventureWorks2008R2
GO
CREATE PROCEDURE UP_GetOrderIDAndOrderQtyByProductID(
@ProductID INT
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT 
        SalesOrderDetailID
        , OrderQty
    FROM Sales.SalesOrderDetail WITH(NOLOCK)
    WHERE ProductID = @ProductID;
END
GO

接下来，我们执行两次这个存储过程，传入不同的参数：

EXEC dbo.UP_GetOrderIDAndOrderQtyByProductID 870
EXEC dbo.UP_GetOrderIDAndOrderQtyByProductID 945

从这个执行计划来看，ProductID为870和945走的相同的执行计划Clustered Index Scan，这个和上一小节得到的结果是不一样的。上一节中ProductID = 945的查询语句执行计划走的是Index Seek + Key Lookup。
03_SP_

当我们选择第二个执行计划的Clustered Index Scan的时候，我们观察Properties中的Estimated Number of Rows，这里显示的是4668，但实际上正确得行数应该是257。如下如所示：
04_SP_

这到底是为什么呢？从另外一个角度来讲，这个不正确的统计估值甚至会导致SQL Server走到一个不是最优的执行计划上来（根据上一小节，ProductID = 945的最优执行计划其实是Index Seek + Key Lookup）。
答案其实就是存储过程的参数嗅探问题。这是因为，我们在首次执行这个存储过程的时候，传入的参数ProductID = 870对应的订单总数为4668，SQL Server在编译，缓存执行计划的时候，连同这个值一起记录到执行计划缓存中了。从而影响到存储过程的第二次及以后的执行计划方案，进而影响到存储过程的执行效率。
我们可以通过如下方法来查看执行计划中传入参数的值，右键 => Show Execution Plan XML => 搜索 ParameterCompiledValue
05__
在此例中，我们很清楚的发现传入参数值是870，同时也很清楚得看到了参数嗅探对于执行计划的影响：

...
<ColumnReference Column="@ProductID" ParameterCompiledValue="(870)" ParameterRuntimeValue="(870)" />
...

至此，我们分别从查询语句和存储过程两个方便看到了参数嗅探的表象。

参数嗅探导致的问题

从参数嗅探的表象这一章节，我们可以对此参数嗅探的问题窥探一二。但是，参数嗅探可能会导致哪些常见的问题呢？根据我们的经验，如果你遭遇了MSSQL Server以下奇怪问题，你可能就遇到参数嗅探这个“大魔头”了。

ALTER PROCEDURE解决性能问题

某些传入参数导致存储过程执行非常缓慢，但是ALTER PROCDURE（所有代码没做任何改动）后，性能恢复正常。这个场景是我们之前经常遇到的，原因是当你ALTER PROCEDURE后，MSSQL Server会主动清除对应的存储过程执行计划缓存，然后再次执行该存储过程的时候，系统会重新编译并缓存该存储过程执行计划。

相同的存储过程，相同的传入参数，执行时快时慢

这个听起来非常奇怪吧，当我们执行相同的存储过程，传入相同的参数值，但是执行效率时快时慢，请注意下面例子中的注释部分。

USE AdventureWorks2008R2
GO
--SQL Server 创建执行计划，优化ProductID = 870对应的大量订单量，运行时间500毫秒
EXEC dbo.UP_GetOrderIDAndOrderQtyByProductID 870

--SQL Server直接获取缓存中的执行计划，对于小量订单来说可能不是最好的执行计划，不过没关系，执行时间450毫秒
EXEC dbo.UP_GetOrderIDAndOrderQtyByProductID 945

现在我们清空了执行计划缓存，为了方便，我直接清除所有的执行计划缓存。

DBCC FREEPROCCACHE

再次执行存储过程，这次我们交换了执行顺序，先执行ProductID 945，然后执行ProductID 870。

USE AdventureWorks2008R2
GO
--SQL Server 创建执行计划，优化ProductID = 945，对应于小量订单的最优执行计划，运行时间100毫秒
EXEC dbo.UP_GetOrderIDAndOrderQtyByProductID 945

--SQL Server直接获取缓存中的执行计划，对于大量订单的ProductID 870来说，可能是很差的执行计划，执行时间60秒
EXEC dbo.UP_GetOrderIDAndOrderQtyByProductID 870

从这两个批次执行的时间对比来看，ProductID 945和870执行时间有比较大的差异，特别是ProductID = 870。这种相同的存储过程，相同的传入参数，执行时快时慢的问题，也是由于参数嗅探导致的。
注意：这里只是为了描述这种现象，由于表数据量本来不大的原因，可能实际上执行时间可能没有那么大的差异。

查询语句放在存储过程中和单独执行效率差异大

某一个查询语句，放在存储过程中执行和拿出来单独执行，时间消耗差异大，一般情况是拿出来单独执行的语句很快，放到存储过程中执行很慢。这个情况也是我们在产品环境常见的一种典型参数嗅探导致的问题。

参数嗅探的解决方法

上一节，我们探讨了参数嗅探可能会导致的问题。当发现这些问题的时候，我们来看看两类人的不同解决方法。请允许我将这两类人分别命名为菜鸟和老鸟，没有任何歧视，只是一个名字代号而已。

菜鸟的解决方法

菜鸟的理论很简单粗暴，既然参数嗅探是因为查询语句或者存储过程的执行计划缓存导致，那么我只需要清空内存就可以解决这个问题了嘛。嗯，来看看菜鸟很傻很天真的做法吧。

  • 方法一：重启Windows OS。果然很黄很暴力，重启Windows操作系统，彻底清空Windows所有内存内容。
  • 方法二：重启SQL Server Service。稍微温柔一点点啦，重启SQL Server Service，彻底清空SQL Server占用的所有内存，当然执行计划缓存也被清空了。
  • 方法三：DBCC命令清空SQL Server执行计划缓存。又温柔了不少吧，彻底清空了SQL Server所有的执行计划缓存，包含有问题的和没有问题的缓存。
DBCC FREEPROCCACHE

老鸟的解决方法

当菜鸟还在为自己的解决方法解决了参数嗅探问题而沾沾自喜的时候，老鸟的思维已经走得很远很远了，老鸟就是老鸟，是菜鸟所望尘莫及的。老鸟的思维逻辑其实也很简单，既然是某个或者某些查询语句或存储过程的执行计划缓存有问题，那么，我们只需要重新编译缓存这些害群之马就好了。

  • 方法一：创建存储过程使用WITH RECOMPILE
USE AdventureWorks2008R2
GO
ALTER PROCEDURE dbo.UP_GetOrderIDAndOrderQtyByProductID(
@ProductID INT
)
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON
    SELECT 
        SalesOrderDetailID
        , OrderQty
    FROM Sales.SalesOrderDetail WITH(NOLOCK)
    WHERE ProductID = @ProductID;
END
GO

再重新执行两次存储过程，传入不同的参数值，我们可以看到均走到最优的执行计划上来了，说明参数嗅探的问题已经解决。这个方法带来的一个问题就是每次执行这个存储过程系统都会重新编译，无法使用执行计划缓存。但是相对来说，重新编译的系统开销要远远小于参数嗅探导致的系统性能消耗，所以，两害取其轻。
06__WITH_RECOMPILE

  • 方法二：查询语句使用Query Hits

如果我们知道ProductID对应的订单总数分布，认为ProductID = 945为最好的执行计划，那么我们可以强制SQL Server按照参数输入945来执行存储过程，我们可以添加Query Hits来实现。这种方法的难点在于对表中数据分布有着精细的认识，可操作性不强，因为表中数据分布是随时在改变的。

USE AdventureWorks2008R2
GO
ALTER PROCEDURE dbo.UP_GetOrderIDAndOrderQtyByProductID(
@ProductID INT
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT 
        SalesOrderDetailID
        , OrderQty
    FROM Sales.SalesOrderDetail WITH(NOLOCK)
    WHERE ProductID = @ProductID
    --OPTION (RECOMPILE);
    OPTION (OPTIMIZE FOR (@ProductID=945));
    --OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));
END
GO
  • 方法三：DBCC清除特定语句或存储过程缓存

当清除执行计划缓存后，SQL Server再次执行会重新编译对应语句或者存储过程，以获得最好的执行计划。在此以清除特定存储过程执行计划缓存为例。

USE AdventureWorks2008R2
GO

declare
      @plan_id varbinary(64)
;

SELECT TOP 1 @plan_id = cache.plan_handle
FROM sys.dm_exec_cached_plans cache 
CROSS APPLY sys.dm_exec_query_plan(cache.plan_handle) AS pla
CROSS APPLY sys.dm_exec_sql_text(cache.plan_handle) AS txt
WHERE pla.objectid  = object_id(N'UP_GetOrderIDAndOrderQtyByProductID','P')
and  txt.objectid  = object_id(N'UP_GetOrderIDAndOrderQtyByProductID','P')

DBCC FREEPROCCACHE (@plan_id); 
GO
  • 方法四：更新表对象统计信息

表统计信息过时导致执行计划评估不准确，进而影响查询语句执行效率。这个也是导致参数嗅探问题另一个重要原因。这种情况，我们只需要手动更新表统计信息。这个解决方法的难点在于找到有问题的查询语句和对应有问题的表。统计信息更新方法如下，如果发现StatsUpdated时间太过久远就应该是被怀疑的对象：

USE AdventureWorks2008R2
GO
UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN;

SELECT 
    name AS index_name
    , STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail')
GO
  • 方法五：重整表对象索引

另外一个导致执行计划评估不准确的重要原因是索引碎片过高（超过30%），这个也会导致参数嗅探问题的重要原因。这种情况我们需要手动重整索引碎片，方法如下：

USE AdventureWorks2008R2
GO
select 
        DB_NAME = DB_NAME(database_id)
        ,SCHEMA_NAME = SCHEMA_NAME(schema_id)
        ,OBJECT_NAME = tb.name
        ,ix.name
        ,avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id('Sales.SalesOrderDetail','U'),NULL,NULL,'LIMITED') AS fra
    CROSS APPLY sys.indexes AS ix WITH (NOLOCK)
    INNER JOIN sys.tables as tb WITH(NOLOCK)
    ON ix.object_id = tb.object_id
WHERE ix.object_id = fra.object_id
    and ix.index_id = fra.index_id

USE AdventureWorks2008R2
GO
ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON Sales.SalesOrderDetail REBUILD;
  • 方法六：创建缺失的索引

还有一个重要的导致执行计划评估不准确的因素是表缺失索引，这个也是会导致参数嗅探的问题。查找缺失索引的方法如下：

USE AdventureWorks2008R2
GO
SELECT TOP 10
    database_name = db_name(details.database_id)
    , schema_name = SCHEMA_NAME(tb.schema_id)
    , object_name = tb.name
    , avg_estimated_impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)
    , last_user_seek = dm_migs.last_user_seek
    , create_index = 
'CREATE INDEX [IX_' + OBJECT_NAME(details.OBJECT_ID,details.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(details.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
    WHEN details.equality_columns IS NOT NULL
    AND details.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(details.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + details.statement
    + ' (' + ISNULL (details.equality_columns,'')
    + CASE WHEN details.equality_columns IS NOT NULL AND details.inequality_columns 
    IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (details.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + details.included_columns + ')', '')
FROM sys.dm_db_missing_index_groups AS dm_mig WITH(NOLOCK)
    INNER JOIN sys.dm_db_missing_index_group_stats AS dm_migs WITH(NOLOCK)
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS details WITH(NOLOCK)
    ON dm_mig.index_handle = details.index_handle
    INNER JOIN sys.tables AS tb WITH(NOLOCK)
    ON details.object_id = tb.object_id
WHERE details.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
  • 方法七：使用本地变量

这是一个非常奇怪的解决方法，使用这种方法的原因是，对于本地变量SQL Server使用统计密度来代替统计直方图，它会认为所有的本地变量均拥有相同的统计密度，即对应于相同的记录数。这样可以避免因为数据分布不均匀导致的参数嗅探问题。

USE AdventureWorks2008R2
GO
ALTER PROCEDURE dbo.UP_GetOrderIDAndOrderQtyByProductID(
@ProductID INT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE
        @Local_ProductID INT
    ;
    SET
        @Local_ProductID = @ProductID
    ;
    
    SELECT 
        SalesOrderDetailID
        , OrderQty
    FROM Sales.SalesOrderDetail WITH(NOLOCK)
    WHERE ProductID = @Local_ProductID
END
GO

至此结束，本节分享了菜鸟和老鸟关于参数嗅探问题的解决方法，我相信大家应该可以轻松的做出正确选择适合自己的解决方法。

 

posted on 2018-07-30 15:17  简简单单2018  阅读(270)  评论(0)  编辑  收藏  举报