Azure SQL 数据库中扩展事件的环形缓冲区目标代码Ring Buffer target code for extended events in Azure SQL Database

适用于: Azure SQL 数据库

需要完整的代码示例以最简单快速的方式在测试期间捕获和报告扩展事件的信息。You want a complete code sample for the easiest quick way to capture and report information for an extended event during a test. 扩展事件数据最简单的目标是环形缓冲区目标The easiest target for extended event data is the Ring Buffer target.

本主题演示一个 Transact-SQL 代码示例:This topic presents a Transact-SQL code sample that:

  1. 创建一个包含要演示的数据的表。Creates a table with data to demonstrate with.

  2. 创建现有扩展事件的会话,即 sqlserver.sql_statement_startingCreates a session for an existing extended event, namely sqlserver.sql_statement_starting.

    • 此事件仅限于包含特定 Update 字符串的 SQL 语句: statement LIKE '%UPDATE tabEmployee%'The event is limited to SQL statements that contain a particular Update string: statement LIKE '%UPDATE tabEmployee%'.
    • 选择要将事件的输出发送给环形缓冲区类型的目标,即 package0.ring_bufferChooses to send the output of the event to a target of type Ring Buffer, namely package0.ring_buffer.
  3. 启动事件会话。Starts the event session.

  4. 发出几个简单的 SQL UPDATE 语句。Issues a couple of simple SQL UPDATE statements.

  5. 发出 SQL SELECT 语句,从环形缓冲区检索事件输出。Issues a SQL SELECT statement to retrieve event output from the Ring Buffer.

    • sys.dm_xe_database_session_targets 和其他动态管理视图 (DMV) 联接在一起。sys.dm_xe_database_session_targets and other dynamic management views (DMVs) are joined.
  6. 停止事件会话。Stops the event session.

  7. 删除环形缓冲区目标以释放其资源。Drops the Ring Buffer target, to release its resources.

  8. 删除事件会话和演示表。Drops the event session and the demo table.

先决条件Prerequisites

代码示例Code sample

只要稍加修改,就可以在 Azure SQL 数据库或 Microsoft SQL Server 上运行以下环形缓冲区的代码示例。With very minor modification, the following Ring Buffer code sample can be run on either Azure SQL Database or Microsoft SQL Server. 不同之处在于步骤 5 的 FROM 子句中使用的某些动态管理视图 (DMV) 的名称中出现了节点“_database”。The difference is the presence of the node '_database' in the name of some dynamic management views (DMVs), used in the FROM clause in Step 5. 例如:For example:

  • sys.dm_xe_database_session_targetssys.dm_xe_database_session_targets
  • sys.dm_xe_session_targetssys.dm_xe_session_targets

 

GO
----  Transact-SQL.
---- Step set 1.

SET NOCOUNT ON;
GO


IF EXISTS
    (SELECT * FROM sys.objects
        WHERE type = 'U' and name = 'tabEmployee')
BEGIN
    DROP TABLE tabEmployee;
END
GO


CREATE TABLE tabEmployee
(
    EmployeeGuid         uniqueIdentifier   not null  default newid()  primary key,
    EmployeeId           int                not null  identity(1,1),
    EmployeeKudosCount   int                not null  default 0,
    EmployeeDescr        nvarchar(256)          null
);
GO


INSERT INTO tabEmployee ( EmployeeDescr )
    VALUES ( 'Jane Doe' );
GO

---- Step set 2.


IF EXISTS
    (SELECT * from sys.database_event_sessions
        WHERE name = 'eventsession_gm_azuresqldb51')
BEGIN
    DROP EVENT SESSION eventsession_gm_azuresqldb51
        ON DATABASE;
END
GO


CREATE
    EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE
    ADD EVENT
        sqlserver.sql_statement_starting
            (
            ACTION (sqlserver.sql_text)
            WHERE statement LIKE '%UPDATE tabEmployee%'
            )
    ADD TARGET
        package0.ring_buffer
            (SET
                max_memory = 500   -- Units of KB.
            );
GO

---- Step set 3.


ALTER EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE
    STATE = START;
GO

---- Step set 4.


SELECT 'BEFORE_Updates', EmployeeKudosCount, * FROM tabEmployee;

UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 102;

UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 1015;

SELECT 'AFTER__Updates', EmployeeKudosCount, * FROM tabEmployee;
GO

---- Step set 5.


SELECT
    se.name                      AS [session-name],
    ev.event_name,
    ac.action_name,
    st.target_name,
    se.session_source,
    st.target_data,
    CAST(st.target_data AS XML)  AS [target_data_XML]
FROM
               sys.dm_xe_database_session_event_actions  AS ac

    INNER JOIN sys.dm_xe_database_session_events         AS ev  ON ev.event_name = ac.event_name
        AND CAST(ev.event_session_address AS BINARY(8)) = CAST(ac.event_session_address AS BINARY(8))

    INNER JOIN sys.dm_xe_database_session_object_columns AS oc
         ON CAST(oc.event_session_address AS BINARY(8)) = CAST(ac.event_session_address AS BINARY(8))

    INNER JOIN sys.dm_xe_database_session_targets        AS st
         ON CAST(st.event_session_address AS BINARY(8)) = CAST(ac.event_session_address AS BINARY(8))

    INNER JOIN sys.dm_xe_database_sessions               AS se
         ON CAST(ac.event_session_address AS BINARY(8)) = CAST(se.address AS BINARY(8))
WHERE
        oc.column_name = 'occurrence_number'
    AND
        se.name        = 'eventsession_gm_azuresqldb51'
    AND
        ac.action_name = 'sql_text'
ORDER BY
    se.name,
    ev.event_name,
    ac.action_name,
    st.target_name,
    se.session_source
;
GO

---- Step set 6.


ALTER EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE
    STATE = STOP;
GO

---- Step set 7.


ALTER EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE
    DROP TARGET package0.ring_buffer;
GO

---- Step set 8.


DROP EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE;
GO

DROP TABLE tabEmployee;
GO

 

环形缓冲区内容Ring Buffer contents

我们使用 ssms.exe 运行代码示例。We used ssms.exe to run the code sample.

为了查看结果,我们单击了 target_data_XML 列标题下的单元格。To view the results, we clicked the cell under the column header target_data_XML.

然后,在结果窗格中,我们单击了 target_data_XML 列标题下的单元格。Then in the results pane we clicked the cell under the column header target_data_XML. 这个单击动作在 ssms.exe 中按结果单元格内容显示的顺序,以 XML 格式创建了另一个文件选项卡。This click created another file tab in ssms.exe in which the content of the result cell was displayed, as XML.

输出显示在以下块中。The output is shown in the following block. 结果看起来很长,但其实只是两个 <event> 元素。It looks long, but it is just two <event> elements.

 

<RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="2" eventCount="2" droppedCount="0" memoryUsed="1728">
  <event name="sql_statement_starting" package="sqlserver" timestamp="2015-09-22T15:29:31.317Z">
    <data name="state">
      <type name="statement_starting_state" package="sqlserver" />
      <value>0</value>
      <text>Normal</text>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>7</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>184</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>328</value>
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 102</value>
    </data>
    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>
---- Step set 4.


SELECT 'BEFORE_Updates', EmployeeKudosCount, * FROM tabEmployee;

UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 102;

UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 1015;

SELECT 'AFTER__Updates', EmployeeKudosCount, * FROM tabEmployee;
</value>
    </action>
  </event>
  <event name="sql_statement_starting" package="sqlserver" timestamp="2015-09-22T15:29:31.327Z">
    <data name="state">
      <type name="statement_starting_state" package="sqlserver" />
      <value>0</value>
      <text>Normal</text>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>10</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>340</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>486</value>
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 1015</value>
    </data>
    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>
---- Step set 4.


SELECT 'BEFORE_Updates', EmployeeKudosCount, * FROM tabEmployee;

UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 102;

UPDATE tabEmployee
    SET EmployeeKudosCount = EmployeeKudosCount + 1015;

SELECT 'AFTER__Updates', EmployeeKudosCount, * FROM tabEmployee;
</value>
    </action>
  </event>
</RingBufferTarget>

释放环形缓冲区占用的资源Release resources held by your Ring Buffer

处理完环形缓冲区后,可以发出 ALTER 将它删除并释放其资源,如下所示:When you are done with your Ring Buffer, you can remove it and release its resources issuing an ALTER like the following:

ALTER EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE
    DROP TARGET package0.ring_buffer;
GO

事件会话的定义会更新,但不会删除。The definition of your event session is updated, but not dropped. 然后可以将环形缓冲区的另一个实例添加到事件会话:Later you can add another instance of the Ring Buffer to your event session:

ALTER EVENT SESSION eventsession_gm_azuresqldb51
    ON DATABASE
    ADD TARGET
        package0.ring_buffer
            (SET
                max_memory = 500   -- Units of KB.
            );

详细信息More information

有关 Azure SQL 数据库中扩展事件的主要主题是:The primary topic for extended events on Azure SQL Database is:

可通过以下链接访问有关扩展事件的其他代码示例主题。Other code sample topics for extended events are available at the following links. 不过,必须定期检查所有示例,以确定这些示例是针对 Microsoft SQL Server 还是 Azure SQL 数据库。However, you must routinely check any sample to see whether the sample targets Microsoft SQL Server versus Azure SQL Database. 然后,用户可以在运行示例时确定是否要做出细微的更改。Then you can decide whether minor changes are needed to run the sample.