建立ColumnStore Index時發生記憶體不足問題解決
緣由 : 日前想測試一下ColumnStore Index效能是不是如傳說中的快速。因此在測試資料庫建立一張資料表，
並在資料表內塞入了100多萬筆資料。完成基本資料建立後，就Create ColumnStore Index了。只是當我執
行語法後，出現了一個錯誤訊息，
[訊息內容如下:]
訊息 8658，層級 17，狀態 1，行 3
無法啟動資料行存放區索引建立，因為它至少需要 147280 KB，而工作負載群組 'default' (2)
和資源集區 'default' (2) 中每個查詢的最大記憶體授與限制為 95288 KB。當您將資料行存放區
索引修改為包含較少資料行，或者使用資源管理員來增加最大記憶體授與限制之後，請重試一次。
由訊息中大約可以知道是因為記憶體不足導致，其中最令我疑惑的是每個查詢的最大記憶體授與限制為 95288 KB
這句，看來SQL有限制單一查詢的最大記憶體。那該如何解決這一個問題呢 ? 做個LAB吧。
首先我先去查了一下建立ColumnStore Index時到底要耗用多少記憶體，在一篇文章中很幸運的找到了說明
及記憶體需求的公式。
[公式如下:]
Memory grant request in MB = [(4.2 *Number of columns in the CS index) + 68]*DOP + (Number of string columns * 34)
Number of columns in the CS index : 表示這一個ColumnStore Index有幾個Columns。
DOP ( degree of parallelism) : 建立該ColumnStore Index時所使用的CPU數。
Number of string columns : 表示這一個ColumnStore Index有幾個String 的Columns。
由上面的公式我來算一下我的 ColumnStore Index到底要多少記憶體才夠。首先我有3個欄位，一個是Int，
其他兩個則是Char(4)跟Char(1)。算式應為 [ (4.2 * 3) + 68 ] * 1 + ( 2 * 34 ) = 148.6 MB。所以跟錯誤訊息中
147280 KB相近。
步驟一 : 如下圖所示在建立ColumnStore Index時出現了8658的錯誤訊息。系統顯示建立索引需要147280 KB記憶體
，但SQL被設定單個查詢最大記憶體不能超過95304 KB。
步驟二 : 如下圖所示，這一台SQL的Max Server Memory被設定為512MB。
步驟三 : 我們可以透過下圖的語法，就可以知道SQL目前單一要求可由集區中獲取的記憶體最大數量為多少
(預設值為百分之25)。我們SQL的Max Server Memory為512MB，而集區中獲取的記憶體最大數量為512MB
的25%，所以大概是128MB(但實際上由SQL訊息看來只有95288 KB)。
參考資料來源 : http://msdn.microsoft.com/zh-tw/library/bb934146.aspx
步驟四 : 我們根據公式可以知道所需記憶體的量其實也跟執行語法時的CPU數量有關，因此遇見這樣問題也可以
先將CPU數量降低(不要使用平行處理)，如下圖紅色圈選處所示，我們將建索引時的CPU數設定為一顆(MAXDOP=1)
。但可以發現本LAB連使用一顆CPU都還是有記憶體不足的情形。
步驟五 : 既然是因為集區中獲取的記憶體最大數量被設定為25%而導致記憶體不夠，那我們就加大它吧。如下圖所示
我們將數值由25%加到50% (紅色圈選處)。
步驟六 : 經過步驟五後，我們在嘗試一次建立ColumnStore Index吧，如下圖所示順利完成。
步驟七 : 完成ColumnStore Index建立後，記得再將集區中獲取的記憶體最大數量由50%改回25%歐。
微軟建議此設定值不要超過70%，不然可能會影響系統運作。
步驟八 : 在步驟七我們將集區中獲取的記憶體最大數量設定值調回25%了。此時如果再去Rebuild該
Columnstore Index還是會產生錯誤的歐，錯誤的原因相同。
步驟九 : 當然我們也可以加大SQL的Max Server Memory，如下圖所示，我將Max Server Memory由512MB
調到了1024MB。這樣一來，即使是25%的限制也有256MB，遠大於147280 KB。但前提是您的Server上還有
足夠的記憶體讓您將Max Server Memory往上加。
步驟十 : 當我們增加Max Server Memory後，在重新執行Rebuild作業。如下圖所示，又可以順利完成作業了。
我是ROCK
rockchang@mails.fju.edu.tw
