metrics .json demo

[shenkaibo]

{
"Queries": [
{
"Name": "mssql_process_status",
"Query": "SELECT status, COUNT(*) count FROM sys.sysprocesses GROUP BY status",
"Description": "Counts the number of processes per status",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "status",
"Label": "status",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "count",
"Label": "count",
"Usage": "Gauge"
}
]
},
{
"Name": "mssql_deadlocks",
"Query": "SELECT cntr_value FROM sys.dm_os_performance_counters where counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'",
"Description": "Number of lock requests per second that resulted in a deadlock since last restart",
"Columns": [
{
"Name": "cntr_value",
"Label": "mssql_deadlocks",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_kill_connection_errors",
"Query": "SELECT cntr_value FROM sys.dm_os_performance_counters where counter_name = 'Errors/sec' AND instance_name = 'Kill Connection Errors'",
"Description": "Number of kill connection errors/sec since last restart",
"Columns": [
{
"Name": "cntr_value",
"Label": "mssql_kill_connection_errors",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_batch_requests",
"Query": "SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters where counter_name = 'Batch Requests/sec'",
"Description": "Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cachesize, complexity of requests, and so on). High batch requests mean good throughput",
"Columns": [
{
"Name": "cntr_value",
"Label": "mssql_batch_requests",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_page_fault_count",
"Query": "SELECT page_fault_count from sys.dm_os_process_memory",
"Description": "Number of page faults since last restart",
"Columns": [
{
"Name": "page_fault_count",
"Label": "mssql_page_fault_count",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_memory_utilization_percentage",
"Query": "SELECT memory_utilization_percentage from sys.dm_os_process_memory",
"Description": "Number of page faults since last restart",
"Columns": [
{
"Name": "memory_utilization_percentage",
"Label": "mssql_memory_utilization_percentage",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_total_physical_memory_kb",
"Query": "SELECT total_physical_memory_kb from sys.dm_os_sys_memory",
"Description": "Total physical memory in KB",
"Columns": [
{
"Name": "total_physical_memory_kb",
"Label": "mssql_total_physical_memory_kb",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_available_physical_memory_kb",
"Query": "SELECT available_physical_memory_kb from sys.dm_os_sys_memory",
"Description": "Available physical memory in KB",
"Columns": [
{
"Name": "available_physical_memory_kb",
"Label": "mssql_available_physical_memory_kb",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_total_page_file_kb",
"Query": "SELECT total_page_file_kb from sys.dm_os_sys_memory",
"Description": "Total page file in KB",
"Columns": [
{
"Name": "total_page_file_kb",
"Label": "mssql_total_page_file_kb",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_available_page_file_kb",
"Query": "SELECT available_page_file_kb from sys.dm_os_sys_memory",
"Description": "Available page file in KB",
"Columns": [
{
"Name": "available_page_file_kb",
"Label": "mssql_available_page_file_kb",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_connections",
"Query": "SELECT DB_NAME(sP.dbid) db_name,COUNT(sP.spid) count FROM sys.sysprocesses sP GROUP BY DB_NAME(sP.dbid)",
"Description": "Number of active connections",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "db_name",
"Label": "db_name",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "count",
"Label": "count",
"Usage": "Gauge"
}
]
},
{
"Name": "mssql_user_errors",
"Query": "SELECT cntr_value FROM sys.dm_os_performance_counters where counter_name = 'Errors/sec' AND instance_name = 'User Errors'",
"Description": "Number of user errors/sec since last restart",
"Columns": [
{
"Name": "cntr_value",
"Label": "mssql_user_errors",
"Usage": "Gauge",
"DefaultValue": 0
}
]
},
{
"Name": "mssql_io_read_stall",
"Query": "SELECT cast(DB_Name(a.database_id) as varchar) as name,max(io_stall_read_ms) as readIO FROM sys.dm_io_virtual_file_stats(null, null) a INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id group by a.database_id",
"Description": "Wait time (ms) of stall since last restart",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "name",
"Label": "name",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "readIO",
"Label": "readIO",
"Usage": "Gauge"
}
]
},
{
"Name": "mssql_io_write_stall",
"Query": "SELECT cast(DB_Name(a.database_id) as varchar) as name,max(io_stall_write_ms) as writeIO FROM sys.dm_io_virtual_file_stats(null, null) a INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id group by a.database_id",
"Description": "Wait time (ms) of stall since last restart",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "name",
"Label": "name",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "writeIO",
"Label": "writeIO",
"Usage": "Gauge"
}
]
},
{
"Name": "mssql_io_queued_read_stall",
"Query": "SELECT cast(DB_Name(a.database_id) as varchar) as name,max(io_stall_queued_read_ms) as readQueuedIO FROM sys.dm_io_virtual_file_stats(null, null) a INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id group by a.database_id",
"Description": "Wait time (ms) of stall since last restart",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "name",
"Label": "name",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "readQueuedIO",
"Label": "readQueuedIO",
"Usage": "Gauge"
}
]
},
{
"Name": "mssql_io_queued_read_stall",
"Query": "SELECT cast(DB_Name(a.database_id) as varchar) as name,max(io_stall_queued_write_ms) as writeQueuedIO FROM sys.dm_io_virtual_file_stats(null, null) a INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id group by a.database_id",
"Description": "Wait time (ms) of stall since last restart",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "name",
"Label": "name",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "writeQueuedIO",
"Label": "writeQueuedIO",
"Usage": "Gauge"
}
]
},
{
"Name": "mssql_io_stall_total",
"Query": "SELECT cast(DB_Name(a.database_id) as varchar) as name,max(io_stall) as totalStall FROM sys.dm_io_virtual_file_stats(null, null) a INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id group by a.database_id",
"Description": "Wait time (ms) of stall since last restart",
"Usage": "GaugesWithLabels",
"Columns": [
{
"Name": "name",
"Label": "name",
"Usage": "GaugeLabel",
"Order": 0
},
{
"Name": "totalStall",
"Label": "totalStall",
"Usage": "Gauge"
}
]
}
],
"MillisecondTimeout": 4000
}

[agustinbavahuddle]

@shenkaibo this is returning all 0s. Any idea why? Did that happen to you? (I know this is quite old)