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
SQL 2012 comes with lot of flexibility,and extended events are really powerful.
Thanks for reading.