Extended Events in SQL Server 2012 – Part 2


In my earlier post on Extended events we discussed how we can use the SSMS GUI in SQL Server 2012 to create new extended events. In this article we will take a deeper dive into the SQL Server 2012 SSMS GUI and explore other extended events features.

How to add fields to a session

Step 1 -> Right click on the session we created. Click on properties
ee14Step 2 -> Click on Events -> Click on Configure
ee15Step 3 -> When we click on configure, the below window will appear
ee16Step 4 -> Now click on the first event ‘query_post_execution_showplan‘ and we will find a list of Actions on the right hand pane being displayed. These are the bunch of global fields from which we can choose and add to the events and the relevant data will be captured by the event trace. For this event we will choose ‘cpu_id’ and ‘database_name’.
ee17ee17Repeat the same for the next event
ee19Step 5 -> Now Click on the ‘Events Fields‘ tab and select the below as depicted. Click OK.
ee20ee21Now lets start the session and examine the Live data. For generating activity on the SQL Server we will run the below queries

USE AdventureWorks2012
go
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
go
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100
go

Now lets watch the Live data
ee22

How to add filters to a session

In this section we will add filters to the session we created. Filters allow you to fetch events based on the criteria provided.
Step 1 -> Right click on the session -> Click on Properties -> Click on Events -> Choose an event -> Click on the ‘Filter(Predicate)’ tab
ee23Step 2 -> From the Field tab choose a filter from the drop down. In our case we will choose duration and set a value in the Value field. Click on OK.
ee24
Step 3 -> Start the session and fire some queries and observe the Live data in action.

Advertisements

Tagged: , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: