-
Notifications
You must be signed in to change notification settings - Fork 30
Expand file tree
/
Copy pathgetdatabaseio.sql
More file actions
120 lines (105 loc) · 4.22 KB
/
getdatabaseio.sql
File metadata and controls
120 lines (105 loc) · 4.22 KB
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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @secondsBetween tinyint = 5;
DECLARE @delayInterval char(8) = CONVERT(Char(8), DATEADD(SECOND, @secondsBetween, '00:00:00'), 108);
IF OBJECT_ID('tempdb..#baseline') IS NOT NULL
DROP TABLE #baseline;
IF OBJECT_ID('tempdb..#baselinewritten') IS NOT NULL
DROP TABLE #baselinewritten;
SELECT DB_NAME(mf.database_id) AS databaseName ,
mf.physical_name ,
divfs.num_of_reads ,
divfs.num_of_bytes_read ,
divfs.io_stall_read_ms ,
divfs.num_of_writes ,
divfs.num_of_bytes_written ,
divfs.io_stall_write_ms ,
divfs.io_stall ,
size_on_disk_bytes ,
GETDATE() AS baselineDate
INTO #baseline
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
INNER JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
WAITFOR DELAY @delayInterval;
;WITH currentLine AS
(
SELECT DB_NAME(mf.database_id) AS databaseName ,
mf.physical_name ,
type_desc,
num_of_reads ,
num_of_bytes_read ,
io_stall_read_ms ,
num_of_writes ,
num_of_bytes_written ,
io_stall_write_ms ,
io_stall ,
size_on_disk_bytes ,
GETDATE() AS currentlineDate
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
INNER JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
)
SELECT database_name
, datafile_type
, num_of_bytes_read_persec = SUM(num_of_bytes_read_persec)
, num_of_bytes_written_persec = SUM(num_of_bytes_written_persec)
INTO #baselinewritten
FROM
(
SELECT
database_name = currentLine.databaseName
, datafile_type = type_desc
, num_of_bytes_read_persec = (currentLine.num_of_bytes_read - T1.num_of_bytes_read) / (1 * DATEDIFF(SECOND,baseLineDate,currentLineDate))
, num_of_bytes_written_persec = (currentLine.num_of_bytes_written - T1.num_of_bytes_written) / (1 * DATEDIFF(SECOND,baseLineDate,currentLineDate))
FROM currentLine
INNER JOIN #baseline T1 ON T1.databaseName = currentLine.databaseName
AND T1.physical_name = currentLine.physical_name
) as T
GROUP BY database_name, datafile_type
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX), @ColumnName2 AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(database_name)
FROM (SELECT DISTINCT database_name FROM #baselinewritten) AS bl
SELECT @ColumnName2= ISNULL(@ColumnName2+ '+','') + ''',' + database_name + '=''' + + ' + CAST(' + QUOTENAME(database_name) + ' as varchar(16))'
FROM (SELECT DISTINCT database_name FROM #baselinewritten) AS bl
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = N'
SELECT ''DatabaseIO'' + '',servername='' + REPLACE(@@SERVERNAME, ''\'', '':'') + '''' + '',type=DatabaseLogBytesWritten''
+ '' '' + ' + STUFF(@ColumnName2, 1, 2, '''') + ' FROM
(
SELECT database_name, num_of_bytes_written_persec
FROM #baselinewritten
WHERE datafile_type = ''LOG''
) as V
PIVOT(SUM(num_of_bytes_written_persec) FOR database_name IN (' + @ColumnName + ')) AS PVTTable
UNION ALL
SELECT ''DatabaseIO'' + '',servername='' + REPLACE(@@SERVERNAME, ''\'', '':'') + '''' + '',type=DatabaseRowsBytesWritten''
+ '' '' + ' + STUFF(@ColumnName2, 1, 2, '''') + ' FROM
(
SELECT database_name, num_of_bytes_written_persec
FROM #baselinewritten
WHERE datafile_type = ''ROWS''
) as V
PIVOT(SUM(num_of_bytes_written_persec) FOR database_name IN (' + @ColumnName + ')) AS PVTTable
UNION ALL
SELECT ''DatabaseIO'' + '',servername='' + REPLACE(@@SERVERNAME, ''\'', '':'') + '''' + '',type=DatabaseLogBytesRead''
+ '' '' + ' + STUFF(@ColumnName2, 1, 2, '''') + ' FROM
(
SELECT database_name, num_of_bytes_read_persec
FROM #baselinewritten
WHERE datafile_type = ''LOG''
) as V
PIVOT(SUM(num_of_bytes_read_persec) FOR database_name IN (' + @ColumnName + ')) AS PVTTable
UNION ALL
SELECT ''DatabaseIO'' + '',servername='' + REPLACE(@@SERVERNAME, ''\'', '':'') + '''' + '',type=DatabaseRowsBytesRead''
+ '' '' + ' + STUFF(@ColumnName2, 1, 2, '''') + ' FROM
(
SELECT database_name, num_of_bytes_read_persec
FROM #baselinewritten
WHERE datafile_type = ''ROWS''
) as V
PIVOT(SUM(num_of_bytes_read_persec) FOR database_name IN (' + @ColumnName + ')) AS PVTTable
'
--PRINT @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery;