Início / dba / Perguntas / 205091
Accepted
Dolan Antenucci
Asked: 2018-04-27 11:43:23 +0800 CST

Consultas não registradas ao usar eventos estendidos no MS-SQL Server para rastrear consultas

  • 772

Estou usando o MS-SQL Server 2012 Standard, rodando no Windows Server 2012 RTM. Usei o SSMS para criar uma Sessão de Evento Estendida, onde usei o modelo interno "Rastreamento de consultas em lote" e salvei o resultado em um arquivo.

Eu tenho um banco de dados chamado "mydb" com uma tabela "dbo.mytable" nele. A certa altura, testei isso em uma caixa diferente e, seguindo as etapas acima, as consultas seriam registradas no rastreamento de eventos estendidos, mas nessa nova caixa (usando a mesma AWS AMI, um dia depois), qualquer consulta em "dbo .mytable" não estão sendo gravados.

Por padrão, o modelo Batch Query Tracking tem um filtro em batch_sql_completed (is_system=0 e dbid>4). Eu removi esses e outros logs de consultas do sistema. Verifiquei que a Sessão de Evento Estendida está em execução. Eu também tentei o modelo "Detailed Query Tracking", para ter certeza de que não era aquele modelo que eu usei antes.

Meu objetivo é simplesmente auditar todas as consultas executadas no banco de dados. As consultas de teste que executei foram inserções simples em "mytable" e select-all's. Estou executando as consultas através do SSMS.

Alguma idéia de por que essas consultas não seriam registradas? Deixe-me saber que outras informações posso fornecer. Obrigado!

Mais detalhes sobre meus testes

Criando a Sessão de Evento Estendido:

  • Vá para "Nova sessão .." opção de menu do botão direito do mouse no SSMS (em Gerenciamento -> Eventos estendidos)
  • Insira as seguintes informações:
    • Nome da sessão: consultas de log
    • Modelo: acompanhamento de lote de consulta
    • Agenda: Iniciar a sessão do evento na inicialização do servidor; Iniciar imediatamente após a criação
    • Rastreamento de causalidade: acompanhe como os eventos se relacionam.
    • Armazenamento de dados: event_file, C:\logs\log-queries.xel
  • Verifique se está em execução:
    • selecione * de sys.dm_xe_sessions;
    • Verificado: vejo na lista

Sobre meu banco de dados de teste:

  • Banco de dados: mydb
  • Tabela: dbo.mytable (cid nchar(10), cname nchar(10), cidade nchar(10))
    • ("dbo" é o esquema; uma das opções padrão quando criei)
  • Consulta: SELECT TOP 1000 [cid], [cname], [city] FROM [mydb].[dbo].[mytable]

Verificando se o log está funcionando:

  • Seguindo este tutorial , extraia as instruções do arquivo de log, e procuro minha consulta no resultado:
IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent

SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
    INTO #ExEvent
FROM sys.fn_xe_file_target_read_file(N'C:\logs\*.xel', null, null, null);


SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
        , ISNULL(t_action.event_name, t_data.event_name) AS event_name
        , t_action.[client_hostname], t_action.[collect_system_time], t_action.[database_name], t_action.[query_plan_hash], t_action.[server_principal_name], t_action.[session_id]
        , t_data.[collect_statement], t_data.[connection_reset_option], t_data.[cpu_time], t_data.[data_stream], t_data.[duration], t_data.[last_row_count], t_data.[line_number], t_data.[logical_reads], t_data.[object_name], t_data.[offset], t_data.[offset_end], t_data.[output_parameters], t_data.[parameterized_plan_handle], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
    FROM (
            SELECT RowId, event_name, [client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id]                  
                FROM (
                        SELECT RowId
                                , event_name
                                , T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
                                , T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
                        FROM   #ExEvent
                        CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
                        WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
                            IN ('client_hostname', 'collect_system_time', 'database_name', 'query_plan_hash', 'server_principal_name', 'session_id')
                    ) AS SourceTable
                        PIVOT(
                            MAX(att_value)
                            FOR att_name IN ([client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id])
                    ) AS PivotTable
            ) AS t_action

        -- Full outer because it might be no events selected only the payload
        FULL OUTER JOIN (
            SELECT RowId, event_name, [collect_statement], [connection_reset_option], [cpu_time], [data_stream], [duration], [last_row_count], [line_number], [logical_reads], [object_name], [offset], [offset_end], [output_parameters], [parameterized_plan_handle], [physical_reads], [result], [row_count], [statement], [writes]
                FROM (
                        SELECT RowId
                                , event_name
                                , T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
                                , T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

                            FROM   #ExEvent
                            CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
                            WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
                                IN ('collect_statement', 'connection_reset_option', 'cpu_time', 'data_stream', 'duration', 'last_row_count', 'line_number', 'logical_reads', 'object_name', 'offset', 'offset_end', 'output_parameters', 'parameterized_plan_handle', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
                        ) AS SourceTable
                            PIVOT (
                            MAX(att_value)
                                FOR att_name IN ([collect_statement], [connection_reset_option], [cpu_time], [data_stream], [duration], [last_row_count], [line_number], [logical_reads], [object_name], [offset], [offset_end], [output_parameters], [parameterized_plan_handle], [physical_reads], [result], [row_count], [statement], [writes])
                        ) AS PivotTable

            ) AS t_data
            ON t_data.RowId = t_action.RowId
sql-server ssms

2 respostas

  1. O problema estava comigo tentando usar o modelo Batch Query Tracking para minha criação de Sessão de Eventos Estendidos.

    Quando sigo este tutorial e crio uma sessão da seguinte forma, o log funciona conforme o esperado:

    CREATE EVENT SESSION [What I do] ON SERVER

ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash
            ,sqlserver.server_principal_name,sqlserver.session_id)),

ADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(0),collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash
            ,sqlserver.server_principal_name,sqlserver.session_id))

ADD TARGET package0.event_file(SET filename=N'C:\logs\WhatIdo.xel')

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB
        ,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
    • 2
  2. Best Answer

    Se você usou esse modelo, ele definitivamente deve registrar as consultas que você executou no SSMS.

    As sessões de eventos estendidos não são iniciadas por padrão quando você as cria - você precisa dizer a elas para iniciar. Talvez você tenha feito isso no outro servidor, mas não neste segundo caso?

    Você pode verificar se a sessão está em execução observando os resultados de:

    select * from sys.dm_xe_sessions

    Se sua sessão não estiver nesse conjunto de resultados, ela não está em execução.

    Você pode iniciar a sessão executando:

    ALTER EVENT SESSION your_session_name
ON SERVER  
STATE = start;

    Se você confirmar que está em execução e ainda não estiver vendo resultados em seu arquivo, faça o script da sessão do evento no SSMS e atualize sua pergunta com os resultados. Talvez esteja configurado de forma diferente por engano ou esteja gravando o arquivo em um local que você não espera.

    Trabalho fantástico adicionando esses detalhes à sua pergunta, isso foi muito útil.

    Parece que o que você estava encontrando é o que Shawn Melton menciona em sua resposta aqui .

    Resumindo, os eventos (consultas, neste caso) só são gravados nesse arquivo quando o MAX_DISPATCH_LATENCY(padrão é 30 segundos) ou MAX_MEMORY(o padrão é 4 MB) é atingido.

    Para o que vale a pena, experimentei que MAX_DISPATCH_LATENCYnem sempre é aplicado (ou seja, se o MAX_MEMORYlimite de buffer não for atingido, o arquivo não será atualizado - mesmo que tenha passado mais de 30 segundos). No entanto, como Shawn mencionou, interromper a sessão de eventos libera todo o buffer de eventos no arquivo.

    • 1

relate perguntas