SQL Server Hash Warning 优化

本系列属于 SQL Server性能优化案例分享 专题

    最近遇到服务器CPU持续居高问题，通过计数器的检查，初步断定存在语句性能问题，然后有针对性地抓取问题语句（来龙去脉将会在另外一篇文章解释，本文关注Hash Warning），其执行计划如下：

    因为这是生产环境，所以下面例子把表名替换成A/B/C/D等表。代码很简单，大概样子如下：

SELECT bo.Scode ,d.PCode ,o.OrderNo ,bo.BNo ,d.OCode ,SUM(d.Qty) AS Qty
FROM dbo.A bo WITH (NOLOCK)
INNER JOIN B o WITH (NOLOCK) ON bo.OrderNo = o.OrderNo
INNER JOIN C d WITH (NOLOCK) ON bo.BNo = d.BNo
INNER JOIN D ct WITH (NOLOCK) ON o.City = ct.City
INNER JOIN E tcc ON ct.CloseTypeID = tcc.ID --产品表  
WHERE o.CancelFlag = 0 AND o.CancelByABC = 0 AND bo.STATUS = 0 AND tcc.ValidFlag = 1 AND ct.ValidFlag = 1
	AND o.PTime IN ( '2018-03-01 12:00:00','2018-03-01 14:00:00','2018-03-01 16:00:00'
			,'2018-03-01 20:00:00','2018-03-01 21:00:00','2018-03-01 23:30:00' ,'2018-03-02 01:00:00' ) --查询条件         
		AND bo.Scode IN (N'xxxx') --仓库代码       
	GROUP BY d.PCode,d.OCode,bo.Scode,o.OrderNo,bo.BNo

Hash Warning解释

    先来解释一下Hash Warning，当优化器在执行查询过程中，可能会遇到临时存储大量数据。在优化器优化过程中，以“预估”影响行数为开销的主要依据。预估过程会粗略计算每个操作符所需的资源，包括CPU、内存空间等，如果预估发生错误，那么就可能出现资源分配过多或者过少的情况。特别是内存资源（专业术语叫内存授予，memory grant），如果出现内存分配过少，有些操作就会拆分到磁盘上运行，此时内存中部分暂存的数据会移到TempDB上以便释放内存容纳更多的数据。当这部分在TempDB上的数据再次被需要时，就需要从磁盘读取。

    很明显，在TempDB中的数据使用会比在缓存中慢得多。这种差距特别在ETL过程中，可能从原来的几分钟变成几小时。

Hash Warning 事件

    在Hash过程中，当Build阶段的输入没有足够的可用内存时，会发生哈希递归（Hash recursion），从而导致输入拆分成多个独立处理的分区。如果某些分区依旧没有足够的可用内存，就会再次以这种方式拆分到子分区。这个过程持续到每个分区都有足够的可用内存或者已经达到最大的递归层级。
    这是最常见的一种拆分，优化器很钟爱Hash join，因为在两个未排序的数据集关联时，Hash Join非常高效，同时也可以用于去重（如distinct）和分组聚集（group by）。

Hash warning常规处理

    Hash Join对于未排序的大数据量关联是非常高效，但是它需要消耗大量的内存。而Hash 拆分通常意味着优化器预估不准确，这个又通常由缺少统计信息或统计信息过时导致。所以如果出现Hash Warning，首先应该更新统计信息或创建缺失统计信息。

    如果问题依旧，可能就要考虑采取其他Join类型，但是优化器通常选择的Join算法是最优的，所以此时你要思考“为什么会选择Hash Join”及“如何帮助优化器选择其他类型的关联算法？”

    通俗地说：当关联的两表中，没有可用的覆盖索引或者一个大表与一个非常小的表关联时，可能就会使用Hash Join。

    在SQL Server中，有三大类关联算法：Hash 、Merge和Loop（嵌套循环） Join，关联均涉及两个数据集，称为内表和外表，在图形化执行计划中，位于Join操作符的右上方的为外表（Outer table），右下方的为内表（Inner table）。这两个术语在超链接中可能能帮得上忙。

    

实操

    前面粗略介绍了一下Hash Join和Hash Warning，下面来尝试处理一下语句，由于表上索引过多，就不全部列出来。先来看原来的执行计划，虽然警告出现在Hash Match，但是只能说，这个是“结果”，那么因果关系里面的因才是我们需要关注的，即应该找到根源。根源往往来至于更加底层（也就是执行计划操作符的右方），我们看看分别是一个嵌套循环操作符和一个索引查找操作符。但是更常见的问题源自于直接访问数据的操作符，即表扫描、索引/聚集索引扫描、索引/聚集索引查找这类上面。所以继续往右看，这个时候发现了一个业务热点表的索引查找。同时鼠标移到箭头上时发现实际行数和估计行数相差较大：    

    鼠标移到这个索引查找图表上，可以看到下图。由于分析过程数据在变动，所以截图前后略有变动，那么这个611是怎么来的？同时圆框部分的“Predicate”和“Seek Predicate”又是什么？

？

    简单来说，Predicate是用于判断表达式的布尔值：TRUE/FALSE/NULL（UNKNOWN）也称为条件表达式。通常用于WHERE、Having和Join中。比如select xxx from xxxx where id=5这种，这里可能读者会有点困惑：难道不是查找id=5的值吗？其实Predicate更准确来说是一个统称，它还可以细分为：seek predicate和residual（残留？残余？没所谓了，就那么个意思） predicate两种，上图中上方的“predicate”实际上就是residual predicate。Residual Predicate往往是一个隐藏的性能开销，因为predicate需要对通过seek predicate查出来的数据再次进行表达式的二次校验。

    知道了这些信息之后，我首先想到的就是去除residual predicate，那怎么去除？首先要知道为什么出现。通过检查操作符用到的索引，发现原索引是大概下面样子：也可以用这个语句来：获取索引定义

CREATE NONCLUSTERED INDEX [IX_XXX] ON [dbo].[XXX]
(
	[A] ASC,
	[B] ASC,
	[C] ASC,
	[D] ASC,
	[E] ASC,
	[F] ASC
)
INCLUDE ( 	[G],
	[H],
	[I])

    但是语句中用到了INCLUDE中的H（需要分别代表什么无所谓了，重点看思路），另外C/D两个在语句中用到了，通过这个索引相对的统计信息（在表的“统计信息”文件夹下可以找到）发现，如果按现有索引定义的顺序，那么语句中用到的非首列的统计信息估算非常不准确，因此我尝试修改索引列顺序，但是保证首列不变，这个太重要了。通过修改了之后，再次执行，发现确实有效，结果如下图：     

    residual predicate已经消失。不过经过对比，提升不大。然后我们继续回到实际行数和预估行数差异大的问题上。首先实际行数中的值是怎么来的，通过检查语句，可以看到该表（目前我们只集中在一个表上）的筛选条件，抛开与其他表的关联条件，可以看到符合where条件的行数刚好是10890行。

    接下来考虑一下611或者613行的预估数量怎么来的？这个预估数量是从统计信息来的。再回头看看hash warning的产生条件。可以做一个大胆假设：问题应该是统计信息出现了问题。

    为了验证这个假设，我们检查一下统计信息，如下图：

    这个时候我发现一个异常，因为之前统计表总行数的时候有75239行，但是这里只有64348行。所以我认为统计信息本身就不够准确。

    这里附上一个脚本，查询统计信息的情况：

SELECT 
        OBJECT_NAME(stats.object_id) AS TableName, 
        stats.name AS StatisticsName,
        stats_properties.last_updated,
        stats_properties.rows_sampled, 
        stats_properties.rows, 
        stats_properties.unfiltered_rows,
        stats_properties.steps,
        stats_properties.modification_counter
FROM sys.stats stats
OUTER APPLY sys.dm_db_stats_properties(stats.object_id, stats.stats_id) as   stats_properties
WHERE OBJECT_NAME(stats.object_id) = 'XXX'--表名，如果不筛选则为全库
ORDER BY modification_counter desc  ;

    手动更新统计信息：

UPDATE STATISTICS  表 统计信息名 WITH FULLSCAN  --注意表跟统计信息中间是空格，不带其他符号

    再次打开可以看到已经准确了：

    另外通过检查，原有语句中有两个表仅用于筛选数据，并不会出现在SELECT和GROUP BY中，所以我把它们从JOIN中移到WHERE里面，以EXISTS来改写。发现单纯通过改写语句，Hash Match就不再出现，而是使用嵌套循环。把两个语句放在一起并开启实际执行计划，发现改写之后，开销比原有语句降低：

    当然，这也或多或少归功于统计信息的更新，但是由于没有做全库备份，而且作为生产环境也不适合备份还原太频繁，所以统计信息是否有很大的影响，这里无从得知，但是还是可以看出，改写的力量。

    到现在为止，其实已经达到了目的，减少了Hash Warning，这个部分可能还可以进一步优化，但是可以看看总结里面的结论，我认为问题并不在这里，所以目前为止就不再花更多精力在Hash Warning上面。

总结

    文章很长，读起来很费劲，正如我一边研究一边记录和整理一样，但是在这个过程中，我再次体会到一直依赖的一个原则：优化代码时，先检查代码是否有改写的空间。如果没有，再去考虑索引/统计信息，而不应该像很多人一样一开始就盲目进入索引“优化”。

    除了改写代码，还发现了统计信息问题。我会找时间写一篇统计信息相关的内容，来思考为什么在这里会出现问题，因为系统每天重建一次索引，按道理这个索引相关的统计信息应该是比较准确的，统计信息的过快过时（out-date）我已经不是第一次遇到了。回到本文，我给这个问题的结论是：统计信息的不准确，导致了优化器预估内存授予（memory grant ）过少，导致hash 递归，拆分数据到TempDB进行运算，从而引发Hash Warning。

    分析执行计划，除了找开销最大的操作符，还要多看一下其源头是否才是问题的根源。

    国外专家的常规建议是通过修改索引改写代码来减缓甚至去掉hash warning。但是我认为，除了这两点之外，统计信息的问题也是一个因素。由于万物互联，没有办法简单说明谁影响了谁，但是可以确定的是，索引跟统计信息是密切关联，索引设计不合理，统计信息就容易出问题。但是如果统计信息如果不准确，哪怕索引是合理的，优化器也可能不会选择这个索引。

    简化来说：Hash Warning→统计信息不准确→索引不合理？统计信息没维护？统计信息衰减过快？→检查代码→检查索引和统计信息维护。除此之外，其他检查也可以在无效时检查一下：服务器资源？服务器资源配置？CPU配置等等。

    关于Hash warning的问题，可以看一下另外一篇关联文章：为什么SQL Server统计信息过快过时？这个应该就是问题的根源。