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

Source: Internet
Author: User

--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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.