SQL Server Extended Events 進階 3：使用Extended Events UI

標籤：

開始採用Extended Events 最大的阻礙之一是需要使用Xquery和XML知識用來分析資料。建立和運行會話可以用T-SQL完成，但是無論使用什麼目標，資料都會被轉換為XML。這個限制在SQL Server 2012中已經被移除，它已經實現了Extended Events 使用者介面。在SQL Server 2014中也基本沒有變化，如我們在進階2中所見，這個UI提供了建立和管理會話功能。在本階中，我們將探索捕獲資料的查看和處理的功能。

查看資料

當建立並啟動會話後，我們有兩種選擇查看事件數目據：即時資料查看器和目標資料檢視器。
為了完成例子中的內容，你可以建立Listing 1中所示的XE_SampleCapture會話。它將捕捉batch，Statement和Procdeure 完成事件以及相關事件欄位：
query_hash - 標識具有相似邏輯但是查詢計劃不同的查詢
query_plan_hash 標識具有不同的查詢計劃但有相似邏輯樹狀結構的查詢計劃

這兩欄位分別關於查詢指紋和計劃指紋。我們使用這些欄位主要用於分析Ad-Hoc負載，尋找非常相似但卻有細微區別的查詢，如具有不同的值(difference values of their concatenated literals)。

CREATE EVENT SESSION [XE_SampleCapture] ON SERVERADD EVENT sqlserver.rpc_completed (    ACTION ( sqlserver.database_id, sqlserver.query_hash,    sqlserver.query_plan_hash )    WHERE ( [sqlserver].[is_system] = ( 0 ) ) ),ADD EVENT sqlserver.sp_statement_completed ( SET collect_statement = ( 1 )    ACTION ( sqlserver.database_id, sqlserver.query_hash,    sqlserver.query_plan_hash )    WHERE ( [sqlserver].[is_system] = ( 0 ) ) ),ADD EVENT sqlserver.sql_batch_completed (    ACTION ( sqlserver.database_id, sqlserver.query_hash,    sqlserver.query_plan_hash )    WHERE ( [sqlserver].[is_system] = ( 0 ) ) ),ADD EVENT sqlserver.sql_statement_completed (    ACTION ( sqlserver.database_id, sqlserver.query_hash,    sqlserver.query_plan_hash )    WHERE ( [sqlserver].[is_system] = ( 0 ) ) )ADD TARGET package0.event_counter,ADD TARGET package0.event_file ( SET filename = N‘C:\temp\XE_SampleCapture‘ ,                                  max_file_size = ( 512 ) ),ADD TARGET package0.histogram ( SET filtering_event_name = N‘sqlserver.sql_statement_completed‘ ,                                 slots = ( 16 ) ,                                 source = N‘sqlserver.database_id‘ ),ADD TARGET package0.ring_buffer ( SET max_events_limit = ( 10000 ) ,                                   max_memory = ( 4096 ) )WITH ( MAX_MEMORY = 16384 KB ,        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,        MAX_DISPATCH_LATENCY = 30 SECONDS ,        MAX_EVENT_SIZE = 0 KB ,        MEMORY_PARTITION_MODE = NONE ,        TRACK_CAUSALITY = ON ,        STARTUP_STATE = OFF );GO

為了產生一些樣本資料，我執行了Jonathan Kehayias 為 AdventureWorks建立的隨機負載產生指令碼(https://www.sqlskills.com/blogs/jonathan/the-adventureworks2008r2-books-online-random-workload-generator/)，它可以產生多個串連，同時建立了一些預存程序，並重複執行他們。你可以在文章底部下載它。你的輸出結果與我的可能並不完全相同，但是結果應該相似。

即時資料查看器

Live Data Viewer總是可用的，無論會話設定了什麼類型的目標，甚至是沒有設定目標，它會簡單的顯示出捕獲的資料。開啟查看器僅需右鍵選擇任何運行中的會話並選擇"Watch Live Data"。你可以使用上面建立的XE_SampleCapture會話，如果喜歡也可以使用system_health會話。圖1顯示了一些資料範例。

Figure 1

初始化後可能不會顯示任何資料，尤其是你嘗試開啟system_health 會話時，因為事件顯示在查看器中的速度主要取決於你收集的事件和謂詞。如果你有一個可以快速捕捉資料的會話，那麼一旦事件會話的調度周期到達，或者緩衝區被填充，事件會被調度到查看器和會話中定義的其他目標。

調度周期和記憶體緩衝
Extended Events引擎首先將事件數目據流寫入指定會話的中間緩衝區。調度周期是一個會話層級的選項，用於定義將事件數目據從記憶體寫入目標的頻率，以秒為單位。

除非你匯出資料，否則即時查看器中的資料並不會被持久化。你必須停止即時查看器的資料推送後才能使用這個選項(通過 Extended Events主菜單中的Stop Data Feed)。選擇即時資料查看器視窗，轉到Extended Events | Export to來選擇匯出目標。

你可以隨時停止資料推送或者關閉查看器，同時在前一階中我們提到過，SQL Server如果檢測對效能有負面影響時，查看器會自動停止。

目標資料檢視器

另外一個查看會話資料的方式是右鍵點擊目標，然後然後選擇View Target Data…選項。這個菜單根據選擇的目標顯示不同的視圖

查看 event_file目標資料

如果是選擇了一個event_file 目標，則顯示已經被寫入硬碟的目標檔案中的事件數目據。這些資訊不會更新，僅顯示開啟檔案那一刻已經存在的資料。

Figure 2

對於一個event_file目標，這個選項與實施資料檢視器對比的好處是它不需要SQL Server同時將資料流寫入目標和Management Studio。它僅僅讀取已經被目標消費後的資料。

event_file 目標捕捉的資料可以在事件會話停止後在SQL Server Management Studio 中查看，因為他們會以.xel檔案儲存在硬碟上。你可以拖拽一個.xel檔案到SSMS中，或者使用 File | Open | File…菜單選項開啟一個.xel檔案。system_health會話的預設檔案路徑在預設的錯誤記錄檔檔案夾中。

這個查看器看起來極為相似，除了windows視窗中的檔案名稱不一樣 (XE_SampleCapture_0_131061823727010000.xel)， 即時查看器顯示Live data和目標查看器顯示event_file。

Figure 3

查看ring_buffer目標資料

當使用ring_buffer目標是，View Target Data選項不是非常有協助，因為它以整個的XML連結格式顯示。

Figure 4

點選連結後，資料以xml格式顯示，但是對於分析也不是很有協助。

Figure 5

當使用ring_buffer 目標是，我們需要使用XQuery分析事件數目據。

查看event_counter, histogram 和pair_matching 目標資料

最後三種目標event_counter, histogram, 和pair_matching，需要你查看目標資料或者使用XQuery分析資料，這是僅有的兩種查看彙總資料的方式。使用我們的XE_SampleCapture會話，或其他具有histogram 目標的會話，右鍵選擇histogram 目標並開啟目標資料檢視，6所示的輸出。

Figure 6

histogram 目標資料檢視開始可能是空的，也可能會有資料。這個查看器需要被重新整理才會顯示最新資料。為了手動跟新它，可以在histogram 目標視窗中右鍵點擊Refresh，或者你可以選擇 Refresh Interval 讓視窗定時更新。

Figure 7

histogram, event_counter 和pair_matching等三種目標在記憶體中儲存資料，一旦你停止事件會話，內中種的資料也會飛釋放，因而視圖中的資料也會消失。如果你需要儲存資料，你可以通過右鍵的Copy選項，或者右鍵的Export to CSV選項匯出.csv檔案(請參考圖7)。

你可以在停止一個會話後選擇複製或者儲存當前顯示在目標資料檢視器中的資料，但是對於這些目標，一旦你在停止會話後重新整理了查看器，資料將會丟失。

Figure 8

顯示選項:定製列

無論哪種查看器，你都可以定製顯示的列。預設情況下，只有事件名和時間戳記顯示在頂部的面板中，因為這兩列是所有所有事件所共有的。當你選中頂部面板中的列時，詳細面板會顯示所有捕獲的欄位。想要在頂部面板中產看任何欄位，你可以在詳細面板中右鍵選擇所需的列，並選擇Show Column in Table。在圖9中，我們添加了duration 欄位到頂部面板中。

Figure 9

我們可以重複以上步驟將我們需要的欄位都添加到頂部面板中。這些列可以通過拖拽列名進行左右移動位置。或者，你可以通過 Extended Events工具列上的Choose Columns… 按鈕，或者Extended Events | Choose Columns… 菜單選項來選擇列或者配置列的順序。如果你沒有看Extended Events工具列，可以使用View | Toolbars菜單來選擇。

Figure 10

在查看器中可以使用的技巧之一就是合并列，換句話說你可以根據已有的兩列或更多列來建立一個新列。這對捕捉查詢語句儲存在不同欄位的事件非常有協助。例如sp_statement_completed 事件使用statement 欄位儲存查詢語句，batch_completed 事件使用batch_text 欄位。如果你同時將這兩個欄位都加到頂部面板中，你會發現對於batch_completed 事件statement 欄位總是為NULL，同樣，對於sp_statement_completed事件batch_text 欄位也為NULL。

Figure 11

資料在不同的欄位中會阻礙我們的分析，但是如果將他們合并為一列緩解了這一問題。在Choose Columns… 視窗的右下角你可以找到一個Merged columns。選擇New，為新列提供一個名字，並選擇你要合并的列，12所示。

Figure 12

新的合并列將以[QueryText]為名顯示在查看器中。注意合并列選項專為字串串連設計，因此如果你選擇了非字串資料型別，並不是所有的資料都可以正常顯示。例如，如果你嘗試串連logical_reads 和database_id，它金輝顯示logical_reads。

在你根據所需的欄位定製查看器後，在需要的情況下，你可以通過 Extended Events菜單來儲存配置，或者通過Display Settings按鈕。

 

Figure 13

設定檔以.viewsetting 檔案格式儲存，然後在以後需要查看或分析event_file 目標時，通過同樣的菜單開啟這個檔案。這個檔案可以被共用或者儲存在多個使用者可以訪問的網路地址中。最後，一旦頂部面板中顯示了所有你需要的列後，如果你要在查看器中移除詳細面板，可以選擇Extended Events | Show Details Pane菜單。

分析資料

查看器提供了一個我們所熟悉的事件檢視。但是與Profiler UI不同，在這裡我們有能力對列進行排序並過濾資料，extended events查看器提供了更好的資料分析選項，我們不再需要將資料在匯出到表中，或者使用第三方工具。

排序

你可以做的最簡單的事情之一就是對事件排序。僅需右鍵選擇列名然後選擇Sort Ascending 或Sort Descending排序(你也可以直接點擊列名排序，再次點擊的時候以反序排序)。當你想要快速的找到執行時間最長，IO最高的事件時，對UI上的資料排序是最簡單的方式。

過濾

使用工具列上的Filters… 按鈕(或者Extended Events | Filter…菜單)對查看器中的資料進行過濾。我們可以使用時範圍或者其他任何欄位進行過濾，包括為事件收集的全域欄位。進一步，我們可以通過使用AND，OR和其他運算(=, <>, Like, Not Like, Null等)建立複雜的條件，就像我們在謂詞中一樣。

Figure 14

應用過濾後，結果集可以被儲存在新的.xel檔案中。如果原始的.xel檔案被關閉時沒有移除過濾條件，請注意重新開啟檔案時，這些過濾依然生效。一個好的習慣是檢查當前顯示的事件數目量，來判斷是否有過濾條件被應用在了分析上，15所示。

 

Figure 15
可以通過filters 視窗或者選擇Extended Events | Clear All Filters菜單來移除過濾條件。

合并.xel 檔案

在我們經常需要過濾結果來分析資料的同時，在一些情況下，我們也需要從多個檔案中抓取資料進行全面的分析。一個例子，當我們在跨越多個節點的Availability Group中排除故障時，你可能為每一個節點設定一個會話，然後將多個節點中的目標檔案合并在一起查看。為完成這個目標，通過SSMS選擇File | Open | Merge Extended Events Files菜單，找到檔案路徑並將他們添加到視窗中，然後選擇OK。

Figure 16

合并後的檔案將按照一個結果集輸出進行分析，同時你可以儲存為單獨的.xel檔案。

搜尋

Profiler UI中也提供了搜尋功能，這對於搜尋查詢語句中的字元尤為有用。Extended events 查看器提供了同樣的功能，位於工具列中的望遠鏡表徵圖(或選擇Extended Events | Find菜單)。

Figure 17

在Find視窗中你可以搜尋任何已經添加到頂部面板的欄位。如果你沒有在查看器的頂部面板添加任何額外的欄位，預設情況下僅可以搜尋name 和timestamp列。每次僅可以在一個列中進行搜尋，你可以選擇其他的標準的搜尋選項，包括匹配大小寫和全字匹配，同時也可以使用萬用字元或者Regex。

標記行

當找到含有我們感興趣的資料行時，我們可以在查看器中使用書籤標記他們(熟悉使用Profiler的使用者過去可能經常會寫下行號，這都已經成為過去時了)。選中我們感興趣的行，然後點擊工具列中的Bookmark 按鈕(或者點擊右鍵菜單中的Toggle Bookmark)即可。書籤表徵圖會顯示在查看器的最左邊。

 

Figure 18

當你標記完所有感興趣的行後，你可以使用工具列上的向前向後導覽按鈕快速的訪問他們(再不需要無止境的滾動滑鼠了)。這些按鈕使你可以輕鬆的瀏覽整個檔案，僅停止在那些感興趣的事件上。如果過你想要移除某個書籤，僅需右鍵點擊行後選擇Toggle Bookmark。如果你想要刪除所有書籤可以使用Clear All Bookmarks按鈕。請注意，當你關閉檔案並再次開啟時所有的書籤都會丟失。

分組

當我們在事件數目據中合并列、過濾資料和尋找指定值時，可能很有趣並且對我們分析資料有協助，但是它們不能幫我們從整體上找到資料的發展趨勢或者異常現象。進入分組，這個分析選項是extended events 查看器遠遠超越Profiler的原因之一。對於查看器頂部面板中的任何欄位選，你都可以右鍵點擊它並選擇Group by this column。

Figure 19

你也可以使用Grouping… 按鈕或者 Extended Events | Grouping… 選項，他們的好處是你可以輕鬆的使用多個列作為分組條件。在查看器中，在我已經使用其中一列進行分組後，如果我右鍵選擇另外一列進行分組，原來的分組條件將被移除。在很多情況下你可能會需要根據多個列進行分組，這時，使用分組視窗將允許你選擇多列，並設定列的順序。

Figure 20

通用的分組欄位包括：

• event (事件名稱)
• object_name or object_id
• database_name or database_id
• login fields (e.g. username, nt_username)
• client_app_name
• client_hostname

在duration, logical_reads 或CPU等欄位上分組沒有任何意義。因為他們提供的是事件的度量指標，但你並不是在尋找那些20ms中完成的事件，或者需要特定IO數量的事件。 而是在某一伺服器，或者特別的程式，或資料庫的事件中尋找某些模式或者異常情況。

嘗試使用Statement(或Batch_Text)進行分組的情況會經常遇到，因為我們想找到那些查詢會被頻繁的執行。當我們使用SQL Trace 捕獲資料時，第三方工具可以為我們提供這些分析，如ClearTrace 和ReadTrace。這些工具會格式化文本資料並進行彙總，一次你可以查看指定的查詢或預存程序的執行頻率。

不幸的是，Extended Events不具有格式化資料功能。如果你嘗試在statement 或batch_text (或我們定義的 QueryText) 欄位上進行分組，你最終得到的結果可能是所有資料都只有一行。

Figure 21

你需要在query_hash 或query_plan_hash欄位上進行分組，而不是在查詢文本上。這兩個欄位在 SQL Server 2008 中被添加到sys.dm_exec_query_stats 管理檢視，通常也被稱為"query fingerprints"。

具有相同文本的查詢具有相同的query_hash，因此可以使用它作為分組條件查看一個查詢的執行頻率。

Figure 22

一旦你可以將相同的查詢進行分組，你接下來就可以對每個分組計算平均值，最大值，最小值等額外的分析。

彙總

最後一個分析工具是使用UI上的彙總選項。在應用彙總前你必須使用至少一個欄位進行分組。對於非數字欄位，數學計算無法被應用(如，batch_text, database_id, xml_report)，唯一可用的彙總選項是COUNT。對於數字欄位，額外的選項包括SUM, MIN, MAX, 和AVG。我們可以在欄位上點擊右鍵並選擇 Calculate Aggregation…，應用彙總選項。你可以可以使用工具列中的 Aggregation…按鈕，或者Extended Events | Aggregation…菜單。

Figure 23

圖24展示了彙總後的結果。

Figure 24

你可以為不同的欄位應用不同的彙總方法，如AVG 應用到duration， MAX應用到logical_reads，但是對於一個欄位每次只能應用一個彙總選項。也就是對於一個欄位你僅可以查看AVG 或者只查看MAX。這裡有一個有趣的變通方式可以為相同的欄位應用不同的彙總選項。你可以建立一個合并列來表示那一列。例如，如果你即想看duration的平均值也想看duration的最大值，你可以首先將AVG 彙總函式應用到duration列上，然後我們在duration 上建立一個合并列(稍不同的名字)，另外一列選擇一個總是為零的欄位如connection_reset_option。一旦你建立了合并列，你就可以在它上面應用MAX 了。

 

Figure 25

關閉和重新開啟檔案時，合并列是被持久化的。但是分組和彙總卻不可以，他們並不會被儲存在.viewsetting檔案中。

總結

SQL Server 2012中，使用者最大的勝利是它引入了目標資料檢視器，這意味著我們不在需要T-SQL和XQuery就可以分析事件數目據了。目標資料檢視器是的我們可以產看除ring_buffer 以外所有的目標事件數目據(還有etw_classic_sync_target,它僅可以通過 ETW 工具進行查看)。

另外， Extended events UI中比Profiler UI提供了更多重要的資料分析選項。除了搜尋和過濾，我們現在可以對資料進行排序，分組，彙總等複雜的分析， 而不再需要將資料匯入表中並使用SQL進行分析，或者使用ReadTrace這樣的第三方工具。

SQL Server Extended Events 進階 3：使用Extended Events UI