Applied SQL…

In SQL Server 2008, Microsoft introduced a feature called Extended Events. Extended Events helped in collecting and analyzing event driven data about the SQL Server instance. Extended Events introduced lesser load than trace or profiler events on the server. But in SQL Server 2008 there was no GUI that allowed direct communication with the events. Complex T-SQL had to be written to fetch the information which was returned in xml format.

In SQL Server 2012, MS has introduced a GUI in the Management Studio (SSMS)  that allows working with Extended Events and viewing event related data a much simpler process. In this article we will understand and see step by step how we can create and manage Extended events via the GUI that is built in the SSMS.

Step 1: Connect to SQL Server 2012 instance -> Open Management Tab -> Open Extended Events -> Right Click on Sessions -> New Session

Step 2 -> In the new session dialog box type in a name of the session. In this case we will use ‘Session1’. For now we will not choose any template.

Step 3 -> Click on Events tab -> On the below window you will find many Events listed from which we need to pick an event or multiple events that we want to capture. Once selected we need to click on the button to add it. For this article we will use the Events
a) query_post_execution_showplan and b) sql_statement_completed
Step 4 -> Click on Data Storage -> Here you specify how you can capture data so you can view it later. Here we will save it as Event File. Mention the path where we would save the file -> Click on OK.
Step 5 -> Go to management studio -> Under sessions you will find New session listed. It would be in a stopped state as this is the default behavior. Right Click on the session and click on ‘Start Session‘
Step 6 -> Now right click on the created session and select ‘Watch Live Data’. If there is no activity on the instance you will not see any data.
Step 7 -> Let us fire few queries on the AdventureWorks2012 database and watch the window. Following are queries that we would run on the database for testing. After running the queries go back and open the ‘Watch Live Data’ window again. Click on the Event data and observe the below.

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

In this article we have seen how we can start a new event trace from SSMS. In my next article we will explore more into the new GUI feature that MS has added to SQL Server 2012.