|
/*******
? 和2008 R2有小区别
*****/
?
CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(SET collect_statement=(1)
? ACTION
? (
? sqlserver.client_app_name,
? sqlserver.client_hostname,
? sqlserver.database_id,
? sqlserver.database_name,
? sqlserver.sql_text,sqlserver.username
? )
??? WHERE ([cpu_time]>(10000))
? )
ADD TARGET package0.asynchronous_file_target
(
?? SET filename='s:monitortest.xel',
?? metadatafile='s:monitortest.xem'
?? )
WITH
(
MAX_MEMORY=1048576 KB,
STARTUP_STATE=ON
)
GO
?
--SELECT *,CAST(event_data as XML) AS 'event_data_XML'
--FROM sys.fn_xe_file_target_read_file
--(
--'s:monitortest*.xel',
-- 's:monitortest*.xem',NULL,NULL
-- );
IF EXISTS ( SELECT? *
??????????? FROM??? tempdb.dbo.sysobjects
??????????? WHERE?? id = OBJECT_ID(N'tempdb..#MyData')
??????????????????? AND type = 'U' )
??? DROP TABLE #MyData
go
CREATE TABLE #MyData
??? (
????? database_id INT NOT NULL,
????? username NVARCHAR(100) NOT NULL,
????? client_hostname NVARCHAR(100) NOT NULL,
?? client_app_name NVARCHAR(100) NOT NULL,
????? sql_text NVARCHAR(MAX) NOT NULL,
????? cpu_time INT NOT NULL
??? )
go
DECLARE @xmlData XML
DECLARE @xmlString NVARCHAR(MAX)
DECLARE @database_id INT
DECLARE @username NVARCHAR(100)
DECLARE @client_hostname NVARCHAR(100)
DECLARE @client_app_name NVARCHAR(100)
DECLARE @sql_text NVARCHAR(MAX)
DECLARE @cpu_time INT
DECLARE myCur CURSOR READ_ONLY
FOR
SELECT TOP 200 event_data --CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file
('s:monitortest*.xel',
?'s:monitortest*.xem',NULL)
OPEN myCur
FETCH NEXT FROM myCur INTO @xmlString
WHILE @@FETCH_STATUS = 0
BEGIN
?? BEGIN TRY
????? SET @xmlData = CAST(@xmlString AS XML)
????? --set @cpu = 0
????? --获取cpu???????????????
???SET @cpu_time = @xmlData.query('//data[@name="cpu_time"]/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)')
???--获取client_app_name?????????????
??????????? SET @client_app_name = @xmlData.query('//action[@name="client_app_name"]/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,
?????? client_app_name,
?????? cpu_time )
???VALUES? ( @database_id,-- database_id - int
?????? @sql_text,? -- sql_text - nvarchar(max)
?????? @username,
?????? @client_hostname,
?????? @client_app_name,
?????? @cpu_time
?????? )
?? END TRY
?? BEGIN CATCH
?? END CATCH
??
??
??????? FETCH NEXT FROM myCur INTO @xmlString
END
CLOSE myCur
DEALLOCATE myCur
?
SELECT b.name,a.username,a.client_hostname,A.client_app_name,a.sql_text,a.cpu_time FROM #MyData AS a inner join sys.databases as b on a.database_id=b.database_id order by a.cpu_time desc go
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
