SQL Server2008中CPU性能監(jiān)控
CPU性能診斷
CPU架構(gòu)
目前的主流企業(yè)服務(wù)器基本可以分為三類：SMP(Symmetric Multi Processing，對(duì)稱多處理架構(gòu))，NUMA(Non-Uniform Memory Access，非一致存儲(chǔ)訪問架構(gòu))和MPP(Massive Parallel Processing，海量并行處理架構(gòu))
SMP(Symmetric Multi Processing)
SMP是非常常見的一種架構(gòu)。在SMP模式下，多個(gè)處理器均對(duì)稱的連接在系統(tǒng)內(nèi)存上，所有處理器都以平等的代價(jià)訪問系統(tǒng)內(nèi)存。它的優(yōu)點(diǎn)是對(duì)內(nèi)存的訪問是平等、一致的;缺點(diǎn)是因?yàn)榇蠹叶际且恢碌模趥鹘y(tǒng)的 SMP 系統(tǒng)中，所有處理器都共享系統(tǒng)總線，因此當(dāng)處理器的數(shù)目增多時(shí)，系統(tǒng)總線的競爭沖突迅速加大，系統(tǒng)總線成為了性能瓶頸，所以目前 SMP 系統(tǒng)的處理器數(shù)目一般只有數(shù)十個(gè)，可擴(kuò)展性受到很大限制。
▲
MPP (Massive Parallel Processing)
MPP則 是邏輯上將整個(gè)系統(tǒng)劃分為多個(gè)節(jié)點(diǎn)，每個(gè)節(jié)點(diǎn)的處理器只可以訪問本身的本地資源，是完全無共享的架構(gòu)。節(jié)點(diǎn)之間的數(shù)據(jù)交換需要軟件實(shí)施。它的優(yōu)點(diǎn)是可擴(kuò)展 性非常好;缺點(diǎn)是彼此數(shù)據(jù)交換困難，需要控制軟件的大量工作來實(shí)現(xiàn)通訊以及任務(wù)的分配、調(diào)度，對(duì)于一般的企業(yè)應(yīng)用而言過于復(fù)雜，效率不高。
▲
NUMA(Non-Uniform Memory Access)
NUMA架構(gòu)則在某種意義上是綜合了SMP和MPP的特點(diǎn)：邏輯上整個(gè)系統(tǒng)也是分為多個(gè)節(jié)點(diǎn)，每個(gè)節(jié)點(diǎn)可以訪問本地內(nèi)存資源，也可以訪問遠(yuǎn)程內(nèi)存資源，但訪問本地內(nèi)存資源遠(yuǎn)遠(yuǎn)快于遠(yuǎn)程內(nèi)存資源。它的優(yōu)點(diǎn)是兼顧了SMP和MPP的特點(diǎn), 易于管理，可擴(kuò)充性好;缺點(diǎn)是訪問遠(yuǎn)程內(nèi)存資源的所需時(shí)間非常的大。
不過目前世界上排名靠前的大型機(jī)都是基于MPP架構(gòu)的，AMD最早支持NUMA架構(gòu)，而盡管Intel一直是SMP的支持者，但從Xeon處理器和酷睿i7開始也逐步走向了NUMA架構(gòu)之路。
SQL Server從2000版本的SP4起開始增加了對(duì)NUMA的支持，從2005版本起得到了全面的采用;SQL Server2008 R2版本起夜增加了對(duì)MPP架構(gòu)的支持;不過從目前SQL Server的情況來看，主要是支持SMP和NUMA兩種架構(gòu)。
#p#
Windows性能監(jiān)控
使用管理工具中的“性能”管理工具可以自動(dòng)從本地或遠(yuǎn)程計(jì)算機(jī)收集性能數(shù)據(jù)?？梢允褂?ldquo;系統(tǒng)監(jiān)視器”查看記錄的計(jì)算機(jī)數(shù)據(jù)，也可以將數(shù)據(jù)導(dǎo)出到電子表格程序或數(shù)據(jù)庫進(jìn)行分析并生成報(bào)告。該工具類似于linux下的top，iostat，vmstat等監(jiān)控命令。
對(duì)于SQL Server占用CPU資源的監(jiān)控主要集中在消耗時(shí)間的百分比和處理器隊(duì)列長度上，下面提供了幾種對(duì)象、計(jì)數(shù)器和相應(yīng)的閾值及描述。
▲
SQL Server性能監(jiān)控
SQL Server內(nèi)部監(jiān)控對(duì)象及其計(jì)數(shù)器可以通過性能工具進(jìn)行收集和評(píng)估，但從SQL Server2005之后提供了一個(gè)叫sys.dm_os_performance_counters的系統(tǒng)視圖，這個(gè)視圖返回的信息列舉與SQL Server直接相關(guān)的性能統(tǒng)計(jì)。
下面語句可以統(tǒng)計(jì)出SQL Server所有的監(jiān)控對(duì)象和計(jì)數(shù)器情況。
SELECT object_name,COUNT(DISTINCT counter_name)
FROM sys.dm_os_performance_counters
GROUP BY object_name
ORDER BY object_name
在本機(jī)中共計(jì)27中SQLServer對(duì)象和334個(gè)計(jì)數(shù)器，其中加粗部分字體為最常關(guān)注的對(duì)象。
以下是對(duì)象類別、計(jì)數(shù)器數(shù)量和對(duì)象描述。
MSSQL$MYSQLSERVER:Access Methods 43 搜索并測量 SQL Server 數(shù)據(jù)庫對(duì)象的分配(例如，索引搜索數(shù)或分配給索引和數(shù)據(jù)的頁數(shù))。
MSSQL$MYSQLSERVER:Broker Activation 6 提供有關(guān)已激活 Service Broker 的任務(wù)的信息。
MSSQL$MYSQLSERVER:Broker Statistics 37 提供 Service Broker 的常規(guī)信息。
MSSQL$MYSQLSERVER:Broker TO Statistics 9
MSSQL$MYSQLSERVER:Broker/DBM Transport 34 提供有關(guān)Service Broker網(wǎng)絡(luò)的信息。
MSSQL$MYSQLSERVER:Buffer Manager 21 提供有關(guān) SQL Server 所用的內(nèi)存緩沖區(qū)的信息，如可用內(nèi)存和 buffer cache hit ratio。
MSSQL$MYSQLSERVER:Buffer Node 9 提供有關(guān)SQL Server請(qǐng)求和訪問可用頁的頻率的信息。
MSSQL$MYSQLSERVER:Buffer Partition 3 提供有關(guān)SQL Server請(qǐng)求和訪問可用頁的頻率的信息。
MSSQL$MYSQLSERVER:Catalog Metadata 4
MSSQL$MYSQLSERVER:CLR 1 提供有關(guān)公共語言運(yùn)行時(shí) (CLR) 的信息。
MSSQL$MYSQLSERVER:Cursor Manager by Type 9 提供游標(biāo)信息。
MSSQL$MYSQLSERVER:Cursor Manager Total 3 提供游標(biāo)信息。
MSSQL$MYSQLSERVER:Databases 26 提供有關(guān) SQL Server 數(shù)據(jù)庫的信息，如可用的日志空間數(shù)量或數(shù)據(jù)庫中活動(dòng)事務(wù)數(shù)。這個(gè)對(duì)象可有多個(gè)實(shí)例。
MSSQL$MYSQLSERVER:Deprecated Features 1 對(duì)使用不推薦使用的功能的次數(shù)進(jìn)行計(jì)數(shù)。
MSSQL$MYSQLSERVER:Exec Statistics 4 提供了有關(guān)執(zhí)行統(tǒng)計(jì)信息的信息。
MSSQL$MYSQLSERVER:General Statistics 24 提供有關(guān)服務(wù)器范圍內(nèi)的常規(guī)活動(dòng)的信息，如連接到 SQL Server 實(shí)例的用戶數(shù)。
MSSQL$MYSQLSERVER:Latches 7 提供有關(guān)加在 SQL Server 所用的內(nèi)部資源(如數(shù)據(jù)庫頁)上的閂鎖的信息。
MSSQL$MYSQLSERVER:Locks 8 提供有關(guān) SQL Server 的單個(gè)鎖請(qǐng)求的信息，如鎖超時(shí)和死鎖。這個(gè)對(duì)象可有多個(gè)實(shí)例。
MSSQL$MYSQLSERVER:Memory Manager 14 提供有關(guān) SQL Server 內(nèi)存的使用信息，如當(dāng)前分配的鎖結(jié)構(gòu)總數(shù)。
MSSQL$MYSQLSERVER:Plan Cache 5 提供有關(guān) SQL Server 高速緩存的信息，該高速緩存用于存儲(chǔ)如存儲(chǔ)過程、觸發(fā)器和查詢計(jì)劃這樣的對(duì)象。
MSSQL$MYSQLSERVER:Resource Pool Stats 15 提供了有關(guān)資源調(diào)控器資源池統(tǒng)計(jì)的信息。
MSSQL$MYSQLSERVER:SQL Errors 1 提供有關(guān) SQL Server 錯(cuò)誤的信息。
MSSQL$MYSQLSERVER:SQL Statistics 11 提供有關(guān) SQL查詢各個(gè)方面的信息，如 SQL Server 收到的 Transact-SQL 語句的批數(shù)。
MSSQL$MYSQLSERVER:Transactions 14 提供了有關(guān) SQL Server 中活動(dòng)事務(wù)的信息，如事務(wù)總數(shù)和快照事務(wù)數(shù)。
MSSQL$MYSQLSERVER:User Settable 1 執(zhí)行自定義監(jiān)視。每個(gè)計(jì)數(shù)器可以是一個(gè)自定義的存儲(chǔ)過程或任何返回一個(gè)被監(jiān)視值的Transact-SQL語句。
MSSQL$MYSQLSERVER:Wait Statistics 12 提供有關(guān)等待的信息。
MSSQL$MYSQLSERVER:Workload Group Stats 12 提供了有關(guān)資源調(diào)控器工作負(fù)荷組統(tǒng)計(jì)的信息。
#p#
關(guān)于CPU的資源消耗基本來自于兩個(gè)方面，首先是低效率的查詢計(jì)劃，再次是過度編譯和重編譯。
過度編譯和重編譯，SQL語句的編譯和重編譯都是CPU密集的活動(dòng)，發(fā)生大量的重編譯，則CPU利用率會(huì)增加。所以對(duì)SQL Server在CPU上的關(guān)注會(huì)集中在這些SQL統(tǒng)計(jì)的編譯/重編譯計(jì)數(shù)器上。
▲
下面語句。
SELECT top 25
qt.text,
qs.plan_generation_num,
qs.execution_count,
dbid,
objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SQL Server中，我們也可以確定平均或累計(jì)占用CPU時(shí)間最多的查詢，SQL Server優(yōu)化器是基于成本的，通過該查詢語句的定位，再進(jìn)一步分析，是統(tǒng)計(jì)信息問題還是索引和連接難題。
SELECT top 50
qt.text AS SQL_text ,
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --統(tǒng)計(jì)總的CPU時(shí)間
--ORDER BY avg_cpu_time DESC --統(tǒng)計(jì)平均單次查詢CPU時(shí)間
以下為SQL Server 2008關(guān)于CPU的一些系統(tǒng)視圖。
▲
dm_os_nodes提供了一個(gè)名為 SQL OS 的內(nèi)部組件可創(chuàng)建模擬硬件處理器位置的節(jié)點(diǎn)結(jié)構(gòu)。
dm_os_schedulers 對(duì)于 SQL Server(每個(gè)計(jì)劃程序都映射到其中的單個(gè)處理器)中的每個(gè)計(jì)劃程序，相應(yīng)地返回一行。使用此視圖可以監(jiān)視計(jì)劃程序的情況或標(biāo)識(shí)失控任務(wù)
dm_os_workers 則對(duì)于系統(tǒng)中的每個(gè)工作線程，相應(yīng)地返回一行。
dm_os_threads 對(duì)于系統(tǒng)中的所有SQLOS工作線程，相應(yīng)地返回一行。
sys.dm_os_tasks 為 SQL Server 實(shí)例中的每個(gè)活動(dòng)任務(wù)返回一行。
計(jì)算可運(yùn)行狀態(tài)下的工作進(jìn)程數(shù)量，來觀察CPU壓力
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address
AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
也可以查找用戶會(huì)話和操作系統(tǒng)線程的對(duì)照關(guān)系
SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
上文中詳細(xì)介紹了SQL Server2008中CPU性能監(jiān)控的知識(shí)，內(nèi)容比較多，希望大家不要漏過任何一個(gè)重要的知識(shí)點(diǎn)，相信對(duì)大家是百益而沒有害處的。
【編輯推薦】
