--如果已经存在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 #mydatagocreate 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 )godeclare @xmldata xmldeclare @xmlstring nvarchar(max)declare @database_id intdeclare @username nvarchar(100)declare @client_hostname nvarchar(100)declare @sql_text nvarchar(max)declare @cpu intdeclare mycur cursor read_onlyforselect 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 mycurfetch next from mycur into @xmlstringwhile @@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 @xmlstringendclose mycurdeallocate mycurselect b.name,a.username,a.client_hostname,a.sql_text,a.cpu from #mydata as ainner join sys.databases as bon a.database_id=b.database_idorder by a.cpu descgo