微軟SQL Server 2008內存及I/O性能管理診斷
微軟的SQL Server 2008我已經使用了一段時間，下面就為大家分享一下關于SQL Server 2008內存性能診斷以及內存監控，以及I/O性能的監控。
內存性能診斷
下面都是針對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分配內存即可。
#p#
SQL Server 2008的內存管理
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內存，并且需要單獨跟蹤?！?/P>
SQL Server 2008的內存方面的系統視圖
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。
#p#
I/O性能診斷
SQL Server性能非常依賴于I/O子系統。除非你的數據庫適合物理內存，SQL Server經常地會有數據庫頁面進出緩存池。這樣就發生了實質的I/O流量。同樣，在事務被明確的提交前，日志記錄需要寫入磁盤。SQL Server為各種目的可以使用tempdb，例如存儲中間結果，排序，保持行的版本或其他。所以好的I/O子系統對于SQL Server性能非常重要。
I/O的性能取決于以下一些方面：
磁盤類型包括IDE、SATA、SCSI、SAS、Fibre Channel drive等類型，其中IDE、SATA一般用在個人電腦上。
同時為了在數據安全、數據性能和數據容量之間做平衡，又發展出了RAID，RAID是一種把多塊獨立的磁盤按不同的方式組合起來形成一個硬盤組，從而提供比單個硬盤更高的存儲性能和提高數據備份技術。RAID主要包括RAID0~RAID7等幾個規范，常用的RAID類型為RAID0、RAID1、RAID5,RAID10。
此外根據連接方式不同還可以分為：Direct Attached Storage(DAS)，Storage Area Networks(SAN)，Fibre Channel Storage Area Networks，iSCSI Storage Area Networks。
吞吐量和IOPS指標
吞吐量主要取決于陣列的架構，光纖通道的大小以及硬盤的個數。陣列的架構與每個陣列不同，但也都存在內部帶寬，不過在一般情況下，內部帶寬都設計的很充足，不是瓶頸所在。其次是光纖通道對數據流量的影響，為了達到1GB/s的數據流量要求，我們必須使用1GB*8=8GB的光纖卡，也可以用4塊2GB的光纖卡。其實是硬盤的個數，可以參考以下指標計算方式，假設為了滿足1GB的數據流量要求，所必須的磁盤個數。
IOPS(Input/Output Operations Per Second)，即每秒進行讀寫(I/O)操作的次數，多用于數據庫等場合，衡量隨機訪問的性能。
決定IOPS的主要取決于陣列的算法、cache命中率以及磁盤個數。Cache命中率取決于數據的分布、Cache Size的大小、數據的訪問規則，以及Cache的算法。
磁盤的限制，每個磁盤能處理的IOPS是有限制的，通常情況下每個磁盤的***IOPS是確定的，比如IDE和SATA硬盤的IOPS大致在100以內(我們可以使用HD Tune工具進行IOPS測試)，而且IOPS的測試結果與測試方式(例如隨機讀寫和順序讀寫、讀和寫的比例、傳輸數據庫尺寸的大小、磁盤的數量)有很大關系，盡管如此磁盤的IOPS指標還是對我們評估磁盤的壓力和是否能夠滿足系統的性能需求有著一定的指導意義。
假設現在的業務需求是10000 IOPS，120塊SCSI磁盤，那么在不同的Cache命中率、不同的讀寫比例情況下，不同的RAID級別對每塊磁盤的IOPS需求是多少呢?
Raid 0 –每個磁盤的I/O計算= (讀+寫) /磁盤個數
Raid 1 --每個磁盤的I/O計算= [讀+(2*寫)]/2
Raid 5 --每個磁盤的I/O計算= [讀+(4*寫)]/磁盤個數
Raid 10 --每個磁盤的I/O計算= [讀+(2*寫)]/磁盤個數
此外當吞吐率超過85%時，會出現I/O瓶頸，因此單個磁盤IOPS計算規則為
((10000*(1-Cache命中率)*讀比例)+10000*寫比例*RAID系數)/磁盤數/0.85
即每塊磁盤的IOPS大約在200左右即可滿足RAID0、RAID5、RAID10的要求。
此外，關于SQL Server的部署一般規劃和建議如下：
操作系統和SQL Server單獨構建在RAID1的磁盤鏡像上;出于高速和安全的原則，日志文件需要單獨安裝在RAID1/RAID10上;tempdb文件***放在RAID0上，而數據文件出于安全、性能、容量、成本的綜合考慮一般則使用RAID5。
在微軟的technet上有一篇關于存儲的***實踐top 10(Storage Top 10 Best Practices)是這么要求的：
1. 了解SQL Server的IO特性和應用系統的IO需求規格。
2. 使用更多/更快的磁盤驅動以獲取良好的性能
3. 不要過度優化存儲，簡單的設計通常能夠提供良好的性能和靈活性。
4. 部署前驗證配置。可以用SQLIO之類的工具模擬測試。
5. 始終把日志文件放在RAID10/RAID1上。
6. 把日志文件和數據文件從物理磁盤上隔離。
7. 認真考慮TempDB的數據配置。
8. 在數據文件的數量和CPU的容量之間平衡。
9. 不要忽視SQL Server的基礎。
10.不要忽視存儲的配置
對于SQL Server占用I/O資源的監控主要集中在磁盤響應時間、隊列長度、磁盤讀寫和傳輸速度上。下面提供了幾種對象、計數器和相應的閾值及描述。
Sys.dm_io_virtual_file_stats能夠返回數據和日志文件的 I/O 統計信息，這也為我們從整體上了解各磁盤和數據庫的吞吐量和等待時間有了一個直觀的認識。
sys.dm_io_pending_io_requests則對應SQL Server 中每個掛起的 I/O 請求，我們將sys.dm_io_pending_io_requests和Sys.dm_io_virtual_file_stats關聯起來，則可以查看當前是否有等待的IO，然后進行去定位和識別。
微軟的SQL Server 2008的一些關于內存和I/O方面的介紹就到這里，聲明我這只是拋磚引玉呢。
【編輯推薦】
