SQL Server2008CPU效能監控

bq_wang發表於2011-03-10
SQLServer

　　CPU效能診斷

　　CPU架構

　　目前的主流企業伺服器基 本可以分為三類：SMP(Symmetric Multi Processing，對稱多處理架構)，NUMA(Non-Uniform. Memory Access，非一致儲存訪問架構)和MPP(Massive Parallel Processing，海量並行處理架構)

　　SMP(Symmetric Multi Processing)

　　SMP是非常常見的一種架構。在SMP模式下，多個處理器均對稱的連線在系統記憶體上，所有處理器都以平等的代價訪問系統記憶體。 它的優點是對記憶體的訪問是平等、一致的;缺點是因為大家都是一致的，在傳統的 SMP 系統中，所有處理器都共享系統匯流排，因此當處理器的數目增多時，系統匯流排的競爭衝突迅速加大，系統匯流排成為了效能瓶頸，所以目前 SMP 系統的處理器數目一般只有數十個，可擴充套件性受到很大限制。

CPU效能診斷
▲

　　MPP (Massive Parallel Processing)

　 　MPP則 是邏輯上將整個系統劃分為多個節點，每個節點的處理器只可以訪問本身的本地資源，是完全無共享的架構。節點之間的資料交換需要軟體實施。它的優點是可擴充套件 性非常好;缺點是彼此資料交換困難，需要控制軟體的大量工作來實現通訊以及任務的分配、排程，對於一般的企業應用而言過於複雜，效率不高。

CPU效能診斷
▲

　　NUMA(Non-Uniform. Memory Access)

　 　NUMA架構則在某種意義上是綜合了SMP和MPP的特點：邏輯上整個系統也是分為多個節點，每個節點可以訪問本地記憶體資源，也可以訪問遠端記憶體資源， 但訪問本地記憶體資源遠遠快於遠端記憶體資源。它的優點是兼顧了SMP和MPP的特點, 易於管理，可擴充性好;缺點是訪問遠端記憶體資源的所需時間非常的大。

　　不過目前世界上排名靠前的大型機都是基於MPP架構的，AMD最早支援NUMA架構，而儘管Intel一直是SMP的支持者，但從Xeon處理器和酷睿i7開始也逐步走向了NUMA架構之路。

　　SQL Server從2000版本的SP4起開始增加了對NUMA的支援，從2005版本起得到了全面的採用;SQL Server2008 R2版本起夜增加了對MPP架構的支援;不過從目前SQL Server的情況來看，主要是支援SMP和NUMA兩種架構。

CPU效能診斷

　　Windows效能監控

　　使用管理工具中的“效能”管理工具可以自動從本地或遠端計算機收集效能資料。可以使用“系統監視器”檢視記錄的計算機資料，也可以將資料匯出到電子表格程式或資料庫進行分析並生成報告。該工具類似於linux下的top，iostat，vmstat等監控命令。

　　對於SQL Server佔用CPU資源的監控主要集中在消耗時間的百分比和處理器佇列長度上，下面提供了幾種物件、計數器和相應的閾值及描述。

Windows效能監控
▲

　　SQL Server效能監控

　 　SQL Server內部監控物件及其計數器可以通過效能工具進行收集和評估，但從SQL Server2005之後提供了一個叫sys.dm_os_performance_counters的系統檢視，這個檢視返回的資訊列舉與SQL Server直接相關的效能統計。

　　下面語句可以統計出SQL Server所有的監控物件和計數器情況。

#div_code img { border: 0px none; }
SELECT object_name,COUNT(DISTINCT counter_name)

　　FROM sys.dm_os_performance_counters

　　GROUP BY object_name

　　ORDER BY object_name

     在本機中共計27中SQLServer物件和334個計數器，其中加粗部分字型為最常關注的物件。

　 以下是物件類別、計數器數量和物件描述。

　　MSSQL$MYSQLSERVER:Access Methods 43 搜尋並測量 SQL Server 資料庫物件的分配(例如，索引搜尋數或分配給索引和資料的頁數)。

　　MSSQL$MYSQLSERVER:Broker Activation 6 提供有關已啟用 Service Broker 的任務的資訊。

　　MSSQL$MYSQLSERVER:Broker Statistics 37 提供 Service Broker 的常規資訊。

　　MSSQL$MYSQLSERVER:Broker TO Statistics 9

　　MSSQL$MYSQLSERVER:Broker/DBM Transport 34 提供有關Service Broker網路的資訊。

　　MSSQL$MYSQLSERVER:Buffer Manager 21 提供有關 SQL Server 所用的記憶體緩衝區的資訊，如可用記憶體和 buffer cache hit ratio。

　　MSSQL$MYSQLSERVER:Buffer Node 9 提供有關SQL Server請求和訪問可用頁的頻率的資訊。

　　MSSQL$MYSQLSERVER:Buffer Partition 3 提供有關SQL Server請求和訪問可用頁的頻率的資訊。

　　MSSQL$MYSQLSERVER:Catalog Metadata 4

　　MSSQL$MYSQLSERVER:CLR 1 提供有關公共語言執行時 (CLR) 的資訊。

　　MSSQL$MYSQLSERVER:Cursor Manager by Type 9 提供遊標資訊。

　　MSSQL$MYSQLSERVER:Cursor Manager Total 3 提供遊標資訊。

　　MSSQL$MYSQLSERVER:Databases 26 提供有關 SQL Server 資料庫的資訊，如可用的日誌空間數量或資料庫中活動事務數。這個物件可有多個例項。

　　MSSQL$MYSQLSERVER:Deprecated Features 1 對使用不推薦使用的功能的次數進行計數。

　　MSSQL$MYSQLSERVER:Exec Statistics 4 提供了有關執行統計資訊的資訊。

　　MSSQL$MYSQLSERVER:General Statistics 24 提供有關伺服器範圍內的常規活動的資訊，如連線到 SQL Server 例項的使用者數。

　　MSSQL$MYSQLSERVER:Latches 7 提供有關加在 SQL Server 所用的內部資源(如資料庫頁)上的閂鎖的資訊。

　　MSSQL$MYSQLSERVER:Locks 8 提供有關 SQL Server 的單個鎖請求的資訊，如鎖超時和死鎖。這個物件可有多個例項。

　　MSSQL$MYSQLSERVER:Memory Manager 14 提供有關 SQL Server 記憶體的使用資訊，如當前分配的鎖結構總數。

　　MSSQL$MYSQLSERVER:Plan Cache 5 提供有關 SQL Server 快取記憶體的資訊，該快取記憶體用於儲存如儲存過程、觸發器和查詢計劃這樣的物件。

　　MSSQL$MYSQLSERVER:Resource Pool Stats 15 提供了有關資源調控器資源池統計的資訊。

　　MSSQL$MYSQLSERVER:SQL Errors 1 提供有關 SQL Server 錯誤的資訊。

　　MSSQL$MYSQLSERVER:SQL Statistics 11 提供有關 SQL查詢各個方面的資訊，如 SQL Server 收到的 Transact-SQL 語句的批數。

　　MSSQL$MYSQLSERVER:Transactions 14 提供了有關 SQL Server 中活動事務的資訊，如事務總數和快照事務數。

　　MSSQL$MYSQLSERVER:User Settable 1 執行自定義監視。每個計數器可以是一個自定義的儲存過程或任何返回一個被監視值的Transact-SQL語句。

　　MSSQL$MYSQLSERVER:Wait Statistics 12 提供有關等待的資訊。

　　MSSQL$MYSQLSERVER:Workload Group Stats 12 提供了有關資源調控器工作負荷組統計的資訊。


　　關於CPU的資源消耗基本來自於兩個方面，首先是低效率的查詢計劃，再次是過度編譯和重編譯。

　　過度編譯和重編譯，SQL語句的編譯和重編譯都是CPU密集的活動，發生大量的重編譯，則CPU利用率會增加。所以對SQL Server在CPU上的關注會集中在這些SQL統計的編譯/重編譯計數器上。

CPU的資源消耗
▲

     下面語句。

#div_code img { border: 0px none; }
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中，我們也可以確定平均或累計佔用CPU時間最多的查詢，SQL Server優化器是基於成本的，通過該查詢語句的定位，再進一步分析，是統計資訊問題還是索引和連線難題。

#div_code img { border: 0px none; }
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 --統計總的CPU時間

　　--ORDER BY avg_cpu_time DESC --統計平均單次查詢CPU時間

      以下為SQL Server 2008關於CPU的一些系統檢視。

CPU的資源消耗
▲

　　dm_os_nodes提供了一個名為 SQL OS 的內部元件可建立模擬硬體處理器位置的節點結構。

　　dm_os_schedulers 對於 SQL Server(每個計劃程式都對映到其中的單個處理器)中的每個計劃程式，相應地返回一行。使用此檢視可以監視計劃程式的情況或標識失控任務

　　dm_os_workers 則對於系統中的每個工作執行緒，相應地返回一行。

　　dm_os_threads 對於系統中的所有SQLOS工作執行緒，相應地返回一行。

　　sys.dm_os_tasks 為 SQL Server 例項中的每個活動任務返回一行。

　　計算可執行狀態下的工作程式數量，來觀察CPU壓力

#div_code img { border: 0px none; }
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

　　也可以查詢使用者會話和作業系統執行緒的對照關係

#div_code img { border: 0px none; }
　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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-689039/，如需轉載，請註明出處，否則將追究法律責任。

相關文章