--如果已经存在Event Session删除IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongQuery')DROP EVENT SESSION MonitorLongQuery ON SERVERGO--创建Extended Event sessionCREATE 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> 10000OR 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')GOSELECT sessions.name AS SessionName,sevents.package as PackageName,sevents.name AS EventName,sevents.predicate, sactions.name AS ActionName, stargets.name AS TargetNameFROM sys.server_event_sessions sessionsINNER JOIN sys.server_event_session_events seventsON sessions.event_session_id= sevents.event_session_idINNER JOIN sys.server_event_session_actions sactionsON sessions.event_session_id= sactions.event_session_idINNER JOIN sys.server_event_session_targets stargetsON sessions.event_session_id= stargets.event_session_idWHERE sessions.name='MonitorLongQuery'GO--启动Event Session捕获数据ALTER EVENT SESSION MonitorLongQueryON SERVER STATE = STARTGO--查询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 SessionALTER EVENT SESSION MonitorLongQueryON SERVER STATE = STOPGO   --删除Event SessionIF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongQuery')DROP EVENT SESSION MonitorLongQuery ON SERVERGO---查看结果以表格形式IF EXISTS ( SELECT  *            FROM    tempdb.dbo.sysobjects            WHERE   id = OBJECT_ID(N'tempdb..#MyData')                    AND type = 'U' )    DROP TABLE #MyDatagoCREATE TABLE #MyData    (      database_id INT NOT NULL ,      sql_text NVARCHAR(MAX) NOT NULL    )goDECLARE @xmlData XMLDECLARE @database_id INTDECLARE @sql_text NVARCHAR(MAX)DECLARE myCur CURSOR READ_ONLYFORSELECT  CAST(event_data AS XML) event_dataFROM sys.fn_xe_file_target_read_file('s:\monitor\LogQuery_0_129954594949480000.xet', 's:\monitor\LongQuery_0_129954594949480000.xem',NULL,NULL)OPEN myCurFETCH NEXT FROM myCur INTO @xmlDataWHILE @@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    ENDCLOSE myCurDEALLOCATE myCurSELECT * FROM #MyData WHERE sql_textgo