--如果已经存在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 


------------将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:\monitor\LogQuery_0_130638808366940000.xet',
 's:\monitor\LongQuery_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