次の方法で共有

简要介绍SQL Server 2008新的事件处理系统——SQL Server Extended Events

  • [アーティクル]

SQL Server Extended Events（下面简称XEvent）是SQL Server 2008里新加的事件处理系统，用来取代SQL Server原先的SQL Trace的跟踪机制。事件处理系统对一个复杂服务器系统的排错，调试是极为关键的。和SQL Server原来的事件处理系统相比较，XEvent具有下列的优势：

  • 消耗更少的系统资源，更适用于在产品服务器上的排错和调试。并且每收集一个系统事件所消耗的资源都是可预测的。
  • 不仅仅能收集事件数据，还能收集在这事件触发点的系统动态运行信息，例如内存，T-SQL Stack等等。
  • 可配置性，能够根据系统负载的需求配置所需收集的事件信息。

下面是一个简单的例子介绍如何使用XEvent来做系统排错工作。

当前台应用性能下降的时候，数据库管理员经常需要确定是否有资源锁的申请冲突，既所谓我们说的blocking。在SQL Server 2008里面，你可以查询系统视图sys.dm_exec_requests，并且能发现有一些进程正在等待资源锁，比如像下面这个结果：

 

我们可以看到有个Session 54的状态是Suspended,意味着一个查询的操作被blocked, 但是我们看不到是哪一个Query占用了资源，以及查询计划和T-SQL Stack等等，如果我有下面的XEvent Session我们就能够解决这个问题。（在下面的例子中我们假设XEvent Session在Block发生之前已经启动好了）

1、在查询编辑器执行下面的命令，这会创建一个XEvent Session来捕捉lock_acquired和lock_released，同时我们还会抓下sql_text，tsql_stack和plan_handle等信息。

-- Perform cleanup.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FindBlockers')

    DROP EVENT SESSION FindBlockers ON SERVER

GO

-- 我们这边假设是数据库”DAA”里出了block,所以我们会使用一个predicate去过滤掉其他数据库的Block问题

--

DECLARE @dbid int

SELECT @dbid = db_id('DAA')

IF @dbid IS NULL

BEGIN

    RAISERROR('DAA is not installed. Install DAA before proceeding', 17, 1)

    RETURN

END

DECLARE @sql nvarchar(1024)

SET @sql = '

CREATE EVENT SESSION FindBlockers ON SERVER

--这里我们加了lock_acquired的Event,并要求返回sql_text，database_id,tsql_stack, --plan_handle和session_id的信息

--

ADD EVENT sqlserver.lock_acquired

    (action

        ( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,

         sqlserver.plan_handle, sqlserver.session_id)

    WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0)

    ),

ADD EVENT sqlserver.lock_released

    (WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0 ))

ADD TARGET package0.pair_matching

    ( SET begin_event=''sqlserver.lock_acquired'',

            begin_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',

            end_event=''sqlserver.lock_released'',

            end_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',

    respond_to_memory_pressure=1)

WITH (max_dispatch_latency = 1 seconds)'

EXEC (@sql)

--

-- Create the metadata for the event session

-- Start the event session

--

ALTER EVENT SESSION FindBlockers ON SERVER

STATE = START

2、一旦XEVENT然后我们可以执行下面的语句去找到那些block了别人的查询语句： 

--

-- The pair matching targets report current unpaired events using

-- the sys.dm_xe_session_targets dynamic management view (DMV)

-- in XML format.

-- The following query retrieves the data from the DMV and stores

-- key data in a temporary table to speed subsequent access and

-- retrieval.

--

SELECT

objlocks.value('(action/value)[5]', 'int')

        AS session_id,

    objlocks.value('(data/value)[5]', 'int')

        AS database_id,

    objlocks.value('(data/text)[1]', 'nvarchar(50)' )

        AS resource_type,

    objlocks.value('(data/value)[9]', 'bigint')

        AS resource_0,

    objlocks.value('(data/value)[10]', 'bigint')

        AS resource_1,

    objlocks.value('(data/value)[11]', 'bigint')

        AS resource_2,

    objlocks.value('(data/text)[2]', 'nvarchar(50)')

        AS mode,

    objlocks.value('(action/value)[1]', 'varchar(MAX)')

        AS sql_text,

    CAST(objlocks.value('(action/value)[4]', 'varchar(MAX)') AS xml)

        AS plan_handle,

    CAST(objlocks.value('(action/value)[3]', 'varchar(MAX)') AS xml)

        AS tsql_stack

INTO #unmatched_locks

FROM (

    SELECT CAST(xest.target_data as xml)

        lockinfo

    FROM sys.dm_xe_session_targets xest

    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address

    WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'

) heldlocks

CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

--

-- Join the data acquired from the pairing target with other

-- DMVs to return provide additional information about blockers

--

SELECT ul.*

    FROM #unmatched_locks ul

    INNER JOIN sys.dm_tran_locks tl ON ul.database_id = tl.resource_database_id AND ul.resource_type = tl.resource_type

    WHERE resource_0 IS NOT NULL

    AND session_id IN

        (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)

    AND tl.request_status='wait'

上面的语句返回下面的结果，我们可以看到是一个Session 53的Update更新语句Block了Session 54的查询： 

有下面sql_text里可以看到，整个更新语句begin tran Update BlockTest set Col1 = 'Updated' where ID = 1中，由于少了一个Commit tran，所以block了其他查询对表BlockTest的操作，

XEvent还有很多强大的功能和更广应用，有机会的话以后再为大家介绍。

项目经理 林默

Comments

  • Anonymous
    May 20, 2009
    尊敬的各位读者， 首先感谢您阅读我们这篇博客摘要。如果您对其中一篇博文有任何想法或疑问，请直接在相关博文后面直接留言，以便负责博客的相关研发工程师能給于及时跟踪或答复。如果您的问题与我们的博文内容没有直接的联系，建议您直接在微软中文技术论坛（

  • Anonymous
    November 25, 2010
    Hi,我想请教一下，SQL Server Extended Events功能可以捕获有关hash join 中的相关信息吗？比如说在执行hash join 过程中，内存中详细的buckets 的分布情况， 就好像下面的URL中显示的信息，www.cnblogs.com/.../1832109.html 遗憾的是这个功能在MYSQL 跟oracle中才有，我始终没有SQL Server中发现有类似的功能。

  • Anonymous
    December 21, 2010
    SQL Server Extended Events到目前为止没有类似信息，我会向产品组反馈您的意见