6

I am trying to examine some T-SQL with extended events like I used to with SQL profiler. I have the following event session:

IF EXISTS (SELECT name FROM sys.dm_xe_sessions WHERE Name = 'PySoup tracing')
BEGIN
    DROP EVENT SESSION [PySoup tracing] ON SERVER
END

CREATE EVENT SESSION [PySoup tracing] ON SERVER 
ADD EVENT sqlserver.rpc_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sp_statement_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sql_batch_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sql_statement_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text ))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PySoup tracing.xel')--,
--ADD TARGET package0.ring_buffer(SET max_events_limit=(1000000))
GO

ALTER EVENT SESSION [PySoup tracing] ON SERVER
    STATE = START;

I thought that the ACTION clause was supposed to list the columns that were returned by the event. However, I don't see sqlserver.client_app_name column when I view the event data in the GUI.

xevent screengrab

What am I doing wrong?

Jack Douglas
  • 39,869
  • 15
  • 101
  • 176
Justin Dearing
  • 2,677
  • 6
  • 35
  • 51

2 Answers2

8

I have seen this issue before, what seems to happen if you have this type of sequence:

  1. create session
  2. start session
  3. stop session
  4. add columnar data (action or field)
  5. start session

The GUI doesn't show the added columnar data. Your options here would be to directly query the target or to clear/rename the old session definition's XEL files.

Thomas Stringer
  • 42,224
  • 9
  • 117
  • 154
5

Not that this would be deemed the answer in your situation, since you already marked it. This is just my take on it, with a bit of testing.

I created the event session using SQL Server 2012 instance and SSMS version, minus the application name event.

IF EXISTS (SELECT name FROM sys.dm_xe_sessions WHERE Name = 'PySoup tracing')
BEGIN
    DROP EVENT SESSION [PySoup tracing] ON SERVER
END

CREATE EVENT SESSION [PySoup tracing] ON SERVER 
ADD EVENT sqlserver.rpc_completed (ACTION(sqlserver.sql_text )),
ADD EVENT sqlserver.sp_statement_completed (ACTION(sqlserver.sql_text )),
ADD EVENT sqlserver.sql_batch_completed (ACTION(sqlserver.sql_text )),
ADD EVENT sqlserver.sql_statement_completed (ACTION(sqlserver.sql_text ))
ADD TARGET package0.event_file(SET filename=N'tracing.xel')--,
--ADD TARGET package0.ring_buffer(SET max_events_limit=(1000000))
GO

ALTER EVENT SESSION [PySoup tracing] ON SERVER
    STATE = START;

enter image description here

I never closed my live data viewer and never stopped the session. I went into the properties of the session and added the client_app_name. Then reviewed output again, and it did not take immediately but did begin showing the application name as expected. enter image description here

Starting with SQL Server 2012, you have the ability to change your session properties without having to stop and start the session. So I can add and remove fields, include filters, etc without having to bother with a stop/start. In your case I don't know if this is true or not but the live data view does cause the dispatch latency to change. It could have just taken a while for it to pick it up. You can take a look on this from my answer on another question.

Community
  • 1