原文:
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：
Data File Write Size ：
?
用XEvent收集，的確要比之前的方法簡單很多，此方法感謝一位Cookies_Tang網友提醒。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
