May 3, 2012 at 9:29 am
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)
May 3, 2012 at 10:30 am
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[/url]
May 3, 2012 at 12:00 pm
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
May 3, 2012 at 3:41 pm
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[/url]
May 4, 2012 at 10:01 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply