主页 / dba / 问题 / 205091
Accepted
Dolan Antenucci
Asked: 2018-04-27 11:43:23 +0800 CST

在 MS-SQL Server 中使用扩展事件跟踪查询时查询不记录

  • 772

我正在使用在 Windows Server 2012 RTM 上运行的 MS-SQL Server 2012 Standard。我使用 SSMS 创建了一个扩展事件会话，我在其中使用了内置的“批量查询跟踪”模板并将结果保存到一个文件中。

我有一个名为“mydb”的数据库，其中有一个“dbo.mytable”表。有一次我在另一个盒子上测试了这个，按照上面的步骤，查询将被记录到扩展事件跟踪中，但是在这个新盒子上（使用相同的 AWS AMI，一天后），任何关于“dbo”的查询.mytable" 没有被记录。

默认情况下，批查询跟踪模板在 batch_sql_completed（is_system=0 和 dbid>4）上有一个过滤器。我删除了这些，以及其他系统查询日志。我验证了扩展事件会话正在运行。我还尝试了“详细查询跟踪”模板，以确保它不是我以前使用的那个模板。

我的目标是简单地审计在数据库上运行的所有查询。我运行的测试查询是对“mytable”和全选的简单插入。我正在通过 SSMS 运行查询。

关于为什么不记录这些查询的任何想法？让我知道我可以提供哪些其他信息。谢谢！

有关我的测试的更多详细信息

创建扩展事件会话：

  • 转到 SSMS 中的“New Session..”右键单击菜单选项（在 Management -> Extended Events 下）
  • 输入以下信息：
    • 会话名称：日志查询
    • 模板：查询批量跟踪
    • Schedule：在服务器启动时启动事件会话；创建后立即开始
    • 因果关系跟踪：跟踪事件如何相互关联。
    • 数据存储：event_file，C:\logs\log-queries.xel
  • 验证它正在运行：
    • 从 sys.dm_xe_sessions 中选择 *；
    • 已验证：我在列表中看到它

关于我的测试数据库：

  • 数据库：mydb
  • 表：dbo.mytable (cid nchar(10), cname nchar(10), city nchar(10))
    • （“dbo”是模式；我创建时的默认选项之一）
  • 查询：SELECT TOP 1000 [cid], [cname], [city] FROM [mydb].[dbo].[mytable]

验证日志记录是否有效：

  • 按照本教程，从日志文件中提取语句，然后在结果中查找我的查询：
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 个回答

  1. 问题出在我尝试使用批量查询跟踪模板创建扩展事件会话时。

    当我遵循本教程并按如下方式创建会话时，日志记录按预期工作：

    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

    如果您使用该模板，它肯定会记录您从 SSMS 运行的查询。

    扩展事件会话在您创建它们时默认情况下不会启动- 您需要告诉它们开始。也许您是在另一台服务器上这样做的，但在第二种情况下不是这样吗？

    您可以通过查看以下结果来检查会话是否正在运行：

    select * from sys.dm_xe_sessions

    如果您的会话不在该结果集中，则它没有运行。

    您可以通过运行以下命令启动会话：

    ALTER EVENT SESSION your_session_name
ON SERVER  
STATE = start;

    如果您确认它正在运行，但您仍然没有在文件中看到结果，请在 SSMS 中编写事件会话脚本并使用结果更新您的问题。也许它错误地配置了不同的内容，或者它正在将文件写入您不希望的地方。

    将这些细节添加到您的问题中真是太棒了，这真的很有帮助。

    听起来您遇到的是 Shawn Melton 在他的回答中提到的内容。

    简而言之，只有在达到MAX_DISPATCH_LATENCY（默认为 30 秒）或MAX_MEMORY（默认为 4 MB）时，事件（在本例中为查询）才会写入该文件。

    对于它的价值，我已经体验到MAX_DISPATCH_LATENCY并不总是强制执行（即，如果MAX_MEMORY未达到缓冲区限制，则文件不会更新 - 即使它已经超过 30 秒）。但是，正如 Shawn 所提到的，停止事件会话会将整个事件缓冲区刷新到文件中。

    • 1

相关问题