-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathgo-zabbix-mssql.config.yaml
113 lines (112 loc) · 9.84 KB
/
go-zabbix-mssql.config.yaml
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
---
sql_metrics: [
"SELECT 'db_state_ONLINE_total', Sum(CASE WHEN d.state = 0 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_state_RESTORING_total', Sum(CASE WHEN d.state = 1 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_state_RECOVERING_total', Sum(CASE WHEN d.state = 2 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_state_RECOVERY_PENDING_total', Sum(CASE WHEN d.state = 3 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_state_SUSPECT_total', Sum(CASE WHEN d.state = 4 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_state_EMERGENCY_total', Sum(CASE WHEN d.state = 5 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_state_OFFLINE_total', Sum(CASE WHEN d.state = 6 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_recovery_model_FULL_total', Sum(CASE WHEN d.recovery_model = 1 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_recovery_model_BULK_LOGGED_total', Sum(CASE WHEN d.recovery_model = 2 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_recovery_model_SIMPLE_total', Sum(CASE WHEN d.recovery_model = 3 THEN 1 ELSE 0 END) FROM sys.databases d",
"SELECT 'db_perf_point_in_time_recovery', CASE WHEN 1 > 1.0 * COUNT(*) / NULLIF((SELECT COUNT(*) FROM sys.databases d WHERE database_id > 4), 0) THEN 0 ELSE 1 END FROM sys.databases d WHERE database_id > 4 AND recovery_model IN (1)",
"SELECT 'db_perf_page_file_usage_percent', CAST(100 * (1 - available_page_file_kb * 1. / total_page_file_kb) as decimal(9,2)) FROM sys.dm_os_sys_memory",
"SELECT 'db_perf_connection_memory_per_connection_bytes', CAST((cntr_value / (SELECT 1.0 * cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'User Connections')) * 1024 as int) FROM sys.dm_os_performance_counters WHERE counter_name = 'Connection Memory (KB)'",
"SELECT 'db_perf_available_physical_memory_bytes', available_physical_memory_kb * 1024 FROM sys.dm_os_sys_memory",
"SELECT 'db_perf_signal_wait_percent', CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) FROM sys.dm_os_wait_stats",
"SELECT 'db_perf_sql_compilation_per_batch_request', 100.0 * cntr_value / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec') FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Compilations/sec'",
"SELECT 'db_perf_sql_recompilation_per_batch_request', 100.0 *cntr_value / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec') FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Re-Compilations/sec'",
"SELECT 'db_perf_page_lookup_per_batch_request', 100.0 * cntr_value / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec') FROM sys.dm_os_performance_counters WHERE counter_name = 'Page lookups/sec'",
"SELECT 'db_perf_page_split_per_batch_request', 100.0 * cntr_value / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec') FROM sys.dm_os_performance_counters WHERE counter_name = 'Page splits/sec'",
"SELECT 'db_perf_average_tasks', (SELECT AVG(current_tasks_count) FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255)",
"SELECT 'db_perf_average_runnable_tasks', (SELECT AVG(runnable_tasks_count) FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255)",
"SELECT 'db_perf_average_pending_disk_io', (SELECT AVG(pending_disk_io_count) FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255)",
"SELECT 'db_perf_buffer_pool_rate_bytes_per_sec', (1.0*cntr_value * 8 * 1024) / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE object_name like '%Buffer Manager%' AND lower(counter_name) = 'Page life expectancy') FROM sys.dm_os_performance_counters WHERE object_name like '%Buffer Manager%' AND counter_name = 'database pages'",
"SELECT 'db_perf_memory_grants_pending', cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Memory Grants Pending'",
"SELECT 'db_perf_readahead_per_page_read', 100.0 *cntr_value / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page Reads/sec') FROM sys.dm_os_performance_counters WHERE counter_name = 'Readahead pages/sec'",
"SELECT 'db_perf_total_target_memory_ratio', 100.0 * cntr_value / (SELECT 1.0*cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)') FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'",
"SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @secondsBetween tinyint = 5
DECLARE @delayInterval char(8) = CONVERT(Char(8), DATEADD(SECOND, 5, '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_bytes_read,
divfs.num_of_bytes_written,
divfs.num_of_reads,
divfs.num_of_writes,
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
, type_desc
, mf.physical_name
, divfs.num_of_bytes_read
, divfs.num_of_bytes_written
, divfs.num_of_reads
, divfs.num_of_writes
, 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)
, num_of_reads_persec = SUM(num_of_reads_persec)
, num_of_writes_persec = SUM(num_of_writes_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) / (DATEDIFF(SECOND,baselinedate,currentlinedate))
, num_of_bytes_written_persec = (currentLine.num_of_bytes_written - T1.num_of_bytes_written) / (DATEDIFF(SECOND,baselinedate,currentlinedate))
, num_of_reads_persec = (currentLine.num_of_reads - T1.num_of_reads) / (DATEDIFF(SECOND,baselinedate,currentlinedate))
, num_of_writes_persec = (currentLine.num_of_writes - T1.num_of_writes) / (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
SELECT 'db_io_log_writes_bytes_per_sec_' + V.database_name, 0 + V.num_of_bytes_written_persec FROM ( SELECT database_name, num_of_bytes_written_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_log_writes_bytes_per_sec_total', SUM(V.num_of_bytes_written_persec) FROM ( SELECT num_of_bytes_written_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_rows_writes_bytes_per_sec_' + V.database_name, 0 + V.num_of_bytes_written_persec FROM ( SELECT database_name, num_of_bytes_written_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_rows_writes_bytes_per_sec_total', SUM(V.num_of_bytes_written_persec) FROM ( SELECT num_of_bytes_written_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_log_reads_bytes_per_sec_' + V.database_name, 0 + V.num_of_bytes_read_persec FROM ( SELECT database_name, num_of_bytes_read_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_log_reads_bytes_per_sec_total', SUM(V.num_of_bytes_read_persec) FROM ( SELECT num_of_bytes_read_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_rows_reads_bytes_per_sec_' + V.database_name, 0 + V.num_of_bytes_read_persec FROM ( SELECT database_name, num_of_bytes_read_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_rows_reads_bytes_per_sec_total', SUM(V.num_of_bytes_read_persec) FROM ( SELECT num_of_bytes_read_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_log_writes_per_sec_' + V.database_name, 0 + V.num_of_writes_persec FROM ( SELECT database_name, num_of_writes_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_log_writes_per_sec_total', SUM(V.num_of_writes_persec) FROM ( SELECT num_of_writes_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_rows_writes_per_sec_' + V.database_name, 0 + V.num_of_writes_persec FROM ( SELECT database_name, num_of_writes_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_rows_writes_per_sec_total', SUM(V.num_of_writes_persec) FROM ( SELECT num_of_writes_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_log_reads_per_sec_' + V.database_name, 0 + V.num_of_reads_persec FROM ( SELECT database_name, num_of_reads_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_log_reads_per_sec_total', SUM(V.num_of_reads_persec) FROM ( SELECT num_of_reads_persec FROM #baselinewritten WHERE datafile_type = 'LOG' ) as V
UNION ALL
SELECT 'db_io_rows_reads_per_sec_' + V.database_name, 0 + V.num_of_reads_persec FROM ( SELECT database_name, num_of_reads_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V
UNION ALL
SELECT 'db_io_rows_reads_per_sec_total', SUM(V.num_of_reads_persec) FROM ( SELECT num_of_reads_persec FROM #baselinewritten WHERE datafile_type = 'ROWS' ) as V"
]