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

The job of an SQL Consultant has been 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 a 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 capture 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 the 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 Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.sql_statement_completed
(
-- Add action - event property
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
-- Predicate - time 1000 milisecond
WHERE sqlserver.sql_statement_completed.duration > 1000
)
-- Add target for capturing the data - XML File
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\LongRunningQuery.xet', metadatafile='c:\LongRunningQuery.xem'),
-- Add target for capturing the data - Ring Buffer
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
GO
-- Enable Event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO
-- Run long query (longer than 1000 ms)
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO
-- Stop the event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=STOP
GO
-- Read the data from Ring Buffer
SELECT CAST(dt.target_data AS XML) AS xmlLockData
FROM sys.dm_xe_session_targets dt
JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'LongRunningQuery'
GO
-- Read the data from XML File
SELECT 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)') AS XML).value('(frame/@handle)[1]','VARCHAR(50)') AS handle
FROM
(
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 event
DROP EVENT SESSION LongRunningQuery
ON SERVER
GO

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

SQL SERVER - Introduction to Extended Events - Finding Long Running Queries longrunningquery

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 computer restarts (if you are using DMV, it will reset when SQL services restart).

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.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Generate Report for Index Physical Statistics – SSMS
Next Post
SQLAuthority News – Three Posts on Reporting – T-SQL Tuesday #005

Related Posts

35 Comments. Leave new

  • Hi pinal, iam not able to see Extend events in my object explorer.coulu you please guide me.
    iam using : Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)
    Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM)

    Reply
  • Jerry W. Francis II
    May 21, 2015 9:53 pm

    Thanks for all your great articles. There is a typo in the following line where you apparently shifted right by one key. (That or you were as frustrated by XE as I am!! :) )

    — Add target for capturing the data – Ring Bugger

    Reply
  • Really Extended events are excellent. But i didn’t find any alerting system, like whenever if any event (Like deadlock) occur it should send a mail. But i didn’t see this mechanism.

    Reply
  • Hi Pinal

    Can we use extended events in SQL server 2005.?

    Reply
  • Can i track procedure call (i mean procedure parameter values) using extended events.

    Reply
  • Hi Pinal,

    I’m not from database domain but I want help regarding long run query.
    I’ve problem of deadlock in database for that I got two syntax.

    select * from sys.sysprocesses where dbid =db_id() and spid @@SPID and blocked 0 and lastwaittype LIKE ‘LCK%’

    From the above query result I get spid & blocked one by one and run following command.

    dbcc inputbuffer (151) ‘where is 151 is value from spid or blocked field

    from the above output, only those records whose EventInfo field contain subledger I will kill that session with command
    Kill 116

    I want to automated all this process in single run.
    Because sometime the list is very big.

    Thanks

    Reply
  • Hello Pinal , I did everything to create the extended event in 2008R2 instance but failed to create the ADD TARGET package0.asynchronous_file_target(
    SET filename=’c:\LongRunningQuery.xet’, metadatafile=’c:\LongRunningQuery.xem’),

    any suggestions ? error wrong syntax at ADD TARGET. I copy pasted and couple of other ways to do it but none worked.

    Reply
  • Less processing overhead.

    Reply
  • Thanks for the great article in XE. Please let me know if i can set some alerts to report the long running sessions as and when they occur, like a mail to my inbox with session details like id, login name, dbname, sql text and duration

    Reply
    • You have to write a scheduled task which would get information and send it via email. Xevents can’t do that.

      Reply
  • XML parsing: line 7, character 80, illegal qualified name character

    Reply

Leave a Reply

Menu