實戰:sqlserver 2008 擴充事件-XML轉換為標準的table格式

標籤：sqlserver擴充事件   sqlserver 2008r2擴充事件   擴充事件xml   sql server extended   

--如果已經存在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_idSET @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_textSET @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

 

實戰:sqlserver 2008 擴充事件-XML轉換為標準的table格式