Actual combat: SQL Server 2008 extended event-xml converted to standard table format

--If the event session already exists delete the IF EXISTS (SELECT * from sys.server_event_sessions WHERE name= ' monitorlongquery ') drop event S Ession monitorlongquery on Server GO--Creating Extended Event Session Create event session monitorlongquery on server- -Added event (SQL completion event) Add Event sqlserver.sql_statement_completed (---Specifies the event information to be collected ACTION (sqlserver.database_id, S qlserver.session_id, Sqlserver.username, Sqlserver.client_hostname, Sqlserver.sql_text, Sqlserver.tsql_stack)- -filter information (CPU exceeds or the entire run time exceeds 10S) WHERE sqlserver.sql_statement_completed.cpu> 10000 or Sqlserver.sql_statement_  Completed.duration> 10000)--Specify the storage location of the event information to be collected (can be stored in memory or to a file) ADD TARGET package0.asynchronous_file_target (SET FILENAME = N ' S:\monitor\LogQuery.xet ', metadatafile = ' S:\monitor\LongQuery.xem ') GO SELECT sessions.name as Ses Sionname,sevents.package as PackageName, sevents.name as EventName, Sevents.predicate, sactions.name as ActionName, star Gets.name as TargetName fromSys.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--Start event session capture Data ALTER event session M Onitorlongquery on SERVER state = START GO--Query 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-stop event session ALTER event session monitorlongquery on SERVER state = stop Go --Delete event Session IF EXISTS (SELECT * from sys.server_event_sessions WHERE name= ' monitorlongquery ') drop event S Ession monitorlongquery on SERVER GO------------the XMLConvert to a regular tabular 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 N ULL, username NVARCHAR (+) NOT NULL, Client_hostname NVARCHAR (+) NOT NULL, Sql_text NVARCHAR (MAX) not N ULL, CPU INT not NULL) godeclare @xmlData xmldeclare @xmlString NVARCHAR (MAX) DECLARE @database_id intdeclare @use Rname NVARCHAR (+) DECLARE @client_hostname NVARCHAR (+) DECLARE @sql_text NVARCHAR (MAX) DECLARE @cpu intdeclare mycur CURSOR read_onlyforselect TOP 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 to @xmlStringWHILE @ @FETCH_STATUS = 0 BEGIN begin TRY SET @xmlData = CAST (@xmlString as XML)--set @cpu = 0--Gets the CPU SET @cpu = @xmlData. Query('//data[@name = ' cpu ']/value '). Value (' (value) [1] ', ' INT ')--get database_idset @database_id = @xmlData. Query ('//actio n[@name = "database_id"]/value '). Value (' (value) [1] ', ' INT ')--get username SET @username = @xmlData. Query ('//acti on[@name = "username"]/value '). Value (' (value) [1] ', ' NVARCHAR (100) ')--get hostname SET @client_hostname = @xmlDat A.query ('//action[@name = "Client_hostname"]/value '). Value (' (value) [1] ', ' NVARCHAR (100) ')--Get Sql_textset @sql_text = @xmlData. Query ('//action[@name = ' sql_text ']/value '). Value (' (value) [1] ', ' NVARCHAR (MAX) ')--begins inserting the data insert #MyData ( database_id, Sql_text, username, client_hostname, CPU) VALUES (@database_id,--Database_id-int @sql_text,--sq L_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 

Actual combat: SQL Server 2008 extended event-xml converted to standard table format