SQL Server擴充事件（Extended Events）-- 使用擴充事件跟蹤查詢語句

標籤：擴充事件   xevent   extented events   

SQL Server 擴充事件（Extended Events ）-- 使用擴充事件跟蹤查詢語句

 

建立擴充事件會話

展開“Object Explorer”、“Management”、“Extended Events”、“Sessions”目錄，你會發現一到兩個預設的會話。預設，在SQL Server 2012包含system_health會話，而根據不同的SQL Server2012的版本，可能有AlwaysOn_health會話。你可以在空閑時檢查這些會話，現在，我們來建立一個會話來更好的理解擴充事件是如何工作的。每個會話包含了相同的基礎組件。

 

可以通過右鍵“Sessions”的“New Session Wizard”嚮導來建立會話，也可以通過T-SQL來建立。這裡我們通過右鍵“Session”的“New Session”，在“General”頁，輸入會話的名字Check Queries。在這頁可以不必配置任何選項，如果沒有必要。在“Template”下拉式功能表的 “Query Detail Sampling”模板捕獲關於T-SQL語句、預存程序和批處理的事件數目據。你可以修改預設設定，例如添加或刪除事件，或者使用模板設定的配置項。一旦你儲存了會話，你就不能使用正在被使用的模板，但你可以按需修改屬性設定。

 

650) this.width=650;" title="clip_image001" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image001" src="http://s3.51cto.com/wyfs02/M01/58/51/wKioL1SuXs7hHUYUAAJPmXrL9E8284.jpg" border="0" height="679" />

 

“General”頁的大部分選項都是很明確的。唯一一個不太清晰的的選項是“Casualty tracking”下的“Track how events are related to one another”選項，讓你在相關任務之間跟蹤事件，當一個任務的工作被另一個做了的時候。

 

這裡，我們只輸入會話的名稱，進入到“Events”頁去選擇你想監控的事件。

 

650) this.width=650;" title="clip_image002" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M02/58/51/wKioL1SuXs-DRtsRAATlBz_OgJs882.jpg" border="0" height="725" />

 

如果你在“Event library”內選擇一個事件，下面會顯示它的描述資訊。以及相關的事件域的列表。我們添加query_post_execution_showplan和sql_statement_completed事件。

 

650) this.width=650;" title="clip_image003" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image003" src="http://s3.51cto.com/wyfs02/M00/58/51/wKioL1SuXtGC2pQUAAQCI2_Lytw587.jpg" border="0" height="680" />

 

直接點OK儲存。

 

右鍵點擊建立的會話，選擇“Start Session”，再次右鍵點擊該會話，選擇“Watch Live Data”。運行如下的查詢：

 

USE AdventureWorks2012GOSELECT * FROM Production.TransactionHistoryORDER BY TransactionDate DESC, ReferenceOrderID, ReferenceOrderLineID;SELECT * FROM Person.PersonWHERE BusinessEntityID <> 100OR BusinessEntityID <> 200ORDER BY PersonType, Lastname, FirstName;SELECT * FROM Sales.CurrencyORDER BY Name;

 

結果顯示如下：

 

650) this.width=650;" title="clip_image004" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image004" src="http://s3.51cto.com/wyfs02/M01/58/51/wKioL1SuXtKyvm7xAAJ-PX6dShQ749.jpg" border="0" height="490" />

 

該會話對於剛才啟動並執行每一條語句都記錄了一個sql_statement_completed事件，每個語句一個query_post_execution_showplan事件，和大量與SQL Server管理相關的query_post_execution_showplan事件。

 

對於query_post_execution_showplan事件，可以看到像duration、estimated_rows和showplan_xml。如果你雙擊showplan_xml值，該XML將會在新的視窗開啟，更具可讀性。

 

650) this.width=650;" title="clip_image005" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M02/58/51/wKioL1SuXtOxqy71AAI_g3k4syY622.jpg" border="0" height="501" />

 

當雙擊statement的值時，將彈出詳細的觸發該事件的語句。

 

650) this.width=650;" title="clip_image006" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image006" src="http://s3.51cto.com/wyfs02/M00/58/51/wKioL1SuXtSQrc7OAAJ7zoIvwVU512.jpg" border="0" height="544" />

 

儘管我們只添加了兩個事件到我們的會話，我們仍然收到了大量的資訊。我們需要添加過濾條件限制返回我們感興趣的事件。

 

添加過濾條件到會話

過濾條件限制了返回基於特定準則的會話的數量。在添加過濾條件到會話前，先關閉“Live Data”視窗。然後在“Object Explorer”，右鍵點擊該會話選擇“Properties”，在“Session Properties”對話方塊，進入“Events”頁點擊“Configure”按鈕。出現一個讓你選擇配置過濾條件的域的螢幕。進入到“Filter(Predicate)”視窗，在“Selected events”選擇“query_post_execution_showplan”事件。

 

我們對query_post_execution_showplan事件添加了兩個過濾條件。第一個過濾條件，針對duration域，大於（>）操作符，值為500000。該事件將返回執行時間大於500000微秒的會話。

 

另一個過濾條件，source_database_id必需等於5，在我的測試環境，AdventureWorks2012資料庫的ID為5。我添加該過濾條件，使得該會話只包含與這個資料庫相關的query_post_execution_showplan事件。

 

650) this.width=650;" title="clip_image007[4]" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image007[4]" src="http://s3.51cto.com/wyfs02/M01/58/51/wKioL1SuXtWiuRITAANc4gyNd6Q869.jpg" border="0" height="680" />

 

再針對sql_statement_completed事件，在duration域，我們添加了相同的過濾條件。

 

650) this.width=650;" title="clip_image008[4]" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image008[4]" src="http://s3.51cto.com/wyfs02/M02/58/51/wKioL1SuXteS1TrwAAL-KyLxXxs241.jpg" border="0" height="677" />

 

過濾條件配置完後，點擊“OK”關閉會話屬性對話方塊。右鍵點擊“Object Explorer”，選擇“Watch Live Data”。再次運行剛才的語句，只有一條語句返回了事件。

 

650) this.width=650;" title="clip_image009" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M00/58/51/wKioL1SuXtiiOwqPAAGEOxm6ido956.jpg" border="0" height="499" />

 

因為只有一條SELECT語句超過了500000微秒。所以，會話只包含一個sql_statement_completed事件和一個query_post_execution_showplan事件。

 

添加域到會話

先關掉“Live Data”視窗，再次開啟會話的屬性。進入“Events”頁點擊“Configure”。進入“Global Fields(Actions)”視窗。在全域域，像database_id和database_name域是對多個事件可用。一直以來，添加一個或多個域到事件中用來提供有用的資訊。例如，有時候可能去知道哪個query_post_execution_showplan事件與哪個sql_statement_completed事件在結果中是相關的，這比較困難。如果我們添加transaction_id域到每個事件，這就可以更好的關聯這些事件。

 

選擇“Selected events”中的query_post_execution_showplan事件，在全域域中選擇transaction_id域。

 

650) this.width=650;" title="clip_image010" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image010" src="http://s3.51cto.com/wyfs02/M01/58/51/wKioL1SuXtmzOxEaAAUxFAM_Otc324.jpg" border="0" height="680" />

 

進入“Event Fields”視窗，選擇“database_name”域。注意，有些在全域域中列出的域，也會包含在事件域中，像database_name域。

 

650) this.width=650;" title="clip_image011" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image011" src="http://s3.51cto.com/wyfs02/M01/58/54/wKiom1SuXhnidUZfAARJl2DcZC4504.jpg" border="0" height="681" />

 

不論域是在哪裡列出的，記住只要你只需要添加必要的域，因為任何你添加的組件都會增加負載。當然，我們不必添加database_name域到這個事件，因為我們的過濾條件只返回針對一個資料庫的事件。然後，在這裡包含資料庫名只是為瞭解釋可選的事件域是如何工作的。

 

現在再針對sql_statement_completed事件重複上述操作，在全域域，選擇database_name和transaction_id域。

 

650) this.width=650;" title="clip_image012" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image012" src="http://s3.51cto.com/wyfs02/M02/58/54/wKiom1SuXh2gs07lAATgp1SizIY739.jpg" border="0" height="678" />

 

現在進入sql_statement_completed事件相關的“Event Field”視窗。你可以選擇包含parameterized_plan_handle域和statement域。預設情況下，第一個沒有選擇，第二個被選擇。這裡，我們保持預設設定。點擊“OK”儲存會話關閉會話屬性對話方塊。

 

接下來，開啟“Live Data”視窗，運行上述查詢。將會看到更詳細的資訊，點擊query_post_execution_showplan事件，可以看到包含資料庫名和事務ID，在這裡是630312。

 

650) this.width=650;" title="clip_image013" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image013" src="http://s3.51cto.com/wyfs02/M00/58/54/wKiom1SuXh2RDXEbAAHS2UE1Ing654.jpg" border="0" height="395" />

 

點擊sql_statement_completed，也顯示了事務ID為630312，以及資料庫名。

 

650) this.width=650;" title="clip_image014" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image014" src="http://s3.51cto.com/wyfs02/M01/58/54/wKiom1SuXh7DcguLAAHRggOabXM263.jpg" border="0" height="425" />

 

可以看到，添加的資訊可以協助我們更好的理解資料的來源，以及這些片段之間的關係。

 

儲存會話資料

再次關閉“Live Data”視窗，開啟會話屬性，進入“Data Storage”頁，裡面可以選擇要儲存的格式。

 

SQL Server提供了多種格式來儲存會話事件：

l Etw_classic_sync_target：輸出事件以Event Tracing for Windows(ETW)格式。

l Event_counter：輸出事件到記憶體記錄會話中跟蹤事件觸發的次數。

l Event_file：輸出事件到磁碟上的一個目錄的檔案。

l Histogram：輸出事件到記憶體基於域和行動分組計數事件。

l Pair_matching：輸出事件到記憶體跟蹤基於目標配置沒有對應事件的事件。

l Ring_buffer：輸出事件到記憶體跟蹤基於目標配置的一定量的事件數目據。

 

當選擇一種資料存放區類型，將會在頁面底部有一些選項可供配置如何儲存資料。這裡，我們選擇event_file類型，配置為最大檔案為200MB，最大檔案數量為10個。點擊“OK”儲存。

 

650) this.width=650;" title="clip_image015[4]" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image015[4]" src="http://s3.51cto.com/wyfs02/M02/58/54/wKiom1SuXiLDznS5AALoBqF3CJU860.jpg" border="0" height="679" />

 

再次運行上述查詢。在SSMS中展開該會話，會看到一個該事件檔案的的參照package0.evnet_file，雙擊該節點。將會開啟一個新視窗顯示捕獲的資料，類似在“Live Data”中顯示的。

 

650) this.width=650;" title="clip_image016" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image016" src="http://s3.51cto.com/wyfs02/M00/58/54/wKiom1SuXiTBWirOAAPdIkYITPM417.jpg" border="0" height="503" />

 

當查看完該檔案的內容後，關閉該視窗。並右鍵選擇該會話，選擇“Stop Session”。當你想再次捕獲資料的時候，你再開啟。

本文出自 “SQL Server Deep Dives” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1600824

SQL Server擴充事件（Extended Events）-- 使用擴充事件跟蹤查詢語句