sql server 2008 r2 xevent
时间：2021-07-01 10:21:17
帮助过：2人阅读
如果已经存在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 #MyData
go
CREATE 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
)
go
DECLARE @xmlData XML
DECLARE @xmlString NVARCHAR(
MAX)
DECLARE @database_id INT
DECLARE @username NVARCHAR(
100)
DECLARE @client_hostname NVARCHAR(
100)
DECLARE @sql_text NVARCHAR(
MAX)
DECLARE @cpu 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:\monitor\LogQuery_0_130638808366940000.xet‘,
‘s:\monitor\LongQuery_0_130638808366940000.xem‘,
NULL,
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 = @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 @xmlString
END
CLOSE myCur
DEALLOCATE myCur
SELECT b.name,a.username,a.client_hostname,a.sql_text,a.cpu
FROM #MyData
AS a
inner join sys.databases
as b
on a.database_id
=b.database_id
order by a.cpu
desc
go
sql server 2008 r2 xevent
标签：
