Why Should I Use Extended Events in SQL Server 2008 ?

Leave a comment (0) Go to comments

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

EOF - Why Should I Use Extended Events in SQL Server 2008 ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.