SQL Server 优化器特性导致的内存授予相关BUG

我们有时会遇到一些坑,要不填平,要不绕过.这里为大家介绍一个相关SQL Server优化器方面的特性导致内存授予的相关BUG,及相关解决方式,也顺便回答下邹建同学的相关疑问.

问题描述

一个简单的查询消耗了匪夷所思的内存.(邹建同学发现的)

原文链接

Code

create table test_mem
(
id int identity(1,1) primary key,
itemid int not null,
date datetime not null,
str1 varchar(max) null
)

INSERT test_mem( itemid,date )
    SELECT TOP(1000)
        ABS(CHECKSUM(NEWID())) % 200,
        DATEADD(day, CHECKSUM(NEWID()) % (3 * 360), GETDATE())
    FROM sys.all_columns A, sys.all_columns B
    go 100

select * from  test_mem where itemid=28 order by date

执行代码后执行计划如图1-1

                                                图1-1

可以看出如此小的数据集排序居然消耗如此恐怖的内存数据量级,这样简单查询如果数据量再大些完全可能严重影响吞吐.

问题分析:通过执行计划我们发现只是一个简单的聚集索引扫描加上一个排序.问题就出现在聚集索引扫描上,通过语义分析我们发现我们的那个Itemid=28也包含在聚集索引扫描中过滤了,但优化器在做内存评估时并未注意到此状况,还是按照全表的相关内存大小评估的.

我们可以根据行大小大概算出优化器”认为”的数据大小.

Select 100000.0*4051.0/1024.0/1024.0 (约等于386MB!)

原来优化器以为他要对386MB的数据排序…

问题总结:优化器在做聚集索引扫描时同时为我们做了Filter过滤,但对接下来的内存评估时确忽略了运算符中的过滤.致使内存评估出现严重问题.

解决:了解了问题点后解决就简单了.在去年6月份的Pass分享中我曾经提过Filter运算符,我们只需让他在我们的执行计划中重现即可.

Trace Flag 9130 可以使得这个运算符可以重现.

Code

select * from  test_mem where itemid=28 order by date
option(querytraceon 9130)

可以通过执行计划看出,内存授予正常,如图1-2所示

                                                 图1-2

注:此坑一旦踩上影响着实不小,看到的朋友请扩散.

后记:此问题我已经反应给微软的CSS团队.