Início / dba / Perguntas / 9903
Accepted
Craig Efrein
Asked: 2011-12-30 03:14:59 +0800 CST

Consultando eventos estendidos no SQL Server 2008

  • 772

Eu criei a seguinte sessão de evento usando um script que obtive do livro SQL Server 2008 Internals de Kimberly Tripp. Ele foi modificado para enviar para um arquivo em vez do buffer de anel.

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
)

Aqui está o script original para consultar os dados.

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

Como eu poderia modificá-lo para ler os dois arquivos xel e xem em vez do buffer de anel. Eu tenho pesquisado por aí, mas a curva de aprendizado tem sido difícil. Agradeço antecipadamente por sua ajuda.

sql-server-2008 extended-events

2 respostas

  1. Best Answer

    Tente brincar com este exemplo:

    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
    • 7

  2. Aqui está outra consulta

    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];
    • 2

relate perguntas