[SQL SERVER]SQL2016-新查詢提示 for Memory

2017-01-02

SLQ Server 2016新增兩個查詢提示MIN_GRANT_PERCENT 和MAX_GRANT_PERCENT，

設定值介於0~100.0之間。

SQL Server針對查詢進行compile過程中，會估算需要多少記憶體，

但這機制僅適用動態查詢，對於快取中的計畫並不適用，

這是因為SQL Server只會針對快取計畫的第一次執行參數進行記憶體估算，

而這也是導致發生記憶體低估或高估的效能問題主因。

一般我們最常見需要記憶體分配的運算子如Sort、Hash join(aggregation、union)，

不管是低估Sort或高低Hash都會造成巨大效能問題。

針對記憶體授權問題，你現在有更快方式修正，就是透過MIN_GRANT_PERCENT and MAX_GRANT_PERCENT這兩個查詢提示，

好消息是，這兩個查詢提示也已經套用在SQL2012 SP3以後版本(有使用SQL2012版本的朋友，建議升級SP3，因為不只是修正Bug，更包含一系列效能調整相關功能)。

Memory Grant提示有點像微型記憶體資源管理員(resource governor)，

可以讓你針對個別查詢限制記憶體最大和最小使用量，如果指定值小於執行查詢所需記憶體總量，

該查詢依然會要求總記憶體量，並不用擔心會發生執行查詢錯誤情況，下面我簡單重現記憶體授權效能問題。

create proc USP_testMemory
(@c2 varchar(40))
as
set nocount on
select c1,c2,c3,c4 
from dbo.TestLike 
where c2=@c2
order by c3

--第一次執行參數 FeiFei
exec USP_testMemory 'FeiFei'

可以看到雖然只有一筆資料，但依照預設記憶體查詢設定，最少也需要1024 kb的記憶體授權。

--第二次執行參數 this isnot possible
exec USP_testMemory 'this isnot possible'

這次返回78033筆資料，但記憶體授權卻還是1024KB，低估記憶體授權，

導致排序運算子使用tempdb資源，解決這效能問題的方法很多，

但這裡我示範使用MIN_GRANT_PERCENT來解決。

alter proc USP_testMemory
(@c2 varchar(40))
as
set nocount on
select c1,c2,c3,c4 
from dbo.TestLike 
where c2=@c2
order by c3
option(MIN_GRANT_PERCENT=1.5)--表示我們將使用更多記憶體

--第二次執行參數 this isnot possible
exec USP_testMemory 'this isnot possible'

雖然因為cache plan影響估計的資料列數目，我們使用提示告訴SQL SERVER將使用更多記憶體，