--如果已经存在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
转载于:https://blog.51cto.com/ocpyang/1199743