SQL SERVER 記憶體配置及常見記憶體問題（1）——簡介

標籤：

原文: SQL SERVER 記憶體配置及常見記憶體問題（1）——簡介

一、問題：

1、SQL Server 所佔用記憶體數量從啟動以後就不斷地增加：

      首先，作為成熟的產品，記憶體溢出的機會微乎其微。對此要瞭解SQL SERVER與windows是如何協調、共用記憶體。並且SQL SERVER的內部對記憶體的管理機制。

2、在Windows 2003以上版本啟動並執行SQL Server，記憶體使用量量突然急劇下降：

     記憶體是Sqlserver的生命線。在errorlog中，出現一下情況：      

這類問題往往不是sql server導致的，而是Windows感覺到急迫的記憶體壓力，迫使sql server 釋放記憶體。

3、使用者在做操作時，遇到記憶體申請失敗：不是使用者想申請多少就有多少

4、記憶體壓力導致的效能下降：記憶體壓力是效能問題最常見的原因之一。

二、作業系統層面看SQL SERVER 記憶體配置：

作業系統不缺記憶體不代表sql server不缺記憶體

專用術語：Virtual Address Space(虛擬位址空間)：一個應用程式能訪問的最大地址空間。虛擬位址空間存放的資料資訊不一定在實體記憶體中，可能會放在快取檔案中（Paging file）Physical Memory（實體記憶體）：要頻繁訪問的資料對象必須能長時間地緩衝在實體記憶體中，才能達到最佳化的效率。Reserved Memory（保留記憶體）：是虛擬位址空間的一段地址，不是真正物理空間，由某些API提前申請保留。Committed Memory（提交記憶體）：將保留記憶體的頁面正式提交。最終到實體記憶體中，Shared Memory（共用記憶體）：windows提供了在進程和作業系統間共用記憶體的機制。可理解為在多個進程中可見的記憶體。Private Bytes：某個進程提交的地址空間中，非共用部分。Working Set：某個進程的地址空間中，存放在實體記憶體的那一部分。Page Fault（頁面訪問錯誤）：分為Soft/Hard ，訪問一個存在虛擬位址空間，但不存在實體記憶體中的頁面時，就會發生PageFault，如果不是訪問越界而導致的，一種目標頁面存在於硬碟，會產生硬碟讀寫，成為Hard Fault。另一種存在實體記憶體中，但沒有直接放在這個進程的working set下。需要windows重新導向，這種不會導致硬碟操作，成為soft fault，一般soft帶來的效能影響可以忽略，只要關係hard fault。System Working Set：Windows系統也有Working set 可以通過效能監控器裡的memory：cache bytes來監控，發生在系統記憶體上的page fault可以用過Mmeory：cache faults/sec看到。System Cache（系統快取）：提高磁碟I/O速度，可以用過效能監控器：Memory：cache resident bytes監控。Non Paged Pool （非頁交換區）：包含一定範圍內的系統虛擬位址的記憶體交換區，可以保證在任何時候都駐留在實體記憶體中，可以通過Memory:pool Nonpaged Bytes來監控。這一塊緩衝可以被所有的經常共用，一個最常見的用途是存放所有對象的指標（Object Handles）Paged pool（頁交換區）：系統空間中可以調入或調出系統進程工作集（Working set）的虛擬記憶體地區。可以通過Memory：pool Paged Bytes和Memory：pool paged resident bytes監控。Stack（棧）：每個線程有兩個棧，一個給核心模式（kernel mode），一個給使用者模式（user mode）。每個棧是一塊記憶體空間，存放線程啟動並執行過程或函數的調用地址，以及所有參數的值。In Process:運行在同一個進程的地址空間裡。Out of Process：運行在不同的進程地址空間裡。Memory Leak（記憶體流失）：一種是sql server作為進程，不斷地向windows申請記憶體資源知道整個windows記憶體耗盡。另一種是sql server 內部，某個sql server組件不斷地申請記憶體，知道把能申請到的記憶體耗盡，使得其他組件不能正常運行。前一種情況非常少見。32位下windows的地址空間及AWE：

預設情況下，windows進程有4G虛擬位址空間，2G給核心態（kernel mode），剩下的2G給使用者態（user mode）。這兩部分會嚴格區分。

任何一個使用者進程的地址空間分布圖：

方法1：在boot.ini檔案中使用/3GB參數，可以在企業版的windows下講核心態降到1G，講使用者態升到3G。

方法2：使用Address Windowsing Extensions(地址空間擴充，awe)，是一種允許32位應用程式指派64GB實體記憶體，並把視圖或視窗映射到2G虛擬位址空間的機制。不是所有的記憶體申請都使用awe，只有先reserve，再commit的記憶體調用才使用awe。

Windows層面上的記憶體使用量檢查：在檢查sql server記憶體使用量情況時，首先要檢查windows層面的記憶體使用量情況。

需要檢查的內容有：windows系統自身記憶體使用量數量及記憶體分布。是否有記憶體壓力，壓力是否比較嚴重。甚至每個進程的記憶體使用量情況。最後就是是否互相影響。

可以使用效能監控器實現。

Windows系統使用方式：

1、整體流量分析：

Committed bytes：

整個windows系統的記憶體總數，包括實體記憶體的資料和檔案快取中的資料。

Commit Limit：

整個windows系統能夠申請的最大記憶體數，等於實體記憶體加上檔案快取的大小。如果Commit Limit≈Committed bytes，說明系統記憶體接近極限。如果快取檔案不能自動成長，系統將不能提供更多的記憶體空間。

Available MBytes：

現在系統閒置實體記憶體，直接反映windows層面有沒有記憶體壓力。

Page File:%Usage 和Page File:%Peak Usage：

反映快取檔案使用量的多少，資料在檔案快取中存得越多，說明實體記憶體數量和實際需求量的差距越大，效能也越差。

Pages/sec：

Hard Page Fault每秒需要從磁碟讀取或寫入的頁面數目。是Memory:pages input/sec + Memory:pages output/sec之和。

Memory:page faults/sec 是soft page fault 和 hard page fault的總和。但由於soft page fault 對效能影響不大，所以用處沒pages/sec那麼有用。pages/sec不能長時間保持在一個比較高的值。

2、Windows系統自身記憶體使用量情況：

Memory：cache bytes：

系統的working set ，也就是系統使用的實體記憶體數目。

           Memory：System cache resident bytes (system cache)：系統告訴緩衝消耗的實體記憶體。

           Memory：Pool paged resident bytes：頁交換區消耗的實體記憶體。

           Memory：System Driver resident bytes：可調頁的裝置驅動程式代碼消耗的實體記憶體。

           Memory：System Code resident bytes：Ntoskrnl.exe中可調頁代碼消耗的記憶體。

3、System pool：Memory :pool Nonpaged bytes(非頁交換區)和Memory：pool paged resident bytes（頁交換區）

單個Process使用方式：

Process:%processor Time：目標進程消耗的CPU資源數，包括使用者態和核心態的時間。

Process:Page Faults/sec 目標進程上發生的PageFaults的數目。

Process:Handle Count 目標進程handle資料，如果進程內部有對象老是建立不及時回收，就會發生Thread Leak

Process:Pool Paged Bytes目標進程所使用的Paged Pool的大小。

Process:Pool Nonpaged Bytes目標進程所使用的Non-Paged pool大小。

Process:working set 某個進程的地址空間中，存放在實體記憶體的那部分。

Process:Virual Bytes:某個進程所申請的虛擬位址空間大小。

Process:Private bytes：某個進程提交了地址空間中非共用的部分。

記憶體永遠是最重要的系統資源。

SQL SERVER記憶體使用量和Windows之間的關係：Sqlserver有兩個重要的記憶體計數器：Total Server Memory 和Target Server Memory。Total Server Memory：自己分配的Buffer pool 記憶體總和。

Target Server Memory：理論上能夠使用的最多記憶體數目。

SQL Server 記憶體使用量量陡然下降現象：原因：1、windows在某種情況下申請了太多核心態記憶體，反而壓縮了使用者態可以使用的實體記憶體。2、有些硬體驅動程式申請了太多核心態記憶體，也佔用太多實體記憶體。3、某些應用突然申請大量實體記憶體。使用下面方法避免SQL SERVER 記憶體被急劇搶佔：
1、開啟Lock page in memory功能只有05/08企業版才有2、使用sp_configure 設定sql 的MAX SERVER MEMORY。3、升級新版本或者補丁。4、升級硬體驅動。
合理配置SQL SERVER 記憶體兩條原則：（1）Windows 系統和其他關鍵應用服務要有足夠的記憶體，不要在運行過程中因為記憶體不足，而搶SQL SERVER已經申請的記憶體。（2）在滿足第一點的前提下，SQL SERVER使用儘可能多的記憶體，並保證記憶體使用量數量的穩定性。方法：1、使用64位2、專用伺服器3、設定SQL Server Max Server Memory4、給SQL Server 啟動帳號賦予Lock Pages in memory許可權。5、“set working set size”不要使用。
三、SQL Server 內部獨特的記憶體管理員模式：SQL SERVER 開放的記憶體調節介面：1、Min Server Memory (sp_configure)：最終由windows確定，不保證SQL Server使用最小實體記憶體數。2、Max Server Memory(sp_configure)：資料放在實體記憶體還是緩衝檔案中，由windows決定。3、Set Working Set Size (sp_configure)：不要使用。4、AWE Enalbed（sp_configure）：對32位系統有意義。5、Lock Pages in memory （企業版會自動開啟）：有一定機會確保sql server的實體記憶體數。記憶體使用量分類：按用途分類：Database Cache：存放資料頁的緩衝區。各類Consumer：    Connection:：預設4K    General：包含語句的編譯、範式化、每個鎖資料結構、事務上下文、表格和索引的中繼資料等。預設8K。    Query Plan：預設8k，    Optimizer：預設8k，    Utilities：像BCP、Log Manager、Parallel Queries、Backup的特殊操作。預設8k，線程記憶體：進程內的每個線程分配0.5MB記憶體。存放線程的資料結構和相關資訊。預設512K第三方代碼申請的記憶體（COM,XP...）
按申請方式分類：有些記憶體申請方式是：預留Reserve一大塊記憶體，然後使用的時候一小塊一小塊commit，而另外一些記憶體申請直接從地址空間Commit，這種叫Stolen。

SQL SERVER 記憶體配置及常見記憶體問題（1）——簡介