微軟SQL Server 2008內(nèi)存及I/O性能管理診斷
微軟的SQL Server 2008我已經(jīng)使用了一段時(shí)間，下面就為大家分享一下關(guān)于SQL Server 2008內(nèi)存性能診斷以及內(nèi)存監(jiān)控，以及I/O性能的監(jiān)控。
內(nèi)存性能診斷
下面都是針對(duì)Window 32位系統(tǒng)環(huán)境下，64位的不在下面描述情況下。
用戶模式和內(nèi)核模式(user mode& kernel mode)
為了防止用戶程序訪問并篡改操作系統(tǒng)的關(guān)鍵部分，Windows使用了2種處理器存取模式：用戶模式和內(nèi)核模式。顧名思義，內(nèi)核模式是給操作系統(tǒng)核心代碼和基本驅(qū)動(dòng)用的，用戶模式給用戶應(yīng)用程序。在內(nèi)核模式下程序可以訪問所有的內(nèi)存和硬件，并使用所有的處理器指令。操作系統(tǒng)程序比用戶程序有更高的權(quán)限，使得系統(tǒng)設(shè)計(jì)者可以確保用戶程序不會(huì)意外的破壞系統(tǒng)的穩(wěn)定性。
物理內(nèi)存(Physical Memory)
即實(shí)際購買的內(nèi)存的大小，內(nèi)存條上的容量。CPU的地址線可以直接進(jìn)行尋址的內(nèi)存空間大小，在32位操作系統(tǒng)平臺(tái)上，CPU的***尋址空間為4GB，也即可以支持***4G的物理內(nèi)存空間。在32位操作系統(tǒng)上即便你購買的是64G內(nèi)存，也只能說擁有4GB的物理內(nèi)存空間
虛擬內(nèi)存(Virtual Memory)
如果計(jì)算機(jī)缺少運(yùn)行程序或操作所需的隨機(jī)存儲(chǔ)內(nèi)存，則Windows使用虛擬內(nèi)存進(jìn)行補(bǔ)償。虛擬內(nèi)存將計(jì)算機(jī)的RAM和硬盤上的臨時(shí)空間組合在一起，當(dāng)RAM不足時(shí)，虛擬內(nèi)存將數(shù)據(jù)從RAM移動(dòng)到稱為“分頁文件”的空間中，將數(shù)據(jù)移入與移出分頁文件可以釋放RAM，以便完成工作。
虛擬地址空間(Virtual Address Space，簡稱VAS)
在Windows系統(tǒng)中，任何一個(gè)進(jìn)程都被賦予了其自己的虛擬地址空間，該虛擬地址空間覆蓋了一個(gè)相當(dāng)大的范圍，對(duì)于32位系統(tǒng)的虛擬地址空間范圍從0x00000000~0xffffffff(4GB)。Windows采用分頁機(jī)制，將4G的地址空間分成固定大小的頁，并且將虛擬地址中的每一頁映射到物理內(nèi)存中。
在缺省的情況下虛擬地址空間中的低2G，即0x0000000~0x7FFFFFFFF是用戶地址空間，而4G虛擬地址空間中的高2G即0x8000000~0xFFFFFFFF是分配給內(nèi)核模式。實(shí)際上用戶進(jìn)程擁有的虛擬地址空間只有2GB。
虛擬內(nèi)存管理器(Virtual Memory Manager)
虛擬內(nèi)存管理器負(fù)責(zé)虛擬地址空間和物理內(nèi)存的地址映射，如果缺乏足夠內(nèi)存，則需要使用到page file文件來保持臨時(shí)數(shù)據(jù)也即虛擬內(nèi)存，同時(shí)使用page table entry(PTE)來跟蹤每一個(gè)地址映射關(guān)系。
到這里為止，64G的內(nèi)存有60G都無法訪問，豈不是浪費(fèi)了，那怎么辦?
/3GB和increaseUserVA
通過/3GB的方式，可以減少內(nèi)核模式占用地址空間，從而增加SQL Server進(jìn)程的地址空間。默認(rèn)情況下，用戶模式和內(nèi)核模式各自占用2G尋址空間，3G選項(xiàng)可以使得SQL Server獲得多1G的虛擬地址空間。
/3GB開關(guān)用法：
在Boot.ini文件中修改其中的段落即可：
或者使用bootcfg命令
在Windows Server 2008中可以運(yùn)行BCDEdit命令，加以調(diào)整。
物理地址擴(kuò)展PAE(Physical Address Extension)
物理地址擴(kuò)展(PAE)是32位Intel CPU的一種擴(kuò)展，這樣可以在32位系統(tǒng)上支持***64G的物理內(nèi)存，即4GB以上物理內(nèi)存允許將更多物理內(nèi)存映射為應(yīng)用程序的虛擬地址空間。
使用方式,在Boot.ini文件中修改其中的段落即可：
在Windows Server 2008操作系統(tǒng)下也可以通過以下命令執(zhí)行
/PAE和/3GB
兩者的目標(biāo)是不同的，又可以在同樣的地方進(jìn)行配置，所以難免產(chǎn)生疑惑，簡單的來說就是如果計(jì)算機(jī)可用物理內(nèi)存超過16GB，就需要確保boot.ini文件中沒有/3gb參數(shù)即可。
地址窗口化擴(kuò)展插件AWE(Address Windowing Extensions)
AWE是Windows的內(nèi)存管理功能的一組擴(kuò)展，它能夠使應(yīng)用程序使用的內(nèi)存量超過通過標(biāo)準(zhǔn)32位尋址可使用的2~3G內(nèi)存。AWE允許應(yīng)用程序獲取物理內(nèi)存，然后將非分頁內(nèi)存的視圖映射到32位地址空間。雖然32位地址空間限制為4GB，但是非分頁內(nèi)存卻可以遠(yuǎn)遠(yuǎn)大于4GB。
在SQL Server 2008下，可以登錄SQL Server Management Studio，找到相應(yīng)的數(shù)據(jù)庫實(shí)例，點(diǎn)擊右鍵選擇屬性，然后在“選擇頁”中點(diǎn)擊內(nèi)存，在服務(wù)器內(nèi)存選項(xiàng)中，復(fù)選使用AWE分配內(nèi)存即可。
#p#
SQL Server 2008的內(nèi)存管理
SQL Server 主要的內(nèi)存組件是緩沖池。其中高速數(shù)據(jù)緩沖區(qū)用以把數(shù)據(jù)從磁盤加載到內(nèi)存中，實(shí)現(xiàn)數(shù)據(jù)的高速讀寫。而過程高速緩沖區(qū)則用來存儲(chǔ)相應(yīng)的執(zhí)行計(jì)劃，減少編譯過程，也是高速緩沖倉庫的主要構(gòu)成部分。用戶倉庫高速緩沖是用戶倉庫的主要組成本部分。對(duì)象倉庫則僅僅是內(nèi)存塊組成的內(nèi)存池，不需要進(jìn)行LRU或成本計(jì)算例如SQL Server網(wǎng)絡(luò)接口(SNI)利用對(duì)象存儲(chǔ)倉庫作為網(wǎng)絡(luò)緩沖池。
SQL Server 內(nèi)存管理器由一個(gè)三層的層次結(jié)構(gòu)組成。該層次結(jié)構(gòu)的底層為內(nèi)存節(jié)點(diǎn)。下一層由內(nèi)存 Clerk、內(nèi)存緩存和內(nèi)存池組成。***一層由內(nèi)存對(duì)象組成。這些對(duì)象通常用于在 SQL Server 實(shí)例中分配內(nèi)存。
內(nèi)存節(jié)點(diǎn)(sys.dm_os_memory_nodes)提供低級(jí)分配器的界面和實(shí)現(xiàn)。在NUMA中內(nèi)存節(jié)點(diǎn)和CPU節(jié)點(diǎn)可以對(duì)應(yīng)起來的，在 SQL Server 中，只有內(nèi)存 Clerk 可訪問內(nèi)存節(jié)點(diǎn)。
內(nèi)存 Clerk(sys.dm_os_memory_clerks) 訪問內(nèi)存節(jié)點(diǎn)界面以分配內(nèi)存。內(nèi)存節(jié)點(diǎn)還會(huì)跟蹤 Clerk 分配的內(nèi)存以進(jìn)行診斷。分配大量內(nèi)存的每個(gè)組件，都必須使用 Clerk 界面來創(chuàng)建其自己的內(nèi)存 Clerk 并分配其全部內(nèi)存。各組件會(huì)在 SQL Server 啟動(dòng)時(shí)創(chuàng)建其相應(yīng)的 Clerk。
內(nèi)存對(duì)象(sys.dm_os_memory_objects)是指多個(gè)堆。它們所提供的分配的粒度比內(nèi)存 Clerk 所提供的分配的粒度更精細(xì)。SQL Server 組件使用內(nèi)存對(duì)象，而不使用內(nèi)存 Clerk。內(nèi)存對(duì)象使用內(nèi)存 Clerk 的頁分配器接口來分配頁。內(nèi)存對(duì)象不使用虛擬內(nèi)存接口或共享內(nèi)存接口。根據(jù)分配模式的不同，組件可以創(chuàng)建不同的內(nèi)存對(duì)象類型來分配任意大小的區(qū)域。
SQL Server的緩沖池只提供8KB的內(nèi)存塊;大于8KB的大內(nèi)存塊需求是被單獨(dú)管理的，且一般是直接從操作系統(tǒng)或者說是從緩沖池外獲取到的，此外只有數(shù)據(jù)高速緩沖頁面才能使用AWE內(nèi)存，并且需要單獨(dú)跟蹤?！?/P>
SQL Server 2008的內(nèi)存方面的系統(tǒng)視圖
sys.dm_os_memory_cache_clock_hands 返回特定緩存時(shí)鐘的每個(gè)指針的狀態(tài)。提供給用戶關(guān)于每個(gè)緩存存儲(chǔ)區(qū)和用戶存儲(chǔ)區(qū)的時(shí)鐘指針信息——指針是否正在轉(zhuǎn)動(dòng)、圈數(shù)、被移除的條目數(shù)量等。此視圖對(duì)于查找當(dāng)前時(shí)鐘指針的狀態(tài)以及時(shí)鐘指針的移動(dòng)歷史非常有用。
sys.dm_os_memory_cache_counters 返回緩存運(yùn)行狀況的快照。提供有關(guān)已分配的緩存條目、緩存條目的使用情況以及內(nèi)存源的運(yùn)行時(shí)信息。提供給用戶每個(gè)存儲(chǔ)區(qū)的總結(jié)信息——使用的內(nèi)存數(shù)量、條目數(shù)、正在使用的條目數(shù)。用戶可以使用該視圖找到緩存的內(nèi)存使用，以及一個(gè)緩存中的條目數(shù)量。
sys.dm_os_memory_cache_hash_tables 針對(duì) SQL Server 實(shí)例中的每個(gè)活動(dòng)緩存返回一行。即用戶關(guān)于緩存存儲(chǔ)區(qū)的散列表信息——***、最小、平均桶長等。此視圖對(duì)于查找緩存存儲(chǔ)區(qū)中每個(gè)緩存表的每個(gè)散列桶的條目分布非常有用。
sys.dm_os_memory_cache_entries 返回有關(guān)緩存中所有條目的信息。使用此視圖可對(duì)緩存條目進(jìn)行跟蹤，直至它們的關(guān)聯(lián)對(duì)象。還可使用此視圖獲取有關(guān)緩存條目的統(tǒng)計(jì)信息。
sys.dm_os_sys_info返回一組有關(guān)計(jì)算機(jī)和有關(guān) SQL Server 可用資源及其已占用資源的有用雜項(xiàng)信息。
sys.dm_os_sys_memory 從操作系統(tǒng)返回內(nèi)存信息。SQL Server 受操作系統(tǒng)級(jí)別的外部內(nèi)存條件和基礎(chǔ)硬件物理限制的約束并對(duì)其有所響應(yīng)。確定整個(gè)系統(tǒng)的狀態(tài)是評(píng)估 SQL Server 內(nèi)存使用量的重要方面。
sys.dm_os_virtual_address_dump則返回有關(guān)調(diào)用進(jìn)程的虛擬地址空間中的頁范圍的信息。
DBCC MemoryStatus命令提供了SQL Server的當(dāng)前內(nèi)存狀態(tài)的快照，也可以作為我們分析內(nèi)存瓶頸的重要依據(jù)。
內(nèi)存壓力
對(duì)于SQL Server占用內(nèi)存資源的監(jiān)控主要集中在頁面吞吐能力、頁面錯(cuò)誤和可用內(nèi)存上上，對(duì)虛擬內(nèi)存的監(jiān)控，則重點(diǎn)在于分頁文件的使用率上。下面提供了幾種對(duì)象、計(jì)數(shù)器和相應(yīng)的閾值及描述。
SQL Server提供的sys.dm_os_performance_counters計(jì)數(shù)器視圖，主要對(duì)緩沖區(qū)管理器和內(nèi)存管理器的一些計(jì)數(shù)器進(jìn)行監(jiān)控，比如頁面的生存周期、檢查點(diǎn)、惰性寫入器和緩沖命中率等指標(biāo)。
以下為緩沖池內(nèi)數(shù)據(jù)庫緩沖池中各個(gè)數(shù)據(jù)庫的分布情況。
以下為返回當(dāng)前數(shù)據(jù)庫中每個(gè)對(duì)象的緩存頁計(jì)數(shù)，加以適當(dāng)?shù)男薷奈覀円部梢缘玫綌?shù)據(jù)緩沖池中對(duì)象數(shù)據(jù)頁和索引頁的分布情況。
以下為緩沖池中前十位消耗內(nèi)存***的內(nèi)存組件。
我們需要重點(diǎn)關(guān)注的內(nèi)存組件為以下：
sys.dm_exec_cached_plans針對(duì) SQL Server 為了加快查詢執(zhí)行而緩存的每個(gè)查詢計(jì)劃返回一行?？梢杂么藙?dòng)態(tài)管理視圖來查找緩存的查詢計(jì)劃、緩存的查詢文本、緩存計(jì)劃占用的內(nèi)存量，以及重新使用緩存計(jì)劃的計(jì)數(shù)。同樣我們還可以和sys.dm_exec_sql_text聯(lián)合起來進(jìn)一步加工獲取到緩沖***的前10條SQL。
CACHESTORE_SQLCP—SQL執(zhí)行計(jì)劃(臨時(shí)緩存計(jì)劃、自動(dòng)參數(shù)化計(jì)劃和預(yù)編譯計(jì)劃)
CACHESTORE_OBJCP—對(duì)象計(jì)劃(存儲(chǔ)過程、函數(shù)、觸發(fā)器等執(zhí)行計(jì)劃)
CACHESTORE_PHDR—Bound Trees是在SQL Server中代數(shù)化的結(jié)構(gòu)過程，被用于視圖、約束和默認(rèn)值。
CACHESTORE_XPRO是預(yù)定義的系統(tǒng)存儲(chǔ)過程，這里僅包含實(shí)現(xiàn)過程的函數(shù)名稱和DLL名稱。
以下SQL用來確認(rèn)在緩沖區(qū)外進(jìn)行分配了內(nèi)存的內(nèi)部組件(即通過多頁分配器請(qǐng)求內(nèi)存)，借以了解內(nèi)存是否存在壓力。
sys.dm_exec_cached_plans針對(duì) SQL Server 為了加快查詢執(zhí)行而緩存的每個(gè)查詢計(jì)劃返回一行?？梢杂么藙?dòng)態(tài)管理視圖來查找緩存的查詢計(jì)劃、緩存的查詢文本、緩存計(jì)劃占用的內(nèi)存量，以及重新使用緩存計(jì)劃的計(jì)數(shù)。同樣我們還可以和sys.dm_exec_sql_text聯(lián)合起來進(jìn)一步加工獲取到緩沖***的前10條SQL。
#p#
I/O性能診斷
SQL Server性能非常依賴于I/O子系統(tǒng)。除非你的數(shù)據(jù)庫適合物理內(nèi)存，SQL Server經(jīng)常地會(huì)有數(shù)據(jù)庫頁面進(jìn)出緩存池。這樣就發(fā)生了實(shí)質(zhì)的I/O流量。同樣，在事務(wù)被明確的提交前，日志記錄需要寫入磁盤。SQL Server為各種目的可以使用tempdb，例如存儲(chǔ)中間結(jié)果，排序，保持行的版本或其他。所以好的I/O子系統(tǒng)對(duì)于SQL Server性能非常重要。
I/O的性能取決于以下一些方面：
磁盤類型包括IDE、SATA、SCSI、SAS、Fibre Channel drive等類型，其中IDE、SATA一般用在個(gè)人電腦上。
同時(shí)為了在數(shù)據(jù)安全、數(shù)據(jù)性能和數(shù)據(jù)容量之間做平衡，又發(fā)展出了RAID，RAID是一種把多塊獨(dú)立的磁盤按不同的方式組合起來形成一個(gè)硬盤組，從而提供比單個(gè)硬盤更高的存儲(chǔ)性能和提高數(shù)據(jù)備份技術(shù)。RAID主要包括RAID0~RAID7等幾個(gè)規(guī)范，常用的RAID類型為RAID0、RAID1、RAID5,RAID10。
此外根據(jù)連接方式不同還可以分為：Direct Attached Storage(DAS)，Storage Area Networks(SAN)，F(xiàn)ibre Channel Storage Area Networks，iSCSI Storage Area Networks。
吞吐量和IOPS指標(biāo)
吞吐量主要取決于陣列的架構(gòu)，光纖通道的大小以及硬盤的個(gè)數(shù)。陣列的架構(gòu)與每個(gè)陣列不同，但也都存在內(nèi)部帶寬，不過在一般情況下，內(nèi)部帶寬都設(shè)計(jì)的很充足，不是瓶頸所在。其次是光纖通道對(duì)數(shù)據(jù)流量的影響，為了達(dá)到1GB/s的數(shù)據(jù)流量要求，我們必須使用1GB*8=8GB的光纖卡，也可以用4塊2GB的光纖卡。其實(shí)是硬盤的個(gè)數(shù)，可以參考以下指標(biāo)計(jì)算方式，假設(shè)為了滿足1GB的數(shù)據(jù)流量要求，所必須的磁盤個(gè)數(shù)。
IOPS(Input/Output Operations Per Second)，即每秒進(jìn)行讀寫(I/O)操作的次數(shù)，多用于數(shù)據(jù)庫等場合，衡量隨機(jī)訪問的性能。
決定IOPS的主要取決于陣列的算法、cache命中率以及磁盤個(gè)數(shù)。Cache命中率取決于數(shù)據(jù)的分布、Cache Size的大小、數(shù)據(jù)的訪問規(guī)則，以及Cache的算法。
磁盤的限制，每個(gè)磁盤能處理的IOPS是有限制的，通常情況下每個(gè)磁盤的***IOPS是確定的，比如IDE和SATA硬盤的IOPS大致在100以內(nèi)(我們可以使用HD Tune工具進(jìn)行IOPS測試)，而且IOPS的測試結(jié)果與測試方式(例如隨機(jī)讀寫和順序讀寫、讀和寫的比例、傳輸數(shù)據(jù)庫尺寸的大小、磁盤的數(shù)量)有很大關(guān)系，盡管如此磁盤的IOPS指標(biāo)還是對(duì)我們?cè)u(píng)估磁盤的壓力和是否能夠滿足系統(tǒng)的性能需求有著一定的指導(dǎo)意義。
假設(shè)現(xiàn)在的業(yè)務(wù)需求是10000 IOPS，120塊SCSI磁盤，那么在不同的Cache命中率、不同的讀寫比例情況下，不同的RAID級(jí)別對(duì)每塊磁盤的IOPS需求是多少呢?
Raid 0 –每個(gè)磁盤的I/O計(jì)算= (讀+寫) /磁盤個(gè)數(shù)
Raid 1 --每個(gè)磁盤的I/O計(jì)算= [讀+(2*寫)]/2
Raid 5 --每個(gè)磁盤的I/O計(jì)算= [讀+(4*寫)]/磁盤個(gè)數(shù)
Raid 10 --每個(gè)磁盤的I/O計(jì)算= [讀+(2*寫)]/磁盤個(gè)數(shù)
此外當(dāng)吞吐率超過85%時(shí)，會(huì)出現(xiàn)I/O瓶頸，因此單個(gè)磁盤IOPS計(jì)算規(guī)則為
((10000*(1-Cache命中率)*讀比例)+10000*寫比例*RAID系數(shù))/磁盤數(shù)/0.85
即每塊磁盤的IOPS大約在200左右即可滿足RAID0、RAID5、RAID10的要求。
此外，關(guān)于SQL Server的部署一般規(guī)劃和建議如下：
操作系統(tǒng)和SQL Server單獨(dú)構(gòu)建在RAID1的磁盤鏡像上;出于高速和安全的原則，日志文件需要單獨(dú)安裝在RAID1/RAID10上;tempdb文件***放在RAID0上，而數(shù)據(jù)文件出于安全、性能、容量、成本的綜合考慮一般則使用RAID5。
在微軟的technet上有一篇關(guān)于存儲(chǔ)的***實(shí)踐top 10(Storage Top 10 Best Practices)是這么要求的：
1. 了解SQL Server的IO特性和應(yīng)用系統(tǒng)的IO需求規(guī)格。
2. 使用更多/更快的磁盤驅(qū)動(dòng)以獲取良好的性能
3. 不要過度優(yōu)化存儲(chǔ)，簡單的設(shè)計(jì)通常能夠提供良好的性能和靈活性。
4. 部署前驗(yàn)證配置。可以用SQLIO之類的工具模擬測試。
5. 始終把日志文件放在RAID10/RAID1上。
6. 把日志文件和數(shù)據(jù)文件從物理磁盤上隔離。
7. 認(rèn)真考慮TempDB的數(shù)據(jù)配置。
8. 在數(shù)據(jù)文件的數(shù)量和CPU的容量之間平衡。
9. 不要忽視SQL Server的基礎(chǔ)。
10.不要忽視存儲(chǔ)的配置
對(duì)于SQL Server占用I/O資源的監(jiān)控主要集中在磁盤響應(yīng)時(shí)間、隊(duì)列長度、磁盤讀寫和傳輸速度上。下面提供了幾種對(duì)象、計(jì)數(shù)器和相應(yīng)的閾值及描述。
Sys.dm_io_virtual_file_stats能夠返回?cái)?shù)據(jù)和日志文件的 I/O 統(tǒng)計(jì)信息，這也為我們從整體上了解各磁盤和數(shù)據(jù)庫的吞吐量和等待時(shí)間有了一個(gè)直觀的認(rèn)識(shí)。
sys.dm_io_pending_io_requests則對(duì)應(yīng)SQL Server 中每個(gè)掛起的 I/O 請(qǐng)求，我們將sys.dm_io_pending_io_requests和Sys.dm_io_virtual_file_stats關(guān)聯(lián)起來，則可以查看當(dāng)前是否有等待的IO，然后進(jìn)行去定位和識(shí)別。
微軟的SQL Server 2008的一些關(guān)于內(nèi)存和I/O方面的介紹就到這里，聲明我這只是拋磚引玉呢。
【編輯推薦】
