Watch Live Data using Extended Events in SQL Server 2012


Extended events are one of most powerful feature which was exposed from SQL server 2008(On wards).Till SQL 2012 most of your actions involving extended events used to be through TSQL codes.I strongly believe that TSQL is really powerful and still the best way to work with extended events.

Starting SQL 2012 administrators have the option to create/manage and work with extended events via GUI too.

This is pretty neat and lets do a demo to create an event using GUI start watching the live data –

1. We will proceed to create an extended event session using GUI.For the purpose of this demo we will choose the event ‘blocked_process_report’ and will name the session as ‘Event_Blocking’

If you right click the session,you will get a wizard which will guide you through the process very clearly.

2. Once you select the event.You have an option to configure the same to add additional info like database_id,session_id etc.

3.The TSQL code for the event session is

CREATE EVENT SESSION [Event_Blocking] 
ON SERVER ADD EVENT sqlserver.blocked_process_report( ACTION(sqlserver.database_name,
sqlserver.session_id, sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)) 
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO

4.The wizard will guide you to start the session and watch the live data as soon as you close the window.

5. Now as we have created a blocked process report event,lets simulate some blocking.

6.We will create a database,a table and will insert data to the data.

--Create a database
CREATE DATABASE Test_Extd_Blocking
GO

--Create a Table
Use[Test_Extd_Blocking]
CREATE TABLE Test_Table(ID Int Identity,Name Varchar(20))
GO
--Insert data to the table
BEGIN TRAN
Use[Test_Extd_Blocking]
INSERT INTO Test_Table VALUES('Ha')
GO
INSERT INTO Test_Table VALUES('Pe')
GO
INSERT INTO Test_Table VALUES('Ka')
--COMMIT TRAN

You might have noticed that I have used a BEGIN TRAN,but have not commited it.

7. We will now open up another session and will run the query

--Select data from the table
SELECT ID,Name FROM Test_Table

This is good enough to see a blocking report and lets watch the LIVE Data for the extended events session.

8. You will notice that the session have captured the blocking and you can see the LIVE Data

The report will be similar to what we get normally and will be in XML format

Conclusion

SQL 2012 comes with lot of flexibility,and extended events are really powerful.

Thanks for reading.

Advertisements

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