SQL SERVER – Introduction to Extended Events – Finding Long Running Queries

The job of an SQL Consultant is very interesting as always. The month before, I was busy doing query optimization and performance tuning projects for our clients, and this month, I am busy delivering my performance in Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning Course. I recently read white paper about Extended Event by SQL Server MVP Jonathan Kehayias. You can read the white paper here: Using SQL Server 2008 Extended Events. I also read another appealing chapter by Jonathan in the book, SQLAuthority Book Review – Professional SQL Server 2008 Internals and Troubleshooting. After reading these excellent notes by Jonathan, I decided to upgrade my course and include Extended Event as one of the modules.

This week, I have delivered Extended Events session two times and attendees really liked the said course. They really think Extended Events is one of the most powerful tools available. Extended Events can do many things. I suggest that you read the white paper I mentioned to learn more about this tool. Instead of writing a long theory, I am going to write a very quick script for Extended Events. This event session captures all the longest running queries ever since the event session was started. One of the many advantages of the Extended Events is that it can be configured very easily and it is a robust method to collect necessary information in terms of troubleshooting. There are many targets where you can store the information, which include XML file target, which I really like.

In the following Events, we are writing the details of the event at two locations: 1) Ringer Buffer; and 2) XML file. It is not necessary to write at both places, either of the two will do.

-- Extended Event for finding *long running query*IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')DROP EVENT SESSION LongRunningQuery ON SERVER
GO-- Create EventCREATE EVENT SESSION LongRunningQueryON SERVER-- Add event to capture eventADD EVENT sqlserver.sql_statement_completed(-- Add action - event propertyACTION (sqlserver.sql_text, sqlserver.tsql_stack)-- Predicate - time 1000 milisecondWHERE sqlserver.sql_statement_completed.duration > 1000)-- Add target for capturing the data - XML FileADD TARGET package0.asynchronous_file_target(SET filename='c:\LongRunningQuery.xet', metadatafile='c:\LongRunningQuery.xem'),-- Add target for capturing the data - Ring BuggerADD TARGET package0.ring_buffer(SET max_memory = 4096)WITH (max_dispatch_latency = 1 seconds)GO-- Enable EventALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO-- Run long query (longer than 1000 ms)SELECT *FROM AdventureWorks.Sales.SalesOrderDetailORDER BY UnitPriceDiscount DESCGO-- Stop the eventALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=STOP
GO-- Read the data from Ring BufferSELECT CAST(dt.target_data AS XML) AS xmlLockDataFROM sys.dm_xe_session_targets dtJOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_addressJOIN sys.server_event_sessions ss ON ds.Name = ss.NameWHERE dt.target_name = 'ring_buffer'AND ds.Name = 'LongRunningQuery'GO-- Read the data from XML FileSELECT event_data_XML.value('(event/data[1])[1]','VARCHAR(100)') AS Database_ID,event_data_XML.value('(event/data[2])[1]','INT') AS OBJECT_ID,event_data_XML.value('(event/data[3])[1]','INT') AS object_type,event_data_XML.value('(event/data[4])[1]','INT') AS cpu,event_data_XML.value('(event/data[5])[1]','INT') AS duration,event_data_XML.value('(event/data[6])[1]','INT') AS reads,event_data_XML.value('(event/data[7])[1]','INT') AS writes,event_data_XML.value('(event/action[1])[1]','VARCHAR(512)') AS sql_text,event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS tsql_stack,CAST(event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') ASXML).value('(frame/@handle)[1]','VARCHAR(50)') AS handleFROM(SELECT CAST(event_data AS XML) event_data_XML, *FROM sys.fn_xe_file_target_read_file('c:\LongRunningQuery*.xet','c:\LongRunningQuery*.xem',
NULL, NULL)) T
GO-- Clean up. Drop the eventDROP EVENT SESSION LongRunningQueryON SERVER
GO

Just run the above query, afterwards you will find following result set.

This result set contains the query that was running over 1000 ms. In our example, I used the XML file, and it does not reset when SQL services or computers restarts (if you are using DMV, it will reset when SQL services restarts).

This event session can be very helpful for troubleshooting. Let me know if you want me to write more about Extended Events. I am totally fascinated with this feature, so I’m planning to acquire more knowledge about it so I can determine its other usages.