SQLServer性能監(jiān)控指標(biāo)說明

1、性能監(jiān)控指標(biāo)說明 -MSSQL SERVER 2005 趙小艷 2010年6月30日 2010-06-259/27 從MSSQL SERVER2005 JF始，微軟開始了漫長(zhǎng)的向ORACLE學(xué)習(xí)之旅，本文以oracle優(yōu) 化為基礎(chǔ)，整理了一部分MSSQL SERVER2005監(jiān)控指標(biāo)，這些指標(biāo)不能全部反映MSSQL SERVER2005的性能信息，因此還需要從更多的開發(fā)的角度后繼完成優(yōu)化過程，英中最重要 的一環(huán)是設(shè)計(jì)。 由于下一步的工作就是實(shí)際的優(yōu)化，請(qǐng)大家指出文檔中的不足和錯(cuò)誤之處。 本文整理用到的資料：淸華大學(xué)岀版社的SQL SERVER 2005性能調(diào)優(yōu)，微軟技術(shù)資料 庫(kù)，MSDNo 目

2、錄 1. 配置硬件5 1.1. 內(nèi)存5 1.1.1. 物理地址空間5 1.1.2. 虛擬地址空間5 1.1.3. 虛擬內(nèi)存管理器5 1.1.43GB 內(nèi)存5 1.1.5 PAE6 1.1.6 AWE6 1.1.7選擇/3GB 、 /PAE.還是/AWE6 64bit系統(tǒng)內(nèi)存配置6 1.1.9.內(nèi)存預(yù)計(jì)大小6 1.2. I/O7 1.2.1. 網(wǎng)絡(luò) 107 1.2.2磁盤107 1.23.存儲(chǔ)設(shè)計(jì)7 1.2.4. 配置分區(qū)7 1.2.5扇區(qū)對(duì)齊8 1.2.6. 卷的類型8 1.2.7. NTFS分配空間的大小8 1.2.8. 磁盤破碎8 1.3. CPU9 1.4. 32bit x86 處理器9

3、 1.4.1多核9 1.4.2. 超線程9 1.4.3 64bit9 1.4.4X64 或 IA649 1.4.5高速緩存10 1.5. 系統(tǒng)架構(gòu)10 1.5.1. SMP10 1.5.2 NUMA10 1.53SOFT NUMA10 2. 系統(tǒng)監(jiān)控指標(biāo)11 2.1. 內(nèi)存11 2.1.1. 外部壓力11 2.1.2內(nèi)部?jī)?nèi)存壓力11 2.13.確認(rèn)內(nèi)存瓶頸11 2.2. 磁盤12 2.2.1. 基于配置的磁盤瓶頸12 2.2.2. 基于模式的磁盤瓶頸12 223指標(biāo)13 23Cpu13 2.3.1. 基于配置的cpu瓶頸14 2.3.2. 基于模式的cpu瓶頸14 2.4. 數(shù)據(jù)庫(kù)鏡像性能14

4、 3. 服務(wù)器設(shè)程14 3.1. Cpu14 4. 數(shù)據(jù)庫(kù)等待事件15 4.1. 常見等待類型15 5. 數(shù)據(jù)庫(kù)指標(biāo)16 5.1. 指標(biāo)采集和排列16 5.1.1. 操作系統(tǒng)資源16 5.1.2. 重要統(tǒng)計(jì)信息17 5.1.3. 等待事件18 5.1.4. 數(shù)據(jù)文件和日志文件18 5.1.5. TEMPDB使用21 5.1.6. 系統(tǒng)當(dāng)前資源髙消耗sql22 5.1.7. 當(dāng)前阻塞和物理io多的會(huì)話請(qǐng)求信息22 5.1.8. 查詢計(jì)劃多的sql語句24 5.1.9. 索引使用情況25 6. 優(yōu)化方法26 6.1. 統(tǒng)計(jì)信息26 6.2. 綁定變量26 6.3. 提示27 7. 錯(cuò)誤27 1.配

5、置硬件 1.1.1.物理地址空間 物理地址是處理器用來訪問位于其總線上的所有部件的地址集合。PAE地址總線是36bito 64bit服務(wù)器生產(chǎn)商將地址總線限制在44bit。 1.1.2.虛擬地址空間 在32bit系統(tǒng)中，進(jìn)程有4gb虛擬地址空間，虛擬內(nèi)存由內(nèi)核和用戶進(jìn)程共享。內(nèi)核和 用戶進(jìn)程各使用2g. 1.13.虛擬內(nèi)存管理器 虛擬內(nèi)存管理器（vmm管理所有內(nèi)存，并在系統(tǒng)中所有使用內(nèi)存的進(jìn)程間共享。虛擬 內(nèi)存管理器使每一個(gè)進(jìn)程在需要內(nèi)存時(shí)提供4gb的虛擬內(nèi)存的錯(cuò)覺，事實(shí)上所有進(jìn)程爭(zhēng)奪有 限的物理內(nèi)存。 操作系統(tǒng)的內(nèi)存有限，為了讓進(jìn)程有使用4gb虛擬內(nèi)存的錯(cuò)誤，用交換空間來保證所有 進(jìn)程的虛擬

6、內(nèi)存空間的使用。 如果系統(tǒng)有足夠的物理內(nèi)存，那么vmm只要分發(fā)內(nèi)存并在進(jìn)程結(jié)束時(shí)淸理內(nèi)存即可。 如果沒有足夠的物理內(nèi)存，vmm必需為每一個(gè)進(jìn)程在需要時(shí)提供所需的內(nèi)存。Vmm通過頁(yè) 而文件暫存進(jìn)程有一段時(shí)間沒有訪問的數(shù)據(jù)來實(shí)現(xiàn)，這個(gè)過程叫分頁(yè)。進(jìn)程的數(shù)據(jù)經(jīng)常被分 頁(yè)或者交換到磁盤。 1.1.4. 3GB 內(nèi)存 增加32bit系統(tǒng)中進(jìn)程內(nèi)存數(shù)量的一種方法是將分配給內(nèi)核的一部分空間拿來用作用戶 模式地址空間。 /3GB選項(xiàng)將邊界移動(dòng)到3GB,每個(gè)進(jìn)程額外獲得1GB的vas。此時(shí)內(nèi)核只有l(wèi)gb虛擬內(nèi) 存，有可能會(huì)產(chǎn)生問題導(dǎo)致朋潰。 /USERVA是windows server2OO3新增的功能，提供從

7、內(nèi)核中獲取指左的不同地址空間數(shù) 戢的方式，使每個(gè)進(jìn)程額外獲得Ogb到lgb的地址空間，與3GB方式相同，當(dāng)然也會(huì)導(dǎo)致 1.1.5. /PAE Intel引入用來突破32bit地址總線的限制，將地址總線擴(kuò)展為36bit。允許32bit最多訪 問64GB內(nèi)存，要求是：企業(yè)版或者更高版本的windows服務(wù)器，必需運(yùn)行（ windows 2003 spl之上的版本。 這個(gè)配置也是在boot.ini中。也可以在datacenter版本的os F*也會(huì)使用PAE。 :Ll動(dòng)態(tài)卷在windows 2000引入，支持額外的特性,如跨區(qū)可擴(kuò)展卷和軟件驅(qū)動(dòng)RAID。 隨著硬件的發(fā)展，動(dòng)態(tài)磁盤被放棄了。 1.2.

8、7. NTFS分配空間的大小 在windows中格式化一個(gè)分區(qū)時(shí)，會(huì)指立NTFS分配單元的大小，微如室測(cè)試表明，對(duì) 于數(shù)據(jù)文件和日志文件的分區(qū)，最佳單元大小是64KB。 1.2.8.磁盤破碎 在SQL SERVER中破碎可能一兩種形式發(fā)生：發(fā)生在數(shù)據(jù)陳舊并經(jīng)歷了大量的插入、更 新、刪除時(shí)；發(fā)生在創(chuàng)建文件時(shí)，文件系統(tǒng)沒有足夠的連續(xù)空間在單獨(dú)的片段中創(chuàng)建文件, 結(jié)果是導(dǎo)致一個(gè)文件分布在磁盤表明的多個(gè)文件片段中。 當(dāng)sql server文件一旦被創(chuàng)建后就不會(huì)變得更加破碎，如果創(chuàng)建時(shí)沒有連續(xù)的空間，文 件在多個(gè)片段中創(chuàng)建，如果此后進(jìn)行了碎片整理，那么文件就不會(huì)呈碎片狀，將來也不會(huì)。 理想的場(chǎng)景是對(duì)sq

9、l server文件使用專用磁盤，并設(shè)宜合適的大小，并禁用自動(dòng)增長(zhǎng)。 如果沒有專用的驅(qū)動(dòng)器用于數(shù)據(jù)庫(kù)，而且構(gòu)建工作從頭開始，那么最好的方法是： 1. 安裝os 2. 整理磁盤碎片 3. 安裝所有應(yīng)用程序 4. 整理磁盤碎片 5. 以最大大小創(chuàng)建數(shù)據(jù)文件和日志文件 6. 停止sql server,檢查碎片，有需要整理磁盤碎片 7. 禁用自增長(zhǎng)，或者將增量設(shè)置大一些。 8. 周期性進(jìn)行磁盤碎片整理。 1.3. CPU 1.4.32bitx86 處理器 以后會(huì)被64bit取代。 1.4.1. 多核 摩爾定律是處理器的能力每28個(gè)月就會(huì)翻番，現(xiàn)在有變?yōu)槊?0個(gè)月就翻番的趨勢(shì)。多 核是指同一個(gè)芯片上有個(gè)

10、cpu。Ms的per-processor的許可證發(fā)給每個(gè)插槽，而不是每個(gè)內(nèi) 核。對(duì)用戶來說4個(gè)雙核cpu同8個(gè)單核服務(wù)器性能相當(dāng)，但是許可成本減半。 1.4.2. 超線程 線程是運(yùn)行在cpu上的執(zhí)行單元，cpu每次只能執(zhí)行一個(gè)線程，它通過在線程間切換產(chǎn) 生并發(fā)的錯(cuò)覺。 超線程是intel處理器的技術(shù)，試圖通過復(fù)制架構(gòu)狀態(tài)來提供兩個(gè)邏輯cpu,完成同時(shí)使 用處理器不同部件執(zhí)行不同任務(wù)的工作。使用超線程提供理論上30%性能的好處，實(shí)踐中， 僅僅是1.1到1.15倍cpu的能力。 如果工作負(fù)荷是從髙速緩存中行緊致循環(huán)，那么超線程不會(huì)有好處。 如果選擇并行汁劃時(shí)，并行訃劃所作的事情是假定每個(gè)處理器在給

11、左的時(shí)間內(nèi)能夠完成 相同數(shù)捲的工作這個(gè)前提下，將要做的工作分割并指派給可用的處理器上，在使用超線程時(shí), 任何不是當(dāng)前執(zhí)行的線程將被停止，所以并行計(jì)劃在超線程下得不到性能提高。 在windows server2000上意識(shí)不到超線程，但是2003 spl可以使用，隨著處理器的更 新?lián)Q代，超線程越來越好。有機(jī)會(huì)通過超線程獲得好處，但是由于測(cè)試成本以及應(yīng)用程序的 成本，一般來說客戶會(huì)關(guān)閉超線程。 1.4.3. 64bit 64bit有一個(gè)潛在的缺點(diǎn)，就是缺少64bit驅(qū)動(dòng)程序。有些應(yīng)用還未開發(fā)岀支持64bit的應(yīng)用。 1.4.4. X64 或 IA64 X64處理器運(yùn)行速度超過3Ghz, IA64在

12、1.6Ghz左右。IA64處理器的高速緩存大于X64 處理器的髙速緩存 IA64越來越在專用機(jī)器上出現(xiàn)，包括髙性能工作站，大型高可伸縮性 系統(tǒng)。 1.4.5.高速緩存 髙速緩存產(chǎn)生的原因在于處理器速度遠(yuǎn)遠(yuǎn)髙于主存儲(chǔ)器，存儲(chǔ)器無法跟上處理器對(duì)內(nèi)存 的渴望。處理器設(shè)計(jì)者為了解決這一問題，增加了多層高速緩存。 處理器分L1,L2,L3,離處理器越遠(yuǎn)，規(guī)模越大速度更慢。L1只保存地址查找、數(shù)據(jù)和指 令，L2,L3保存內(nèi)存的任意備份。 Processor Cache被實(shí)現(xiàn)為透明的look-thru cache,芯片上有著控制功能用來管理填充高 速緩存的過程，管理髙速緩存條目。 Sql server200

13、0啟動(dòng)時(shí)占用29M大小內(nèi)存，使用sqlcmd連接到數(shù)據(jù)庫(kù)并執(zhí)行一個(gè)簡(jiǎn)單的 査詢，內(nèi)存增05m。SQLSERVER2005啟動(dòng)就有50m,同樣的命令，內(nèi)存增加3m左右。 Sql server2000 的 sqlservr.exe 大小為 89m, SQLSERVER2005 該文件大得多，為 28m,增 加的代碼為髙速緩存重用部分，包含查詢汁劃重用，增加的內(nèi)存則是查詢計(jì)劃存儲(chǔ)空間等。 15 系統(tǒng)架構(gòu) 主要是指機(jī)器架構(gòu)，如單插槽，雙插槽，四插槽，甚至對(duì)稱多處理器(SMP). 1.5.1. SMP 對(duì)稱多處理器通過一個(gè)系統(tǒng)總線連接到一個(gè)共享內(nèi)存上。SMP加劇了處理器和內(nèi)存之 間的速度問題。系統(tǒng)處理器

14、有數(shù)十個(gè)的時(shí)候，系統(tǒng)總線會(huì)成為瓶頸。 1.5.2. NUMA 非一致性內(nèi)存訪問(NUMA)是一種超越SMP的可伸縮性的架構(gòu)，NUMA系統(tǒng)中有多個(gè)系統(tǒng) 總線，每一個(gè)系統(tǒng)總線能連接的處理器最大為4,最多4個(gè)cpu共享一塊內(nèi)存。以下語句確 認(rèn)系統(tǒng)是否使用NUMA Select distinct memorynode_id from sys.dm_os_memory_clerks 返回0說明未使用NUMAo AMD處理器都作為NUMA實(shí)現(xiàn)的* 1.5.3. SOFT NUMA 這是SQL SERVER2005的新特性，允許將cpu聚合成soft-NUMA肖點(diǎn)，如果沒有硬件NUMA, 但是cpu個(gè)數(shù)很多

15、，可以提髙性能。 建議使用SOFT NUMA來對(duì)cpu進(jìn)行分組。 2系統(tǒng)監(jiān)控指標(biāo) 注意：凡是sqlserver的指標(biāo)值，都可以使用以下sql語句獲得： select cntr_value from sys dm_os_pm工f where ob j e c t_n ame =1MSSQL$ZXY:Suffer Manager 1 and counter_name=total pages * 以2.1.1第2點(diǎn)的Sql server:Buffer managertotal pages為例，其中応色字體代表實(shí)例名稱, 將紅色字體替換為Buffer manager,樁色字體替換為total page

16、s即可。需要注意的是復(fù)制時(shí) 注意空格要和原來一樣。 21內(nèi)存 2.1.1. 外部壓力 1、Process-working set顯示每一個(gè)進(jìn)程虛擬地址空間中最近引用的頁(yè)而規(guī)模。消耗內(nèi)存最 多的進(jìn)程就是消耗大量?jī)?nèi)存的應(yīng)用。指一個(gè)進(jìn)程的4GB虛擬地址空間中被映射到RAM 中的部分的大小，通常是該進(jìn)程的虛擬內(nèi)存中的活躍部分。 2、Sql server-Sql server:Buffer manager-total pages 顯示 sql server 已獲得的頁(yè)面數(shù) 3、Sql server-Sql server:buffer manager-target pages 顯示 sql server

17、的 buffer pool 必需白勺 理想頁(yè)而數(shù)。 2.1.2. 內(nèi)部?jī)?nèi)存壓力 內(nèi)部壓力通常是收縮緩沖池導(dǎo)致的。緩沖池規(guī)模變小,通常是sp_configure的max server memory的值減小導(dǎo)致。或者是其他數(shù)據(jù)庫(kù)對(duì)象消耗了內(nèi)存空間導(dǎo)致，如：作業(yè)，擴(kuò)展存儲(chǔ) 過程，com對(duì)象，sqlclr,鏈接服務(wù)器。 通過dbcc memorystatus査看buffer counts部分，確定內(nèi)存是否存在壓力。其中committed memory和target memory最重要Committed memory表示sql server已經(jīng)得到的內(nèi)存數(shù)量， target memory表示有效運(yùn)行所需

18、的內(nèi)存數(shù)雖:。當(dāng)兩個(gè)存在差別過大，說明可能存在內(nèi)存壓 力。 2.1.3. 確認(rèn)內(nèi)存瓶頸 lx SQLServer:Buffer Manager- Page Life Expectancy 顯示數(shù)據(jù)頁(yè)在緩沖池中駐留的時(shí) 間長(zhǎng)度（單位是秒）。值越大，系統(tǒng)越健康。如果存在內(nèi)存瓶頸，這個(gè)值會(huì)低于300s 或者更少。 2、SQLServer:Buffer Manager- Buffer cache hit ratio 顯示數(shù)據(jù)庫(kù)內(nèi)存命中率，所請(qǐng)求的 數(shù)據(jù)或者說頁(yè)而在緩沖池（物理內(nèi)存）被找到的次數(shù)。如果這個(gè)值很低，說明內(nèi)存不 足。這個(gè)值至少大于98%. 3、緩沖池的大小由兩個(gè)參數(shù)決泄:sp_configu

19、re的 min server memory和 max server memory.當(dāng)其他應(yīng)用使用了這些內(nèi)存后，操作系統(tǒng)就不會(huì)再將這些內(nèi)存分給sql server. 4、SQLServer:Buffer Manager- Stolen pages顯示了內(nèi)存被其他進(jìn)程挪用的頁(yè)而 X這 個(gè)指標(biāo)與目標(biāo)內(nèi)存頁(yè)而數(shù)比例較大時(shí)可能存在問題。 5、SQLServer:Memory Manager- Memory Grants Pending 顯示等待內(nèi)存授權(quán)的進(jìn)程隊(duì)列。 這個(gè)指標(biāo)值為0時(shí)理想狀態(tài)。 6、SQLServer:Buffer Manager- Checkpoint pages/sec 顯示檢查點(diǎn)操作

20、每秒寫入磁盤的 臟頁(yè)數(shù)目。如果這個(gè)值很髙，說明缺少內(nèi)存。 7、SQLServer:Buffer Manager- Lazy writes/sec顯示每秒將臟頁(yè)從小寫到磁盤的次數(shù)。 這個(gè)值應(yīng)該盡可能接近0,當(dāng)大于20或者更多，確信緩沖池不夠。 8、Memtoleave大小基于工作者線程的數(shù)目動(dòng)態(tài)變化，是一個(gè)獨(dú)立的內(nèi)存地址范圍，供 鏈接服務(wù)器，擴(kuò)展存儲(chǔ)過程，com對(duì)象或第三方ddl使用。 22 磁盤 2、PhysicalDisk-AVG.Disk Aueue Length顯示每一個(gè)磁盤的隊(duì)列長(zhǎng)度。不要選擇_TOTAL對(duì)象, 而是査看某驅(qū)動(dòng)盤符的隊(duì)列長(zhǎng)度，如果參數(shù)值持續(xù)大于2,則可能影響性能。 2、

21、PhysicalDisk-AVG.Disk sec/Read顯示每次讀取的平均磁盤時(shí)間。 3、PhysicalDisk-AVG.Disk sec/Write顯示每次寫入的平均磁盤時(shí)間 4、如果存在磁盤性能問題，控制器使用率等問題，上面兩個(gè)計(jì)數(shù)器的平均值髙于正常值。 理想情況下值小于10毫秒。 5、SQL Server-Access Methods-Full scans/sec顯示每秒請(qǐng)求完全索引掃描或者全表掃描 的數(shù)目，如果掃描頻率每秒大于1,那么說明索引缺少或者索引比較差。 6、SQL Server-Access Methods-Page Splits/sec顯示每秒頁(yè)而拆分的次數(shù)?？梢酝ㄟ^

22、適當(dāng) 的索引維護(hù)或者好的填充因子來避免發(fā)生。 2.2.1.基于配置的磁盤瓶頸 仁 數(shù)據(jù)文件和日志文件布局（將這兩類文件隔離，并且將每一類文件也隔離） 2、數(shù)據(jù)文件和日志文件自動(dòng)增長(zhǎng)管理（手動(dòng)管理） 3、Tempdb配置（指定初始化大?。?4、Tempdb內(nèi)部爭(zhēng)用 5、磁盤/控制器爭(zhēng)用（用存儲(chǔ)廠商的性能監(jiān)控工具） 2.2.2.基于模式的磁盤瓶頸 1、索引不當(dāng)或者缺少索引，使用數(shù)據(jù)庫(kù)優(yōu)化顧問（database tuning advisor）來解決?；蛘?查看 sys.dm_db_index_usage_statSQ 2、文件和文件組，將讀取數(shù)據(jù)量大且頻繁的表單獨(dú)放到文件或者組合到文件組中，可以適

23、當(dāng)降低io并便于移植以提高性能。 3、分區(qū)，降低io 4、索引/表碎片，當(dāng)索引和表的碎片較多時(shí)會(huì)產(chǎn)生頁(yè)拆分（類似于oracle的行鏈接和行遷 移）。產(chǎn)生的原意有兩種：內(nèi)部和外部。內(nèi)部碎片是指8kb數(shù)據(jù)頁(yè)包含數(shù)據(jù)很少。外部 碎片是指數(shù)據(jù)不是順序存放的。在dm_db_index_physical_stats提供當(dāng)前碎片級(jí)別的所 有信息：只有有聚簇索引的表才能重建索引：使用alter index命令重建索引。 2.2.3.指標(biāo) 2、SQL Server-General Statistics-Temp Tables Creation Rate 顯示每秒創(chuàng)建的臨時(shí)表和臨時(shí) 變疑的數(shù)目。 2、SQL Se

24、rver-General Statistics-Temp Tables For Destruction 顯示不再需要的正在等到銷 毀的臨時(shí)表的數(shù)目。 3、Sys.dm_db_task_space_usage 和 sys.dm_exec_requests 查看每一個(gè)服務(wù)器進(jìn)程使用的 tempdb空間，具體見515。 2.2.4. 存儲(chǔ)性能壓力測(cè)試指標(biāo) 1. IOPS是每秒能夠?yàn)檩斎胼敵稣?qǐng)求提供服務(wù)的度量。得到的子系統(tǒng)實(shí)際吞葉量和io請(qǐng)求 的大小的函數(shù)。 2. Block size提供sql執(zhí)行的10的大小的指示 3. MB/sec每秒有多少M(fèi)B的數(shù)據(jù)進(jìn)入或者離開子系統(tǒng)。 4. Latency為每

25、一個(gè)io完成所必須等待的時(shí)間長(zhǎng)度。 5. DISK Queue length磁盤隊(duì)列的長(zhǎng)度，說明當(dāng)前io請(qǐng)求時(shí)排隊(duì)等候的io請(qǐng)求的數(shù)目。 2.2.5. 度量IO性能 %DISK Read Time,%Disk Timez%Disk Write Time %Disk Idle Time 說明采用周期內(nèi)磁盤讀取，踩 入，使用和空閑各占的百分比。 Avg.Disk Bytes/sec/Read, Avg.Disk Bytes/sec/Transfer, Avg.Disk Bytes/sec/Write 說明每次讀取、 寫入或者傳送的平均字節(jié)數(shù)。 Avg.Disk Queue length, Avg.

26、Disk Read Queue length, Avg.Disk Write Queue length 說明了平均 磁盤隊(duì)列長(zhǎng)度，磁盤隊(duì)列長(zhǎng)度是等待被送到磁盤的請(qǐng)求的數(shù)目。 Avg.Disk sec/Read, Avg.Disk sec/Transfer, Avg.Disk sec/Write 說明 io 的延遲，每次讀取或者寫入 所占用時(shí)間的長(zhǎng)短。 Disk Bytes/sec, Disk Read Bytes/sec, Disk Write Bytes/sec 說明以 Bytes/sec 為單位顯示吞吐雖 Disk Read/sec, Disk Transfer/sec, Disk Wri

27、te/sec 顯示得到的 IOPS 的數(shù)目。 lx System-Processor Queue Length顯示系統(tǒng)隊(duì)列長(zhǎng)度,如果T均值大于3,那么說明cpu 存在瓶頸。 2、Processor%Privilege Time顯示操作系統(tǒng)內(nèi)部操作所花費(fèi)的時(shí)間。 3、Processor-%User Time顯示用戶模式操作花費(fèi)的時(shí)間 4、Processor-%User Time-sqlservr顯示sql server進(jìn)程消耗的cpu時(shí)間的確切數(shù)量。這個(gè) 最好從性能監(jiān)視器來看。 5、SQL Server-SQL StatisticsSQL Compilations/sec 顯示查詢計(jì)劃的編譯次數(shù)

28、。 6、SQL Server-SQL Statistics-SQL Re-Compilations/sec 顯示查詢汁劃的重編譯次數(shù)。 7、當(dāng)SQL Compilations/sec和SQL Re-Compilations/sec次數(shù)過髙的話，說明可能未使用綁定 變量導(dǎo)致計(jì)劃重新編譯，或者說重新編譯次數(shù)比編譯次數(shù)過髙的話，那么說明存在應(yīng)用 上的瓶頸. 2.3.1.基于配f 的cpu瓶頸 很難有建議或者計(jì)數(shù)器來協(xié)助spconfigure的cpu相關(guān)配置(Affinity Mask,Hyper Threading,Priority Boost,Maximum Degree of Paralleli

29、sm,Max Worker Threads),故不加以描述 2.3.2.基于模式的cpu瓶頸 當(dāng)SQL Compilations/sec和SQL Re-Compilations/sec次數(shù)過髙的話，說明可能未使用綁定變量 導(dǎo)致計(jì)劃重新編譯，或者說新編濟(jì)比編譯次數(shù)過高的話，那么說明存在應(yīng)用上的瓶j 24數(shù)據(jù)庫(kù)鏡像性能 目前不關(guān)心，以后補(bǔ)充。 3. 服務(wù)器設(shè)置 3.1. Cpu 建議不使用超線程 如果系統(tǒng)有8顆以上cpu并且沒有硬件NUMA,那么建議配置soft-NUMA. 系統(tǒng)是否NUMA判斷如下： Select Case count(distinct parent_nodewhen 1 the

30、n ZNUMA disabled else NUMA enabled end as a From sys.dm_os_schedulers where parent_nodejd32 4. 數(shù)據(jù)庫(kù)等待事件 SQL Server-Wait StatisticsLock Waits 顯示進(jìn)程等待獲得鎖的時(shí)間。 SQL Server-Wait Statistics-Memory Grant Queue Waits 顯示進(jìn)程在隊(duì)列中等待內(nèi)存鎖花費(fèi) 的時(shí)間。 SQL Server-Wait StatisticsPage I/O Latches 41 常見等待類型 WAITFOR:是人工挑起的等待，不用關(guān)

31、注。 LAZYWRITER_SLEEP:表示lazywiter進(jìn)程處于睡眠狀態(tài)并等待運(yùn)行的時(shí)間，不用關(guān)注。 SQLTRACE_BUFFER_FLUSH：當(dāng)系統(tǒng)等待sql trace緩沖區(qū)被寫到磁盤時(shí)出現(xiàn)的等待。一般可以 看做永遠(yuǎn)存在的特性。 SLEEP_BPOOL_FLUSH:檢査點(diǎn)等待時(shí)，會(huì)計(jì)數(shù)。 RESOURCE_SEMAPHORE:所有hash, sort,批量復(fù)制以及索引創(chuàng)建等操作均要使用工作區(qū)。 一般來說工作區(qū)大小為25%到70%之間。說明了內(nèi)存壓力。 CXPACKET：顯示并行正在發(fā)生，在oltp系統(tǒng)中，他不應(yīng)該是主要等待事件，但是代表了主 動(dòng)生成cpu時(shí)間的線程，如果是主要等待事

32、件，說明存在cpu瓶頸。 SOS_SCHEDULER_YIELD:當(dāng)任務(wù)自動(dòng)產(chǎn)生處理器時(shí)間并等待再次被調(diào)度時(shí)發(fā)生，如果這個(gè)等待 值比較髙，說明存在cpu等待。 RESOURCE_SEMAPHORE_QUERY_COMPILE顯示系統(tǒng)中并發(fā)編譯的次數(shù)。這個(gè)次數(shù)過多說明需 要査詢參數(shù)化。 l/O_COMPLETION,ASYNC/O_COMPLETION:顯示等待非數(shù)據(jù)頁(yè)而的I/O,或者長(zhǎng)期運(yùn)行的I/O綁 定操作，說明了磁盤瓶頸。 PAGEIOLATCH_*顯示將數(shù)據(jù)頁(yè)從磁盤檢索到內(nèi)存的時(shí)間，說明了 10壓力 LCKJVI等待獲得某個(gè)資源的鎖。 LOGBUFFER：說明正在等待日志緩存寫入事務(wù)日志

33、文件，大的值說明日志文件磁盤io瓶頸。 LOGMGR_RESERVE_APPEND：日志文件太小，添加日志文件。 PAGELATCH_*說明在任務(wù)等待不處于I/O請(qǐng)求中的緩沖區(qū)閂鎖時(shí)發(fā)生，說明有連接同時(shí)想訪 問緩存中同一個(gè)數(shù)據(jù)頁(yè)。 LATCH_* 5. 數(shù)據(jù)庫(kù)指標(biāo) 51指標(biāo)釆集和排列 5.1.1.操作系統(tǒng)資源 .采集方式 下而的語句獲得SQL Server可用資源及其已占用資源的有用雜項(xiàng)信息的指標(biāo)，可以每 15分鐘采集一回。 select cpu_count as系統(tǒng)的邏輯CPU數(shù),hyperthread_ratio as邏輯內(nèi)核數(shù)與物理內(nèi) 核數(shù)的比，round (physica

34、l_memory_in_bytes 1024/1024,0) as 物理內(nèi) ,round(virtual_memory_in_bytes 1024 1024,0) as 虛扌以內(nèi) , bpool_committed as緩存池已扌浪交內(nèi)存頁(yè)數(shù)，匕t as緩存也目 標(biāo)內(nèi)存貞數(shù)，bpool_visible as進(jìn)程熄擬地址空間可用內(nèi)存貝數(shù)，max_workers_count as最大工作線程數(shù)-，proo3ss_k3rn31_time_ms as內(nèi)核態(tài)奄秒 process_user_time_ms as 用戶態(tài)毫秒數(shù)，sqlserver_starime as 啟動(dòng)時(shí)囘 from sys dm_os

35、_sys_2nfo .指標(biāo)說明 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 系統(tǒng)的邏輯CPU數(shù) int 我感覺是是否使用超線程的標(biāo)志 邏輯內(nèi)核數(shù)與物理內(nèi)核數(shù)的比 int 物理內(nèi)存 Int 可用物理內(nèi)存的數(shù)量 虛擬內(nèi)存 int 對(duì)用戶模式進(jìn)程可用的虛擬內(nèi)存的數(shù)量，可以 用來判斷是否使用/3GB開關(guān)。 緩存池已提交內(nèi)存貞數(shù) int 此數(shù)量表示緩沖池中的已提交物理內(nèi)存頁(yè) 數(shù)，單位為8KB。不包括緩沖池中的保留內(nèi)存 緩存池目標(biāo)內(nèi)存貞數(shù) int 目標(biāo)數(shù)量是使用諸如系統(tǒng)當(dāng)前狀態(tài)(包括它 的負(fù)載、當(dāng)前進(jìn)程所請(qǐng)求的內(nèi)存、計(jì)算機(jī)上 安裝的內(nèi)存量以及配置參數(shù))等多種輸入汁 算得到的。如果bpool_commit_

36、target大于 bpool_committed值，則緩沖池將嘗試獲得額 外內(nèi)存。如果 bpool_commit_target小于 bpool committed值，則緩沖池將收縮。 進(jìn)程虛擬地址空間可用內(nèi)存頁(yè) 數(shù) int 不使用地址窗口化擴(kuò)展插件(AWE)時(shí)，如果 緩沖池已獲得它的內(nèi)存目標(biāo) (bpool committed = bpool commit target),貝9 bpool_visible 的值等于 bpool_committed 的 值。 在32位版本的SQL Server上使用AWE 時(shí)，bpool_visible表示在訪問緩沖池所分配的 物理內(nèi)存時(shí)使用的AWE映射窗口的大小

37、。此 映射窗口的大小由進(jìn)程地址空間綁定，因此， 可見數(shù)量將小于提交數(shù)量，并且通過為數(shù)拯 庫(kù)頁(yè)之外的其他用途而消耗內(nèi)存的內(nèi)部組件 會(huì)進(jìn)一步減少可見數(shù)量。如果bpool_visible 的值太低，則可能收到內(nèi)存不足錯(cuò)誤。 最大工作線程數(shù) int 5.1.2.重要統(tǒng)計(jì)信息 .釆集方式 下面語句獲得高速緩沖區(qū)命中率的值，采集可以每30s采集一次 select round(cas*(sum(case when counter_name=Buffer cache hit ratio then cntrvalue else 0 end) as decimal(38,16) )/sum(case

38、 when counter_name= * Buffer cache hit raio base * then cntr_value else 0 end )*100 ,2)as b from sys dm_os_pE2rf ormancOCountozrs where counte rename like 1 Buffer cache hit rat;io% * 下面的語句返回計(jì)劃緩沖區(qū)不同池的命中率，可以每30s采集一次： select instance_namez round (case : sum (case v;hen counter_name= Cache Hit Ratio th

39、en cntrvalue else 0 end) as decimal(38,16)/sum(case when counter_name= Cache Hit Ratio Base then cntr_value else 0 end )*100 ,2)as b from sys dm_os_performance_counters where ob j ec*t_name= MSSQL$ZXY : Plan Cache * group by instancename having sum(case when counter_name=1 Cache Hit Racio Base1 then

40、 cntr_value else 0 end )0 下面語句獲得每秒索引掃描和全部掃描次數(shù)，可以每30s采集一次： select sum(case when counter_name=1 Full Scans/sec 1 then cntr_value else 0 end) as full_table_scans, sum (case vzhen counter_name= Range Scans/sec then cntr_value else 0 end ) as rangescans from sys dmOsjmrf ormanc：E_c:ount;e：rs whmrm counte

41、 rename in ( 1 Full Scans/sec1,Range Scans/sec1) .指標(biāo)說明 5.1.3.等待事件 按照平均等待事件來說明數(shù)搦庫(kù)等待事件 .采集方式 select wait_type waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms from sys.dm_os_wait_stats where waitingtaskSCOuntrl order by wait_time_ms/waiting_tasks_count desc .指

42、標(biāo)說明 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 wait_type nvarchar(60) 等待事件名稱 waiting tasks cou nt bigint 等待次數(shù) wait_time_ms bigint 該等待類型的總等待時(shí)間（亳 秒）。該時(shí)間包括 sign al wait time msa max_wait_time_ms bigint 最長(zhǎng)等待時(shí)間（亳秒） signal_wait_time_ms bigint 該等待事件從收到信號(hào)到運(yùn) 行等待時(shí)間（亳秒） .等待事件說明 參見第四章。 5.1.4.數(shù)據(jù)文件和日志文件 .采集方式 下而分別說明數(shù)據(jù)庫(kù)空間，數(shù)據(jù)庫(kù)空間

43、使用等信息，采集周期可以是每天一次，或者每周一 次。 數(shù)據(jù)庫(kù)空間： select name as數(shù)據(jù)庫(kù)空間,data_space_id as空間編號(hào)，type as空間類 型,is_default as 默認(rèn)數(shù)據(jù)空間標(biāo)志 from sys . data_spaces 數(shù)據(jù)庫(kù)空間使用信息： sp_helpdb 返回信息如下： 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 n ame sys name 數(shù)據(jù)庫(kù)名稱。 db size n varchar(13) 數(shù)據(jù)庫(kù)總計(jì)大小。 dbid smallint 數(shù)據(jù)庫(kù)IDo status n varchar(600) 指定數(shù)據(jù)庫(kù)詳細(xì)空間使用信息： sp_helpdb數(shù)據(jù)

44、庫(kù)名稱 取返回的第二個(gè)結(jié)果集，具體信息如下: 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 n ame n char(128) 邏輯文件名。 fileid smallint 文件IDo filename n char(260) 操作系統(tǒng)文件名（物理文件名 稱）。 filegroup n varchar(128) 文件所屬的文件組。 NULL=文件為日志文件。它 決不是文件組的一部分。 size n varchar(18) 文件大小（MB）。 maxsize n varchar(18) 文件大小可達(dá)到的最大值。此 字段中的UNLIMITED值表 示文件可以一直增長(zhǎng)到磁盤 己滿。 growth n varchar

45、(18) 文件的增量。表示每次需要新 的空間時(shí)給文件增加的空間 大小。 usage varchar(9) 文件用法。對(duì)于數(shù)據(jù)文件，該 值為data only1,對(duì)丁日志 文件，該值為log onlyo 所有數(shù)據(jù)庫(kù)對(duì)應(yīng)的數(shù)據(jù)文件和日志文件io信息： select dbid,eqp fNumbm工Rmads, BytmsREad /1024/1024 as bytesread,IoStallReadMS, Numberwrites,Byteswritten /1024/1024 as bytewitten,IoStallwriteMS,IoStallMS from (select * from s

46、ysfiles) aa cross apply fn_virtualfilestats (null,aa fileid) eqp 所有數(shù)據(jù)庫(kù)臨時(shí)表空間文件空間分配信息： select b . name as數(shù)據(jù)庫(kù)z c . name as文件的邏輯名稱 c . filename as文件物理名稱 version_store_reserved_page_count as 統(tǒng) 區(qū)，總、頁(yè) 數(shù)，unallocated_e:-:tent_page_count 未分丙C取總、頁(yè)數(shù)， userobj ect_reserved_page_count as統(tǒng)區(qū)用戶對(duì)象用戶對(duì)象總頂 數(shù)，internal_obj

47、 ect_reserved_page_count as統(tǒng)區(qū)文件內(nèi)部用戶對(duì)象總蟲數(shù)，mixed_extent_page_count as混合區(qū)總貝數(shù) from sys dm_db_fa inner j oin sysdatabases b on adatabase_id=b dbid inner join sysfiles c on a file_id=c .filmid .指標(biāo)說明 數(shù)據(jù)庫(kù)空間: 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 數(shù)據(jù)庫(kù)空間名稱 nvarchar 空間編號(hào) int 空間類型 char(2) 數(shù)據(jù)空間類型： FG=文件組 PS=分區(qū)方案 默認(rèn)數(shù)據(jù)空間標(biāo)志 bit 1=這

48、是默認(rèn)數(shù)據(jù)空間。當(dāng)未在CREATE TABLE 或CREATE INDEX語句中指立文件組或分區(qū) 方案時(shí)，將使用此數(shù)據(jù)空間。 0=這不是默認(rèn)數(shù)據(jù)空間。 所有數(shù)據(jù)庫(kù)對(duì)應(yīng)的數(shù)據(jù)文件和日志文件io信息: 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 dbid smalli nt 數(shù)據(jù)庫(kù)編號(hào) Fileid smalli nt 文件編號(hào) NumberReads bigint 文件讀次數(shù) BytesRead bigint 文件讀字節(jié)數(shù) IoStallReadMS bigint 用戶等待文件的讀取I/O完成所費(fèi)的總時(shí)間 （以亳秒為單位）。 Numberwrites bigint 文件寫次數(shù) Byteswritten big

49、int 文件寫字節(jié)數(shù) IoStallwriteMS bigint 用戶等待文件的寫I/O完成所費(fèi)的總時(shí)間 （以亳秒為單位）。 IoStallMS bigint 等待文件讀寫完成總時(shí)間（以亳秒為單位） 所有數(shù)據(jù)庫(kù)臨時(shí)表空間文件空間分配信息: 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 數(shù)據(jù)庫(kù)名稱 Varchar(20) 文件的邏借名稱 Varchar(20) 文件物理名稱 Varchar(260) 未分配區(qū)總頁(yè)數(shù) bigint 文件的未分配區(qū)中的總頁(yè)數(shù)，不包含已分配區(qū) 的未使用頁(yè) 統(tǒng)一區(qū)總頁(yè)數(shù) bigint 統(tǒng)一區(qū)總頁(yè)數(shù) 統(tǒng)一區(qū)用戶對(duì)象用戶對(duì)象總 頁(yè)數(shù) bigint 統(tǒng)一區(qū)用戶對(duì)象總頁(yè)數(shù)包含已分配未使用 頁(yè)。

50、 統(tǒng)一區(qū)文件內(nèi)部用戶對(duì)象總 頁(yè)數(shù) bigint 統(tǒng)一區(qū)文件中內(nèi)部對(duì)象總頁(yè)數(shù)，包含已分配 未使用頁(yè)。 混合區(qū)總頁(yè)數(shù) bigint 混合區(qū)已分配和未分配總頁(yè)數(shù)之和。 5.1.5. TEMPDB 使用 .釆集方式 select obj.objectid/sum(tmp.user_objects_alloc_page_count) as user_objects_alloc_page_count/ sum(tmp.user_objects_dealloc_page_count) as user_objects_dealloc_page_countz sum(ernal_ob

51、jects_alloc_page_count) as internal_objects_alloc_page_count, sum(ernal_objects_dealloc_page_count) as internal_objects_dealloc_page_count from sys.dm_db_task_space_usage tmp left outer join sys.dm_exec_requests req on tmp sessiond=req.sessiond outer apply sys.dm_exec_sql_text(req.sql_handle)

52、 as obj where obj.objectid is not null group by obj.objectid .指標(biāo)說明 指標(biāo)名稱 數(shù)拯類型 指標(biāo)說明 objectid Varchar(260) 對(duì)象編號(hào) user objects alloc page count bigint 為用戶對(duì)象保留或分配的頁(yè)數(shù) user objects dealloc page cou nt bigint 為用戶對(duì)象釋放并不再保留的頁(yè)數(shù) in ter nal objects alloc page cou nt bigint 為內(nèi)部對(duì)象保留或分配的頁(yè)數(shù) in ternal objects d

53、ealloc page co unt bigint 為內(nèi)部對(duì)象釋放并不再保留的頁(yè)數(shù) 5.1.6.系統(tǒng)當(dāng)前資源高消耗sql .釆集方式 返回系統(tǒng)當(dāng)前正運(yùn)行的資源髙消耗信息的sql: select ob j ectid , total_cpime, total_exec_count, num_ofstatment z total_logical_reads total_exec_count as avg_logical_reads z total_logical_writes total_e:ec_count as avg_logical_writes, total_physical

54、_reads total_exec_count as avg_physical_reads,text from ( select qs sql_handlez sum (qs total_worker_time) as total_cpu_time z cast; (sum (qs executioncount) as decimal (38,16) as total_exec_count, count; (*) as numofstatment, sum (total_logical_reads) as total_logical_reads z sum (totallogicalwrite

55、s) as total_logical_writes, sum(total_physical_reads) as total_physical_reads from sys dmEXEOqumryStats qs group by qssql_handle)dt cross apply sys . dm_exec_sql_,text (dt sqlhandlE) eqp where objectid is noc null .指標(biāo)說明 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 objectid Int 數(shù)據(jù)庫(kù)對(duì)象 Total cpu time bigint Cpu自編譯以來使用時(shí)間總毫秒數(shù)

56、total exec count decimal(38, 16) 上次編譯后執(zhí)行次數(shù) n u m of statme nt Int 語句個(gè)數(shù) avg logical reads decimal(38, 16) 平均邏輯讀次數(shù) avg logical writes decimal(38, 16) 平均邏輯寫次數(shù) avg physical reads decimal(38, 16) 平均物理讀次數(shù) Text Nvarchar(4000) Sql腳本 5.1.7.當(dāng)前阻塞和物理io多的會(huì)話請(qǐng)求信息 . 采集方式 以下語句返回系統(tǒng)中有阻塞和物理io多的會(huì)話，可以每15s采集一次 sele

57、ct sessionid,request_idz start_timez status,command,sql_handlez planhandl e, database_idz user_idz connection_idz blocking_session_id,wait_typez wait_ time,wait_resource, open_transaction_countz open_resultset_count,transactionid,cpu_timez t otal_elapsed_time,reads,writes,logicalreads zlock_timeout,

58、granted_que ry_memory from sys dm_e:ec_re quests where( logical_reads0 and reads logical_reads=02)or blockingsession_id 0 .指標(biāo)說明 指標(biāo)名稱 數(shù)據(jù)類型 指標(biāo)說明 Sessiond smalli nt 會(huì)話編號(hào) Requestjd Int 請(qǐng)求號(hào) Start_time datetime 請(qǐng)求達(dá)到的時(shí)間 Status nvarchar(30) 請(qǐng)求狀態(tài)： 、后臺(tái) 2、正在運(yùn)行 3、可運(yùn)行 4、睡眠 5、已掛起 Comma nd nvarchar(16) 正在處理

59、的命令的當(dāng)前類型。常用命令 類型包括： lx SELECT 2、INSERT 3、UPDATE 4、DELETE 5、BACKUP LOG 6、BACKUP DB 7、DBCC 8x WAITFOR Sql handle var bin ary(64) 請(qǐng)求的SQL文本的哈希映射 Plan handle varbinary(64) 用于執(zhí)行SQL的計(jì)劃的哈希映射 Databasejd smalli nt 對(duì)其執(zhí)行請(qǐng)求的數(shù)據(jù)庫(kù)的ID Userjd Int 提交請(qǐng)求的用戶的ID Con nectionjd int 請(qǐng)求到達(dá)時(shí)所采用的連接的ID Blockingsessi ond int 正在阻塞請(qǐng)

60、求的會(huì)話的ID。如果此列 為NULL,則表示請(qǐng)求未被阻塞，或鎖 定會(huì)話的會(huì)話信息不可用（或無法進(jìn)行 標(biāo)識(shí)）O -2 =阻塞資源由孤立的分布式事務(wù)擁 有。 -3=阻塞資源由延遲的恢復(fù)事務(wù)擁有。 -4 =由于內(nèi)部閂鎖狀態(tài)轉(zhuǎn)換而導(dǎo)致此 時(shí)無法確泄阻塞閂鎖所有者的會(huì)話ID Wait_type n varchar(60) 如果請(qǐng)求當(dāng)前被阻塞，則此列返回等待 類型 Wait_time Int 如果請(qǐng)求當(dāng)前被阻塞，則此列返回當(dāng)前 等待的持續(xù)時(shí)間（以亳秒為單位）。 Wait_resource nvarchar(60) 如果請(qǐng)求當(dāng)前被阻塞，則此列返回請(qǐng)求 當(dāng)前等待的資源。 Open_transactio n_