常規(guī)計(jì)數(shù)器收集操作系統(tǒng)服務(wù)器的服務(wù)器性能信息，包括Processor、磁盤(pán)、網(wǎng)絡(luò)、內(nèi)存

Processor 處理器

1.1 % Processor Time指處理器用來(lái)執(zhí)行非閑置線(xiàn)程時(shí)間的百分比。通過(guò)此計(jì)數(shù)器可以確定服務(wù)器當(dāng)前CPU使用百分比。

1.2 % Privileged Time是在特權(quán)模式下進(jìn)程線(xiàn)程執(zhí)行代碼所花時(shí)間的百分比。當(dāng)調(diào)用 Windows 系統(tǒng)服務(wù)時(shí)，此服務(wù)經(jīng)常在特權(quán)模式運(yùn)行，以便獲取對(duì)系統(tǒng)專(zhuān)有數(shù)據(jù)的訪(fǎng)問(wèn)。

PhysicalDisk物理磁盤(pán)（某個(gè)RAID）

2.1 Avg. Disk Queue Length等待請(qǐng)求的隊(duì)列長(zhǎng)度，若此值過(guò)大，說(shuō)明磁盤(pán)存在瓶頸，自然磁盤(pán)延遲的值也會(huì)過(guò)大。

2.2 Avg. Disk sec/Read；Avg. Disk sec/Transfer；Avg. Disk sec/Write這三個(gè)計(jì)數(shù)器表示磁盤(pán)每次讀寫(xiě)需要多少時(shí)間，表示磁盤(pán)的延遲情況，以秒為單位，若延遲超過(guò)25mm表示磁盤(pán)性能較差。

LogicalDisk邏輯磁盤(pán)（某個(gè)卷）

3.1 % Free Space某個(gè)磁盤(pán)的空閑百分比；Free Megabytes 磁盤(pán)剩余大小MB

3.2 Disk Read Bytes/sec；Disk Transfers/sec；Disk Write Bytes/sec這三個(gè)計(jì)數(shù)器表示每秒磁盤(pán)的讀寫(xiě)情況，表示磁盤(pán)的IO性能（iops）

Memory 內(nèi)存

4.1 Available Mbytes可以?xún)?nèi)存大小MB，此內(nèi)存能立刻分配給一個(gè)進(jìn)程或系統(tǒng)使用。它等于分配給待機(jī)(緩存的)、空閑和零分頁(yè)列表內(nèi)存的總和。

4.2 Cache Bytes 系統(tǒng)使用的內(nèi)存

4.3 Commit Limit可以申請(qǐng)的內(nèi)存總大小（虛擬內(nèi)存和物理內(nèi)存）；Committed Bytes已經(jīng)使用的內(nèi)存大小（虛擬內(nèi)存和物理內(nèi)存）；均是以Byte（字節(jié)）為單位。

4.4 Free System Page Table Entries指系統(tǒng)當(dāng)前未使用的頁(yè)表項(xiàng)數(shù)目。表示內(nèi)存是否空閑。

4.5 Page Faults/sec 每秒出錯(cuò)頁(yè)面的數(shù)量，也就是未在內(nèi)存中命中的頁(yè)面數(shù)。因此伴隨著Pages/sec計(jì)數(shù)器是由于出錯(cuò)頁(yè)面從磁盤(pán)讀取數(shù)據(jù)頁(yè)的速度。此值越大說(shuō)明內(nèi)存可能存在一定瓶頸，或者存在大量數(shù)據(jù)存取。

Network Interface 網(wǎng)絡(luò)接口

5.1 Current Bandwidth指以位/每秒估計(jì)的網(wǎng)絡(luò)接口的當(dāng)前帶寬。對(duì)于那些帶寬不變的或無(wú)法估計(jì)出準(zhǔn)確數(shù)據(jù)的界面，這個(gè)值為名義帶寬。

5.2 Bytes Received/sec；Bytes Sent/sec；Bytes Total/sec每秒種發(fā)送和接收的字節(jié)數(shù)，表示當(dāng)前網(wǎng)絡(luò)的使用情況。

5.3 Output Queue Length輸出數(shù)據(jù)包隊(duì)列，此值大于2說(shuō)明傳輸存在延遲。

5.4 Packets Outbound Discarded 被選為丟棄的數(shù)據(jù)包數(shù)目，丟棄的原因可能是釋放緩沖空間。Packets Outbound Errors 由于錯(cuò)誤不能傳輸?shù)臄?shù)據(jù)包數(shù)目

5.5 Packets Received Discarded 被選為丟棄的數(shù)據(jù)包數(shù)目，丟棄的原因可能是釋放緩沖空間。Packets Received Errors 由于錯(cuò)誤不能傳輸?shù)臄?shù)據(jù)包數(shù)目

SQL Server計(jì)數(shù)器

SQL Server中的Access Methods對(duì)象提供監(jiān)視訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)中的邏輯數(shù)據(jù)的計(jì)數(shù)器；Buffer Manager計(jì)數(shù)器監(jiān)視對(duì)磁盤(pán)上的數(shù)據(jù)庫(kù)也的物理訪(fǎng)問(wèn)。

Access Methods 存取方式

Access Methods監(jiān)視用于訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)的方法有助于確定是否可以通過(guò)添加或修改索引、添加或移動(dòng)分區(qū)、添加文件或文件組、對(duì)索引進(jìn)行碎片整理或者重寫(xiě)查詢(xún)來(lái)提高查詢(xún)性能。 Access Methods 計(jì)數(shù)器也可用于監(jiān)視數(shù)據(jù)庫(kù)中數(shù)據(jù)、索引和可用空間的數(shù)量，從而指示每個(gè)服務(wù)器實(shí)例的數(shù)據(jù)卷和碎片。索引的碎片過(guò)多會(huì)降低性能。

1.1 Page Splits/sec每秒由于索引頁(yè)溢出而發(fā)生的頁(yè)拆分?jǐn)?shù)。此數(shù)據(jù)越大表示某表的修改頻率較高，且要確認(rèn)該索引是否適合建立該字段上。

1.2 Workfiles Created/sec每秒創(chuàng)建的工作文件數(shù)

1.3 Worktables Created/sec每秒創(chuàng)建的工作表數(shù)

Buffer Manager 緩存管理

監(jiān)視用于存儲(chǔ)數(shù)據(jù)頁(yè)的內(nèi)存；讀取和寫(xiě)入數(shù)據(jù)庫(kù)頁(yè)時(shí)的物理io等，通過(guò)此對(duì)象可以判斷是否存在物理內(nèi)存瓶頸可以了解服務(wù)器的查詢(xún)性能等等。

2.1 Buffer cache hit ratio命中高速緩沖區(qū)頁(yè)的百分比。若此值過(guò)低說(shuō)明可能內(nèi)存存在一定瓶頸

2.2 Database pages緩沖池中數(shù)據(jù)庫(kù)內(nèi)容的頁(yè)數(shù)

2.3 Page life expectancy頁(yè)指示頁(yè)面在沒(méi)有引用的情況下，在此節(jié)點(diǎn)的緩沖池中停留的時(shí)間（以秒計(jì)）。此值若降低說(shuō)明，服務(wù)器頻繁查詢(xún)數(shù)據(jù)庫(kù)中數(shù)據(jù)。

2.4 Checkpoint pages/sec Checkpoint的執(zhí)行頻率，若執(zhí)行的頻率過(guò)高。則說(shuō)明服務(wù)器過(guò)于頻繁的修改數(shù)據(jù)。

2.5 Lazy writes/sec 若懶寫(xiě)入過(guò)于頻繁，說(shuō)明沒(méi)有空閑的緩沖區(qū)供使用，內(nèi)存可能存在一定瓶頸。“惰性編寫(xiě)器”是一個(gè)系統(tǒng)進(jìn)程，用于成批刷新臟的過(guò)期的緩沖區(qū)（包含更改的緩沖 區(qū)，必須將這些更改寫(xiě)回磁盤(pán)，才能將緩沖區(qū)重用于其他頁(yè)），并使它們可用于用戶(hù)進(jìn)程。

Memory Manager 內(nèi)存管理

監(jiān)視總體的服務(wù)器內(nèi)存使用情況，以估計(jì)用戶(hù)活動(dòng)和資源使用，有助于查明性能瓶頸。

3.1 Memory Grants Pending指定等待工作空間內(nèi)存授權(quán)的進(jìn)程總數(shù)。指等待分配內(nèi)存的進(jìn)程數(shù)量，若此值過(guò)高或內(nèi)存或磁盤(pán)存在一定瓶頸。

3.2 Target Server Memory (KB)；Total Server Memory (KB)理想上能使用內(nèi)存量與內(nèi)存管理器提交的內(nèi)存量。若此兩值存在差異則說(shuō)明內(nèi)存異常。

General Statistics 常規(guī)統(tǒng)計(jì)

4.1 User Connections；Logins/sec；Logouts/sec 用戶(hù)并發(fā)情況

4.2 Processes blocked當(dāng)前阻塞數(shù)量

4.3 Temp Tables For Destruction等待被清除系統(tǒng)線(xiàn)程破壞的臨時(shí)表/表變量數(shù)。無(wú)用的臨時(shí)表和表變量。

SQL Statistics; Wait Statistics請(qǐng)求與等待統(tǒng)計(jì)

5.1 Batch Requests/sec；SQL Compilations/sec；SQL Re-Compilations/sec每秒的請(qǐng)求、編譯和重編譯的次數(shù)，這些對(duì)于優(yōu)化供參考。

5.2 Lock waits等待鎖的進(jìn)程數(shù)。

5.3 Log write waits等待寫(xiě)入日志緩存的進(jìn)程數(shù)

5.4 Page IO latch waits與頁(yè) I/O 閂鎖相關(guān)的統(tǒng)計(jì)信息。用于判斷內(nèi)存或磁盤(pán)的瓶頸情況

5.5 Page latch waits與頁(yè)閂鎖（不包括 I/O 閂鎖）相關(guān)的統(tǒng)計(jì)信息。用于判斷內(nèi)存或磁盤(pán)的瓶頸情況

Databases針對(duì)數(shù)據(jù)庫(kù)級(jí)別

6.1 Log Flush Wait Time；Log Flushes/sec日志刷新等待和頻率，若數(shù)據(jù)異常則可能存在磁盤(pán)瓶頸

6.2 Transactions/sec 事務(wù)數(shù)

Locks鎖

7.1 Lock Requests/sec；Lock Timeouts/sec鎖請(qǐng)求頻率；鎖超時(shí)頻率

7.2 Number of Deadlocks/sec 死鎖情況。

------------------------------------------------------------------------------------

磁盤(pán)相關(guān)

?????????? ins_dir = MSSQL_DIR + "/ms" + str(port)data_dir = ins_dir + '/data'log_dir = ins_dir + '/log' backup_dir = ins_dir + '/backup' ins_size = func_os.get_dir_size( ins_dir)??????????data_size = func_os.get_dir_size(data_dir)log_size = func_os.get_dir_size(log_dir) backup_size = func_os.get_dir_size( backup_dir)real_ins_size = ins_size - backup_size??????????other_size = real_ins_size - data_size - log_size
總空間：real_ins_size = 實(shí)例目錄空間 - 備份空間（落地備）數(shù)據(jù)空間：data_size其他文件空間：other_size = 總空間 - 數(shù)據(jù)空間 - 日志空間 = tepmdb空間大小（日志+數(shù)據(jù)）+ 其它（errorlog+agentlog+fulltextlog+系統(tǒng)庫(kù)+JOBS）BinLog空間：log_size

SQL Server性能相關(guān)

動(dòng)態(tài)管理視圖和函數(shù)

?? 與session執(zhí)行有關(guān)sys.dm_exec_connectionssys.dm_exec_sessions與系統(tǒng)有關(guān)sys.dm_os_performance_counters與IO相關(guān)sys.dm_io_virtual_file_stats

目錄視圖

?? 數(shù)據(jù)庫(kù)和文件目錄視圖sys.master_filessys.databases

sessions

?? 命令 select count(*) from sys.dm_exec_sessions where login_name not in ( 'root', 'aurora', 'sa', 'eagleye') and login_name not like 's\_%' escape '\' and login_name not like %mssqld' and login_name not like '%Administrator' and status not in ( 'Preconnect', 'Dormant‘）解釋sys.dm_exec_sessions：是服務(wù)器范圍的視圖，顯 示了有關(guān)所有活動(dòng)用戶(hù)連接和內(nèi)部任務(wù)的信息。login_name 當(dāng)前執(zhí)行的會(huì)話(huà)所使用的 SQL Server 登錄名。status????會(huì)話(huà)的狀態(tài)。 可能的值：Running - 當(dāng)前正在運(yùn)行一個(gè)或多個(gè)請(qǐng)求Sleeping - 當(dāng)前沒(méi)有運(yùn)行任何請(qǐng)求Dormant – 會(huì)話(huà)因連接池而被重置，并且現(xiàn)在處于登錄前狀態(tài)。Preconnect - 會(huì)話(huà)在資源調(diào)控器分類(lèi)器中。分類(lèi)資源調(diào)控器支持對(duì)傳入會(huì)話(huà)的分類(lèi)。分類(lèi)基于函數(shù)中包含的一組用戶(hù)編寫(xiě)的條件。函數(shù)邏輯的結(jié)果使資源調(diào)控器可以將會(huì)話(huà)歸入現(xiàn) 有工作負(fù)荷組類(lèi)。會(huì)話(huà)傳入鏈接——分類(lèi)（分類(lèi)器函數(shù)）——路由到工作負(fù)荷組——工作負(fù)荷組使用相關(guān)聯(lián)的資源池——資源池提供同時(shí)限制應(yīng)用程序資源

bufferhit

命令 SELECT a.cntr_value * 1.0 / b.cntr_value * 100.0 AS BufferCacheHitRatio FROM sys.dm_os_performance_counters a JOIN ( SELECT cntr_value, OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base' AND OBJECT_NAME = 'MSSQL$MS%d:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio' AND a.OBJECT_NAME = 'MSSQL$MS%d:Buffer Manager'解釋 sys.dm_os_performance_counters?????? 為服務(wù)器維護(hù)的每個(gè)性能計(jì)數(shù)器返回一行。counter_name????計(jì)數(shù)器的名稱(chēng)OBJECT_NAME??????????計(jì)數(shù)器的所屬類(lèi)別 Buffer Manager 對(duì)象提供了計(jì)數(shù)器，用于監(jiān)視 SQL Server 如何使用：內(nèi)存存儲(chǔ)數(shù)據(jù)頁(yè)、內(nèi)部數(shù)據(jù)結(jié)構(gòu)和過(guò)程緩存。計(jì)數(shù)器監(jiān)視 SQL Server 讀取和寫(xiě)入數(shù)據(jù)庫(kù)頁(yè)時(shí)的物理 I/O。Buffer Manager中的Buffer cache hit ratio base和Buffer cache hit ratioBuffer cache hit ratio 官方解釋在緩沖區(qū)高速緩存中找到而不需要從磁盤(pán)中讀取的頁(yè)的百分比。經(jīng)驗(yàn)正常情況應(yīng)該在 99%以上

checkpoint

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager' AND counter_name = 'Checkpoint pages/sec'解釋由要求刷新所有臟頁(yè)的檢查點(diǎn)或其他操作每秒刷新到磁盤(pán)的頁(yè)數(shù)。經(jīng)驗(yàn) 1. 顯示執(zhí)行 checkpoint；會(huì)發(fā)生checkpoint 2. alter database 添加或刪除數(shù)據(jù)庫(kù)文件；會(huì)發(fā)生checkpoint 3. 停止服務(wù)；會(huì)發(fā)生checkpoint 4. engine定期生成checkpoint，跟 recovery interval、恢復(fù)模式相關(guān) 5. 備份；會(huì)發(fā)生checkpoint 6. 在數(shù)據(jù)庫(kù)中執(zhí)行了最小日志記錄操作，例如，在使用大容量日志恢復(fù)模式的數(shù)據(jù)庫(kù)中執(zhí)行大容量復(fù)制操作；會(huì)發(fā)生checkpoint 7 執(zhí)行了需要關(guān)閉數(shù)據(jù)庫(kù)的活動(dòng)。例如，AUTO_CLOSE 設(shè)置為 ON 并且關(guān)閉了數(shù)據(jù)庫(kù)的最后一個(gè)用戶(hù)連接，或者執(zhí)行了需要重新啟動(dòng)數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)選項(xiàng)更改；會(huì)發(fā)生checkpoint 8. 08R2 checkpoint是實(shí)例級(jí)別的配置，但進(jìn)行的過(guò)程（實(shí)現(xiàn)方式）是數(shù)據(jù)庫(kù)級(jí)別的，這個(gè)msdn沒(méi)有詳細(xì)說(shuō)明但可以做實(shí)驗(yàn)證明，大概方法可以構(gòu)造兩個(gè)數(shù)據(jù)庫(kù)和相應(yīng)數(shù)據(jù)，不同的DB session手動(dòng)執(zhí)行checkpoint觀(guān)察sys.dm_os_buffer_descriptors 9. 臟頁(yè)的多少影響checkpoint開(kāi)銷(xiāo)大小 10. 08R2鏡像端不支持checkpoint

pagelife

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager????' AND counter_name = 'Page life expectancy'解釋表示頁(yè)面在緩存中的預(yù)期 "壽命"，單位為秒。微軟建議最少 300秒。如果在一個(gè)實(shí)例中經(jīng)常低于 300秒，意味著數(shù)據(jù)保留的時(shí)間少于 5分鐘就被移出內(nèi)存。經(jīng)驗(yàn) 1. 正常PLE應(yīng)該是大于 300且為一條持續(xù)平穩(wěn)上升的直線(xiàn) 2. PLE經(jīng)常性的波動(dòng)可能是內(nèi)存壓力的信號(hào)，具體再結(jié)合lazy_write，page_writes/page_reads等一起觀(guān)察判定

sqlcompilations

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:SQL Statistics' AND counter_name = 'SQL Compilations/sec'解釋每秒的 SQL 編譯數(shù)。 表示編譯代碼路徑被進(jìn)入的次數(shù)。 包括 SQL Server 中語(yǔ)句級(jí)重新編譯導(dǎo)致的編譯。 當(dāng) SQL Server 用戶(hù)活動(dòng)穩(wěn)定后，該值將達(dá)到穩(wěn)定狀態(tài)。經(jīng)驗(yàn)編譯截?cái)嘀饕荂PU開(kāi)銷(xiāo)，一般可以和Batch Requests/sec對(duì)照來(lái)看

logins

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:General Statistics' AND counter_name = 'Logins/sec'解釋 General Statistics 對(duì)象提供計(jì)數(shù)器，用于監(jiān)視服務(wù)器范圍內(nèi)的常規(guī)活動(dòng)Logins/sec?? 每秒啟動(dòng)的登錄數(shù)。 這不包括已入池連接。

transactions

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE 'MSSQL$MS%d:database%%' AND instance_name = '_Total' AND counter_name LIKE 'Transactions/sec%%'解釋 Database 對(duì)象提供了計(jì)數(shù)器，來(lái)監(jiān)視大容量復(fù)制操作、備份和還原吞吐量以及事務(wù)日志活動(dòng)。_Total???? 有的計(jì)數(shù)器的instance_name中有值為_(kāi)Total的實(shí)例，它是對(duì)該計(jì)數(shù)器所有實(shí)例的聚合值( SUM)。Transactions/sec?????? 每秒為數(shù)據(jù)庫(kù)啟動(dòng)的事務(wù)數(shù)經(jīng)驗(yàn)理解SQLServer的Transactions和Batch Requests，一個(gè)Batch Requests可能有多個(gè)Transactions，Batch Requests更能說(shuō)明系統(tǒng)負(fù)載

locktimeout

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Locks' AND counter_name = 'Lock Timeouts/sec' AND instance_name = '_Total'解釋Locks 對(duì)象提供了有關(guān)各種資源類(lèi)型的 SQL Server 鎖的信息 Lock Timeouts/sec????每秒超時(shí)的鎖請(qǐng)求數(shù)，包括對(duì) NOWAIT 鎖的請(qǐng)求。

deadlock

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'解釋 Number of Deadlocks/sec???? 每秒導(dǎo)致死鎖的鎖請(qǐng)求數(shù)。經(jīng)驗(yàn)經(jīng)常死鎖可能會(huì)導(dǎo)致嚴(yán)重的問(wèn)題，借助Profiler可以解，但在RDS場(chǎng)景下最好的處理方式是打開(kāi)Flag 1222 通過(guò)日志分析

lockwaits

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Locks' AND counter_name = 'Lock Waits/sec' AND instance_name = '_Total'解釋 Lock Waits/sec?? 每秒要求調(diào)用者等待的鎖請(qǐng)求數(shù)。

fullscans

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Access Methods' AND counter_name = 'Full Scans/sec'解釋 Access Methods?????? 對(duì)象提供用于監(jiān)視如何訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)中的邏輯數(shù)據(jù)的計(jì)數(shù)器。 Full Scans/sec????每秒不受限制的完全掃描數(shù)。這些掃描可以是基表掃描，也可以是全文索引掃描。

pagesplits

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Access Methods ' AND counter_name = 'Page Splits/sec'解釋Page Splits/sec?? 每秒由于索引頁(yè)溢出而發(fā)生的頁(yè)拆分?jǐn)?shù)。經(jīng)驗(yàn)頁(yè)拆分會(huì)搬移數(shù)據(jù)，性能開(kāi)銷(xiāo)，和fillfactor參數(shù)有聯(lián)系； 12相對(duì)于 08有優(yōu)化，碰到大條記錄拆分一次放不下可能會(huì)嘗試生成一個(gè)新頁(yè)存放，優(yōu)化出現(xiàn)連續(xù)拆分的情況

lazy_writes

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager' AND counter_name = 'Lazy writes/sec'解釋Lazy writes/sec?? 每秒被緩沖區(qū)管理器的惰性編寫(xiě)器寫(xiě)入的緩沖區(qū)數(shù)。惰性編寫(xiě)器????一個(gè)系統(tǒng)進(jìn)程，用于成批刷新臟的老化的緩沖區(qū)（包含更改的緩沖區(qū)，必須將這些更改寫(xiě)回磁盤(pán)，才能將緩沖區(qū)重用于其他頁(yè)），并使它們可用于用戶(hù)進(jìn)程。當(dāng) SQL Server感覺(jué)到內(nèi)存壓力的時(shí)候，會(huì)將最久沒(méi)有使用的數(shù)據(jù)頁(yè)面和執(zhí)行計(jì)劃從緩 沖池中清理掉，做這個(gè)動(dòng)作的就是Lazy Writer。經(jīng)驗(yàn)Lazy writes有單獨(dú)一個(gè)系統(tǒng)進(jìn)程，刷臟頁(yè)的過(guò)程和checkpoint類(lèi)似同樣會(huì)有性能影響，對(duì)比checkpoint可以理解為兩者的行為類(lèi)似但目的不 同，Lazy writes是保證內(nèi)存有可用頁(yè)，一般有內(nèi)存壓力的時(shí)候會(huì)頻繁出現(xiàn)，Checkpoint聚合寫(xiě)、優(yōu)化IO、維護(hù)數(shù)據(jù)一致性，可以結(jié)合PLE一起觀(guān)察內(nèi)存 問(wèn)題

pagereads

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager ' AND counter_name = 'Page reads/sec'理解Page reads/sec??每秒發(fā)出的物理數(shù)據(jù)庫(kù)頁(yè)讀取數(shù)。經(jīng)驗(yàn)大量物理讀寫(xiě)和IOPS升高是一致的；頻繁持續(xù)的物理讀寫(xiě)過(guò)高先找找是否有需要優(yōu)化改寫(xiě)的 SQL或者考慮增加內(nèi)存，當(dāng)然內(nèi)存是有成本的最好的方式還是通過(guò)rewrite queries/ add intelligent indexes處理；

pagewrite

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager' AND counter_name = 'Page writes/sec'理解Page writes/sec??每秒執(zhí)行的物理數(shù)據(jù)庫(kù)頁(yè)寫(xiě)入數(shù)。經(jīng)驗(yàn)大量物理讀寫(xiě)和IOPS升高是一致的；頻繁持續(xù)的物理讀寫(xiě)過(guò)高先找找是否有需要優(yōu)化改寫(xiě)的 SQL或者考慮增加內(nèi)存，當(dāng)然內(nèi)存是有成本的最好的方式還是通過(guò)rewrite queries/ add intelligent indexes處理；

qps

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:SQL Statistics' AND counter_name = 'Batch Requests/sec'理解 SQL Statistics??????對(duì)象提供計(jì)數(shù)器來(lái)監(jiān)視編譯和發(fā)送到 SQL Server 實(shí)例的請(qǐng)求類(lèi)型。通過(guò)監(jiān)視查詢(xún)編譯和重新編譯的次數(shù)以及 SQL Server 實(shí)例收到的批數(shù)，可了解 SQL Server 處理用戶(hù)查詢(xún)的速度，以及查詢(xún)優(yōu)化器處理查詢(xún)的效率。Batch Requests/sec??每秒收到的 Transact- SQL 命令批數(shù)。經(jīng)驗(yàn)這個(gè)Batch Requests跟MySQL的QPS不同，SQLServer一個(gè)Batch Requests可能包含多個(gè)Transactions

traffic_kb

命令 SELECT round( SUM(net_packet_size * 1.0 * num_reads / 1024), 0) AS read_kb, round( SUM(net_packet_size * 1.0 * num_writes / 1024), 0) AS write_kb FROM sys.dm_exec_connections WHERE session_id > 50理解dm_exec_connections???? 返回與 SQL Server 實(shí)例建立的連接有關(guān)的信息以及每個(gè)連接的詳細(xì)信息。net_packet_size??用于信息和數(shù)據(jù)的網(wǎng)絡(luò)包的大小。可為 Null 值。num_reads?? 此連接中已發(fā)生的讀包次數(shù)。可為 Null 值。num_writes??此連接中已發(fā)生的寫(xiě)數(shù)據(jù)包次數(shù)。可為 Null 值。session_id????標(biāo)識(shí)與此連接關(guān)聯(lián)的會(huì)話(huà)。可為 Null 值。

db_io

命令 SELECT SUM(fs.num_of_reads + fs.num_of_writes) AS [mssql_db_log_io] FROM sys.dm_io_virtual_file_stats( NULL, NULL) AS fs INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.file_id = f.file_id INNER JOIN sys.databases AS d ON d.database_id = f.database_id AND d.name IN (%s) GROUP BY d.name ORDER BY d.name解釋sys.dm_io_virtual_file_stats???? 返回?cái)?shù)據(jù)和日志文件的 I/O 統(tǒng)計(jì)信息。num_of_reads???? 對(duì)文件發(fā)出的讀取次數(shù)。num_of_writes????在該文件中寫(xiě)入的次數(shù)。sys.master_files??存儲(chǔ)在 master 數(shù)據(jù)庫(kù)中的每個(gè)數(shù)據(jù)庫(kù)文件都在表中占用一行。database_id 應(yīng)用此文件的數(shù)據(jù)庫(kù)的 ID。 master database_id 始終為 1。file_id????數(shù)據(jù)庫(kù)內(nèi)文件的 ID。主 file_id 始終為 1sys.databases???? 實(shí)例中的每個(gè)數(shù)據(jù)庫(kù)都對(duì)應(yīng)一行 name???? 數(shù)據(jù)庫(kù)名稱(chēng)

activesession

命令 select COUNT(*) from sys.dm_exec_sessionswhere login_name not in ( 'root', 'aurora', 'sa', 'eagleye') and login_name not like 's\_%' escape '\' and login_name not like '%mssqld' and login_name not like '%Administrator' and status not in ( 'Preconnect', 'Dormant', 'Sleeping') and session_id > 50解釋比之前的session監(jiān)控多了Sleeping和session_id> 50的過(guò)濾條件Sleeping??????當(dāng)前沒(méi)有運(yùn)行任何請(qǐng)求經(jīng)驗(yàn)activesession過(guò)高一般業(yè)務(wù)都會(huì)出現(xiàn)卡慢，但這個(gè)值只是一個(gè)結(jié)果表現(xiàn)，具體導(dǎo)致原因還需要其它進(jìn)一步排查

connectionreset

?? 命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%s:General Statistics' AND counter_name = 'Connection Resets/sec'解釋 Connection Resets/sec????從連接池啟動(dòng)的登錄總次數(shù)。????????????????

------------------------------------------------------------------------------------

死鎖的四個(gè)必要條件：

• 互斥條件(Mutual exclusion)：資源不能被共享，只能由一個(gè)進(jìn)程使用。
• 請(qǐng)求與保持條件(Hold and wait)：已經(jīng)得到資源的進(jìn)程可以再次申請(qǐng)新的資源。
• 非剝奪條件(No pre-emption)：已經(jīng)分配的資源不能從相應(yīng)的進(jìn)程中被強(qiáng)制地剝奪。
• 循環(huán)等待條件(Circular wait)：系統(tǒng)中若干進(jìn)程組成環(huán)路，該環(huán)路中每個(gè)進(jìn)程都在等待相鄰進(jìn)程正占用的資源

1. 圖形化查詢(xún):

sqlserver -->工具--> sql server profiler

2. 使用日志跟蹤

以全局方式打開(kāi)指定的跟蹤標(biāo)記

??????? DBCC TRACEON(1222,-1)

?????? DBCC TRACEON(1204,-1)

使用? EXEC master..xp_readerrorlog 查看日志。

3.使用sql分析查詢(xún)死鎖與阻塞腳本：

/* 功能說(shuō)明: 數(shù)據(jù)庫(kù)死鎖與阻塞語(yǔ)句查詢(xún) */ DECLARE @spid INT DECLARE @blk INT DECLARE @count INT DECLARE @index INT DECLARE @LOCK TINYINTSET @LOCK=0CREATE TABLE #temp_who_lock (id INT IDENTITY(1, 1),spid INT,blk INT )--if @@error<>0 return @@error INSERT INTO #temp_who_lock(spid,blk) SELECT 0,blocked FROM (SELECT *FROM master..sysprocessesWHERE blocked > 0)a WHERE NOT EXISTS(SELECT *FROM master..sysprocessesWHERE a.blocked = spidAND blocked > 0) UNION SELECT spid,blocked FROM master..sysprocesses WHERE blocked > 0--if @@error<>0 return @@error SELECT @count = Count(*),@index = 1 FROM #temp_who_lock--select @count,@index--if @@error<>0 return @@error IF @count = 0 BEGINSELECT '沒(méi)有阻塞和死鎖信息' --return 0 ENDWHILE @index <= @count BEGINIF EXISTS(SELECT 1FROM #temp_who_lock aWHERE id > @indexAND EXISTS(SELECT 1FROM #temp_who_lockWHERE id <= @indexAND a.blk = spid))BEGINSET @LOCK=1SELECT @spid = spid,@blk = blkFROM #temp_who_lockWHERE id = @indexSELECT '引起數(shù)據(jù)庫(kù)死鎖的是: ' + Cast(@spid AS VARCHAR(10)) + '進(jìn)程號(hào),其執(zhí)行的SQL語(yǔ)法如下' ;SELECT @spid,@blkDBCC inputbuffer(@spid)DBCC inputbuffer(@blk)ENDSET @index=@index + 1 ENDIF @LOCK = 0 BEGINSET @index=1WHILE @index <= @countBEGINSELECT @spid = spid,@blk = blkFROM #temp_who_lockWHERE id = @indexIF @spid = 0SELECT '引起阻塞的是:' + Cast(@blk AS VARCHAR(10)) + '進(jìn)程號(hào),其執(zhí)行的SQL語(yǔ)法如下' ELSESELECT '進(jìn)程號(hào)SPID：' + Cast(@spid AS VARCHAR(10)) + '被' + '進(jìn)程號(hào)SPID：' + Cast(@blk AS VARCHAR(10)) + '阻塞,其當(dāng)前進(jìn)程執(zhí)行的SQL語(yǔ)法如下'PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk));if(@spid <> 0)BEGINDBCC inputbuffer(@spid) --ENDDBCC inputbuffer(@blk) --引起阻塞語(yǔ)句SET @index=@index + 1END ENDDROP TABLE #temp_who_lock--return 0 --KILL 54

4. SQLServer 死鎖的診斷和定位

---------------------------------------------------------------------------------------------------------

SqlServer內(nèi)存瓶頸分析--SQLServer:Buffer Manager

SELECT TOP 312 * FROM sys.dm_os_performance_counters where object_name ='SQLServer:Buffer Manager' order by counter_name

1.Buffer cache hit ratio ?平均值>=90% ??? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

緩沖區(qū)是創(chuàng)建數(shù)據(jù)頁(yè)面的緩沖頁(yè)面池，這一般是SQL server內(nèi)存池中最大的部分。這個(gè)計(jì)數(shù)器應(yīng)該盡可能高。

低值表示只有少數(shù)的請(qǐng)求是在緩沖區(qū)，其他請(qǐng)求是在磁盤(pán)獲取服務(wù)

這種情況發(fā)生可能是SQL server還在準(zhǔn)備狀態(tài)下，或緩沖區(qū)的增長(zhǎng)需求超過(guò)最大可用內(nèi)存，因此如果此值比較低，應(yīng)該考慮為系統(tǒng)增加內(nèi)存。

2.Checkpoint pages/sec ? ?平均值<30? ???

美好一個(gè)檢查點(diǎn)操作移到磁盤(pán)的頁(yè)面數(shù)量，這些值應(yīng)該相當(dāng)?shù)?#xff0c;高值意味著緩沖中更多的頁(yè)面被標(biāo)記為臟頁(yè)面。

臟頁(yè)面是在緩沖區(qū)中被修改的頁(yè)面。一旦被修改，就標(biāo)記為臟并在下一個(gè)檢查點(diǎn)被寫(xiě)回磁盤(pán)。

如果這個(gè)計(jì)數(shù)器較高說(shuō)明系統(tǒng)發(fā)生大量的寫(xiě)入操作，可能是I/O問(wèn)題的征兆。 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

3.Lazy writes/sec ?? ?平均值<20 ? ? ??? ? ?

?記錄了緩沖區(qū)管理器的Lazy write進(jìn)程每秒寫(xiě)入的緩沖區(qū)數(shù)量。這個(gè)進(jìn)程會(huì)把時(shí)間較長(zhǎng)的臟頁(yè)面從緩沖區(qū)中刪除，將內(nèi)存釋放

?數(shù)值過(guò)高說(shuō)明I/O有問(wèn)題或是內(nèi)存問(wèn)題。 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

4.Page life expectancy ? ? 平均值>300 ? ?????

頁(yè)面平均壽命，表示一個(gè)不被引用的頁(yè)面在緩沖池中停留的時(shí)間。數(shù)值低意味著頁(yè)面從緩沖區(qū)中被移走

降低了緩沖的效率并指出內(nèi)存壓力的可能。 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

SELECT TOP 312 * FROM sys.dm_os_performance_counters where object_name ='SQLServer:Memory Manager' order by counter_name

1.Memory Grants Pending ? ?　　平均值=0

值高說(shuō)明內(nèi)存短缺，需要將進(jìn)度掛起等待內(nèi)存空閑

2.?Target Server Memory(KB) ?　　 接近物理內(nèi)存大小

表示SQLServer希望消耗的動(dòng)態(tài)內(nèi)存總數(shù)。

3.Total Server Memory (KB ?)　　　　接近于Target Server Memory
當(dāng)前分配給SQLerver的總內(nèi)存。 如果低于Target Server Memory

那么可能是SQLserver內(nèi)存需求很低，服務(wù)器最大內(nèi)存設(shè)置的太低
?

解決方案：

優(yōu)化應(yīng)用程序工作負(fù)載

為SQLServer分配更多內(nèi)存

增加系統(tǒng)內(nèi)存

更新32位處理器為64位

啟用3G進(jìn)程空間

在32位SQLserver中使用4G以上內(nèi)存

?

啟用3G進(jìn)程空間：

在Boot.ini文件中增加3GB開(kāi)關(guān)

[boot loader]

timeout=30

default=multi(0)disk(0)rdisk(0)partition(1)\WINNT

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\WINNT=

"Microsoft Windows Server 2008 Advanced Server"

/fastdetect /3GB

?

總結(jié)

以上是生活随笔為你收集整理的SQL Server监控常规计数器收集参考（mssql阻塞的跟踪查询,死锁的跟踪查询,改善）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。