How can I shred this Extended Events XML as fast as possible?

Question

I created an Extended Events session in SQL Server 2008 R2. The session runs, and collects events as they happen, exactly like you'd expect.

If I shred the xml when there are relatively few events, performance is acceptable. When I have thousands of events, it takes for ever to shred the xml.

I know I am doing something wrong, I just don't have enough knowledge about the internals of the XML engine to understand what.

This is the definition of my Extended Events session:

IF EXISTS 
(
    SELECT 1 
    FROM sys.server_event_sessions dxs 
    WHERE dxs.name = 'queries'
)
BEGIN
    IF EXISTS (
        SELECT 1 
        FROM sys.dm_xe_sessions dxs 
        WHERE dxs.name = 'queries'
    )
    BEGIN
        ALTER EVENT SESSION queries ON SERVER STATE = STOP;
    END
    DROP EVENT SESSION queries ON SERVER;
END

CREATE EVENT SESSION queries ON SERVER 
ADD EVENT sqlserver.sql_statement_starting
(
    ACTION 
    (
        package0.collect_system_time
        --, package0.event_sequence
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        --, sqlserver.database_name
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
    )
    WHERE sqlserver.username = N'<some username>'
        AND sqlserver.database_id = 6 /* specific database */
        AND sqlserver.client_hostname <> '<my machine>'
) 
, ADD EVENT sqlserver.error_reported 
(
    ACTION 
    (
        package0.collect_system_time
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
    )
    WHERE sqlserver.username <> N'<some username>'
        /* fluff errors below */
        AND error <> 5703 /* Changed language setting to %.*ls. */
        AND error <> 5701 /* Changed database context to '%.*ls'. */
        AND error <> 2528 /* DBCC execution completed. If DBCC printed error messages, contact your system administrator. */
        AND error <> 7969 /* No active open transactions. */
        --AND error <> 14205/* (unknown) */
        AND error <> 4035 /* Processed %I64d pages for database '%ls', file '%ls' on file %d. */
        AND error <> 18265/* Log was backed up. Database: %s, creation date(time): %s(%s), first LSN: %s, last LSN: %s, number of dump devices: %d, device information: (%s). This is an informational message only. No user action is required. */
        AND error <> 3014 /* %hs successfully processed %I64d pages in %d.%03d seconds (%d.%03d MB/sec). */
        AND error <> 14570/* (Job outcome) */
        AND error <> 8153 /* Warning: Null value is eliminated by an aggregate or other SET operation. */

)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 102400
)
WITH 
(
    STARTUP_STATE=OFF
    , TRACK_CAUSALITY = ON
);
GO

IF EXISTS 
(
    SELECT 1 
    FROM sys.server_event_sessions dxs 
    WHERE dxs.name = 'queries'
)
BEGIN
    ALTER EVENT SESSION queries ON SERVER STATE = START;
END

This is how I'm shredding the results:

/***************************

shred the Event Data into readable form

***************************/

DECLARE @xml XML;

SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries';

SELECT t.EventName
    , DateStamp = DATEADD(HOUR, -6, t.EventDateStamp)
    , DatabaseName = d.name
    , t.ErrorNumber
    , t.ErrorSeverity
    , t.ErrorState
    , t.ErrorMessage
    , t.CollectSystemTime
    , t.ClientAppName
    , t.ClientHostName
    , t.PlanHandle
    , t.SqlText
    , t.UserName
FROM (
        SELECT EventName = s.value('(@name)[1]','varchar(500)')
            , EventDateStamp = s.value('(@timestamp)[1]','datetime')
            , DatabaseID = s.value('(data[(@name)[1] eq "source_database_id"]/value/text())[1]','varchar(255)')
            , ErrorNumber = s.value('(data[(@name)[1] eq "error"]/value/text())[1]','int')
            , ErrorSeverity = s.value('(data[(@name)[1] eq "severity"]/value/text())[1]','int')
            , ErrorState = s.value('(data[(@name)[1] eq "state"]/value/text())[1]','int')
            , ErrorMessage = s.value('(data[(@name)[1] eq "message"]/value/text())[1]','varchar(255)')
            , CollectSystemTime = s.value('(action[(@name)[1] eq "collect_system_time"]/text/text())[1]','varchar(255)')
            , ClientAppName = s.value('(action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
            , ClientHostName = s.value('(action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
            , PlanHandle = CONVERT(xml, s.value('(action[(@name)[1] eq "plan_handle"]/value/text())[1]','varchar(255)')).value('(plan/@handle)[1]', 'varchar(255)')
            , SqlText = s.value('(action[(@name)[1] eq "sql_text"]/value/text())[1]','nvarchar(max)')
            , UserName = s.value('(action[(@name)[1] eq "username"]/value/text())[1]','varchar(128)')
        FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
    ) t
    LEFT JOIN sys.databases d ON t.DatabaseID = d.database_id
ORDER BY t.UserName
    , t.EventDateStamp;

Answer 1

Thanks to @Tom V for identifying this blog post that identifies the need for a temporary table.

Adapting the ideas in the blog post, this now works very quickly:

/***************************

shred the Event Data into readable form

***************************/

DECLARE @xml XML;

SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries';

IF OBJECT_ID('tempdb..#xmlResults') IS NOT NULL
DROP TABLE #xmlResults;

CREATE TABLE #xmlResults
(
    RowNum INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , xeXML XML NOT NULL
);

INSERT INTO #xmlResults (xeXML)
SELECT xm.s.query('.')
FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
OPTION (Optimize FOR (@xml = Null)); -- Useful for SQL 2008

SELECT *
FROM #xmlResults

SELECT t.EventName
    , DateStamp = DATEADD(HOUR, -6, t.EventDateStamp)
    , DatabaseName = d.name
    , t.ErrorNumber
    , t.ErrorSeverity
    , t.ErrorState
    , t.ErrorMessage
    , t.CollectSystemTime
    , t.ClientAppName
    , t.ClientHostName
    , t.PlanHandle
    , t.SqlText
    , t.UserName
FROM (
        SELECT EventName =          xeXML.value('(event/@name)[1]','varchar(500)')
            , EventDateStamp =      xeXML.value('(event/@timestamp)[1]','datetime')
            , DatabaseID =          xeXML.value('(event/data[(@name)[1] eq "source_database_id"]/value/text())[1]','varchar(255)')
            , ErrorNumber =         xeXML.value('(event/data[(@name)[1] eq "error"]/value/text())[1]','varchar(255)')
            , ErrorSeverity =       xeXML.value('(event/data[(@name)[1] eq "severity"]/value/text())[1]','varchar(255)')
            , ErrorState =          xeXML.value('(event/data[(@name)[1] eq "state"]/value/text())[1]','varchar(255)')
            , ErrorMessage =        xeXML.value('(event/data[(@name)[1] eq "message"]/value/text())[1]','varchar(255)')
            , CollectSystemTime =   xeXML.value('(event/action[(@name)[1] eq "collect_system_time"]/text/text())[1]','varchar(255)')
            , ClientAppName =       xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
            , ClientHostName =      xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
            , PlanHandle = CONVERT(xml, xeXML.value('(event/action[(@name)[1] eq "plan_handle"]/value/text())[1]','varchar(255)')).value('(plan/@handle)[1]', 'varchar(255)')
            , SqlText =             xeXML.value('(event/action[(@name)[1] eq "sql_text"]/value/text())[1]','nvarchar(max)')
            , UserName =            xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','varchar(128)')
        FROM #xmlResults xm
    ) t
    LEFT JOIN sys.databases d ON t.DatabaseID = d.database_id
ORDER BY t.UserName
    , t.EventDateStamp;