sqlserver 2008 R2扩展事件

--如果已经存在Event Session删除
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongQuery')
DROP EVENT SESSION MonitorLongQuery ON SERVER
GO
--创建Extended Event session
CREATE EVENT SESSION MonitorLongQuery ON SERVER
--增加Event(SQL完成事件)
ADD EVENT sqlserver.sql_statement_completed
(
--指定收集的Event信息
ACTION
(
sqlserver.database_id,
sqlserver.session_id,
sqlserver.username,
sqlserver.client_hostname,
sqlserver.sql_text,
sqlserver.tsql_stack
)
--Filter信息（CPU超过或者整个运行时间超过10S）
WHERE sqlserver.sql_statement_completed.cpu> 10000
OR sqlserver.sql_statement_completed.duration> 10000
)
--指定收集的Event信息储存位置（可以存储到内存也可以到文件）
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = N's:\monitor\LogQuery.xet',
METADATAFILE = 'S:\monitor\LongQuery.xem'
)
GO
SELECT sessions.name AS SessionName,sevents.package as PackageName,
sevents.name AS EventName,
sevents.predicate, sactions.name AS ActionName, stargets.name AS TargetName
FROM sys.server_event_sessions sessions
INNER JOIN sys.server_event_session_events sevents
ON sessions.event_session_id= sevents.event_session_id
INNER JOIN sys.server_event_session_actions sactions
ON sessions.event_session_id= sactions.event_session_id
INNER JOIN sys.server_event_session_targets stargets
ON sessions.event_session_id= stargets.event_session_id
WHERE sessions.name='MonitorLongQuery'
GO
--启动Event Session捕获数据
ALTER EVENT SESSION MonitorLongQuery
ON SERVER STATE = START
GO
--查询
SELECT CAST(event_data AS XML) event_data,*
FROM sys.fn_xe_file_target_read_file
('s:\monitor\LogQuery_0_129954478780290000.xet',
 's:\monitor\LongQuery_0_129954478780330000.xem',NULL,NULL)
go
-停掉Event Session
ALTER EVENT SESSION MonitorLongQuery
ON SERVER STATE = STOP
GO
   
--删除Event Session
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongQuery')
DROP EVENT SESSION MonitorLongQuery ON SERVER
GO
---查看结果以表格形式
IF EXISTS ( SELECT  *
            FROM    tempdb.dbo.sysobjects
            WHERE   id = OBJECT_ID(N'tempdb..#MyData')
                    AND type = 'U' )
    DROP TABLE #MyData
go
CREATE TABLE #MyData
    (
      database_id INT NOT NULL ,
      sql_text NVARCHAR(MAX) NOT NULL
    )
go
DECLARE @xmlData XML
DECLARE @database_id INT
DECLARE @sql_text NVARCHAR(MAX)
DECLARE myCur CURSOR READ_ONLY
FOR
SELECT  CAST(event_data AS XML) event_data
FROM sys.fn_xe_file_target_read_file
('s:\monitor\LogQuery_0_129954594949480000.xet',
 's:\monitor\LongQuery_0_129954594949480000.xem',NULL,NULL)
OPEN myCur
FETCH NEXT FROM myCur INTO @xmlData
WHILE @@FETCH_STATUS = 0
    BEGIN
        --获取database_id
        SET @database_id = @xmlData.query('//action[@name="database_id"]/value').value('(value)[1]',
                                                              'INT')
        --获取sql_text
        SET @sql_text = @xmlData.query('//action[@name="sql_text"]/value').value('(value)[1]',
                                                              'NVARCHAR(MAX)')
        --开始插入数据
        INSERT #MyData
                ( database_id, sql_text )
        VALUES  ( @database_id, -- database_id - int
                  @sql_text  -- sql_text - nvarchar(max)
                  )
          
        FETCH NEXT FROM myCur INTO @xmlData
    END
CLOSE myCur
DEALLOCATE myCur
SELECT * FROM #MyData WHERE sql_text
go