全世界的 SQL Server DBA 都有一個似乎永遠無法解決的難題：故障排除，其中所執行的絕大多數故障排除都是為了查找某種性能問題。即使是最精心構建并經過測試的應用程序系統都會隨著時間的推移發生變化，進而導致可能出現重大的性能問題。

例如，工作負載可能會發生變化（如并發用戶的數量、需要執行的查詢數以及需要運行的新月末報告）、需要處理的數據量可能會增加、運行系統的硬件平臺可能會發生變化（如處理器內核數量、可用的服務器內存數量以及 I/O 子系統容量），另外還可能引入新的并發工作負載（如事務復制、數據庫鏡像以及更改數據捕獲）。

但這些并不是問題的全部。在設計和測試應用程序系統時，設計中往往會出現很多意外的問題，這些也都需要進行故障排除。顯然，無論在應用程序生命周期的哪個階段發現問題，都必須通過故障排除來找出原因并給出解決方案。

在一個復雜的應用程序系統中，可能會有許多需要進行分析的硬件和軟件組件，但我關注的只是 SQL Server。拋開各種性能故障排除方法不談（這些屬于本文以外的內容），您對 SQL Server 進行故障排除時都需要哪些工具呢？

SQL Server 2005 中的故障排除

在最近的幾個 SQL Server 版本中，可用于性能故障排除的工具大為增加。SQL Server 包含大量的 DBCC（數據庫控制臺命令）命令，可用于深入了解數據庫引擎各個部分的當前狀況。此外還包括 SQL Server Profiler，它可以通過編程的方式使用底層 SQL Trace 機制。

盡管 SQL Server 為進行故障排除不斷加以改進，但這些選擇依然存在某些問題。DBCC 輸出的后處理顯得有些笨拙，因為必須先將輸出結果轉儲到一個臨時表中然后才能對其進行操作。而且，如果配置不當，在運行 SQL Trace/Profiler 時可能會導致性能急劇降低（例如在某個繁忙系統中跟蹤所有“Lock:Acquired”（鎖定:獲得）和“Lock:Released”（鎖定:釋放）事件但卻忘記篩選該事件的 DatabaseId 和 ObjectId 列）。圖 1 中的屏幕快照顯示的是用于配置篩選器以進行新跟蹤的對話框。

圖 1 在 SQL Server 2008 Profiler 中配置篩選器

SQL Server 2005 添加了動態管理視圖和函數（統稱為 DMV）幫助從數據庫引擎獲取信息。DMV 廢棄了某些 DBCC 命令、系統表和存儲過程，同時公開了許多新的引擎工作區域。這些 DMV 是一些功能強大且可以組合的命令，可用在復雜的 T-SQL 語句中以篩選 DMV 結果并進行后處理。

例如，圖 2 中所示的代碼只返回數據庫中所有索引的葉級碎片和頁密度（均已圓整），它還包括一個碎片級篩選器。使用原來的 DBCC SHOWCONTIG 命令則很難實現這一點。（有關 DMV 的詳細信息，請參閱“動態管理視圖和函數 (Transact-SQL)。”此外，SQL Server 2005 還添加了大量可用于故障排除的其它功能，包括 DDL（數據定義語言）觸發器和事件通知。 

 圖 2 使用 DMV 獲取功能強大的結果

SELECT 
  OBJECT_NAME (ips.[object_id]) AS 'Object Name',  
  si.name AS 'Index Name',  
  ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',  
  ips.page_count AS 'Pages',  
  ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density' 
FROM sys.dm_db_index_physical_stats (  
  DB_ID ('SQLskillsDB'), NULL, NULL, NULL, 'DETAILED') ips  
CROSS APPLY sys.indexes si  
WHERE 
  si.object_id = ips.object_id  
  AND si.index_id = ips.index_id  
  AND ips.index_level = 0 -- only the leaf level  
  AND ips.avg_fragmentation_in_percent > 10; -- filter on fragmentation  
GO 

Microsoft 內部的各種團隊也提供了許多有用的性能故障排除工具，如 SQLdiag 實用工具、SQL Server RML 實用工具、SQL Server 2005 性能儀表板報告和DMVStats。此外還有用于 SQL Server 2005 的Windows 事件跟蹤 (ETW) 提供程序，它可將 SQL Trace 事件與 Windows 其它部分中的事件集成在一起。

盡管 SQL Server 2005 在增強 DBA 對數據庫引擎進行故障排除的能力方面取得了長足進步，但仍存在許多 DBA 幾乎無法有效進行故障排除的情況。一個經常被引用的示例就是某些查詢會使用過量的 CPU 資源，但 DMV 卻無法提供足夠的信息來確定究竟是哪些查詢導致了這些問題。但與 SQL Server 2005 不同，SQL Server 2008 可通過一個名為 SQL Server 擴展事件的新功能來突破此類限制。

擴展事件

擴展事件系統的功能遠遠超出了 SQL Server 在以前提供的任何事件跟蹤和故障排除機制。在我看來，擴展事件系統具有如下特色：

事件同步觸發，但可同步或異步進行處理。

任何目標都可以消耗任何事件，而任何動作都可以與任何事件配對，從而能夠更深入地監控系統。

“智能”謂詞允許您使用布爾邏輯來構建復雜的規則。

可以使用 Transact-SQL 對擴展事件會話進行全面控制。

可以監控性能關鍵代碼而不會對性能產生影響。

在深入講述之前，我先花一些時間來定義一些新術語。

事件 事件是指代碼中定義的點。此類示例包括：T-SQL 語句完成執行時的點或結束獲取鎖定時的點。每個事件都有一個定義的負載（該事件返回的列的集合），它是使用 ETW 模型（其中每個事件都返回一個通道和關鍵字作為負載的一部分）來定義的，以便能夠與 ETW 集成。SQL Server 2008 最初提供 254 個定義的事件，預計在今后還會增加。

使用下列代碼可以查看這些定義事件的列表： 、

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
  AND xo.[object_type] = 'event' 
ORDER BY xp.[name]; 

使用下列代碼可以查找某個特定事件的負載：  

SELECT * FROM sys.dm_xe_object_columns  
  WHERE [object_name] = 'sql_statement_completed';  
GO 

請注意，擴展事件系統包含一組說明性的 DMV，用來描述所有事件、目標等。有關詳細內容，請參閱“SQL Server 擴展事件動態管理視圖。”

謂詞 謂詞是指在事件消耗前利用一組邏輯規則來篩選事件的方法。謂詞可以很簡單，如檢查事件負載中的其中一個返回列是否為某個特定值（例如，通過對象 ID 來篩選“鎖定-獲得”事件）。

它們還具有一些高級功能，如統計會話期間某個特定事件發生的次數、僅允許事件在發生一次后消耗，或者動態更新謂詞本身以抑制包含類似數據的事件的消耗。

謂詞可以使用布爾邏輯來編寫，以使其能夠盡可能走捷徑。這使得只需執行最少數量的同步處理即可確定是否需要消耗事件。

動作 動作是指在消耗某個事件前同步執行的一組命令。任何動作都可以被鏈接到任何事件。它們通常會收集大量數據并追加到事件負載中（如 T-SQL 堆棧或查詢執行計劃）或

行某個被追加到事件負載中的計算。

由于執行這些動作可能需要極高的代價，因此事件的動作僅在所有謂詞都計算完畢后才執行，如果在隨后確定該事件不會被消耗，則將不會有同步執行某個動作的點。使用下列代碼可找到預定義動作的列表：

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
  AND xo.[object_type] = 'action' 
ORDER BY xp.[name]; 

目標 目標只提供一種消耗事件的方法，任何目標都可以消耗任何事件（或至少可以在目標空閑時對事件進行處理 — 如獲取非審計事件的審計目標）。目標可以同步（例如，觸發事件的代碼等待該事件被消耗）或異步消耗事件。

目標的范圍從簡單的使用者（如事件文件和環緩沖區）直到能夠執行事件配對操作的復雜使用者。使用下列代碼可以找到可用目標的列表：

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
  AND xo.[object_type] = 'target' 
ORDER BY xp.[name]; 

有關目標的詳細內容，請參閱“SQL Server 擴展事件目標。”

數據包 數據包是一個用于定義擴展事件對象（如事件、動作和目標）的容器。數據包位于它所描述的模塊（如可執行程序或 DLL）中，如圖 3 所示。

圖 3 模塊、數據包和擴展事件對象之間的關系

使用擴展事件引擎注冊數據包時，它所定義的全部對象都可供使用。有關數據包和擴展事件術語完整列表的詳細信息，請參閱“SQL Server 擴展事件數據包。”

會話 會話是一種將多個擴展事件對象鏈接到一起進行處理的方法 — 事件包含動作，將被目標所消耗。會話可鏈接任何注冊的數據包中的對象，任何數量的會話都可以使用同一個事件、動作等。使用下列代碼可查看已定義了哪些擴展事件會話：

SELECT * FROM sys.dm_xe_sessions;  
Go 

可使用 T-SQL 命令來創建、刪除、更改、停止和啟動會話。可以想見，這將提供很多靈活性，甚至提供通過對會話本身所捕獲的數據進行程序化分析來動態更改會話的功能。有關會話的詳細內容，請參閱“SQL Server 擴展事件會話。”

性能注意事項

使用 CREATE EVENT SESSION 將擴展事件會話放置在一起時，需要認真正確配置一些設置，因為它們可能會在無意中對性能產生影響。首先需要決定是以同步還是異步方式消耗事件。正如您所料，同步目標對所監控代碼的性能產生的影響要大過異步目標。

如前所述，同步消耗某個事件時，觸發該事件的代碼必須一直等待，直到該事件被消耗為止。顯然，如果事件消耗是一個復雜的過程，則這可能會降低代碼的性能。

例如，在一個每秒處理數千個小事務的繁忙系統中，同步消耗 sql_statement_completed 事件及捕獲查詢計劃動作很可能會對性能產生負面影響。另外還要記住，謂詞始終是同步執行的，因此應注意不要為性能關鍵代碼觸發的事件創建過于復雜的謂詞。

另一方面，您可能會被強制同步消耗事件。要計算某個特定事件的出現次數，最簡單的方法很可能是使用 synchronous_event_counter 目標。

您必須考慮的第二件事情是如果決定使用異步目標，那么該如何配置事件緩沖。事件緩沖的默認可用內存數為 4MB。事件被觸發，隨后被目標消耗，期間的默認調度延遲為 30 秒。這意味著如果您希望每 10 秒生成一些事件統計數據，則必須對延遲時間進行調整。

對用于緩沖事件的內存進行分區的方法是將其綁定到事件緩沖設置。默認情況下是為整個實例創建一組緩沖區。在 SMP（對稱多處理器）和 NUMA（非統一內存訪問）計算機中，這可能會使處理器不得不等待訪問內存，從而導致出現性能問題。

第三個注意事項是如何處理事件丟失。在定義擴展事件會話時，可指定事件是否可以“丟失”。這意味著如果沒有足夠的內存來緩沖某個事件，則可將其直接丟棄。默認設置是允許丟棄單個事件，但也可以允許整個事件緩沖區都丟失（適用于事件緩沖區很快就被填滿的會話），甚至還可以指定任何事件都不得丟失。

使用***一個選項時應格外注意，因為它會強制觸發事件的代碼一直等待，直到有足夠的緩沖區內存來存儲該事件為止。設置此選項幾乎肯定會對性能產生不利影響。請注意，如果無意間啟用了此選項，服務器仍會擁有足夠的響應能力讓您能夠禁用此設置。

通常，您需要通盤考慮這些選項。實際上我并沒有辦法為您提供通用的***方法，我能做的只是向您強調深入領會它們，否則您可能會遇到性能問題。有關這些設置的詳細信息，請參閱“CREATE EVENT SESSION (T-SQL)”。

事件的壽命

定義并啟動擴展事件會話后，處理過程將照常進行，直到所監控的代碼遇到某個事件為止。圖 4 介紹了擴展事件系統所遵循的步驟。具體步驟如下：

執行檢查以查看是否有任何擴展事件會話正在監控該事件。如果沒有，控制權將返給包含該事件的代碼，然后繼續進行處理。

確定事件的負載，將所需的全部信息都收集到內存中 — 換言之，構建事件的負載。

如果為該事件定義了任何謂詞，則執行它們。此時，謂詞結果可能是不應消耗該事件。如果是這種情況，控制權將返給包含該事件的代碼，然后繼續進行處理。

此時系統已得知事件將被消耗，因此將執行鏈接到該事件的所有動作。現在事件已具有完整的負載，已為消耗做好準備。

將事件提供給同步目標（如果有的話）。

如果存在任何異步目標，將會緩沖該事件以便隨后進行處理。

控制權將返給包含該事件的代碼，然后繼續進行處理。

圖 4“擴展事件”事件的壽命（單擊圖像可查看大圖）

如前所述，在創建事件會話時應格外注意，要避免同步動作或異步目標的緩沖對所監控代碼的性能產生影響。

使用擴展事件

SQL Server 2008 聯機叢書中包括兩個擴展事件的使用示例：“如何：確定哪些查詢持有鎖”和“如何：查找具有最多鎖定的對象。”

我將通過一個對擴展事件會話進行設置和對結果進行分析的示例對此加以講解。當我在 2007 年年末使用擴展事件時，我發現把簡單的會話組裝起來非常容易（直接使用 T-SQL DDL 語句即可），但對結果進行分析卻比較麻煩。

這些結果均以 XML 形式表示，最初這令我很吃驚，但隨后我意識到面對可能被收集到單個會話中的大量可能的事件和動作的組合，要想存儲此類可擴展架構，可能再也沒有比這更可行的選擇了。

現在，我已在 SQL Server 存儲引擎團隊擔任開發人員多年，雖然我自認為已經相當精通 C、C++ 和匯編編程程序，但從 XML 數據中通過編程方式找出提取事件負載字段所需的代碼仍花了我數小時的時間。我并不是要勸阻您使用擴展事件，相反，我只是告誡您如果還不熟悉 XML 數據的使用，那么***在查看結果前對學習曲線有所準備。

我的具體情況是這樣的：我是一名 DBA，使用 SQL Server 2008 的資源管理器功能在公司的其中一臺生產服務器上對各種組合進行沙箱測試。我創建了兩個資源管理器資源池（開發部和市場部），用來表示使用該服務器的團隊。利用資源管理器可以限制每個池的 CPU 和查詢執行內存使用情況，但不能限制它們所使用的 I/O 資源數量。因此，我想根據各個團隊在該服務器上的 I/O 使用情況向其開具賬單，以建立一種攤銷升級到新 SAN（存儲區域網絡）所花成本的退單機制。

我設想觸發捕獲 I/O 信息的***時機是在任何 T-SQL 語句完成時，并且我還知道數據包 package0 中有一個名為 sql_statement_completed 的事件。那么在事件負載中都收集到了哪些數據呢？

執行下列代碼將為我提供所有數據的列表（既包括讀取的，也包括寫入的）：

SELECT [name] FROM sys.dm_xe_object_columns  
  WHERE [object_name] = 'sql_statement_completed';  
GO 

我并不認為這些是實際的讀取和寫入（這時數據是從磁盤讀取或向磁盤寫入，而不是僅在緩沖池的內存中），但它們可使我了解各個團隊使用的 I/O 資源的比例。

現在，我需要找出究竟是哪個團隊執行了哪個特定的 T-SQL 語句，因此需要有一個動作來通知我。執行此代碼可以為我提供事件觸發時我所能采取的所有動作的列表，其中包括在 sqlserver 數據包中收集 session_resource_pool_id 的動作：

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
   AND xo.[object_type] = 'action' 
ORDER BY xp.[name]; 

我可以得到已為資源管理器定義的資源池的列表，并將其與擴展事件會話所收集到的 ID 相關聯。現在我已準備好定義會話。請注意，在執行此代碼時，將首先檢查是否存在相同名稱的事件會話。如果發現具有同名的事件會話，它會將其刪除。以下是相關代碼：

IF EXISTS (  
SELECT * FROM sys.server_event_sessions  
    WHERE name = 'MonitorIO')  
DROP EVENT SESSION MonitorIO ON SERVER;  
GO  
 
CREATE EVENT SESSION MonitorIO ON SERVER  
ADD EVENT sqlserver.sql_statement_completed  
  (ACTION (sqlserver.session_resource_pool_id))  
ADD TARGET package0.ring_buffer;  
GO 

 

然后它將創建一個包含單個事件 sql_statement_completed 的新會話，同時執行 session_resource_pool_id 動作，將所有內容都記錄到環緩沖區中（此時我仍在原型設計階段）。（在生產中，我很可能會選擇使用異步文件目標。）

要啟動會話，需要執行下列代碼：

ALTER EVENT SESSION MonitorIO ON SERVER  
STATE = START;  
GO 

 

現在它已啟動并開始運行。

在模擬了市場部和開發部的一些活動后，我已做好對會話結果進行分析的準備。下列代碼將從環緩沖區中提取數據：

SELECT CAST(xest.target_data AS XML) StatementData  
  FROM sys.dm_xe_session_targets xest  
JOIN sys.dm_xe_sessions xes ON 
  xes.address = xest.event_session_address  
WHERE xest.target_name = 'ring_buffer' 
  AND xes.name = 'MonitorIO';  
GO 

 

但是，它會將數據作為一個很大的 XML 值提取出來。如果需要將其進一步分解，可使用圖 5 所示的代碼。

[[17436]]  圖 5 分解 XML 數據

SELECT 
  Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,  
  Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,  
  Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID  
FROM 
(SELECT CAST(xest.target_data AS XML) StatementData  
  FROM sys.dm_xe_session_targets xest  
  JOIN sys.dm_xe_sessions xes ON 
    xes.address = xest.event_session_address  
  WHERE xest.target_name = 'ring_buffer' 
    AND xes.name = 'MonitorIO') Statements  
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results);  
GO 

這種做法效果不錯，但會為捕獲的每個事件都生成一行輸出。這并不是一種可怕的格式，另外我還希望得到匯總輸出，因此我決定使用派生表，如圖 6 所示。

[[17436]]  圖 6 獲取聚合輸出

 

 

SELECT DT.ResourcePoolID,  
  SUM (DT.Reads) as TotalReads,  
  SUM (DT.Writes) AS TotalWrites  
FROM 
(SELECT   
  Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,  
  Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,  
  Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID  
FROM 
(SELECT CAST(xest.target_data AS XML) StatementData  
  FROM sys.dm_xe_session_targets xest  
  JOIN sys.dm_xe_sessions xes ON 
    xes.address = xest.event_session_address  
  WHERE xest.target_name = 'ring_buffer' 
    AND xes.name = 'MonitorIO') Statements  
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results)) AS DT  
WHERE DT.ResourcePoolID > 255 –- only show user-defined resource pools  
GROUP BY DT.ResourcePoolID;  
GO 

 

大功告成！其中肯定包含一些復雜的代碼，但它們都運行正常。至此我已得到了我所需的結果。看一下有關我的測試數據的本次查詢輸出，如圖 7 所示。

圖 7 輸出的查詢結果

ResourcePoolID	TotalReads	TotalWrites
256	3831	244
257	5708155	1818

 

我知道資源池 256 用于市場部，而 257 用于開發部，因此這些數字對于我弄清這些團隊在公司中的數據庫使用情況很有意義。如果不使用擴展事件，我可能無法輕松得出這些結果。

***，我將使用下列代碼來停止此會話：

 

 

ALTER EVENT SESSION MonitorIO ON SERVER  
STATE = STOP;  
GO 

 

要了解此示例中各階段輸出的詳細內容，請查看本文隨附的屏幕演示。其地址為：technetmagazine.com/video。

 

system_health 擴展事件會話

實際上 SQL Server 2008 還提供了一個名為 system_health 的預定義會話，它被設置為默認運行。此會話源自產品支持團隊的想法，它可以跟蹤通常被用來對客戶系統進行調試的信息（例如當客戶系統發生死鎖或出現其它嚴重錯誤時）。此會話的創建和啟動是 SQL Server 2008 實例安裝過程的一部分，它在環緩沖區中跟蹤事件，因此不會消耗太多內存。

您可以使用下列代碼來查看環緩沖區中包含的內容：

 

 

SELECT CAST (xest.target_data AS XML)  
FROM sys.dm_xe_session_targets xest   
JOIN sys.dm_xe_sessions xes ON 
xes.address = xest.event_session_address   
WHERE xes.name = 'system_health';  
GO 

Microsoft PSS SQL 支持博客中包含很多有關此會話跟蹤內容的詳細信息。

結束語

有人告訴我 SQL Server 團隊計劃未來向 sqlserver.exe 中添加更多事件。事實上，其數量已從 2007 年二月 CTP（社區技術預覽版）中的 165 猛增到 RTM（批量生產版）中的 254。

此外還有許多的確非常有趣的事件，如用于更改數據捕獲的事件（我已在 2008 年 11 月《TechNet 雜志》中的“跟蹤企業數據庫中的更改”中對其做過介紹）、數據壓縮事件以及索引頁分割事件等。索引頁分割看起來有望找出那些導致性能降低碎片的索引，而無需在全部索引中定期運行 sys.dm_db_index_physical_stats DMV。

原文地址

本文來源：微軟TechNet中文站

責任編輯：彭凡 來源： 微軟TechNet中文網

SQL Server 2008