0

I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now

CREATE EVENT SESSION [PerQuerymonitor] ON SERVER 
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlos.wait_info_external(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.disk_log_read(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)) 
ADD TARGET package0.event_file
(SET filename=N'C:\SQLDatabase\PerQuerymonitor.xel',
max_file_size=(204800))
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Appreciate your kind help.

  • 1
    Possible duplicate of How to read extended events .xel file – Denis Rubashkin Mar 15 at 7:02
  • Exactly. The post that Denis linked to show how to shred the XML from a file XE trace. From there you can the date for the events. The rest is just about constructing a suitable WHERE clause for your datetime requirements. – Tibor Karaszi Mar 15 at 10:40
0

This isn't answering your question, but please be careful with extended events. This one has no filters on it at all, so it's collecting a lot of data. If this is run on a small test box then you won't know and won't care. If you ran this on a highly transactional production system then there's a good chance your users would be able to tell you that something bad is happening.

If you want to know more about extended events read Jonathan Kehayias's work.

https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/

If you have a PluralSight subscription he and his coworker Erin Stellato have some great videos teaching you a lot more on this subject.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.