利用XEvent 监控SQL Server Log文件对磁盘的写操作大小是多少

利用XEvent 监控SQL Server Log文件对磁盘的写操作大小是多少

http://www.cnblogs.com/nzperfect/p/3748092.html

本篇是上一篇SQL Server Log文件对磁盘的写操作大小是多少的续，使用XEvent收集SQL Server Data文件和Log文件的写大小，脚本如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

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网友提醒。