SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.
Let me Give you a example for better understanding, Problem Statement : We wanted to keep track of all the queries which are taking long time to execute or we need to figure out all the queries which are using ‘WAITFOR’ clause.
Here is the Code DEMO
|
select * from sys.dm_os_wait_stats where wait_type = 'WAITFOR'
go waitfor delay '00:00:06'
go
select * from sys.dm_os_wait_stats where wait_type = 'WAITFOR'
go
create event session WaitTest on server
add event sqlos.wait_info
add target package0.asynchronous_file_target
(set filename=N'c:\Wait.xel')
with (max_dispatch_latency=1 seconds)
go
alter event session WaitTest on server state = start
waitfor delay '00:00:04'
waitfor delay '00:00:05'
waitfor delay '00:00:06'
waitfor delay '00:00:07'
waitfor delay '00:00:08'
alter event session WaitTest on server state = stop
select * from fn_xe_file_target_read_file('c:\*.xel', 'c:\*.xem', NULL, NULL)
where event_data like '%5000%'
go |
