當前位置：首頁 > 人文社科 > 生活经验 >内容正文

生活经验

SQL Server Extended Events 进阶 3：使用Extended Events UI

發布時間：2023/11/27 生活经验 43 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 SERVER
ADD 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列。每次僅可以在一個列中進行搜索，你可以選擇其他的標準的搜索選項，包括匹配大小寫和全字匹配，同時也可以使用通配符或者正則表達式。

標記行

當找到含有我們感興趣的數據行時，我們可以在查看器中使用書簽標記他們(熟悉使用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這樣的第三方工具。

轉載于:https://www.cnblogs.com/yanyan45/p/Working_with_the_Extended_Events_UI.html

總結

以上是生活随笔為你收集整理的SQL Server Extended Events 进阶 3：使用Extended Events UI的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。