Friday, April 19, 2013

SQL 2012 Extended Event Wizard Question

I'm having a bit of trouble figuring out how to translate my 2008 R2 Extended Events into the GUI for 2012. Most of it seems pretty straight forward, but I'm having an issue how to put the values from the WHERE clause into the GUI.

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:

Choose to not use a template:

Select the Events:

Select the Actions:

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

1 comment:

  1. 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