sql server 2008 r2 xevent

--如果已经存在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:monitorLogQuery.xet',  
METADATAFILE = 'S:monitorLongQuery.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:monitorLogQuery_0_129954478780290000.xet',  
 's:monitorLongQuery_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   


------------将XML转换为常规的表格式 
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 ,
      username NVARCHAR(100) NOT NULL,
      client_hostname NVARCHAR(100) NOT NULL,
      sql_text NVARCHAR(MAX) NOT NULL ,
      cpu INT NOT NULL
    )
go


DECLARE @xmlData XML
DECLARE @xmlString NVARCHAR(MAX)
DECLARE @database_id INT
DECLARE @username NVARCHAR(100)
DECLARE @client_hostname NVARCHAR(100)
DECLARE @sql_text NVARCHAR(MAX)
DECLARE @cpu INT

DECLARE myCur CURSOR READ_ONLY
FOR
SELECT TOP 200 event_data --CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file

('s:monitorLogQuery_0_130638808366940000.xet',
 's:monitorLongQuery_0_130638808366940000.xem',NULL,NULL) 

OPEN myCur

FETCH NEXT FROM myCur INTO @xmlString

WHILE @@FETCH_STATUS = 0 

BEGIN
         BEGIN TRY
            SET @xmlData = CAST(@xmlString AS XML)
            --set @cpu = 0
            --获取cpu                                                          
            SET @cpu = @xmlData.query('//data[@name="cpu"]/value').value('(value)[1]',
                                                                  'INT')
                    
            --获取database_id
            SET @database_id = @xmlData.query('//action[@name="database_id"]/value').value('(value)[1]',
                                                                  'INT')
            --获取username                                                  
            SET @username = @xmlData.query('//action[@name="username"]/value').value('(value)[1]',
                                                                  'NVARCHAR(100)')
            --获取hostname                                                  
            SET @client_hostname = @xmlData.query('//action[@name="client_hostname"]/value').value('(value)[1]',
                                                                  'NVARCHAR(100)')
                                                                  
            --获取sql_text
            SET @sql_text = @xmlData.query('//action[@name="sql_text"]/value').value('(value)[1]',
                                                          'NVARCHAR(MAX)')
            

            --开始插入数据
            INSERT #MyData
                    ( database_id, 
                     sql_text, 
                     username,
                      client_hostname,
                      cpu )
            VALUES  ( @database_id, -- database_id - int
                      @sql_text,  -- sql_text - nvarchar(max)
                      @username,
                      @client_hostname,
                      @cpu
                      )
         END TRY
         BEGIN CATCH
         END CATCH
        
        
        FETCH NEXT FROM myCur INTO @xmlString
END
CLOSE myCur
DEALLOCATE myCur



SELECT b.name,a.username,a.client_hostname,a.sql_text,a.cpu FROM #MyData AS a
inner join sys.databases as b
on a.database_id=b.database_id
order by a.cpu desc
go