SQL Server 2008 Extended Events 2

SQL Server 2008 Extended Events 2:

Now lets create a New Event Session to capture LongRunning Queries which are running for more that 10 seconds

Now in this post we will write the details of the event Ringer Buffer and XML file. It is not necessary to write at both places but for this demo we will write to both of them.


-- Extended Event for finding the long running queries
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
-- Create Event Session
CREATE EVENT SESSION LongRQ
ON SERVER
-- Add events to capture events needed as necessary
ADD EVENT sqlserver.sql_statement_completed
(
-- Add appropriate action to be performed
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
-- Use Predicate to filter only queries taking time greater than 10000 milisecond(Millisecond is thousandth of a second.)
WHERE sqlserver.sql_statement_completed.duration > 10000
)
--Add target for capturing the data in Files
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata1.xel' ,
metadatafile = 'G:Datadata1.xem'),
-- CONFIGURES THE Ring Buffer Target
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
GO
-- Enable Event Session
ALTER EVENT SESSION LongRQ ON SERVER
STATE=START
GO

--Now run some Long Running Queries which runs for more that 10 Seconds

--Something like Select * should run for more than 10 seconds on a large table or use inner join/Cross join to multiply the query to itself

-- For Reading the data from Ring Buffer, the event session needs to be still running.
--if you stop the session then the information in Ring Buffer can be lost which is one of the situation I have faced.
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 = 'LongRQ'
GO

--We can also use the below Query to Read the data from Ring Buffer
Select name,target_data,CAST(target_data as XML),* From sys.dm_xe_sessions s
Inner Join sys.dm_xe_session_targets t On s.address =t.event_session_address

--Now we can stop the session
ALTER EVENT SESSION LongRQ ON SERVER
STATE=STOP
GO
*/
-- For Reading the data from XML File use the Query Below

select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('G:Datadata1*.xel','G:Datadata1*.xem', null, null)

-- Time to Clean up and Drop the event
DROP EVENT SESSION LongRQ
ON SERVER
GO

Leave a Reply

Your email address will not be published. Required fields are marked *