Here is my query (which works in 2012, if I simply execute it)
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorExpensiveQuery') DROP EVENT SESSION MonitorExpensiveQuery ON SERVER GO --Creating a Extended Event session with CREATE EVENT SESSION command CREATE EVENT SESSION MonitorExpensiveQuery ON SERVER ADD EVENT sqlserver.sql_statement_completed ( ACTION ( sqlserver.client_app_name ,sqlserver.client_hostname ,sqlserver.username ,sqlserver.nt_username ,sqlserver.session_id ,sqlserver.sql_text ) WHERE ( duration > 3000000 OR cpu_time > 1000 OR reads > 10000 OR writes > 10000 ) ) ,ADD EVENT sqlserver.sp_statement_completed ( ACTION ( sqlserver.client_app_name ,sqlserver.client_hostname ,sqlserver.username ,sqlserver.nt_username ,sqlserver.session_id ,sqlserver.sql_text ) WHERE ( duration > 3000000 OR cpu_time > 1000 OR reads > 10000 OR writes > 10000 ) ) ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS, STARTUP_STATE = ON) -- The target GO ALTER EVENT SESSION MonitorExpensiveQuery ON SERVER STATE = START GO
So I kick off the GUI and the first few pages seem pretty straight forward...Add my Session name:
But when I get to the Filter page, which I am guessing is the equivalent of the "WHERE" caluse, I don't see any of my fields (duration, cpu_time, reads or writes):
So I'm unsure how to properly set this up. Any help appreciated
I've been through this several times, I still don't see a way to do it. I've reached out to #SQLHelp on Twitter and not gotten a response. I have however noticed that if I use the manual setup, instead of the wizard, then I am able to get the fields (duration, cpu_time, physical_reads, writes). Important to note that CPU and Reads have changed their names in 2012. I guess if I really wanted to use the Wizard, I'd have to create it with no filters, then edit it and add the filters in.
ReplyDelete