如何設置SQL Server的Min Memory per Query

SQL Server的min memoey per query是一個經常被SQL Server DBA問到的設置，默認這個值是1MB。很多DBA都會在性能調優的項目或者培訓過程中問到怎麼調整這個值，調大還是調小，調大多少或者調小多少？

首先我們需要知道的是這個設置的含義，SQL Server聯機幫助文件中的定義是：
e^2gIE X0min memory per query選項用於指定分配給查詢執行時所需要的最小內存量 (KB)。例如，如果將min memory per query設置為 2048 KB，則查詢保證將至少獲取那麼多的總內存。可以將min memory per query設置為從 512 到 2,147,483,647 KB (2 GB) 的任何值。默認值為 1,024 KB。

我們會注意到，首先min memory per query是per query的，其次min代表是最小值。因此如果我們調大min memory per query會出現兩種情況：
I*f PUsd1T0典型的OLTP環境ITPUB個人空間!k*d*~nDb?
典型的OLTP環境通常有很多並發查詢的系統，在這種環境下調大min memory per query可能就會一下子增加很多內存的保留請求，這樣就有可能會造成爭搶甚至嚴重的阻塞。ITPUB個人空間,R4sh)h;g K1E
典型的OLAP環境
`2Fu o(yY
s0典型的OLAP環境通常並發查詢比較少，但是單個查詢比較復雜，例如很多查詢中都會有排序、分組等操作。對於這種環境，調大min memory per query會增加每個查詢獲得的內存保留值，這樣就可以改善查詢的性能。

了解了調大調小的依據之後，我們就需要討論一下具體怎麼調整這個值，通常環境下我們不需要去調整這個設置，因為這是一個高級的SQL Server選項。不過如果我們需要去調整這個設置的話，我們要通過以下幾個步驟：

1. 選擇重點關注的查詢，通常就是那些並發執行量大的查詢，或者是執行內存需求比較大的查詢
2. 在系統沒有負載的情況下評估這些查詢所需的內存量
3. 在生產系統中監控這些查詢的最大並發量
4. 查詢生產系統SQL Server實例的可用內存

我們會看到很多文章中都建議這麼計算min memory per query：
"^C5_#m9U ~0SQL Server實例的可用內存  / （並發查詢數量 * 平均每個查詢所需要的內存尺寸）

其實計算出上面這個公式的值還不足夠，在實際環境中我們會需要更加復雜的判斷規則。舉個例子，某個生產系統中有兩個需要關注的查詢，一個並發量有100，每個查詢實例都需要2MB（典型的事務操作），另外查詢並發量10，每個實例需要內存50MB（典型的分析操作），那麼平均每個查詢所需的內存就為(100 * 2 + 10 * 50) / (100 + 50) = 4.7，並發量為110，而系統可用內存為2048MB，那麼我們得到的min memory per query就應該是3.9MB（2048 / (110 * 4.7)）。我們很明顯就會注意到對於事務操作這個值過高了，事務操作查詢的每個實例都會浪費1.9MB的內存。因此為了正確設置這個值，我個人會把上面這個公式得到的值看作系統的內存負載系數。

如果這個內存負載系數大於1的話，表示系統有足夠的內存，這種情況直接將min memory per query調整到最小內存所需的值就可以了，或者根本不去調整min memory per query。

如果內存負載系數小於1的話，代表系統沒有足夠的內存，這個時候我們才需要考慮是否調整min memory per query。分三種情況：
L n Z/mKz0需要快速響應事務
|*~ e.Q^?,a)jBx5[0建議將min memory per query直接調整為交易查詢所需的內存，這樣多數事務操作就會獲得足夠的內存。不過要確保調整後的min memory per query不超過SQL Server實例的可用內存 / (交易查詢並發數量  + 分析查詢並發數量)，因為分析查詢執行時間非常長，這樣就會造成分析查詢會長時間阻塞一部分交易查詢獲得足夠的內存，而導致交易查詢的響應時間不穩定，甚至有些會超時。ITPUB個人空間Ju0E!u+g)U`O%M4} x
需要改善分析性能
%?'x?3`s-H1v
K%Fw"e0建議將min memory per query調整的大一些，調大min memory per query後分析查詢就有更多幾率獲得較大的保留內存。建議不要超出(SQL Server實例可用內存 / (交易查詢並發數量  + 分析查詢並發數量)過多，超過這個值過多會使得交易查詢經常超時，當然你還可以把查詢超時設置的更大，甚至是無限制等待，或者引入隊列操作機制。最高不能超過SQL Server實例可用內存 / (分析查詢並發數量)，超出這個值後就會導致交易查詢永遠無法獲得啟動內存。
3r'ZH hEU%`w D0PS：有人會質疑這樣設置會導致某些交易查詢浪費內存，並且由於這些交易查詢數量非常大，可能會阻塞分析查詢。不過實際情況中交易查詢的執行時間都很短，這樣在等待隊列中的分析查詢只需付出一些等待的時間代價就可以獲得較多的查詢內存，多數情況下為了得到這些額外的內存所耗費的等待時間相比得到額外內存而減少的執行時間都要小很多。當然如果能夠配合查詢優先級別的技術，提高分析查詢的優先級別就更好了。
\6H%wRtC7\"n4]%f0