使用扩展事件监视系统活动Monitor System Activity Using Extended Events

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

此过程说明如何将扩展事件和 Windows 事件跟踪 (ETW) 配合使用来监视系统活动。This procedure illustrates how Extended Events can be used with Event Tracing for Windows (ETW) to monitor system activity. 此过程还说明如何使用 CREATE EVENT SESSION、ALTER EVENT SESSION 和 DROP EVENT SESSION 语句。The procedure also shows how the CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION statements are used.

若要完成这些任务,需使用 SQL Server Management StudioSQL Server Management Studio 中的查询编辑器执行以下过程。Accomplishing these tasks involves using Query Editor in SQL Server Management StudioSQL Server Management Studio to carry out the following procedure. 此过程还要求使用命令提示符运行 ETW 命令。The procedure also requires using the command prompt to run ETW commands.

使用扩展事件监视系统活动To monitor system activity using Extended Events

  1. 在查询编辑器中,发出下列语句创建事件会话并添加两个事件。In Query Editor, issue the following statements to create an event session and add two events. checkpoint_begin 和 checkpoint_end 事件在数据库检查点开始和结束时激发。These events, checkpoint_begin and checkpoint_end, fire at the beginning and end of a database checkpoint.

    CREATE EVENT SESSION test0  
    ON SERVER  
    ADD EVENT sqlserver.checkpoint_begin,  
    ADD EVENT sqlserver.checkpoint_end  
    WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)  
    go  
    
  2. 添加具有 32 个存储桶的存储桶存储目标,以根据数据库 ID 对检查点数目进行计数。Add the bucketing target with 32 buckets to count the number of checkpoints based on the database ID.

    ALTER EVENT SESSION test0  
    ON SERVER  
    ADD TARGET package0.histogram  
    (  
          SET slots = 32, filtering_event_name = 'sqlserver.checkpoint_end', source_type = 0, source = 'database_id'  
    )  
    go  
    
  3. 发出下列语句添加 ETW 目标。Issue the following statements to add the ETW target. 这样便能查看开始和结束事件,这些事件用于确定检查点的长度。This will enable you to see the begin and end events, which is used to determine how long the checkpoint takes.

    ALTER EVENT SESSION test0  
    ON SERVER  
    ADD TARGET package0.etw_classic_sync_target  
    go  
    
  4. 发出下列语句启动会话并开始事件收集。Issue the following statements to start the session and begin event collection.

    ALTER EVENT SESSION test0  
    ON SERVER  
    STATE = start  
    go  
    
  5. 发出下列语句激发三个事件。Issue the following statements to cause three events to fire.

    USE tempdb  
          checkpoint  
    go  
    USE master  
          checkpoint  
          checkpoint  
    go  
    
  6. 发出下列语句查看事件计数。Issue the following statements to view the event counts.

    SELECT CAST(xest.target_data AS xml) Bucketizer_Target_Data_in_XML  
    FROM sys.dm_xe_session_targets xest  
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address  
    JOIN sys.server_event_sessions ses ON xes.name = ses.name  
    WHERE xest.target_name = 'histogram' AND xes.name = 'test0'  
    go  
    
  7. 在命令提示符下,发出下列命令查看 ETW 数据。At the command prompt, issue the following commands to view the ETW data.

    备注

    若要获得 tracerpt 命令的帮助,请在命令提示符下输入 tracerpt /?To get help for the tracerpt command, at the command prompt, enter tracerpt /?.

    logman query -ets --- List the ETW sessions. This is optional.  
    logman update XE_DEFAULT_ETW_SESSION -fd -ets --- Flush the ETW log.  
    tracerpt %temp%\xeetw.etl -o xeetw.txt --- Dump the events so they can be seen.  
    
  8. 发出下列语句停止事件会话并将其从服务器上删除。Issue the following statements to stop the event session and remove it from the server.

    ALTER EVENT SESSION test0  
    ON SERVER  
    STATE = STOP  
    go  
    
    DROP EVENT SESSION test0  
    ON SERVER  
    go  
    

另请参阅See Also

CREATE EVENT SESSION (Transact-SQL) CREATE EVENT SESSION (Transact-SQL)
ALTER EVENT SESSION (Transact-SQL) ALTER EVENT SESSION (Transact-SQL)
DROP EVENT SESSION (Transact-SQL) DROP EVENT SESSION (Transact-SQL)
扩展事件目录视图 (Transact-SQL) Extended Events Catalog Views (Transact-SQL)
扩展事件动态管理视图 Extended Events Dynamic Management Views
SQL Server 扩展事件目标SQL Server Extended Events Targets