SQL Server2008引擎組件和CPU性能監控

　　SQLServer2008引擎組件

　　首先讓我們先來看看SQL Server2008的引擎組件，SQLServer2008有四大組件：協議、關係引擎、存儲引擎和SQLOS。

　　協議層(Protocol Layer)

　　當一個應用程式與SQL Server資料庫引擎通訊時，協議層提供的應用程式編程接口利用微軟自定義的tabular data stream(TDS)package來規範通訊格式。這一層的意義在於向應用程式提供訪問SQL Server的接口。

　　SQL Server Network Interface(簡稱SNI)

　　SNI是在伺服器和客戶端之間建立網絡連接的一種協議，他提供一組在資料庫引擎和SQL Server客戶端使用的API函數。SNI代替了SQL Server2000下的Net- Libraries組件和MDAC組件。

　　SQL Server支持共享內存(Shared memory)、TCP/IP、命名管道(Named Pipes)、虛擬接口適配器(Virtual Interface Adapter，即VIA)四種協議。

　　一旦建立連接，SNI就會向伺服器的TDS斷點創建一條安全的連接，用來進行數據的請求和返回。

　　表格格式數據流端點(Tabular Data Stream，簡稱TDS)

　　TDS是一種微軟的與資料庫進行交互的私有協議，SQL Server在安裝時為其支持的四種協議各創建一個端點，如果協議被激活，那麼所有用戶均可以使用這個協議。此外還有一個專門為專用管理員連接(DAC)而設置的端點。

　　一條SQL語句則會通過TCP/IP連接以TDS消息的形式發送給SQL Server。

　　協議層(Protocol Layer)

　　一旦協議層接收到TDS包，就會在反轉和解包工作，以找到所包含的請求。協議層也負責打包結果和狀態消息，並以TDS消息的形式返回客戶端。

　　關係引擎(Relational Engine)

　　關係引擎又成為查詢處理器，包括用來確定某個查詢所要做的操作及進行這些操作最佳方式的SQL Server組件。同時關係引擎也負責向存儲引擎請求數據時查詢的執行，並處理返回的結果。

　　命令解析器(Cmd Parser)

　　命令解析器處理髮送給SQL Server的T-SQL語言事件。它會先檢查T-SQL語法，並返回任何錯誤信息客戶端，如果語法有效，就會進一步產生執行計劃或者去查找一個已經存在的執行計劃。命令解析器通過T-SQL哈希值向位於緩衝池中的Plan Cache發出匹配要求，以檢查是否存在該執行計劃;如果不存在則把T-SQL翻譯成可以執行的內部格式，即查詢樹。

　　查詢優化器(Optimizer)

　　查詢優化器從命令解析器中獲取查詢樹，並為它的實際執行做準備。

　　生成執行計劃的第一步是對每個查詢進行規範化，規範化的過程有可能將單個查詢分解成多個粒度合適的查詢。然後進行最優化，SQL Server的查詢優化器是基於成本的，它會選擇它認為成本最低的執行計劃，它使用一些內部指標(內存需求、CPU利用率和I/O需求數目)作為選擇的依據。此外查詢優化器還會考慮請求語句的類型、檢查受到影響的各表的數據量、表中的索引，以及SQL Server統計數據。

　　SQL管理器

　　SQL管理器負責管理與存儲過程及其計劃有關的事務，並負責管理查詢的自動化參數。

　　資料庫管理器

　　資料庫管理器管理查詢編譯和查詢優化所需的對元數據的訪問。

　　查詢執行器(Query Executor)

　　查詢執行器運行查詢優化器生成的執行計劃，就像調度員負責調度執行計劃中的所有命令。

　　存儲引擎(Storage Engine)

　　存儲引擎包括存取方法、事務管理和緩衝區管理器。

　　存取方法(Access Methods)

　　SQL Server需要定位資料庫時，會調用存取方法代碼。它提供了一組代碼，用來創建和請求對數據頁面和索引頁面進行掃描，並且將準備好的OLE DB數據行集返回給關係引擎。存取方法並不真正進行操作，它只負責向緩衝區管理器發出請求。

　　事務管理器(Transaction Manager)

　　事務管理器包括兩個組件：日誌管理器和鎖管理器。

　　鎖管理器負責數據的並發保護和基於特定隔離級別的管理。日誌管理器負責將事務日誌提前記錄於日誌文件中，從而起到保護數據的作用。

　　緩衝區管理器(Buffer Manager)

　　緩衝區管理器用來管理緩衝區內存池中數據頁面的分布。

　　SQLOS

　　SQLOS則可以理解為SQL Server2008的作業系統，主要負責處理與作業系統之間的工作，SQL Server通過該接口層向作業系統申請內存分配、調度資源、管理進程和線程以及同步對象。

　　緩衝池(Buffer Pool)

　　緩衝池在SQL Server中是內存的最大消耗者，主要包括數據緩衝池和執行計劃緩衝池。

　　執行計劃緩衝池(Plan Cache)

　　生成執行計劃是比較消耗資源和時間的，因此在Plan Cache緩存這些執行計劃，有助於執行計劃的重用。

　　數據緩衝池(Data Cache)

　　數據緩衝池負責將數據頁和索引頁放在數據告訴緩衝池中，以便多個用戶可以共享數據。

　　SQL查詢示意圖

　　以下為一條基本的SQL查詢示意圖。

▲

　　首先客戶端的SNI通過TCP/IP協議和SQL Server服務端的SNI建立連接，然後建立連接到TDS的連接，並以TDS消息的方式傳送SELECT命令。SQL Server服務端的SNI對TDS消息進行解包，並把SQL命令傳給語法解析器;語法解析器在緩衝池中檢查是否存在執行計劃，如果沒有則創建一個查詢樹，並交給優化器;優化器產生相應的執行計劃;查詢執行器通過OLE DB接口向存儲引擎中的訪問存取方法發出請求讀取數據;存取方法則向緩衝區管理器發起數據請求，如果在緩衝池的數據緩衝池中存在相應的數據，如果沒有則從磁碟上讀取數據頁放到數據緩衝池中，並把數據返回給存取方法;存取方法在把結果返回給關係引擎，並最終以TDS消息的形式返回給客戶端。

　　CPU性能診斷

　　CPU架構

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

　　SMP(Symmetric Multi Processing)

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

▲

　　MPP (Massive Parallel Processing)

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

▲

　　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兩種架構。

▲

　　Windows性能監控

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

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

▲

　　SQL Server性能監控

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

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

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統計的編譯/重編譯計數器上。

▲

     下面語句。

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優化器是基於成本的，通過該查詢語句的定位，再進一步分析，是統計信息問題還是索引和連接難題。

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的一些系統視圖。

▲

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

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

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

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

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

　　計算可運行狀態下的工作進程數量，來觀察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

　　也可以查找用戶會話和作業系統線程的對照關係

　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;