Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extended Events results Expand / Collapse
Author
Message
Posted Thursday, May 3, 2012 9:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
Here is my extended events trace

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test')
DROP EVENT SESSION [test] ON SERVER;
CREATE EVENT SESSION [test]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username)
WHERE (([sqlserver].[username]='test'))),
ADD EVENT sqlserver.sp_statement_completed(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username)
WHERE (([sqlserver].[username]='test')))
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\temp\test.xel')
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 300 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

ALTER EVENT SESSION [test] ON SERVER STATE = START

It does appear to be capturing the info I want but files are growing very large and when I query the files I see hundreds of rows for the same statement executed on the server by user "test".

Is there anything I can change with my extended events session to only capture one row per statement executed? thanks

here is query used to read target file (from http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx)

DECLARE @path nvarchar(260), @mdpath nvarchar(260), @xesession nvarchar(50)
set @xesession = 'test'

-- Get the log file name and substitute * wildcard in
SELECT
@path = LEFT(column_value, LEN(column_value)-CHARINDEX('.', REVERSE(column_value)))
+ '*'
+ RIGHT(column_value, CHARINDEX('.', REVERSE(column_value))-1)
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_object_columns soc
ON s.address = soc.event_session_address
WHERE s.name = @xesession
AND soc.object_name = 'asynchronous_file_target'
AND soc.column_name = 'filename'

-- Get the metadata file name and substitute * wildcard in
SELECT
@mdpath = LEFT(column_value, LEN(column_value)-CHARINDEX('.', REVERSE(column_value)))
+ '*'
+ RIGHT(column_value, CHARINDEX('.', REVERSE(column_value))-1)
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_object_columns soc
ON s.address = soc.event_session_address
WHERE s.name = @xesession
AND soc.object_name = 'asynchronous_file_target'
AND soc.column_name = ' metadatafile'

-- Set the metadata filename if it is NULL to the log file name with xem extension
SELECT @mdpath = ISNULL(@mdpath,
LEFT(@path, LEN(@path)-CHARINDEX('*', REVERSE(@path)))
+ '*xem')

select
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(action[@name="session_id"]/value)[1]','int') as session_id,
n.value('(data[@name="duration"]/value)[1]','int') as duration,
n.value('(data[@name="reads"]/value)[1]','int') as reads,
n.value('(data[@name="writes"]/value)[1]','int') as writes,
n.value('(action[@name="client_app_name"]/value)[1]','varchar(255)') as client_app_name,
n.value('(action[@name="client_hostname"]/value)[1]','varchar(255)') as client_hostname,
n.value('(action[@name="database_id"]/value)[1]','int') as database_id,
n.value('(action[@name="username"]/value)[1]','varchar(255)') as username,
n.value('(action[@name="sql_text"]/value)[1]','varchar(max)') as sql_text,
n.value('(action[@name="plan_handle"]/value)[1]','varchar(max)') as plan_handle
from
(select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file(@path,@mdpath,null,null)) as tab
cross apply event_data.nodes('event') as q(n)



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1294710
Posted Thursday, May 3, 2012 10:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,681, Visits: 1,793
What is an example of the output and which event is the one being repeated? My first guess would be that you have a user defined function in use and it is doing RBAR processing, so you would see the same repeating of events in a trace file if you ran an equivalent SQL Trace. It is impossible to tell without more information about what the test user is executing against the SQL Server, or the event information.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1294790
Posted Thursday, May 3, 2012 12:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
Thanks Jonathan.

I just did a quick comparison between the extended events output and the sql trace output and I get a similar number of rows returned. I must just be using the incorrect action for sql_text because in the extended events trace sql_text shows the full query being executed in all rows but in the sql trace textdata field I see individual statements.

I have attached sample results from the same time frame for both extended events and sql trace. The sql trace used the sql_tuning template with a filter on login.

What would be ideal is to have the extended events trace only log one row for each query regardless whether the query had statements within it. is that possible? thanks


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1294854
Posted Thursday, May 3, 2012 3:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,681, Visits: 1,793
Read this blog post which explains what sql_text is:

Understanding the sql_text Action in Extended Events

In SQL Server 2008 there is no SQL Batch Completed Event, this was only added in SQL Server 2012, so if you need that level of granularity, you need to go back to SQL Trace in SQL Server 2008.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1294982
Posted Friday, May 4, 2012 10:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
Thanks for the explanation and point to the blog post.

Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1295378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse