SQL Server記憶體_ZenDei技術網路在線

背景最近一個客戶找到我說是所有的SQL Server 伺服器的記憶體都被用光了，然後截圖給我看了一臺伺服器的任務管理器。如圖這裡要說明一下任務管理器不會完整的告訴真的記憶體或者CPU的使用情況，也就是說這裡只能得到非精確的信息，有可能就是一個假警報。為了讓我的客戶放心，我檢查了伺服器並且查看了很多 ...

背景

最近一個客戶找到我說是所有的SQL Server 伺服器的記憶體都被用光了，然後截圖給我看了一臺伺服器的任務管理器。如圖

這裡要說明一下任務管理器不會完整的告訴真的記憶體或者CPU的使用情況，也就是說這裡只能得到非精確的信息，有可能就是一個假警報。

為了讓我的客戶放心，我檢查了伺服器並且查看了很多性能指標。我所看到的就是CPU和硬碟使用都是很低的只有記憶體是高的，這恰恰是我們期望的SQLServer 伺服器的狀態。SQL Server會儘可能的使用記憶體，通過緩存儘可能多的磁碟來改善性能。當然如果OS需要它也會立即釋放資源回來。

SQL Server 對記憶體是“貪得無厭”的，它會持有所有分配給它的記憶體，不論是否使用。而這也是我們想要它去做的。因為它會存儲數據和執行計劃在緩存中，然後當使用完這些記憶體時，它不會釋放這些記憶體，緩存到記憶體中，除非兩種情況才會釋放緩存的數據記憶體：1) SQL Server 重啟或者記憶體不足 2) 操作系統需要記憶體
預設的記憶體設定就是使用所有記憶體（安裝時設置），當操作系統需要記憶體時，它也會大量釋放記憶體。然後等到有記憶體時在重新大量持有。但是這種不是最佳實踐，最好還是設定一個最大記憶體限制，這樣操作系統就會保證一定量的記憶體永遠為SQL Server 使用。

當看到資源管理器，Available MB 的記憶體有兩部分組成Standby--備用和Free--可用，這Standby 的空間系統已經把它緩存了，而Free的記憶體意味著沒有被使用。它們都叫做可利用記憶體。因此針對一開始那個客戶擔憂我們大可不必太擔心。當然我們還需要健康其他的性能計數器，查明是否存在記憶體影響性能的隱患。需要關註的指標如下：

Page Life Expectancy
Available Bytes
Buffer Cache Hit Ratio
Target & Total Server Memory
Memory Grants Pending
Pages/sec (Hard Page Faults)
Batch Requests/sec & Compilations/sec

介紹下這些性能參數：

Page Life Expectancy (PLE)

這個性能計數器記錄了數據頁（非鎖定）在緩衝池中的平均時間。在生產高峰這個數值可能比較低，但是一般要保持這個數據在300s以上，數據待在緩衝中時間越長，那麼SQL的IO操作越少。

如果長期這個數值在300s以下，可以考慮增加記憶體，當然由於現在記憶體越來越大，這個值也變得不那麼重要了，但是對於中小系統依然可以作為一個標準閾值。

由於這個閾值基於32位系統的4G記憶體，那麼標準演算法可以大致可以推算:記憶體大小（GB）/4*300。

也可以使用下麵的語句來查詢該計數器:

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page life expectancy'

Available MBytes

該計數器監測還有多少可用記憶體，是否操作系統存在記憶體壓力。一般我們調查是否這個計數器持續在500MB以下，這說明記憶體過低。如果持續低於500則說明你需要增加更多的記憶體。

這個計數器不能通過T-SQL查詢，只能通過性能監視器觀察。

Buffer Cache Hit Ratio

緩衝命中率，這個計數器記錄平均多少頻率從緩衝池中取得數據。我們在OLTP資料庫中一般這個比率是90%-95%（該數值經由@MSSQL123 指出發現是錯誤的，再次進行修改）。由於sqlserver 把預讀也作為緩衝比例，所以導致該值很高，所以該計數器只做理解，不能作為真實性能瓶頸參考了。如果該計數器持續低於90%，則需要增加記憶體。

在可以使用下麵的T-SQL語句查詢：

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Buffer cache hit ratio'

Target & Total Server Memory

伺服器當前總記憶體（buffer）以及目標記憶體，在緩衝池初始化增加記憶體的時候，總記憶體會比目標記憶體稍低一點。這個比例會逐漸接近1，如果總記憶體沒有增長很快，就會顯著低於目標記憶體，這就表示如下兩點：

1) 你可以分配儘可能多的記憶體，SQL能緩存整個資料庫到記憶體中，然後如果資料庫小於機器記憶體，記憶體不會完全用光，在這種情況下，總記憶體將永遠小於目標記憶體。

2) SQL不能增加緩衝池，比如系統記憶體有壓力。如果這種情況你需要增加最大伺服器記憶體，或者增加記憶體來改善性能。

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] IN ('Total Server Memory (KB)','Target Server Memory (KB)')

Memory Grants Pending

這個計數器測量等待記憶體授予的SQL的進程數量。一般推薦閾值為1或者更少。如果大於1這說明記憶體不足按順序等待記憶體釋放再操作SQL。

一般工作中出現這種等待可能是由於糟糕的查詢，缺失索引，排序或者哈希引起的。為了查明原因可以查詢DMV --sys.dm_exec_query_memory_grants 這個視圖，將會展示哪一個查詢需要記憶體授予執行。

如果不是以上原因引起的記憶體等待，則需要增加記憶體來解決這個問題。此時就有理由增加硬體了。查詢的T-SQL語句如下：

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Memory Grants Pending'

Pages/sec (Hard Page Faults)

這裡也使用資料庫級別計數器：當需要讀取或寫入的頁不在記憶體中，需要到磁碟中讀取時計數。這個計數器是一個記錄讀和寫的總和並且不能直接在記憶體中獲取只能從因盤中讀取（導致resulting in hard page faults），這個問題是由於操作系統必須交換文件在磁碟上，當訪問記憶體時，記憶體不足則需要交換文件到磁碟上，由於磁碟讀寫速度遠低於記憶體，性能就會受到嚴重影響。

對於這個計數器，推薦閾值為<50（或者某個穩定值）,如果看到高於這個值，不過需要註意，只要這個值能夠穩定在一個較低的水平，沒有持續性的大批量數據的寫入（磁碟）於讀取（從磁碟載入記憶體），都可以接受。相反，如果長期在一個高位水平，並且觀察到PLE不能穩定在參考值範圍內，說明記憶體可能存在瓶頸。當然，如果資料庫備份或者還原，包括導出、導入數據以及記憶體中映射文件等等這些也會導致性能計數器超出某個穩定值。

Batch Request & Compilations

該計數器包含兩個檢查

SQL Server: SQL Statistics – Batch Request/Sec. 傳入查詢的數量（批處理數量）
SQL Server: SQL Statistics - Compilations/Sec. 新建立的執行計劃數量

如果Compilations/sec是25%或者相對Batch Requests/sec更高，則執行計劃將被放到緩存中，但是永遠不會重用執行計劃。寶貴的記憶體就被浪費了，而不是緩存數據。這是糟糕的實踐，我們要做的就是阻止這種情況，

如果Compilation/sec 很高比如100，表示有大量的即席查詢正在運行。這時可以啟用“optimize for ad hoc”把執行計劃緩存，但是只有在第二次查詢時才能被使用。

使用如下T-SQL可以得到相應的指標:

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%SQL Statistics%' AND [counter_name] = 'Batch Requests/sec';

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%SQL Statistics%' AND [counter_name] = 'SQL Compilations/sec';

同樣可以獲得比率:

SELECT ROUND (100.0 * (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%SQL Statistics%' AND [counter_name] = 'SQL Compilations/sec') / (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%SQL Statistics%' AND [counter_name] = 'Batch Requests/sec') ,2) as [Ratio]

關於如何設定資料庫可用的記憶體最大值，如圖所示：

推薦閾值：一般來說，我都是採用10%用於操作系統其它90%分配給資料庫。當然如果記憶體很大可以調整這個比例小於1/9，對於記憶體較小的通常我都預留4-6G左右給操作系統。

我們看一下實際例子：

在性能監視器中看一下這個計數器，我們可以看到這個伺服器處於健康狀態下，有11GB的可用空間，沒有PageFaults（I/O只從緩存中沒有交換到磁碟），緩衝的比率為100%，PLE超過20000s，沒有記憶體等待，充足的總記憶體和較低的編譯比率（編譯數/查詢數）.

這個測量數據很容易理解，這要比任務管理器更具有作用，能依據此做出判斷是否有足夠的記憶體在這台SQL Server伺服器上。

總結

如果只根據任務管理器來做出判斷，我們很容易出現錯誤決定。因為不管系統多少記憶體，SQL Server 會儘可能的使用占用記憶體，這不是bug。緩存數據在記憶體中有很好的效果，意味著伺服器是健康的，也為用戶提供了更好的執行效率。在實際資料庫環境中，一般突然遇到的性能問題多半是因為T-SQL語句引起的，就如我前面提到糟糕的查詢（缺失索引、排序、哈希等等），這個時候通過語句優化可以很好的解決突發問題，這裡就不詳解了。如果伺服器普遍存在文章中出現的記憶體性能計數器問題，那就寫報告提交記憶體增加需求吧。