SQL Server Extended Events (Extended events)--Using Extended Event Trace query statements

SQL Server Extended Event (Extended Events )-- using Extended Event Trace query Statements

creating an extended events session

Expand the "Object Explorer", "Management", "Extended Events", "Sessions" directories, and you will find one to two preset sessions. By default, SQL Server 2012 contains system_health sessions, and depending on the version of SQL Server2012, there may be alwayson_health sessions. You can check these sessions when you're free, and now we'll create a session to better understand how extended events work. Each session contains the same underlying components.

You can create a session by right-clicking the New Session Wizard Sessions, or by using T-SQL. Here we pass the "New session" of the right-click "Session", on the "General" page, enter the name of the session check Queries. On this page you do not have to configure any options if it is not necessary. The "Query Detail sampling" template in the "Templates" drop-down menu captures event data about T-SQL statements, stored procedures, and batches. You can modify preset settings, such as adding or deleting events, or configuring items that are set using templates. Once you've saved the session, you won't be able to use the template you're using, but you can modify the property settings as needed.

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 "/>

Most of the options on the "General" page are clear. The only less clear option is the "track how events is related to one another" option under "Casualty tracking", allowing you to trace events between related tasks when one task's work is done by another.

Here, we just enter the name of the session and go to the Events page to select the event you want to monitor.

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 "/>

If you select an event in the event library, the description is displayed below. and a list of related event fields. We add Query_post_execution_showplan and sql_statement_completed events.

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 "/>

Direct Click OK to save.

Right click on the new session, select "Start Session", right click on the session again, select "Watch Live Data". Run the following query:

Use Adventureworks2012goselect * from Production.transactionhistoryorder by TransactionDate DESC, ReferenceOrderID, Referenceorderlineid; SELECT * from Person.personwhere businessentityid <> 100OR BusinessEntityID <> 200ORDER by PersonType, Lastnam E, FirstName; SELECT * from Sales.currencyorder by Name;

The results are shown below:

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 "/>

The session records a sql_statement_completed event for each statement that was just running, one Query_post_execution_showplan event per statement, and a large number of SQL Server Management-related Query_ Post_execution_showplan event.

For Query_post_execution_showplan events, you can see things like duration, estimated_rows, and Showplan_xml. If you double-click the Showplan_xml value, the XML will open in a new window and be more readable.

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 "/>

When you double-click the value of statement, a detailed statement that triggers the event pops up.

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 "/>

Although we only added two events to our session, we still received a lot of information. We need to add filters to restrict the return of events of interest to us.

add Filter to session

Filtering conditions limit the number of sessions that are returned based on specific criteria. Close the Live Data window before adding filters to the session. Then in "Object Explorer", right click on the session to select "Properties", in the "Session Properties" dialog, go to the "Events" page click on the "Configure" button. A screen appears that lets you select a domain to configure the filter criteria for. Go to the Filter (predicate) window and select the "Query_post_execution_showplan" event in "Selected events".

We have added two filter conditions to the Query_post_execution_showplan event. The first filter condition, for the duration domain, is greater than (>) operator and has a value of 500000. The event returns a session with a execution time greater than 500000 microseconds.

Another filter condition, source_database_id must be equal to 5, in my test environment, the ID of the ADVENTUREWORKS2012 database is 5. I add this filter so that the session contains only the Query_post_execution_showplan events associated with this database.

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 "/>

Again for the Sql_statement_completed event, in the duration domain, we added the same filter condition.

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 "/>

When the filter is configured, click "OK" to close the session Properties dialog box. Right click on "Object Explorer" and select "Watch Live Data". Run the statement just again, and only one statement returns the event.

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 "/>

Because only one SELECT statement exceeds 500000 microseconds. Therefore, the session consists of only one sql_statement_completed event and one Query_post_execution_showplan event.

add domain to session

Turn off the "Live Data" window and open the session's properties again. Go to "Events" page and click "Configure". Go to the "Global Fields (Actions)" window. In global domains, such as database_id and database_name domains are available for multiple events. All along, adding one or more domains to the event is used to provide useful information. For example, it may be difficult to know which Query_post_execution_showplan event is related to which sql_statement_completed event in the results at times. If we add the transaction_id domain to each event, this can be better associated with these events.

Select the Query_post_execution_showplan event in Selected events to select the transaction_id domain in the global domain.

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 "/>

Go to the Event fields window and select the "database_name" field. Note that some domains that are listed in the global domain are also included in the event domain, like the database_name domain.

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 "/>

Regardless of where the domain is listed, remember that as long as you only need to add the necessary domains, any components you add will increase the load. Of course, we don't have to add the database_name domain to this event, because our filters only return events for one database. Then, the database name is included here just to explain how the optional event domain works.

Now repeat for the sql_statement_completed event, in the global domain, select the database_name and transaction_id domains.

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 "/>

Now enter the event Field window related to sql_statement_completed events. You can choose to include the Parameterized_plan_handle domain and the statement domain. By default, the first one is not selected and the second one is selected. Here, we keep the default settings. Click "OK" to save the session close the Session Properties dialog box.

Next, open the "Live Data" window and run the above query. You'll see more detailed information, click the Query_post_execution_showplan event, and you'll see the database name and transaction ID, which is 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 "/>

Clicking Sql_statement_completed also shows the transaction ID of 630312, as well as the database name.

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 "/>

As you can see, the added information helps us to better understand the source of the data and the relationships between those fragments.

Saving session Data

Close the "Live data" window again, open the session properties and go to the "Data Storage" page, where you can select the format you want to save.

SQL Server provides several formats for saving session events:

L Etw_classic_sync_target: Output events in Event tracing for Windows (ETW) format.

L Event_counter: The number of times that event firings are tracked in an output event to a memory-logged session.

L Event_file: A file that outputs events to a directory on disk.

L Histogram: Output event to memory based on domain and action packet count events.

L pair_matching: The output event to memory trace is based on the target configuration with no events corresponding to the event.

L Ring_buffer: Output event to memory tracking a fixed amount of event data based on the target configuration.

When choosing a data storage type, there are options at the bottom of the page to configure how the data is saved. Here, we select the Event_file type, configured as the maximum file size is 200MB and the maximum number of files is 10. Click "OK" to save.

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 "/>

Run the above query again. When you expand the session in SSMs, you see a reference package0.evnet_file for that event file, and you double-click the node. A new window will open showing the captured data, similar to the one shown in "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 "/>

When you are finished viewing the contents of the file, close the window. And right-click the session and select "Stop session". When you want to capture the data again, you turn it on again.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1600824

SQL Server Extended Events (Extended events)--Using Extended Event Trace query statements