SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extended Events results


Extended Events results

Author
Message
Robert klimes
Robert klimes
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5233 Visits: 3468
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
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6719 Visits: 1818
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
Robert klimes
Robert klimes
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5233 Visits: 3468
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
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6719 Visits: 1818
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
Robert klimes
Robert klimes
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5233 Visits: 3468
Thanks for the explanation and point to the blog post.

Bob
-----------------------------------------------------------------------------
How to post to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search