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
Step 2 -> Click on Events -> Click on Configure
Step 3 -> When we click on configure, the below window will appear
Step 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’.
Repeat the same for the next event
Step 5 -> Now Click on the ‘Events Fields‘ tab and select the below as depicted. Click OK.
Now 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
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
Step 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.
Step 3 -> Start the session and fire some queries and observe the Live data in action.