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 WHEREname=''filewritecompleted'')DROP EVENT SESSION [filewritecompleted] ON SERVER;CREATE EVENT SESSION [filewritecompleted]ON SERVERADD 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)GOALTER EVENT SESSION filewritecompletedON SERVERSTATE=STARTGOWAITFOR DELAY '00:05:00'ALTER EVENT SESSION filewritecompletedON SERVERSTATE=STOPGOUSE tempdbGOSELECT CAST(event_data AS xml) AS event_dataINTO TargetEventsFROM 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 ResultsFROM TargetEventsselect * from Results--Log File Write SizeSELECT size ,COUNT(*) AS cnt ,LTRIM(CAST(COUNT(*) * 1.0 / ( SELECT COUNT(*)FROM ResultsWHERE file_id = 2) * 100 AS NUMERIC(18, 4))) + '%' AS ratioFROM ResultsWHERE file_id = 2GROUP BY sizeORDER BY CAST(COUNT(*) * 1.0 / ( SELECT COUNT(*)FROM ResultsWHERE file_id = 2) * 100 AS NUMERIC(18, 4)) desc--Data File Write SizeSELECT size ,COUNT(*) AS cnt ,LTRIM(CAST(COUNT(*) * 1.0 / ( SELECT COUNT(*)FROM ResultsWHERE file_id != 2) * 100 AS NUMERIC(18, 4))) + '%' AS ratioFROM ResultsWHERE file_id != 2GROUP BY sizeORDER BY CAST(COUNT(*) * 1.0 / ( SELECT COUNT(*)FROM ResultsWHERE file_id = 2) * 100 AS NUMERIC(18, 4)) desc--DROP TABLE tempdb.dbo.TargetEvents, tempdb.dbo.Results

在一个OLTP结果如下：

Log File Write Size：

Data File Write Size ：

用XEvent收集，的确要比之前的方法简单很多，此方法感谢一位Cookies_Tang网友提醒。