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 100select * 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 9310 可以使得这个运算符可以重现.

Code

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

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

                                                 图1-2

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

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

12楼cxy486

学习了。另外想问下 9130 这种非官方的 trace flag如果在生产库上使用会有什么副作用吗？

Re: ShanksGao

@cxy486，这个undoc得TF正如他所说,非官方,不支持.我们采用时最好将其影响粒度最小化,比如只在特定的语句中使用.即便有副作用也最小化副作用.

11楼greystar

没看明白，，Select 100000.0*4051.0/1024.0/1024.0 (约等于386MB!) 怎么得来的。，4051，这个值在图上有显示， 100000.0这个值哪来的。

Re: ShanksGao

@greystar，100000是我的数据量大小

Re: 桦仔

@greystar，引用没看明白，，Select 100000.0*4051.0/1024.0/1024.0 (约等于386MB!) 怎么得来的。，4051，这个值在图上有显示， 100000.0这个值哪来的。，，SELECT COUNT(*)，FROM test_mem

10楼JavaLeon

晕，这也有坑，无处不在啊

Re: ShanksGao

@JavaLeon，哈哈,大型系统就是这样,无处不在..

9楼superunusa

好东西。马上研究一下，学习了

Re: ShanksGao

@superunusa，谢谢:)

8楼CareySon

顶，太强了。

Re: ShanksGao

@CareySon，擦,沄剑过奖了..

7楼潇湘隐者

学习了！居然有这样的坑！

Re: ShanksGao

@潇湘隐者，恩，帮忙扩散下，这坑踩上影响不小。

6楼JentleWang

顶！涨姿势了

Re: ShanksGao

@JentleWang，谢谢,希望有帮助.这个影响其实不少.

5楼马非码

竟然还有这种问题，学习了

Re: ShanksGao

@马非码，每个新特性的加入都有可能带来额外的问题,这个就中招了.

4楼Joe.TJ

推荐！顺便沙发！

Re: ShanksGao

@Joe.TJ，谢谢:)

3楼ShanksGao

补充下,各位,由于此坑一旦踩上影响不少.请协助扩散!

2楼nzperfect

好文，学习了。，另外，我在sql 2012和sql 2014中试了下，在不加Query hint的情况下，sql 2012是499928，在sql 2014上是107752，MS有一点变化，但还是不如加了Query hint。

Re: ShanksGao

@nzperfect，我已经跟微软提了相关情况了,希望引起他们注意吧！

1楼superunusa

我在08没有重现了这个问题呀？是有版本限制？，我的用你的脚本查询计划到是一样的。不过看着数据不太一样，，，

Re: ShanksGao

@superunusa，是因为数据不同吧,不过你看下你的语句中也消耗了100M+的内存，这也能说明问题了