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)','VARCHAR(100)') AS Database_ID, event_data_XML.value('(event/data)','INT') AS OBJECT_ID, event_data_XML.value('(event/data)','INT') AS object_type, event_data_XML.value('(event/data)','INT') AS cpu, event_data_XML.value('(event/data)','INT') AS duration, event_data_XML.value('(event/data)','INT') AS reads, event_data_XML.value('(event/data)','INT') AS writes, event_data_XML.value('(event/action)','VARCHAR(512)') AS sql_text, event_data_XML.value('(event/action)','VARCHAR(512)') AS tsql_stack, CAST(event_data_XML.value('(event/action)','VARCHAR(512)') AS XML).value('(frame/@handle)','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.
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)
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)
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
Thanks for the catch Jerry. Will correct it.
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.
Can we use extended events in SQL server 2005.?
Can i track procedure call (i mean procedure parameter values) using extended events.
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
I want to automated all this process in single run.
Because sometime the list is very big.
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.
Less processing overhead.
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
You have to write a scheduled task which would get information and send it via email. Xevents can’t do that.
XML parsing: line 7, character 80, illegal qualified name character
— Predicate – time 1000 milisecond
WHERE sqlserver.sql_statement_completed.duration > 1000
is duration in microseconds or milliseconds?