SQL Server 2008記憶體效能監控

原文首發於it168，連結見http://tech.it168.com/a2011/0221/1158/000001158998.shtml

記憶體相關概念

　　以下均是針對Window 32位系統環境下，64位的不在下面描述情況下。

　　使用者模式和核心模式(user mode& kernel mode)

　　為了防止使用者程式訪問並篡改作業系統的關鍵部分，Windows使用了2種處理器存取模式：使用者模式和核心模式。顧名思義，核心模式是給作業系統核心程式碼和基本驅動用的，使用者模式給使用者應用程式。在核心模式下程式可以訪問所有的記憶體和硬體，並使用所有的處理器指令。作業系統程式比使用者程式有更高的許可權，使得系統設計者可以確保使用者程式不會意外的破壞系統的穩定性。

　　實體記憶體(Physical Memory)

　　即實際購買的記憶體的大小，記憶體條上的容量。CPU的地址線可以直接進行定址的記憶體空間大小，在32位作業系統平臺上，CPU的最大定址空間為4GB，也即可以支援最大4G的實體記憶體空間。在32位作業系統上即便你購買的是64G記憶體，也只能說擁有4GB的實體記憶體空間

　　虛擬記憶體(Virtual Memory)

　　如果計算機缺少執行程式或操作所需的隨機儲存記憶體，則Windows使用虛擬記憶體進行補償。虛擬記憶體將計算機的RAM和硬碟上的臨時空間組合在一起，當RAM不足時，虛擬記憶體將資料從RAM移動到稱為“分頁檔案”的空間中，將資料移入與移出分頁檔案可以釋放RAM，以便完成工作。

　　虛擬地址空間(Virtual Address Space，簡稱VAS)

　 　在Windows系統中，任何一個程式都被賦予了其自己的虛擬地址空間，該虛擬地址空間覆蓋了一個相當大的範圍，對於32位系統的虛擬地址空間範圍從 0x00000000~0xffffffff(4GB)。Windows採用分頁機制，將4G的地址空間分成固定大小的頁，並且將虛擬地址中的每一頁對映 到實體記憶體中。

　　在預設的情況下虛擬地址空間中的低2G，即0x0000000~0x7FFFFFFFF是使用者地址空間，而4G虛擬地址空間中的高2G即0x8000000~0xFFFFFFFF是分配給核心模式。實際上使用者程式擁有的虛擬地址空間只有2GB。

　　虛擬記憶體管理器(Virtual Memory Manager)

　　虛擬記憶體管理器負責虛擬地址空間和實體記憶體的地址對映，如果缺乏足夠記憶體，則需要使用到page file檔案來保持臨時資料也即虛擬記憶體，同時使用page table entry(PTE)來跟蹤每一個地址對映關係。

　　到這裡為止，64G的記憶體有60G都無法訪問，豈不是浪費了，那怎麼辦?

　　/3GB和increaseUserVA

　　通過/3GB的方式，可以減少核心模式佔用地址空間，從而增加SQL Server程式的地址空間。預設情況下，使用者模式和核心模式各自佔用2G定址空間，3G選項可以使得SQL Server獲得多1G的虛擬地址空間。

　　/3GB開關用法：

　　在Boot.ini檔案中修改其中的段落即可：

　　或者使用bootcfg命令

　　在Windows Server 2008中可以執行BCDEdit命令，加以調整。

　　實體地址擴充套件PAE(Physical Address Extension)

　　實體地址擴充套件(PAE)是32位Intel CPU的一種擴充套件，這樣可以在32位系統上支援最大64G的實體記憶體，即4GB以上實體記憶體允許將更多實體記憶體對映為應用程式的虛擬地址空間。

　　使用方式,在Boot.ini檔案中修改其中的段落即可：

　　在Windows Server 2008作業系統下也可以通過以下命令執行

　　/PAE和/3GB

　　兩者的目標是不同的，又可以在同樣的地方進行配置，所以難免產生疑惑，簡單的來說就是如果計算機可用實體記憶體超過16GB，就需要確保boot.ini檔案中沒有/3gb引數即可。

　　地址視窗化擴充套件外掛AWE(Address Windowing Extensions)

　 　AWE是Windows的記憶體管理功能的一組擴充套件，它能夠使應用程式使用的記憶體量超過通過標準32位定址可使用的2~3G記憶體。AWE允許應用程式獲取 實體記憶體，然後將非分頁記憶體的檢視對映到32位地址空間。雖然32位地址空間限制為4GB，但是非分頁記憶體卻可以遠遠大於4GB。

　　在SQL Server 2008下，可以登入SQL Server Management Studio，找到相應的資料庫例項，點選右鍵選擇屬性，然後在“選擇頁”中點選記憶體，在伺服器記憶體選項中，複選使用AWE分配記憶體即可。

SQLServer的記憶體管理

　　SQL Server 主要的記憶體組 件是緩衝池。其中高速資料緩衝區用以把資料從磁碟載入到記憶體中，實現資料的高速讀寫。而過程高速緩衝區則用來儲存相應的執行計劃，減少編譯過程，也是高速 緩衝倉庫的主要構成部分。使用者倉庫高速緩衝是使用者倉庫的主要組成本部分。物件倉庫則僅僅是記憶體塊組成的記憶體池，不需要進行LRU或成本計算例如SQL Server網路介面(SNI)利用物件儲存倉庫作為網路緩衝池。

　　SQL Server 記憶體管理器由一個三層的層次結構組成。該層次結構的底層為記憶體節點。下一層由記憶體 Clerk、記憶體快取和記憶體池組成。最後一層由記憶體物件組成。這些物件通常用於在 SQL Server 例項中分配記憶體。

　　記憶體節點(sys.dm_os_memory_nodes)提供低階分配器的介面和實現。在NUMA中記憶體節點和CPU節點可以對應起來的，在 SQL Server 中，只有記憶體 Clerk 可訪問記憶體節點。

　 　記憶體 Clerk(sys.dm_os_memory_clerks) 訪問記憶體節點介面以分配記憶體。記憶體節點還會跟蹤 Clerk 分配的記憶體以進行診斷。分配大量記憶體的每個元件，都必須使用 Clerk 介面來建立其自己的記憶體 Clerk 並分配其全部記憶體。各元件會在 SQL Server 啟動時建立其相應的 Clerk。

　　記憶體物件(sys.dm_os_memory_objects)是指多個堆。它們所提供的分配的粒度比記憶體 Clerk 所提供的分配的粒度更精細。SQL Server 元件使用記憶體物件，而不使用記憶體 Clerk。記憶體物件使用記憶體 Clerk 的頁分配器介面來分配頁。記憶體物件不使用虛擬記憶體介面或共享記憶體介面。根據分配模式的不同，元件可以建立不同的記憶體物件型別來分配任意大小的區域。

　　SQL Server的緩衝池只提供8KB的記憶體塊;大於8KB的大記憶體塊需求是被單獨管理的，且一般是直接從作業系統或者說是從緩衝池外獲取到的，此外只有資料高速緩衝頁面才能使用AWE記憶體，並且需要單獨跟蹤。

　　SQLServer的記憶體方面的系統檢視

　 　sys.dm_os_memory_cache_clock_hands 返回特定快取時鐘的每個指標的狀態。提供給使用者關於每個快取儲存區和使用者儲存區的時鐘指標資訊——指標是否正在轉動、圈數、被移除的條目數量等。此檢視對 於查詢當前時鐘指標的狀態以及時鐘指標的移動歷史非常有用。

　　sys.dm_os_memory_cache_counters 返回快取執行狀況的快照。提供有關已分配的快取條目、快取條目的使用情況以及記憶體源的執行時資訊。提供給使用者每個儲存區的總結資訊——使用的記憶體數量、條 目數、正在使用的條目數。使用者可以使用該檢視找到快取的記憶體使用，以及一個快取中的條目數量。

　　 sys.dm_os_memory_cache_hash_tables 針對 SQL Server 例項中的每個活動快取返回一行。即使用者關於快取儲存區的雜湊表資訊——最大、最小、平均桶長等。此檢視對於查詢快取儲存區中每個快取表的每個雜湊桶的條目 分佈非常有用。

　　sys.dm_os_memory_cache_entries 返回有關快取中所有條目的資訊。使用此檢視可對快取條目進行跟蹤，直至它們的關聯物件。還可使用此檢視獲取有關快取條目的統計資訊。

　　sys.dm_os_sys_info返回一組有關計算機和有關 SQL Server 可用資源及其已佔用資源的有用雜項資訊。

　　sys.dm_os_sys_memory 從作業系統返回記憶體資訊。SQL Server 受作業系統級別的外部記憶體條件和基礎硬體物理限制的約束並對其有所響應。確定整個系統的狀態是評估 SQL Server 記憶體使用量的重要方面。

　　sys.dm_os_virtual_address_dump則返回有關呼叫程式的虛擬地址空間中的頁範圍的資訊。

　　DBCC MemoryStatus命令提供了SQL Server的當前記憶體狀態的快照，也可以作為我們分析記憶體瓶頸的重要依據。

　　記憶體壓力

　　對於SQL Server佔用記憶體資源的監控主要集中在頁面吞吐能力、頁面錯誤和可用記憶體上上，對虛擬記憶體的監控，則重點在於分頁檔案的使用率上。下面提供了幾種物件、計數器和相應的閾值及描述。

　　SQL Server提供的sys.dm_os_performance_counters計數器檢視，主要對緩衝區管理器和記憶體管理器的一些計數器進行監控，比如頁面的生存週期、檢查點、惰性寫入器和緩衝命中率等指標。

　　以下為緩衝池內資料庫緩衝池中各個資料庫的分佈情況。

　　以下為返回當前資料庫中每個物件的快取頁計數，加以適當的修改我們也可以得到資料緩衝池中物件資料頁和索引頁的分佈情況。

　　以下為緩衝池中前十位消耗記憶體最大的記憶體元件。

　　我們需要重點關注的記憶體元件為以下：

　 　sys.dm_exec_cached_plans針對 SQL Server 為了加快查詢執行而快取的每個查詢計劃返回一行。可以用此動態管理檢視來查詢快取的查詢計劃、快取的查詢文字、快取計劃佔用的記憶體量，以及重新使用快取計 劃的計數。同樣我們還可以和sys.dm_exec_sql_text聯合起來進一步加工獲取到緩衝最大的前10條SQL。

　　CACHESTORE_SQLCP—SQL執行計劃(臨時快取計劃、自動引數化計劃和預編譯計劃)

　　CACHESTORE_OBJCP—物件計劃(儲存過程、函式、觸發器等執行計劃)

　　CACHESTORE_PHDR—Bound Trees是在SQL Server中代數化的結構過程，被用於檢視、約束和預設值。

　　CACHESTORE_XPRO是預定義的系統儲存過程，這裡僅包含實現過程的函式名稱和DLL名稱。

　　以下SQL用來確認在緩衝區外進行分配了記憶體的內部元件(即通過多頁分配器請求記憶體)，藉以瞭解記憶體是否存在壓力。

　 　sys.dm_exec_cached_plans針對 SQL Server 為了加快查詢執行而快取的每個查詢計劃返回一行。可以用此動態管理檢視來查詢快取的查詢計劃、快取的查詢文字、快取計劃佔用的記憶體量，以及重新使用快取計 劃的計數。同樣我們還可以和sys.dm_exec_sql_text聯合起來進一步加工獲取到緩衝最大的前10條SQL。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-691794/，如需轉載，請註明出處，否則將追究法律責任。