4

I have created the following event session using a script I got from Kimberly Tripp's SQL Server 2008 Internals book. It has been modified to send to a file instead of the ring buffer.

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION 
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id=53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\MSSQL\TEMP\stats.xel', 
        metadatafile=N'C:\MSSQL\TEMP\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)

Here is the original script to query the data.

SELECT
theNodes.event_data.value('(data/value)[1]', 'bigint') AS source_database_id,
theNodes.event_data.value('(data/value)[2]', 'bigint') AS object_id,
theNodes.event_data.value('(data/value)[3]', 'bigint') AS object_type,
theNodes.event_data.value('(data/value)[4]', 'bigint') AS cpu,
theNodes.event_data.value('(data/value)[5]', 'bigint') AS duration,
theNodes.event_data.value('(data/value)[6]', 'bigint') AS reads,
theNodes.event_data.value('(data/value)[7]', 'bigint') AS writes,
theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
    SELECT 
        CONVERT(XML, st.target_data) AS ring_buffer
        FROM sys.dm_xe_sessions s
        JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
        WHERE s.name = 'statement_completed'
) AS theData
CROSS APPLY
theData.ring_buffer.nodes('//RingBufferTarget/event') theNodes (event_data)
WHERE theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') is not null

How could I modify it to read from the two files xel and xem instead of the ring buffer. I have been googling around but the learning curve has been rough. Thank you in advance for your help.

  • 2
    I suggest downloading this addin. Makes playing with XEvents much easier. – Martin Smith Dec 29 '11 at 11:46
7

Try to play with this example:

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION 
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id <> 53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\stats.xel', 
        metadatafile=N'C:\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO
ALTER EVENT SESSION [statement_completed_file]
ON SERVER
STATE=START
GO
SELECT event_data, 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data/value)[1]', 'bigint') AS source_database_id,
    event_data.value('(event/data/value)[2]', 'bigint') AS object_id,
    event_data.value('(event/data/value)[3]', 'bigint') AS object_type,
    event_data.value('(event/data/value)[4]', 'bigint') AS cpu,
    event_data.value('(event/data/value)[5]', 'bigint') AS duration,
    event_data.value('(event/data/value)[6]', 'bigint') AS reads,
    event_data.value('(event/data/value)[7]', 'bigint') AS writes,
    event_data.value('(event/action/value)[1]', 'nvarchar(max)') AS sql_text
FROM 
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\stats*.xel', 'C:\stats*.xem', null, null)
) tbl
GO
DROP EVENT SESSION [statement_completed_file] ON SERVER
GO
  • Thanks Oleg, I was hoping actually to figure out a way to just change the cross apply so that it used sys.fn_xe_file_target_read_file instead of the ring_buffer. – Craig Efrein Dec 29 '11 at 11:38
  • You need no any cross apply then, updating an answer – Oleg Dok Dec 29 '11 at 11:40
  • With your example, I can only access the name of the event session but not the data values underneath. If I just run the subquery on the file, I see this : <event name="sp_statement_completed" package="sqlserver" id="62" version="1" timestamp="2011-12-29T13:28:56.789Z"><data name="source_database_id"><value>1</value><text />... but I can't seem to access the values – Craig Efrein Dec 29 '11 at 13:36
  • Added the complete script – Oleg Dok Dec 29 '11 at 13:48
2

Heres another query

declare
    xmlData xml;

select
    @xmlData    =   [xet].[event_data]
from
    [sys].fn_xe_file_target_read_file('C:\stats*.xel', 'C:\stats*.xem', null, null) as [xet];

with cte_xmlData as (
    select
        [e].value('@name', 'nvarchar(max)') as [EventName],
        [e].value('@timestamp', 'datetime') as [EventTimeStamp],
        [d].value('value[1]', 'nvarchar(max)') as [DdlPhase],
        [d].value('text[1]', 'nvarchar(max)') as [DdlPhaseDesc],
        [c].value('@name', 'nvarchar(max)') as [Name],
        [c].value('value[1]', 'nvarchar(max)') as [Value]
    from
        @xmlData.nodes('/RingBufferTarget/event') as [xml]([e])
        inner join @xmlData.nodes('/RingBufferTarget/event/action') as [xml2]([c])
            on ( [c].value('../@timestamp', 'datetime') =  [e].value('@timestamp', 'datetime') )
        inner join @xmlData.nodes('/RingBufferTarget/event/data') as [xml3]([d])
            on ( [d].value('../@timestamp', 'datetime') =  [e].value('@timestamp', 'datetime') )
            and ( [d].value('@name', 'nvarchar(max)') = 'ddl_phase' )
    union
    select
        [e].value('@name', 'nvarchar(max)') as [EventName],
        [e].value('@timestamp', 'datetime') as [EventTimeStamp],
        [d].value('value[1]', 'nvarchar(max)') as [DdlPhase],
        [d].value('text[1]', 'nvarchar(max)') as [DdlPhaseDesc],
        [c].value('@name', 'nvarchar(max)') as [Name],
        case
            when [c].exist('text[1]') = 0 then [c].value('value[1]', 'nvarchar(max)')
            else [c].value('text[1]', 'nvarchar(max)')
        end as [Value]
    from
        @xmlData.nodes('/RingBufferTarget/event') as [xml]([e])
        inner join @xmlData.nodes('/RingBufferTarget/event/data') as [xml2]([c])
            on ( [c].value('../@timestamp', 'datetime') =  [e].value('@timestamp', 'datetime') )
        inner join @xmlData.nodes('/RingBufferTarget/event/data') as [xml3]([d])
            on ( [d].value('../@timestamp', 'datetime') =  [e].value('@timestamp', 'datetime') )
            and ( [d].value('@name', 'nvarchar(max)') = 'ddl_phase' )
)
/**/
select
    [pData].[EventName],
    [pData].[EventTimeStamp],
    [pData].[DdlPhaseDesc],
    [pData].[event_name],
    [pData].[source_database_id],
    [pData].[object_id],
    [pData].[object_type],
    [pData].[cpu],
    [pData].[duration],
    [pData].[reads],
    [pData].[writes],
    [pData].[sql_text]
from
    [cte_xmlData]
    -- Modify the in statement depending on what you want
    pivot(max([value]) for [name] in ([event_name], [source_database_id], [object_id], [object_type], [cpu], [duration], [reads], [writes], [sql_text])) as [pData]
order by
    [EventTimeStamp],
    [DdlPhase];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.