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 可以使得這個運算符可以重制.

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

可以通過執行計劃看出,記憶體授予正常,如圖1-2所示

                                                 圖1-2

注:此坑一旦踩上影響着實不小,看到的朋友請擴散.

後記:此問題我已經反應給微軟的CSS團隊.

Involuntary DBA