利用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：

image

Data File Write Size ：

image

 

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

 
posted @ 2014-05-29 22:34  桦仔  阅读(242)  评论(0)    收藏  举报