XEvent – SQL Server Log文件对磁盘的写操作大小是多少
时间：2022-03-10 17:20
原文:XEvent – SQL Server Log文件对磁盘的写操作大小是多少
本篇是上一篇SQL Server Log文件对磁盘的写操作大小是多少的续，使用XEvent收集SQL Server Data文件和Log文件的写大小，脚本如下：
?
DECLARE @DBNAME VARCHAR(256)
SET
@DBNAME = ‘myDB‘
DECLARE @sqlcmd NVARCHAR(MAX) = ‘IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE
name=‘‘filewritecompleted‘‘)
DROP EVENT SESSION [filewritecompleted] ON SERVER;
CREATE EVENT SESSION [filewritecompleted]
ON SERVER
ADD EVENT sqlserver.file_write_completed
(WHERE (database_id = ‘
+ CAST(DB_ID(‘‘+@DBNAME+‘‘) AS
VARCHAR(3))
+ ‘))
ADD TARGET package0.asynchronous_file_target(
SET filename=‘‘D:\XEvent\filewritecompleted.xel‘‘,
metadatafile=‘‘D:\XEvent\filewritecompleted.xem‘‘)
WITH (MAX_MEMORY = 8192KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, STARTUP_STATE = ON)‘
EXEC(@sqlcmd)
GO
ALTER EVENT SESSION filewritecompleted
ON SERVER
STATE=START
GO
WAITFOR DELAY ‘00:05:00‘
ALTER EVENT SESSION filewritecompleted
ON SERVER
STATE=STOP
GO
USE tempdb
GO
SELECT
CAST(event_data AS
XML) AS event_data
INTO
TargetEvents
FROM
sys.fn_xe_file_target_read_file(‘D:\XEvent\filewritecompleted*.xel‘,
‘D:\XEvent\filewritecompleted*.xem‘, NULL,
NULL)
SELECT
event_data.value(‘(event/@name)[1]‘, ‘varchar(50)‘) AS
event_name ,
DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
event_data.value(‘(event/@timestamp)[1]‘, ‘datetime2‘)) AS
[timestamp] ,
COALESCE(event_data.value(‘(event/data[@name="database_id"]/value)[1]‘,
‘int‘),
event_data.value(‘(event/action[@name="database_id"]/value)[1]‘,
‘int‘)) AS
database_id ,
event_data.value(‘(event/data[@name="mode"]/text)[1]‘,
‘nvarchar(4000)‘) AS
[mode] ,
event_data.value(‘(event/data[@name="file_handle"]/value)[1]‘,
‘nvarchar(4000)‘) AS
[file_handle] ,
event_data.value(‘(event/data[@name="offset"]/value)[1]‘, ‘bigint‘) AS
[offset] ,
event_data.value(‘(event/data[@name="file_id"]/value)[1]‘, ‘int‘) AS
[file_id] ,
event_data.value(‘(event/data[@name="filegroup_id"]/value)[1]‘, ‘int‘) AS
[filegroup_id] ,
event_data.value(‘(event/data[@name="size"]/value)[1]‘, ‘bigint‘) AS
[size]
INTO
Results
FROM
TargetEvents
select
* from Results
--Log File Write Size
SELECT
size
,
COUNT(*) AS
cnt ,
LTRIM(CAST(COUNT(*) * 1.0 / ( SELECT
COUNT(*)
FROM
Results
WHERE
file_id = 2
) * 100 AS
NUMERIC(18, 4))) + ‘%‘
AS
ratio
FROM
Results
WHERE
file_id = 2
GROUP BY
size
ORDER BY CAST(COUNT(*) * 1.0 / ( SELECT
COUNT(*)
FROM
Results
WHERE
file_id = 2
) * 100 AS
NUMERIC(18, 4)) desc
--Data File Write Size
SELECT
size
,
COUNT(*) AS
cnt ,
LTRIM(CAST(COUNT(*) * 1.0 / ( SELECT
COUNT(*)
FROM
Results
WHERE
file_id != 2
) * 100 AS
NUMERIC(18, 4))) + ‘%‘
AS
ratio
FROM
Results
WHERE
file_id != 2
GROUP BY
size
ORDER BY CAST(COUNT(*) * 1.0 / ( SELECT
COUNT(*)
FROM
Results
WHERE
file_id = 2
) * 100 AS
NUMERIC(18, 4)) desc
--DROP TABLE tempdb.dbo.TargetEvents, tempdb.dbo.Results |
在一个OLTP结果如下：
Log File Write Size：
用XEvent收集，的确要比之前的方法简单很多，此方法感谢一位Cookies_Tang网友提醒。
XEvent – SQL Server Log文件对磁盘的写操作大小是多少,布布扣,bubuko.com
