Sql-server – Querying Extended Events in SQL Server 2008

extended-eventssql-server-2008

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.

Best Answer

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

Related Solutions

Sql-server – Illegal XML character out of the extended event

Since there are no takers yet, I offer a guesswork answer: Yes, I think it's a bug.

Workaround: replace the illegal character once you know which one it is before converting the data to XML like this:

select xml_event_data = CONVERT(xml, replace(event_data,CHAR(3),'&#3;')), *
from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet', 
                '...\Log\error_*.xem', 
                null, 
                null
            )

Sql-server – Visualizing SQL Server Extended Events data

Caveat: Much of the information provided below I learned strictly from going through two Pluralsight courses by Jonathan Keyhayias. Well worth the one month expense for the plus subscription to go through his two courses.

First just a few points of interest that I think will help (or at most be of interest):

When an Extended Event session is started it will have a portion of memory allocated to a buffer space to store data generated by the events of the session. In your session this is set to the default value of 4MB
There are multiple targets available to use. These targets are either synchronous or asynchronous in how they receive data. The two most commonly used targets being Target File and Ring Buffer are both asynchronous. BOL article here indicates what type each target is.
The MAX_DISPATCH_LATENCY is a configuration option that controls when event data is dispatched to the target(s). Dispatching only occurs for asynchronous targets. There are two conditions that will cause event data to be dispatched: (1) memory buffer for the session is full or (2) event data in the buffer exceeds the session's MAX_DISPATCH_LATENCY configured option.
When you open the Live Data Viewer it will attach an additional target to the event session called "streaming target". This will receive the live event stream as memory buffers are being dispatched. It actually will also change the dispatch latency associated with the session to 3 seconds in order to get near real time view of the session.

Now to specific points in your question:

The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask

I am not aware that it does this other than what I stated above. I would expect that the event was captured it just has not meet the thresholds required for it to be dispatched to your live data viewer. I tested this with the following query from AdventureWorks2012:

SELECT * FROM dbo.ErrorLog
WAITFOR DELAY '00:00:01' ;
GO

Using your event session configuration, with the exception that I am filtering to only capture data for AdventureWorks2012 database on my local instance, I can view the target data for this session and find the query was captured:

enter image description here

Executing this query one more time will finally cause it to get dispatched and the data viewer displays one event. Now if you actually want to see all the events that are displayed simply STOP the session and the buffer will get fully dispatched. I see this once I stop my session:

enter image description here

1.Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)

I had thought you could change the MAX_MEMORY to a lower value which would indicate a small buffer size to capture events. However the lowest value you can set this to in SQL Server 2012 is 200KB, which the query I used does not meet that limit to cause it to immediately be dispatched. Only thing I could do was at most execute a query that would cause the buffer to be reached and the previous events captured to be dispatched:

SELECT *
FROM Person.Person
ORDER BY EmailPromotion DESC;

2.Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?

Not that I am aware of currently. I would suggest the best method of getting data out as soon as it occurs is to query the XML for the ring_buffer target and just shred that out. I can repeat the above example and as soon as I execute the query below I see the event.

-- Create XML variable to hold Target Data
DECLARE @target_data XML
SELECT  @target_data = CAST([t].[target_data] AS XML)
FROM    [sys].[dm_xe_sessions] AS s
JOIN    [sys].[dm_xe_session_targets] AS t
        ON [t].[event_session_address] = [s].[address]
WHERE   [s].[name] = N'Simple Query Benchmarking' AND
        [t].[target_name] = N'ring_buffer' ;

-- Return the full XML document
--SELECT @target_data;

--Shred XMl to get needed data
SELECT  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
    n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM @target_data.nodes('RingBufferTarget/event[@name=''sql_batch_completed'']') AS q(n)

enter image description here

Best Answer

Related Solutions

Sql-server – Illegal XML character out of the extended event

Sql-server – Visualizing SQL Server Extended Events data

Related Question