SQL Server 2008內存性能監控
內存的相關概念
以下均是針對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操作系統下也可以通過以下命令執行
#p#
/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計數器視圖，主要對緩沖區管理器和內存管理器的一些計數器進行監控，比如頁面的生存周期、檢查點、惰性寫入器和緩沖命中率等指標。
#p#
以下為緩沖池內數據庫緩沖池中各個數據庫的分布情況。
以下為返回當前數據庫中每個對象的緩存頁計數，加以適當的修改我們也可以得到數據緩沖池中對象數據頁和索引頁的分布情況。
以下為緩沖池中前十位消耗內存***的內存組件。
我們需要重點關注的內存組件為以下：
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。
上文給出了詳細的分析，內容比較多，看起來也比較難理解，對于初學者來說可能是有一定的難度，希望大家能夠認真去學習這些知識，然后很熟練的掌握。
【編輯推薦】
