| 1 | plugin_name: go.d.plugin |
| 2 | modules: |
| 3 | - &module |
| 4 | meta: &meta |
| 5 | id: collector-go.d.plugin-mssql |
| 6 | plugin_name: go.d.plugin |
| 7 | module_name: mssql |
| 8 | monitored_instance: |
| 9 | name: Microsoft SQL Server |
| 10 | link: https://www.microsoft.com/en-us/sql-server |
| 11 | categories: |
| 12 | - data-collection.databases |
| 13 | icon_filename: mssql.svg |
| 14 | related_resources: |
| 15 | integrations: |
| 16 | list: |
| 17 | - plugin_name: apps.plugin |
| 18 | module_name: apps |
| 19 | - plugin_name: cgroups.plugin |
| 20 | module_name: /sys/fs/cgroup |
| 21 | monitored_instance_name: Containers |
| 22 | info_provided_to_referring_integrations: |
| 23 | description: "" |
| 24 | keywords: |
| 25 | - "db" |
| 26 | - "database" |
| 27 | - "mssql" |
| 28 | - "sql server" |
| 29 | - "microsoft" |
| 30 | overview: |
| 31 | multi_instance: true |
| 32 | data_collection: |
| 33 | metrics_description: | |
| 34 | This collector monitors the health and performance of Microsoft SQL Server instances. |
| 35 | |
| 36 | It collects metrics from: |
| 37 | - Performance counters (buffer manager, memory manager, SQL statistics) |
| 38 | - Dynamic management views (DMVs) for wait statistics, locks, and sessions |
| 39 | - Per-database transaction and lock statistics |
| 40 | - SQL Server Agent job status |
| 41 | - Always On Availability Group health, replica states, and per-database synchronization metrics |
| 42 | method_description: | |
| 43 | It connects to the SQL Server instance via TCP using the go-mssqldb driver and executes queries against: |
| 44 | |
| 45 | - `sys.dm_os_performance_counters` - Performance counter values |
| 46 | - `sys.dm_exec_sessions` - Connection information |
| 47 | - `sys.dm_os_wait_stats` - Wait statistics |
| 48 | - `sys.dm_tran_locks` - Lock information |
| 49 | - `sys.dm_io_virtual_file_stats` - I/O stall (latency) statistics |
| 50 | - `sys.dm_os_process_memory` - SQL Server process memory |
| 51 | - `sys.dm_os_sys_memory` - OS physical memory and page file |
| 52 | - `sys.master_files` - Database file sizes |
| 53 | - `msdb.dbo.sysjobs` - SQL Agent job status |
| 54 | - `sys.dm_hadr_availability_group_states` - AG health rollup |
| 55 | - `sys.dm_hadr_availability_replica_states` - Replica operational state |
| 56 | - `sys.dm_hadr_database_replica_states` - Database sync queues and rates |
| 57 | - `sys.dm_hadr_cluster` / `sys.dm_hadr_cluster_members` - WSFC cluster health |
| 58 | - `sys.dm_hadr_database_replica_cluster_states` - Failover readiness |
| 59 | - `sys.dm_hadr_auto_page_repair` - Automatic page repair events |
| 60 | - `sys.dm_hadr_ag_threads` - AG thread usage (SQL Server 2019+) |
| 61 | default_behavior: |
| 62 | auto_detection: |
| 63 | description: | |
| 64 | By default, it tries to connect to SQL Server on localhost:1433 without authentication. |
| 65 | You must configure proper credentials for monitoring. |
| 66 | limits: |
| 67 | description: "" |
| 68 | performance_impact: |
| 69 | description: | |
| 70 | The collector executes lightweight queries against system views. |
| 71 | Most queries complete in milliseconds and have minimal impact on server performance. |
| 72 | additional_permissions: |
| 73 | description: | |
| 74 | The monitoring user requires the VIEW SERVER STATE permission to access DMVs. |
| 75 | SQL Agent job monitoring is part of collector startup, so access to |
| 76 | `msdb.dbo.sysjobs` is required. |
| 77 | Always On AG monitoring requires VIEW ANY DEFINITION for access to availability group catalog views. |
| 78 | On SQL Server 2022+, HADR DMVs may additionally require VIEW SERVER PERFORMANCE STATE. |
| 79 | supported_platforms: |
| 80 | include: [] |
| 81 | exclude: [] |
| 82 | setup: |
| 83 | prerequisites: |
| 84 | list: |
| 85 | - title: Create monitoring user |
| 86 | description: | |
| 87 | Create a SQL Server login with VIEW SERVER STATE permission: |
| 88 | |
| 89 | ```sql |
| 90 | -- Create login |
| 91 | CREATE LOGIN netdata_user WITH PASSWORD = 'YourStrongPassword!'; |
| 92 | |
| 93 | -- Grant VIEW SERVER STATE (required for DMVs) |
| 94 | GRANT VIEW SERVER STATE TO netdata_user; |
| 95 | |
| 96 | -- Grant VIEW ANY DEFINITION (required for Always On AG monitoring) |
| 97 | GRANT VIEW ANY DEFINITION TO netdata_user; |
| 98 | |
| 99 | -- Grant VIEW SERVER PERFORMANCE STATE (required for HADR DMVs on SQL Server 2022+) |
| 100 | -- GRANT VIEW SERVER PERFORMANCE STATE TO netdata_user; |
| 101 | |
| 102 | -- Grant access to msdb for SQL Agent job monitoring (required) |
| 103 | USE msdb; |
| 104 | CREATE USER netdata_user FOR LOGIN netdata_user; |
| 105 | GRANT SELECT ON dbo.sysjobs TO netdata_user; |
| 106 | |
| 107 | -- Optional: Grant access to distribution database for replication monitoring |
| 108 | -- (only if replication is configured) |
| 109 | USE distribution; |
| 110 | CREATE USER netdata_user FOR LOGIN netdata_user; |
| 111 | GRANT SELECT ON dbo.MSreplication_monitordata TO netdata_user; |
| 112 | GRANT SELECT ON dbo.MSpublications TO netdata_user; |
| 113 | GRANT SELECT ON dbo.MSsubscriptions TO netdata_user; |
| 114 | ``` |
| 115 | |
| 116 | **Required permissions:** |
| 117 | - `VIEW SERVER STATE` - Access to dynamic management views |
| 118 | - `SELECT on msdb.dbo.sysjobs` - SQL Agent job status monitoring |
| 119 | |
| 120 | **Optional permissions:** |
| 121 | - `VIEW ANY DEFINITION` - Always On Availability Group monitoring |
| 122 | - `VIEW SERVER PERFORMANCE STATE` - HADR DMVs on SQL Server 2022+ |
| 123 | - `SELECT on distribution.dbo.MSreplication_monitordata` - Replication monitoring |
| 124 | - `SELECT on distribution.dbo.MSpublications` - Publication information |
| 125 | - `SELECT on distribution.dbo.MSsubscriptions` - Subscription counts |
| 126 | - title: Grant Windows Authentication access (optional) |
| 127 | description: | |
| 128 | If you prefer Windows integrated authentication instead of SQL authentication, grant the |
| 129 | Netdata service account access to SQL Server. |
| 130 | |
| 131 | By default, the Netdata service runs as `Local System`. The identity it presents to |
| 132 | SQL Server depends on whether the connection is local or remote: |
| 133 | |
| 134 | **Local connection (Netdata and SQL Server on the same machine):** |
| 135 | |
| 136 | `Local System` always authenticates as `NT AUTHORITY\SYSTEM`, regardless of whether |
| 137 | the machine is domain-joined or in a workgroup. |
| 138 | |
| 139 | ```sql |
| 140 | CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS; |
| 141 | GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]; |
| 142 | GRANT VIEW ANY DEFINITION TO [NT AUTHORITY\SYSTEM]; |
| 143 | USE msdb; |
| 144 | CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]; |
| 145 | GRANT SELECT ON dbo.sysjobs TO [NT AUTHORITY\SYSTEM]; |
| 146 | ``` |
| 147 | |
| 148 | **Remote connection (Netdata connects to SQL Server on another machine):** |
| 149 | |
| 150 | On a domain-joined machine, `Local System` authenticates over the network as the |
| 151 | computer account (`DOMAIN\COMPUTERNAME$`). Replace with your actual values |
| 152 | (e.g., `MYDOM\SQLBOX01$`). |
| 153 | |
| 154 | ```sql |
| 155 | CREATE LOGIN [DOMAIN\COMPUTERNAME$] FROM WINDOWS; |
| 156 | GRANT VIEW SERVER STATE TO [DOMAIN\COMPUTERNAME$]; |
| 157 | GRANT VIEW ANY DEFINITION TO [DOMAIN\COMPUTERNAME$]; |
| 158 | USE msdb; |
| 159 | CREATE USER [DOMAIN\COMPUTERNAME$] FOR LOGIN [DOMAIN\COMPUTERNAME$]; |
| 160 | GRANT SELECT ON dbo.sysjobs TO [DOMAIN\COMPUTERNAME$]; |
| 161 | ``` |
| 162 | |
| 163 | For the default `Local System` service account, remote Windows Authentication works |
| 164 | only on domain-joined machines, where it can authenticate as the computer account. |
| 165 | In workgroups, use a different Windows service account if you need remote Windows |
| 166 | Authentication. |
| 167 | |
| 168 | > **Note**: To verify which account SQL Server sees, connect with Windows Authentication |
| 169 | > and run `SELECT SYSTEM_USER`. |
| 170 | configuration: |
| 171 | file: |
| 172 | name: go.d/mssql.conf |
| 173 | options: |
| 174 | description: | |
| 175 | The following options can be defined globally: update_every, autodetection_retry. |
| 176 | folding: |
| 177 | title: Config options |
| 178 | enabled: true |
| 179 | list: |
| 180 | - name: update_every |
| 181 | description: Data collection interval (seconds). |
| 182 | default_value: 10 |
| 183 | required: false |
| 184 | group: Collection |
| 185 | - name: autodetection_retry |
| 186 | description: Autodetection retry interval (seconds). Set 0 to disable. |
| 187 | default_value: 0 |
| 188 | required: false |
| 189 | group: Collection |
| 190 | |
| 191 | - name: dsn |
| 192 | description: "SQL Server DSN (Data Source Name). See [DSN syntax](https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn). When `cloud_auth.provider` is `azure_ad`, use URL format with `sqlserver://` scheme." |
| 193 | default_value: "sqlserver://localhost:1433" |
| 194 | required: true |
| 195 | group: Target |
| 196 | - name: cloud_auth.provider |
| 197 | description: Cloud auth provider (`none` or `azure_ad`). |
| 198 | default_value: none |
| 199 | required: false |
| 200 | group: Cloud Auth |
| 201 | - name: cloud_auth.azure_ad.mode |
| 202 | description: Azure AD credential mode (`service_principal`, `managed_identity`, or `default`). Required when `cloud_auth.provider` is `azure_ad`. |
| 203 | default_value: "" |
| 204 | required: true |
| 205 | group: Cloud Auth/Azure |
| 206 | - name: cloud_auth.azure_ad.mode_service_principal.tenant_id |
| 207 | description: Azure tenant ID. Required for `service_principal` mode. |
| 208 | default_value: "" |
| 209 | required: false |
| 210 | group: Cloud Auth/Azure |
| 211 | - name: cloud_auth.azure_ad.mode_service_principal.client_id |
| 212 | description: Azure client ID. Required for `service_principal` mode. |
| 213 | default_value: "" |
| 214 | required: false |
| 215 | group: Cloud Auth/Azure |
| 216 | - name: cloud_auth.azure_ad.mode_service_principal.client_secret |
| 217 | description: Azure client secret for `service_principal` mode. |
| 218 | default_value: "" |
| 219 | required: false |
| 220 | group: Cloud Auth/Azure |
| 221 | - name: cloud_auth.azure_ad.mode_managed_identity.client_id |
| 222 | description: Optional client ID of a user-assigned managed identity (`managed_identity` mode). |
| 223 | default_value: "" |
| 224 | required: false |
| 225 | group: Cloud Auth/Azure |
| 226 | - name: timeout |
| 227 | description: Query timeout (seconds). |
| 228 | default_value: 5 |
| 229 | required: false |
| 230 | group: Target |
| 231 | |
| 232 | - name: functions.top_queries.disabled |
| 233 | description: Disable the [top-queries](#top-queries) function. |
| 234 | default_value: false |
| 235 | required: false |
| 236 | group: Functions |
| 237 | - name: functions.top_queries.timeout |
| 238 | description: Query timeout for top-queries function (seconds). Uses collector timeout if not set. |
| 239 | default_value: "" |
| 240 | required: false |
| 241 | group: Functions |
| 242 | - name: functions.top_queries.limit |
| 243 | description: Maximum number of queries to return in the top-queries response. |
| 244 | default_value: 500 |
| 245 | required: false |
| 246 | group: Functions |
| 247 | - name: functions.top_queries.time_window_days |
| 248 | description: | |
| 249 | Number of days of Query Store data to analyze. Set to 0 to include all available data. |
| 250 | Smaller values improve query performance but show less history. |
| 251 | default_value: 7 |
| 252 | required: false |
| 253 | group: Functions |
| 254 | |
| 255 | - name: functions.deadlock_info.disabled |
| 256 | description: Disable the [deadlock-info](#deadlock-info) function. |
| 257 | default_value: false |
| 258 | required: false |
| 259 | group: Functions |
| 260 | - name: functions.deadlock_info.timeout |
| 261 | description: Query timeout for deadlock-info function (seconds). Uses collector timeout if not set. |
| 262 | default_value: "" |
| 263 | required: false |
| 264 | group: Functions |
| 265 | - name: functions.deadlock_info.use_ring_buffer |
| 266 | description: "Use ring_buffer instead of event_file for system_health session.<br/><br/>WARNING: Not recommended for production:<br/>• Data cleared on failover/restart<br/>• 4 MB capacity limit<br/>• High CPU load during queries<br/><br/>Use only for Azure SQL Database without Blob Storage or testing." |
| 267 | default_value: false |
| 268 | required: false |
| 269 | group: Functions |
| 270 | |
| 271 | - name: functions.error_info.disabled |
| 272 | description: Disable the [error-info](#error-info) function. |
| 273 | default_value: false |
| 274 | required: false |
| 275 | group: Functions |
| 276 | - name: functions.error_info.timeout |
| 277 | description: Query timeout for error-info function (seconds). Uses collector timeout if not set. |
| 278 | default_value: "" |
| 279 | required: false |
| 280 | group: Functions |
| 281 | - name: functions.error_info.session_name |
| 282 | description: "Extended Events session name capturing error_reported events.<br/>Must be created by administrator with event_file (recommended) or ring_buffer target." |
| 283 | default_value: netdata_errors |
| 284 | required: false |
| 285 | group: Functions |
| 286 | - name: functions.error_info.use_ring_buffer |
| 287 | description: "Use ring_buffer instead of event_file for error events.<br/><br/>WARNING: Not recommended for production:<br/>• Data cleared on failover/restart<br/>• 4 MB capacity limit<br/>• High CPU load during queries<br/><br/>Use only for Azure SQL Database without Blob Storage or testing." |
| 288 | default_value: false |
| 289 | required: false |
| 290 | group: Functions |
| 291 | |
| 292 | - name: vnode |
| 293 | description: Associates this data collection job with a [Virtual Node](https://learn.netdata.cloud/docs/netdata-agent/configuration/organize-systems-metrics-and-alerts#virtual-nodes). |
| 294 | default_value: "" |
| 295 | required: false |
| 296 | group: Virtual Node |
| 297 | examples: |
| 298 | folding: |
| 299 | title: Config |
| 300 | enabled: true |
| 301 | list: |
| 302 | - name: Basic configuration |
| 303 | description: Connect to local SQL Server with SQL authentication. |
| 304 | config: | |
| 305 | jobs: |
| 306 | - name: local |
| 307 | dsn: "sqlserver://netdata_user:password@localhost:1433" |
| 308 | - name: Windows Authentication |
| 309 | description: | |
| 310 | Connect using Windows integrated authentication (Windows only). |
| 311 | |
| 312 | When no username/password is provided in the DSN, the driver uses the Netdata service account's |
| 313 | Windows credentials. By default, the Netdata service runs as `Local System`, which authenticates |
| 314 | to a local SQL Server as `NT AUTHORITY\SYSTEM`. |
| 315 | |
| 316 | See the [Grant Windows Authentication access](#grant-windows-authentication-access-optional) prerequisite |
| 317 | to configure SQL Server for this. |
| 318 | config: | |
| 319 | jobs: |
| 320 | - name: local |
| 321 | dsn: "sqlserver://localhost:1433" |
| 322 | - name: Named instance |
| 323 | description: Connect to a named SQL Server instance. |
| 324 | config: | |
| 325 | jobs: |
| 326 | - name: named_instance |
| 327 | dsn: "sqlserver://netdata_user:password@localhost/INSTANCENAME" |
| 328 | - name: Remote server |
| 329 | description: Connect to a remote SQL Server. |
| 330 | config: | |
| 331 | jobs: |
| 332 | - name: remote |
| 333 | dsn: "sqlserver://netdata_user:password@192.168.1.100:1433" |
| 334 | - name: Azure SQL with service principal |
| 335 | description: Use Microsoft Entra service principal authentication for Azure SQL. |
| 336 | config: | |
| 337 | jobs: |
| 338 | - name: azure_sql_sp |
| 339 | dsn: "sqlserver://my-server.database.windows.net:1433?database=mydb" |
| 340 | cloud_auth: |
| 341 | provider: azure_ad |
| 342 | azure_ad: |
| 343 | mode: service_principal |
| 344 | mode_service_principal: |
| 345 | tenant_id: "00000000-0000-0000-0000-000000000000" |
| 346 | client_id: "11111111-1111-1111-1111-111111111111" |
| 347 | client_secret: "super-secret-value" |
| 348 | - name: Azure SQL with managed identity |
| 349 | description: Use managed identity authentication (system-assigned by default). |
| 350 | config: | |
| 351 | jobs: |
| 352 | - name: azure_sql_mi |
| 353 | dsn: "sqlserver://my-server.database.windows.net:1433?database=mydb" |
| 354 | cloud_auth: |
| 355 | provider: azure_ad |
| 356 | azure_ad: |
| 357 | mode: managed_identity |
| 358 | - name: Multi-instance |
| 359 | description: | |
| 360 | > **Note**: When you define multiple jobs, their names must be unique. |
| 361 | |
| 362 | Monitoring multiple SQL Server instances. |
| 363 | config: | |
| 364 | jobs: |
| 365 | - name: production |
| 366 | dsn: "sqlserver://netdata_user:password@prod-sql:1433" |
| 367 | |
| 368 | - name: development |
| 369 | dsn: "sqlserver://netdata_user:password@dev-sql:1433" |
| 370 | - name: With custom function settings |
| 371 | description: | |
| 372 | Configure function-specific settings like timeouts and limits. |
| 373 | |
| 374 | > **Warning**: Query Store may contain unmasked literal values (PII). |
| 375 | > Disable functions if not needed or ensure proper access controls. |
| 376 | config: | |
| 377 | jobs: |
| 378 | - name: local |
| 379 | dsn: "sqlserver://netdata_user:password@localhost:1433" |
| 380 | functions: |
| 381 | top_queries: |
| 382 | limit: 100 |
| 383 | time_window_days: 7 |
| 384 | deadlock_info: |
| 385 | use_ring_buffer: true |
| 386 | error_info: |
| 387 | session_name: custom_errors |
| 388 | troubleshooting: |
| 389 | problems: |
| 390 | list: |
| 391 | - name: Connection refused |
| 392 | description: | |
| 393 | Ensure SQL Server is running and accepting TCP connections on the configured port. |
| 394 | Check that the SQL Server Browser service is running if using named instances. |
| 395 | - name: Login failed |
| 396 | description: | |
| 397 | Verify the username and password in the DSN are correct. |
| 398 | Ensure SQL Server is configured for mixed mode authentication if using SQL logins. |
| 399 | - name: Permission denied |
| 400 | description: | |
| 401 | The monitoring user needs VIEW SERVER STATE permission. |
| 402 | Grant it with: `GRANT VIEW SERVER STATE TO netdata_user;` |
| 403 | alerts: |
| 404 | - name: mssql_database_log_percent_used |
| 405 | metric: mssql.database_log_percent_used |
| 406 | info: SQL Server transaction log percent used has been above 90% for the last 15 minutes |
| 407 | link: https://github.com/netdata/netdata/blob/master/src/health/health.d/mssql.conf |
| 408 | functions: |
| 409 | description: | |
| 410 | This collector exposes real-time functions for interactive troubleshooting in the Live tab. |
| 411 | list: |
| 412 | - id: top-queries |
| 413 | name: Top Queries |
| 414 | description: | |
| 415 | Retrieves aggregated SQL query performance metrics from Microsoft SQL Server [Query Store](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store) runtime statistics. |
| 416 | |
| 417 | This function queries `sys.query_store_runtime_stats` and related views across all databases with Query Store enabled, aggregating execution statistics by query hash. It provides comprehensive timing, I/O, memory, and parallelism metrics. |
| 418 | |
| 419 | Use cases: |
| 420 | - Identify slow or resource-intensive queries consuming excessive CPU time or memory |
| 421 | - Analyze I/O patterns (logical reads, physical reads, writes) to detect bottlenecks |
| 422 | - Monitor parallelism (DOP) and tempdb usage for capacity planning |
| 423 | |
| 424 | Query text is truncated at 4096 characters for display purposes. Columns are dynamically detected based on SQL Server version (some metrics only available in 2016+/2017+). |
| 425 | parameters: |
| 426 | - id: __sort |
| 427 | name: Filter By |
| 428 | description: Select the primary sort column. The available options depend on your SQL Server version and include metrics like total execution time, number of calls, CPU time, logical I/O, memory grants, and more. Default is Total Time to focus on most resource-intensive queries. |
| 429 | type: select |
| 430 | required: true |
| 431 | default: totalTime |
| 432 | options: [] |
| 433 | returns: |
| 434 | description: Aggregated query execution statistics from Query Store runtime views, providing comprehensive performance analysis across all monitored databases. Each row represents a unique query pattern (normalized query hash) with cumulative metrics across all its executions. |
| 435 | columns: |
| 436 | - name: Query Hash |
| 437 | type: string |
| 438 | unit: "" |
| 439 | visibility: hidden |
| 440 | description: Unique hash identifier for the normalized query pattern. Queries with identical structure but different literal values share the same digest. |
| 441 | - name: Query |
| 442 | type: string |
| 443 | unit: "" |
| 444 | description: The SQL query text with literal values truncated at 4096 characters. Use this to identify the actual SQL being executed and spot parameterized queries or injection risks. |
| 445 | - name: Database |
| 446 | type: string |
| 447 | unit: "" |
| 448 | description: Database name where the query was executed. Essential for multi-database analysis to identify which database is experiencing query load. |
| 449 | - name: Calls |
| 450 | type: integer |
| 451 | unit: "" |
| 452 | description: Total number of times this query pattern has been executed. High values indicate frequently run queries that may impact server performance significantly. |
| 453 | - name: Error Attribution |
| 454 | type: string |
| 455 | unit: "" |
| 456 | description: "Status of error detail attribution for this query. Values: enabled, no_data, not_enabled, not_supported." |
| 457 | - name: Error Number |
| 458 | type: integer |
| 459 | unit: "" |
| 460 | description: "Most recent error number observed for this query (when error attribution is enabled)." |
| 461 | - name: Error State |
| 462 | type: integer |
| 463 | unit: "" |
| 464 | visibility: hidden |
| 465 | description: "SQL Server error state for the most recent error (when error attribution is enabled)." |
| 466 | - name: Error Message |
| 467 | type: string |
| 468 | unit: "" |
| 469 | description: "Most recent error message for this query (when error attribution is enabled)." |
| 470 | - name: Hash Match Joins |
| 471 | type: integer |
| 472 | unit: "" |
| 473 | description: "Count of Hash Match join operators across all stored plans for this query." |
| 474 | - name: Merge Joins |
| 475 | type: integer |
| 476 | unit: "" |
| 477 | description: "Count of Merge Join operators across all stored plans for this query." |
| 478 | - name: Nested Loops |
| 479 | type: integer |
| 480 | unit: "" |
| 481 | description: "Count of Nested Loops operators across all stored plans for this query." |
| 482 | - name: Sorts |
| 483 | type: integer |
| 484 | unit: "" |
| 485 | description: "Count of Sort operators across all stored plans for this query." |
| 486 | - name: Total Time |
| 487 | type: duration |
| 488 | unit: "milliseconds" |
| 489 | description: Cumulative execution time across all query executions. This is a key metric for identifying the most resource-intensive queries in terms of total server time consumption. |
| 490 | - name: Avg Time |
| 491 | type: duration |
| 492 | unit: "milliseconds" |
| 493 | description: Average execution time per query run, calculated as weighted average when execution count is greater than zero. Compare with Total Time to determine if individual executions or high frequency drives resource usage. |
| 494 | - name: Last Time |
| 495 | type: duration |
| 496 | unit: "milliseconds" |
| 497 | visibility: hidden |
| 498 | description: Execution time of the most recent execution for this query pattern. Useful for identifying recent performance changes or individual outlier executions. |
| 499 | - name: Min Time |
| 500 | type: duration |
| 501 | unit: "milliseconds" |
| 502 | visibility: hidden |
| 503 | description: Minimum execution time observed. Helps identify variability in query performance and spot potential optimization opportunities for outliers. |
| 504 | - name: Max Time |
| 505 | type: duration |
| 506 | unit: "milliseconds" |
| 507 | visibility: hidden |
| 508 | description: Maximum execution time observed. Large gaps between Min Time and Max Time may indicate performance instability due to parameter sniffing, data skew, or lock contention. |
| 509 | - name: StdDev Time |
| 510 | type: duration |
| 511 | unit: "milliseconds" |
| 512 | visibility: hidden |
| 513 | description: Standard deviation of execution time. High values indicate inconsistent query performance, making capacity planning difficult and suggesting need for query optimization or consistent indexing. |
| 514 | - name: Avg CPU |
| 515 | type: duration |
| 516 | unit: "milliseconds" |
| 517 | description: Average CPU time consumed per query execution. High values indicate CPU-intensive operations that may include complex calculations, string manipulations, or excessive function calls. Available in SQL Server 2016+. |
| 518 | - name: Last CPU |
| 519 | type: duration |
| 520 | unit: "milliseconds" |
| 521 | visibility: hidden |
| 522 | description: CPU time of the most recent execution. Useful for identifying recent changes in query patterns and resource usage. |
| 523 | - name: Min CPU |
| 524 | type: duration |
| 525 | unit: "milliseconds" |
| 526 | visibility: hidden |
| 527 | description: Minimum CPU time observed. Helps identify variability in CPU consumption and spot efficient vs. inefficient query executions. |
| 528 | - name: Max CPU |
| 529 | type: duration |
| 530 | unit: "milliseconds" |
| 531 | visibility: hidden |
| 532 | description: Maximum CPU time observed. Spikes may indicate complex queries, large result sets, or parallelism issues. |
| 533 | - name: StdDev CPU |
| 534 | type: duration |
| 535 | unit: "milliseconds" |
| 536 | visibility: hidden |
| 537 | description: Standard deviation of CPU time. High variability suggests inconsistent performance due to varying data volumes, plan cache hit rates, or changing execution contexts. |
| 538 | - name: Avg Logical Reads |
| 539 | type: float |
| 540 | unit: "" |
| 541 | description: Average number of logical read operations (8KB pages) per execution. High values indicate queries scanning large amounts of data through indexes or table scans. Monitor for I/O subsystem impact. |
| 542 | - name: Last Logical Reads |
| 543 | type: integer |
| 544 | unit: "" |
| 545 | visibility: hidden |
| 546 | description: Logical reads from the most recent execution. Useful for identifying immediate query patterns and recent performance changes. |
| 547 | - name: Min Logical Reads |
| 548 | type: integer |
| 549 | unit: "" |
| 550 | visibility: hidden |
| 551 | description: Minimum logical reads observed. Helps identify data access patterns and spot outliers. |
| 552 | - name: Max Logical Reads |
| 553 | type: integer |
| 554 | unit: "" |
| 555 | visibility: hidden |
| 556 | description: Maximum logical reads observed. Very high values may indicate full table scans, missing indexes, or inefficient join operations requiring excessive data access. |
| 557 | - name: StdDev Logical Reads |
| 558 | type: float |
| 559 | unit: "" |
| 560 | visibility: hidden |
| 561 | description: Standard deviation of logical reads. High variability suggests inconsistent access patterns, potentially indicating performance issues with certain queries or data volumes. |
| 562 | - name: Avg Logical Writes |
| 563 | type: float |
| 564 | unit: "" |
| 565 | description: Average number of logical write operations per execution. High values indicate heavy write workloads that may benefit from batching or optimization. |
| 566 | - name: Last Logical Writes |
| 567 | type: integer |
| 568 | unit: "" |
| 569 | visibility: hidden |
| 570 | description: Logical writes from the most recent execution. Helps track recent write activity and identify immediate performance impact. |
| 571 | - name: Min Logical Writes |
| 572 | type: integer |
| 573 | unit: "" |
| 574 | visibility: hidden |
| 575 | description: Minimum logical writes observed. Helps identify read-heavy vs. write-heavy query patterns and data access characteristics. |
| 576 | - name: Max Logical Writes |
| 577 | type: integer |
| 578 | unit: "" |
| 579 | visibility: hidden |
| 580 | description: Maximum logical writes observed. Spikes may indicate bulk insert/update operations, large transactions, or data migration activities. |
| 581 | - name: StdDev Logical Writes |
| 582 | type: float |
| 583 | unit: "" |
| 584 | visibility: hidden |
| 585 | description: Standard deviation of logical writes. High values indicate write performance variability, potentially suggesting inconsistent transaction sizes or periodic bulk operations. |
| 586 | - name: Avg Physical Reads |
| 587 | type: float |
| 588 | unit: "" |
| 589 | description: Average number of physical read operations from storage per execution. High values indicate queries requiring substantial disk I/O for data retrieval, potentially due to full table scans or missing covering indexes. |
| 590 | - name: Last Physical Reads |
| 591 | type: integer |
| 592 | unit: "" |
| 593 | visibility: hidden |
| 594 | description: Physical reads from the most recent execution. Useful for identifying immediate I/O patterns and recent storage subsystem pressure. |
| 595 | - name: Min Physical Reads |
| 596 | type: integer |
| 597 | unit: "" |
| 598 | visibility: hidden |
| 599 | description: Minimum physical reads observed. Helps baseline I/O patterns and identify read-intensive query scenarios. |
| 600 | - name: Max Physical Reads |
| 601 | type: integer |
| 602 | unit: "" |
| 603 | visibility: hidden |
| 604 | description: Maximum physical reads observed. Extremely high values may indicate storage subsystem bottlenecks, full table scans without covering indexes, or queries processing very large data volumes. |
| 605 | - name: StdDev Physical Reads |
| 606 | type: float |
| 607 | unit: "" |
| 608 | visibility: hidden |
| 609 | description: Standard deviation of physical reads. High variability suggests inconsistent disk access patterns, potentially indicating intermittent I/O performance issues or storage contention. |
| 610 | - name: Avg CLR Time |
| 611 | type: duration |
| 612 | unit: "milliseconds" |
| 613 | description: Average CLR (Common Language Runtime) time per execution. High values indicate managed code (stored procedures, functions, triggers) with heavy computations, garbage collection pressure, or inefficient memory allocations. Available in SQL Server 2016+. |
| 614 | - name: Last CLR Time |
| 615 | type: duration |
| 616 | unit: "milliseconds" |
| 617 | visibility: hidden |
| 618 | description: CLR time of the most recent execution. Useful for identifying recent managed code performance changes and detecting inefficient code deployments. |
| 619 | - name: Min CLR Time |
| 620 | type: duration |
| 621 | unit: "milliseconds" |
| 622 | visibility: hidden |
| 623 | description: Minimum CLR time observed. Helps identify efficient managed code executions and spot expensive CLR operations. |
| 624 | - name: Max CLR Time |
| 625 | type: duration |
| 626 | unit: "milliseconds" |
| 627 | visibility: hidden |
| 628 | description: Maximum CLR time observed. Spikes may indicate complex managed code operations, large object allocations, or expensive .NET framework method calls. |
| 629 | - name: StdDev CLR Time |
| 630 | type: duration |
| 631 | unit: "milliseconds" |
| 632 | visibility: hidden |
| 633 | description: Standard deviation of CLR time. High variability suggests inconsistent managed code execution patterns, potentially varying by execution parameters, data volumes, or different code paths being taken. |
| 634 | - name: Avg DOP |
| 635 | type: float |
| 636 | unit: "" |
| 637 | description: Average Degree of Parallelism (DOP) per query. Higher values indicate queries utilizing more CPU cores through parallelism, potentially consuming significant server resources. Values above 1 indicate intra-query parallelism; values of 1 indicate serial execution. |
| 638 | - name: Last DOP |
| 639 | type: integer |
| 640 | unit: "" |
| 641 | visibility: hidden |
| 642 | description: DOP of the most recent execution. Helps track recent parallelism patterns and identify changes in query execution behavior. |
| 643 | - name: Min DOP |
| 644 | type: integer |
| 645 | unit: "" |
| 646 | visibility: hidden |
| 647 | description: Minimum DOP observed. Values of 0 may indicate serial execution; values above 1 suggest parallel query execution within individual queries. |
| 648 | - name: Max DOP |
| 649 | type: integer |
| 650 | unit: "" |
| 651 | visibility: hidden |
| 652 | description: Maximum DOP observed. Very high values (>4) may indicate aggressive parallelism consuming excessive resources and potentially affecting concurrent workloads. Available in SQL Server 2016+. |
| 653 | - name: StdDev DOP |
| 654 | type: float |
| 655 | unit: "" |
| 656 | visibility: hidden |
| 657 | description: Standard deviation of DOP. High variability suggests inconsistent parallelism patterns across executions, potentially indicating performance variability based on data characteristics or query complexity. |
| 658 | - name: Avg Memory (8KB pages) |
| 659 | type: float |
| 660 | unit: "" |
| 661 | description: Average memory grant (in 8KB pages) per execution. High values indicate memory-intensive queries that may benefit from index optimization, reduced result sets, or query tuning to reduce working memory usage. |
| 662 | - name: Last Memory (8KB pages) |
| 663 | type: integer |
| 664 | unit: "" |
| 665 | visibility: hidden |
| 666 | description: Memory grant from the most recent execution. Useful for identifying recent memory pressure and tracking immediate impact of resource-intensive queries. |
| 667 | - name: Min Memory (8KB pages) |
| 668 | type: integer |
| 669 | unit: "" |
| 670 | visibility: hidden |
| 671 | description: Minimum memory grant observed. Helps identify memory-efficient queries and baseline memory requirements for common operations. |
| 672 | - name: Max Memory (8KB pages) |
| 673 | type: integer |
| 674 | unit: "" |
| 675 | visibility: hidden |
| 676 | description: Maximum memory grant observed. Spikes may indicate queries with large sort operations, hash joins, temporary table creation, or excessive parameter lengths consuming working memory. |
| 677 | - name: StdDev Memory |
| 678 | type: float |
| 679 | unit: "" |
| 680 | visibility: hidden |
| 681 | description: Standard deviation of memory grants. High variability suggests inconsistent memory usage patterns, potentially varying by execution parameters, result set sizes, or different code paths being executed. |
| 682 | - name: Avg Rows |
| 683 | type: float |
| 684 | unit: "" |
| 685 | description: Average number of rows processed per query execution. High values indicate queries returning large result sets that may consume significant network bandwidth, memory for result buffers, and client application resources. |
| 686 | - name: Last Rows |
| 687 | type: integer |
| 688 | unit: "" |
| 689 | visibility: hidden |
| 690 | description: Row count from the most recent execution. Helps identify recent query patterns and track immediate data processing requirements. |
| 691 | - name: Min Rows |
| 692 | type: integer |
| 693 | unit: "" |
| 694 | visibility: hidden |
| 695 | description: Minimum rows observed. Helps identify data access patterns and spot outliers in result set sizes. |
| 696 | - name: Max Rows |
| 697 | type: integer |
| 698 | unit: "" |
| 699 | visibility: hidden |
| 700 | description: Maximum rows observed. Extremely high values may indicate full table scans without WHERE clauses, missing or inefficient filters, or data export operations. |
| 701 | - name: StdDev Rows |
| 702 | type: float |
| 703 | unit: "" |
| 704 | visibility: hidden |
| 705 | description: Standard deviation of rows processed. High variability suggests inconsistent result set sizes, potentially due to varying query filters, parameterized inputs, or different data distributions across executions. |
| 706 | - name: Avg Log Bytes |
| 707 | type: float |
| 708 | unit: "" |
| 709 | description: Average transaction log bytes written per query execution (SQL Server 2017+). High values indicate write-intensive operations (INSERT/UPDATE/DELETE), large transactions, or bulk modifications. This measures WAL activity, not diagnostic logging. |
| 710 | - name: Last Log Bytes |
| 711 | type: integer |
| 712 | unit: "" |
| 713 | visibility: hidden |
| 714 | description: Transaction log bytes from the most recent execution. Useful for tracking recent write activity. |
| 715 | - name: Min Log Bytes |
| 716 | type: integer |
| 717 | unit: "" |
| 718 | visibility: hidden |
| 719 | description: Minimum transaction log bytes observed. Helps identify write-efficient queries and baseline requirements. |
| 720 | - name: Max Log Bytes |
| 721 | type: integer |
| 722 | unit: "" |
| 723 | visibility: hidden |
| 724 | description: Maximum transaction log bytes observed. Spikes may indicate bulk operations, large transactions, or queries affecting many rows. |
| 725 | - name: StdDev Log Bytes |
| 726 | type: float |
| 727 | unit: "" |
| 728 | visibility: hidden |
| 729 | description: Standard deviation of transaction log bytes. High variability suggests inconsistent write patterns, potentially varying by the number of rows affected or transaction sizes. |
| 730 | - name: Avg TempDB (8KB pages) |
| 731 | type: float |
| 732 | unit: "" |
| 733 | description: Average tempdb space usage (in 8KB pages) per execution. High values indicate queries that create or use large temporary objects, work tables, sort operations, or have heavy tempdb spillage from disk. High tempdb usage can lead to disk I/O contention and overall performance degradation. |
| 734 | - name: Last TempDB (8KB pages) |
| 735 | type: integer |
| 736 | unit: "" |
| 737 | visibility: hidden |
| 738 | description: Tempdb space from the most recent execution. Useful for identifying recent tempdb pressure and tracking immediate disk I/O impact of resource-intensive queries. |
| 739 | - name: Min TempDB (8KB pages) |
| 740 | type: integer |
| 741 | unit: "" |
| 742 | visibility: hidden |
| 743 | description: Minimum tempdb space observed. Helps identify tempdb-efficient queries and baseline temporary object requirements for common operations. |
| 744 | - name: Max TempDB (8KB pages) |
| 745 | type: integer |
| 746 | unit: "" |
| 747 | visibility: hidden |
| 748 | description: Maximum tempdb space observed. Spikes may indicate queries with large sort operations, hash joins, index spool usage, or temporary table creation consuming substantial tempdb space. Can lead to tempdb autogrow and disk space issues. |
| 749 | - name: StdDev TempDB |
| 750 | type: float |
| 751 | unit: "" |
| 752 | visibility: hidden |
| 753 | description: Standard deviation of tempdb space usage. High variability suggests inconsistent temporary object usage patterns, potentially varying by query complexity, parameter types, or different data access patterns affecting temporary object creation. |
| 754 | performance: | |
| 755 | Executes dynamic SQL to aggregate Query Store data across all enabled databases:<br/>• Execution time depends on Query Store workload and number of monitored databases<br/>• Default limit of 500 rows balances completeness with performance |
| 756 | security: | |
| 757 | Query text may contain unmasked literal values including potentially sensitive data:<br/>• Personal information in WHERE clauses or INSERT values<br/>• Business data and internal identifiers<br/>• Access should be restricted to authorized personnel only |
| 758 | prerequisites: |
| 759 | list: |
| 760 | - title: Enable Query Store |
| 761 | description: | |
| 762 | Query Store must be enabled on each database you want to monitor. |
| 763 | |
| 764 | 1. Verify Query Store is enabled on your databases: |
| 765 | |
| 766 | ```sql |
| 767 | SELECT name, is_query_store_on |
| 768 | FROM sys.databases |
| 769 | WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); |
| 770 | ``` |
| 771 | |
| 772 | 2. Enable Query Store on databases where it is disabled: |
| 773 | |
| 774 | ```sql |
| 775 | ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON; |
| 776 | ``` |
| 777 | |
| 778 | 3. Enable the function in Netdata collector config: |
| 779 | |
| 780 | ```yaml |
| 781 | jobs: |
| 782 | - name: local |
| 783 | dsn: "sqlserver://user:pass@localhost:1433" |
| 784 | query_store_function_enabled: true |
| 785 | ``` |
| 786 | |
| 787 | :::info |
| 788 | |
| 789 | - Query Store is available in SQL Server 2016+ and Azure SQL Database |
| 790 | - Requires ALTER DATABASE permission to enable Query Store |
| 791 | - System databases (master, tempdb, model, msdb) are excluded from queries |
| 792 | |
| 793 | ::: |
| 794 | availability: | |
| 795 | Available when:<br/>• The collector has successfully connected to SQL Server<br/>• Query Store is enabled on at least one user database<br/>• Returns HTTP 503 if collector is still initializing<br/>• Returns HTTP 500 if the query fails<br/>• Returns HTTP 504 if the query times out |
| 796 | require_cloud: true |
| 797 | - id: deadlock-info |
| 798 | name: Deadlock Info |
| 799 | description: | |
| 800 | Retrieves the most recent deadlock event from SQL Server's `system_health` Extended Events session (`xml_deadlock_report`). |
| 801 | |
| 802 | The deadlock graph XML is parsed to attribute the deadlock to the participating processes and their query text, lock mode, lock status, and wait resource. |
| 803 | |
| 804 | Use cases: |
| 805 | - Identify which process was chosen as the deadlock victim |
| 806 | - Inspect the waiting resource and lock mode involved in the deadlock |
| 807 | - Correlate deadlocks with recent application changes or deployments |
| 808 | |
| 809 | Query text and wait resource strings are truncated at 4096 characters for display purposes. |
| 810 | parameters: [] |
| 811 | returns: |
| 812 | description: Parsed deadlock participants from the latest detected deadlock event. Each row represents one process involved in the deadlock. |
| 813 | columns: |
| 814 | - name: Row ID |
| 815 | type: string |
| 816 | unit: "" |
| 817 | visibility: hidden |
| 818 | description: "Unique row identifier composed of deadlock ID and process ID." |
| 819 | - name: Deadlock ID |
| 820 | type: string |
| 821 | unit: "" |
| 822 | description: "Identifier for the deadlock event, derived from the deadlock timestamp to group participating processes." |
| 823 | - name: Timestamp |
| 824 | type: timestamp |
| 825 | unit: "" |
| 826 | description: "Timestamp of the deadlock event from Extended Events when available; otherwise the function execution time." |
| 827 | - name: Process ID |
| 828 | type: string |
| 829 | unit: "" |
| 830 | description: "Deadlock graph process identifier for the process involved in the deadlock." |
| 831 | - name: SPID |
| 832 | type: integer |
| 833 | unit: "" |
| 834 | description: "SQL Server session ID (SPID) for the process when available." |
| 835 | - name: ECID |
| 836 | type: integer |
| 837 | unit: "" |
| 838 | description: "Execution context ID (ECID) for parallel execution contexts when available." |
| 839 | - name: Victim |
| 840 | type: string |
| 841 | unit: "" |
| 842 | description: "\"true\" when the process was chosen as the deadlock victim and rolled back; otherwise \"false\"." |
| 843 | - name: Query |
| 844 | type: string |
| 845 | unit: "" |
| 846 | description: "SQL query text for the process involved in the deadlock. Truncated to 4096 characters." |
| 847 | - name: Lock Mode |
| 848 | type: string |
| 849 | unit: "" |
| 850 | description: "Lock mode reported for the process within the deadlock graph (for example X or S)." |
| 851 | - name: Lock Status |
| 852 | type: string |
| 853 | unit: "" |
| 854 | description: "Lock status for the process. WAITING indicates the process was waiting on a lock." |
| 855 | - name: Wait Resource |
| 856 | type: string |
| 857 | unit: "" |
| 858 | description: "Lock resource identifier from the deadlock graph showing what the process was waiting on." |
| 859 | - name: Database |
| 860 | type: string |
| 861 | unit: "" |
| 862 | description: "Database name mapped from the deadlock graph database ID when available." |
| 863 | performance: | |
| 864 | Executes on-demand queries against the `system_health` ring buffer:<br/>• Not part of regular metric collection<br/>• Overhead is limited to function execution time and XML parsing |
| 865 | security: | |
| 866 | Query text and wait resource strings may include unmasked literal values including sensitive data (PII/secrets):<br/>• SQL literals such as emails, IDs, or tokens<br/>• Schema and table names that may be sensitive in some environments<br/>• Restrict dashboard access to authorized personnel only |
| 867 | availability: | |
| 868 | Available when:<br/>• The collector has successfully connected to SQL Server<br/>• `deadlock_info_function_enabled` is true<br/>• The account has `VIEW SERVER STATE` permission<br/>• Returns HTTP 200 with empty data when no deadlock is found<br/>• Returns HTTP 403 when permission is missing<br/>• Returns HTTP 500 if the query fails<br/>• Returns HTTP 561 when the deadlock graph cannot be parsed<br/>• Returns HTTP 503 if the collector is still initializing or the function is disabled<br/>• Returns HTTP 504 if the query times out |
| 869 | require_cloud: true |
| 870 | - id: error-info |
| 871 | name: Error Info |
| 872 | description: | |
| 873 | Retrieves recent SQL errors from a user-managed Extended Events session that captures `sqlserver.error_reported` |
| 874 | with both the `sql_text` and `query_hash` actions. |
| 875 | |
| 876 | The session must be created by an administrator and include an `event_file` target. Netdata reads the event file |
| 877 | and returns recent error events with error number, message, and SQL text. The `query_hash` action is required for |
| 878 | reliable mapping into `top-queries` (query text fallback is best-effort). |
| 879 | |
| 880 | Use cases: |
| 881 | - Identify recent query errors and their messages |
| 882 | - Correlate errors to query text |
| 883 | - Validate error rates seen in top-queries |
| 884 | parameters: [] |
| 885 | returns: |
| 886 | description: Recent error events from the configured Extended Events session. |
| 887 | columns: |
| 888 | - name: Timestamp |
| 889 | type: timestamp |
| 890 | unit: "" |
| 891 | description: "Timestamp of the error event." |
| 892 | - name: Error Number |
| 893 | type: integer |
| 894 | unit: "" |
| 895 | description: "SQL Server error number." |
| 896 | - name: Error State |
| 897 | type: integer |
| 898 | unit: "" |
| 899 | description: "SQL Server error state." |
| 900 | - name: Error Message |
| 901 | type: string |
| 902 | unit: "" |
| 903 | description: "Error message text." |
| 904 | - name: Query |
| 905 | type: string |
| 906 | unit: "" |
| 907 | description: "SQL text captured with the error event." |
| 908 | - name: Query Hash |
| 909 | type: string |
| 910 | unit: "" |
| 911 | visibility: hidden |
| 912 | description: "Query hash captured with the error event (used for mapping into top-queries)." |
| 913 | performance: | |
| 914 | Executes on-demand queries against the configured Extended Events event file:<br/>• Not part of regular metric collection<br/>• Overhead is limited to function execution time |
| 915 | security: | |
| 916 | Error messages and query text may include unmasked literal values including sensitive data (PII/secrets):<br/>• Restrict dashboard access to authorized personnel only |
| 917 | prerequisites: |
| 918 | list: |
| 919 | - title: Create Extended Events session for error capture |
| 920 | description: | |
| 921 | Create an Extended Events session that captures `sqlserver.error_reported` with `sql_text` and `query_hash` actions: |
| 922 | |
| 923 | ```sql |
| 924 | -- Create the Extended Events session with event_file target |
| 925 | CREATE EVENT SESSION [netdata_errors] ON SERVER |
| 926 | ADD EVENT sqlserver.error_reported( |
| 927 | ACTION(sqlserver.sql_text, sqlserver.query_hash) |
| 928 | ) |
| 929 | ADD TARGET package0.event_file(SET filename=N'netdata_errors'); |
| 930 | GO |
| 931 | |
| 932 | -- Start the session |
| 933 | ALTER EVENT SESSION [netdata_errors] ON SERVER STATE = START; |
| 934 | GO |
| 935 | |
| 936 | -- Grant required permission |
| 937 | GRANT VIEW SERVER STATE TO [netdata_user]; |
| 938 | ``` |
| 939 | |
| 940 | If you use a different session name, set it in the collector config: |
| 941 | |
| 942 | ```yaml |
| 943 | jobs: |
| 944 | - name: local |
| 945 | dsn: "sqlserver://user:pass@localhost:1433" |
| 946 | error_info_session_name: your_session_name |
| 947 | ``` |
| 948 | availability: | |
| 949 | Available when:<br/>• The collector has successfully connected to SQL Server<br/>• `error_info_function_enabled` is true<br/>• The Extended Events session exists and has an event_file target<br/>• The account has `VIEW SERVER STATE` permission<br/>• Returns HTTP 200 with empty data when no errors are found<br/>• Returns HTTP 403 when permission is missing<br/>• Returns HTTP 500 if the query fails<br/>• Returns HTTP 503 if the session is not enabled or the function is disabled<br/>• Returns HTTP 504 if the query times out |
| 950 | require_cloud: true |
| 951 | metrics: |
| 952 | folding: |
| 953 | title: Metrics |
| 954 | enabled: false |
| 955 | description: "" |
| 956 | availability: |
| 957 | - SQL Server 2016+ |
| 958 | - Azure SQL Database |
| 959 | scopes: |
| 960 | - name: global |
| 961 | description: These metrics refer to the entire SQL Server instance. |
| 962 | labels: [] |
| 963 | metrics: |
| 964 | - name: mssql.user_connections |
| 965 | description: User Connections |
| 966 | unit: connections |
| 967 | chart_type: line |
| 968 | dimensions: |
| 969 | - name: user |
| 970 | - name: mssql.session_connections |
| 971 | description: Session Connections |
| 972 | unit: connections |
| 973 | chart_type: line |
| 974 | dimensions: |
| 975 | - name: user |
| 976 | - name: internal |
| 977 | - name: mssql.blocked_processes |
| 978 | description: Blocked Processes |
| 979 | unit: processes |
| 980 | chart_type: line |
| 981 | dimensions: |
| 982 | - name: blocked |
| 983 | - name: mssql.batch_requests |
| 984 | description: Batch Requests |
| 985 | unit: requests/s |
| 986 | chart_type: line |
| 987 | dimensions: |
| 988 | - name: batch |
| 989 | - name: mssql.compilations |
| 990 | description: SQL Compilations |
| 991 | unit: compilations/s |
| 992 | chart_type: line |
| 993 | dimensions: |
| 994 | - name: compilations |
| 995 | - name: mssql.recompilations |
| 996 | description: SQL Re-Compilations |
| 997 | unit: recompilations/s |
| 998 | chart_type: line |
| 999 | dimensions: |
| 1000 | - name: recompilations |
| 1001 | - name: mssql.auto_param_attempts |
| 1002 | description: Auto-Parameterization Attempts |
| 1003 | unit: attempts/s |
| 1004 | chart_type: line |
| 1005 | dimensions: |
| 1006 | - name: total |
| 1007 | - name: safe |
| 1008 | - name: failed |
| 1009 | - name: mssql.sql_errors |
| 1010 | description: SQL Errors |
| 1011 | unit: errors/s |
| 1012 | chart_type: line |
| 1013 | dimensions: |
| 1014 | - name: errors |
| 1015 | - name: mssql.buffer_cache_hit_ratio |
| 1016 | description: Buffer Cache Hit Ratio |
| 1017 | unit: percentage |
| 1018 | chart_type: line |
| 1019 | dimensions: |
| 1020 | - name: hit_ratio |
| 1021 | - name: mssql.buffer_page_life_expectancy |
| 1022 | description: Page Life Expectancy |
| 1023 | unit: seconds |
| 1024 | chart_type: line |
| 1025 | dimensions: |
| 1026 | - name: life_expectancy |
| 1027 | - name: mssql.buffer_page_iops |
| 1028 | description: Buffer Page I/O |
| 1029 | unit: pages/s |
| 1030 | chart_type: line |
| 1031 | dimensions: |
| 1032 | - name: read |
| 1033 | - name: written |
| 1034 | - name: mssql.buffer_checkpoint_pages |
| 1035 | description: Buffer Checkpoint Pages Flushed |
| 1036 | unit: pages/s |
| 1037 | chart_type: line |
| 1038 | dimensions: |
| 1039 | - name: flushed |
| 1040 | - name: mssql.buffer_page_lookups |
| 1041 | description: Buffer Page Lookups |
| 1042 | unit: lookups/s |
| 1043 | chart_type: line |
| 1044 | dimensions: |
| 1045 | - name: lookups |
| 1046 | - name: mssql.buffer_lazy_writes |
| 1047 | description: Buffer Lazy Writes |
| 1048 | unit: writes/s |
| 1049 | chart_type: line |
| 1050 | dimensions: |
| 1051 | - name: lazy_writes |
| 1052 | - name: mssql.memory_total |
| 1053 | description: Total Server Memory |
| 1054 | unit: bytes |
| 1055 | chart_type: line |
| 1056 | dimensions: |
| 1057 | - name: memory |
| 1058 | - name: mssql.memory_connection |
| 1059 | description: Connection Memory |
| 1060 | unit: bytes |
| 1061 | chart_type: line |
| 1062 | dimensions: |
| 1063 | - name: memory |
| 1064 | - name: mssql.memory_pending_grants |
| 1065 | description: Pending Memory Grants |
| 1066 | unit: processes |
| 1067 | chart_type: line |
| 1068 | dimensions: |
| 1069 | - name: pending |
| 1070 | - name: mssql.memory_external_benefit |
| 1071 | description: External Benefit of Memory |
| 1072 | unit: benefit |
| 1073 | chart_type: line |
| 1074 | dimensions: |
| 1075 | - name: benefit |
| 1076 | - name: mssql.page_splits |
| 1077 | description: Page Splits |
| 1078 | unit: splits/s |
| 1079 | chart_type: line |
| 1080 | dimensions: |
| 1081 | - name: page |
| 1082 | - name: mssql.process_memory_resident |
| 1083 | description: SQL Server Process Resident Memory (Working Set) |
| 1084 | unit: bytes |
| 1085 | chart_type: line |
| 1086 | dimensions: |
| 1087 | - name: resident |
| 1088 | - name: mssql.process_memory_virtual |
| 1089 | description: SQL Server Process Virtual Memory Committed |
| 1090 | unit: bytes |
| 1091 | chart_type: line |
| 1092 | dimensions: |
| 1093 | - name: virtual |
| 1094 | - name: mssql.process_memory_utilization |
| 1095 | description: SQL Server Process Memory Utilization |
| 1096 | unit: percentage |
| 1097 | chart_type: line |
| 1098 | dimensions: |
| 1099 | - name: utilization |
| 1100 | - name: mssql.process_page_faults |
| 1101 | description: SQL Server Process Page Faults |
| 1102 | unit: faults |
| 1103 | chart_type: line |
| 1104 | dimensions: |
| 1105 | - name: page_faults |
| 1106 | - name: mssql.os_memory |
| 1107 | description: OS Physical Memory |
| 1108 | unit: bytes |
| 1109 | chart_type: stacked |
| 1110 | dimensions: |
| 1111 | - name: used |
| 1112 | - name: available |
| 1113 | - name: mssql.os_pagefile |
| 1114 | description: OS Page File |
| 1115 | unit: bytes |
| 1116 | chart_type: stacked |
| 1117 | dimensions: |
| 1118 | - name: used |
| 1119 | - name: available |
| 1120 | - name: database |
| 1121 | description: These metrics refer to individual databases. |
| 1122 | labels: |
| 1123 | - name: database |
| 1124 | description: Database name |
| 1125 | metrics: |
| 1126 | - name: mssql.database_active_transactions |
| 1127 | description: Active Transactions |
| 1128 | unit: transactions |
| 1129 | chart_type: line |
| 1130 | dimensions: |
| 1131 | - name: active |
| 1132 | - name: mssql.database_transactions |
| 1133 | description: Transactions |
| 1134 | unit: transactions/s |
| 1135 | chart_type: line |
| 1136 | dimensions: |
| 1137 | - name: transactions |
| 1138 | - name: mssql.database_write_transactions |
| 1139 | description: Write Transactions |
| 1140 | unit: transactions/s |
| 1141 | chart_type: line |
| 1142 | dimensions: |
| 1143 | - name: write |
| 1144 | - name: mssql.database_log_flushes |
| 1145 | description: Log Flushes |
| 1146 | unit: flushes/s |
| 1147 | chart_type: line |
| 1148 | dimensions: |
| 1149 | - name: flushes |
| 1150 | - name: mssql.database_log_flushed |
| 1151 | description: Log Bytes Flushed |
| 1152 | unit: bytes/s |
| 1153 | chart_type: line |
| 1154 | dimensions: |
| 1155 | - name: flushed |
| 1156 | - name: mssql.database_log_growths |
| 1157 | description: Log Growths |
| 1158 | unit: growths |
| 1159 | chart_type: line |
| 1160 | dimensions: |
| 1161 | - name: growths |
| 1162 | - name: mssql.database_log_file_size |
| 1163 | description: Transaction Log File Size |
| 1164 | unit: bytes |
| 1165 | chart_type: stacked |
| 1166 | dimensions: |
| 1167 | - name: used |
| 1168 | - name: free |
| 1169 | - name: mssql.database_log_percent_used |
| 1170 | description: Transaction Log Space Utilization |
| 1171 | unit: percentage |
| 1172 | chart_type: line |
| 1173 | dimensions: |
| 1174 | - name: used |
| 1175 | - name: mssql.database_log_truncations_shrinks |
| 1176 | description: Transaction Log Truncations and Shrinks |
| 1177 | unit: events/s |
| 1178 | chart_type: line |
| 1179 | dimensions: |
| 1180 | - name: truncations |
| 1181 | - name: shrinks |
| 1182 | - name: mssql.database_io_stall |
| 1183 | description: I/O Stall Time |
| 1184 | unit: ms |
| 1185 | chart_type: line |
| 1186 | dimensions: |
| 1187 | - name: read |
| 1188 | - name: write |
| 1189 | - name: mssql.database_data_file_size |
| 1190 | description: Data File Size |
| 1191 | unit: bytes |
| 1192 | chart_type: line |
| 1193 | dimensions: |
| 1194 | - name: size |
| 1195 | - name: mssql.database_backup_restore_throughput |
| 1196 | description: Backup/Restore Throughput |
| 1197 | unit: bytes/s |
| 1198 | chart_type: line |
| 1199 | dimensions: |
| 1200 | - name: throughput |
| 1201 | - name: mssql.database_state |
| 1202 | description: Database State |
| 1203 | unit: state |
| 1204 | chart_type: line |
| 1205 | dimensions: |
| 1206 | - name: online |
| 1207 | - name: restoring |
| 1208 | - name: recovering |
| 1209 | - name: pending |
| 1210 | - name: suspect |
| 1211 | - name: emergency |
| 1212 | - name: offline |
| 1213 | - name: mssql.database_read_only |
| 1214 | description: Database Read-Only Status |
| 1215 | unit: status |
| 1216 | chart_type: line |
| 1217 | dimensions: |
| 1218 | - name: read_only |
| 1219 | - name: read_write |
| 1220 | - name: lock stats |
| 1221 | description: These metrics refer to lock statistics by lock resource type (from performance counters). |
| 1222 | labels: |
| 1223 | - name: resource |
| 1224 | description: Lock resource type (Database, File, Object, Page, Key, Extent, RID, HoBT, etc.) |
| 1225 | metrics: |
| 1226 | - name: mssql.lock_stats_deadlocks |
| 1227 | description: Deadlocks by Resource Type |
| 1228 | unit: deadlocks/s |
| 1229 | chart_type: line |
| 1230 | dimensions: |
| 1231 | - name: deadlocks |
| 1232 | - name: mssql.lock_stats_waits |
| 1233 | description: Lock Waits by Resource Type |
| 1234 | unit: waits/s |
| 1235 | chart_type: line |
| 1236 | dimensions: |
| 1237 | - name: waits |
| 1238 | - name: mssql.lock_stats_timeouts |
| 1239 | description: Lock Timeouts by Resource Type |
| 1240 | unit: timeouts/s |
| 1241 | chart_type: line |
| 1242 | dimensions: |
| 1243 | - name: timeouts |
| 1244 | - name: mssql.lock_stats_requests |
| 1245 | description: Lock Requests by Resource Type |
| 1246 | unit: requests/s |
| 1247 | chart_type: line |
| 1248 | dimensions: |
| 1249 | - name: requests |
| 1250 | - name: lock resource |
| 1251 | description: These metrics refer to lock resource types (from sys.dm_tran_locks). |
| 1252 | labels: |
| 1253 | - name: resource |
| 1254 | description: Lock resource type (Database, File, Object, Page, Key, etc.) |
| 1255 | metrics: |
| 1256 | - name: mssql.locks_by_resource |
| 1257 | description: Lock Count by Resource Type |
| 1258 | unit: locks |
| 1259 | chart_type: line |
| 1260 | dimensions: |
| 1261 | - name: locks |
| 1262 | - name: wait type |
| 1263 | description: These metrics refer to individual wait types (from sys.dm_os_wait_stats). |
| 1264 | labels: |
| 1265 | - name: wait_type |
| 1266 | description: Wait type name |
| 1267 | - name: wait_category |
| 1268 | description: Wait category (CPU, Lock, Latch, Buffer IO, etc.) |
| 1269 | metrics: |
| 1270 | - name: mssql.wait_total_time |
| 1271 | description: Total Wait Time |
| 1272 | unit: ms |
| 1273 | chart_type: line |
| 1274 | dimensions: |
| 1275 | - name: duration |
| 1276 | - name: mssql.wait_resource_time |
| 1277 | description: Resource Wait Time |
| 1278 | unit: ms |
| 1279 | chart_type: line |
| 1280 | dimensions: |
| 1281 | - name: duration |
| 1282 | - name: mssql.wait_signal_time |
| 1283 | description: Signal Wait Time |
| 1284 | unit: ms |
| 1285 | chart_type: line |
| 1286 | dimensions: |
| 1287 | - name: duration |
| 1288 | - name: mssql.wait_max_time |
| 1289 | description: Maximum Wait Time |
| 1290 | unit: ms |
| 1291 | chart_type: line |
| 1292 | dimensions: |
| 1293 | - name: max_time |
| 1294 | - name: mssql.wait_count |
| 1295 | description: Wait Count |
| 1296 | unit: waits/s |
| 1297 | chart_type: line |
| 1298 | dimensions: |
| 1299 | - name: waits |
| 1300 | - name: job |
| 1301 | description: These metrics refer to SQL Server Agent jobs. |
| 1302 | labels: |
| 1303 | - name: job_name |
| 1304 | description: Job name |
| 1305 | metrics: |
| 1306 | - name: mssql.job_status |
| 1307 | description: Job Status |
| 1308 | unit: status |
| 1309 | chart_type: line |
| 1310 | dimensions: |
| 1311 | - name: enabled |
| 1312 | - name: disabled |
| 1313 | - name: replication |
| 1314 | description: These metrics refer to SQL Server replication publications. |
| 1315 | labels: |
| 1316 | - name: publisher_db |
| 1317 | description: Publisher database name |
| 1318 | - name: publication |
| 1319 | description: Publication name |
| 1320 | metrics: |
| 1321 | - name: mssql.replication_status |
| 1322 | description: Replication Status |
| 1323 | unit: status |
| 1324 | chart_type: line |
| 1325 | dimensions: |
| 1326 | - name: started |
| 1327 | - name: succeeded |
| 1328 | - name: in_progress |
| 1329 | - name: idle |
| 1330 | - name: retrying |
| 1331 | - name: failed |
| 1332 | - name: mssql.replication_warning |
| 1333 | description: Replication Warnings |
| 1334 | unit: flags |
| 1335 | chart_type: line |
| 1336 | dimensions: |
| 1337 | - name: expiration |
| 1338 | - name: latency |
| 1339 | - name: merge_expiration |
| 1340 | - name: merge_slow_duration |
| 1341 | - name: merge_fast_duration |
| 1342 | - name: merge_fast_speed |
| 1343 | - name: merge_slow_speed |
| 1344 | - name: mssql.replication_latency |
| 1345 | description: Replication Latency |
| 1346 | unit: seconds |
| 1347 | chart_type: line |
| 1348 | dimensions: |
| 1349 | - name: average |
| 1350 | - name: best |
| 1351 | - name: worst |
| 1352 | - name: mssql.replication_subscriptions |
| 1353 | description: Replication Subscriptions |
| 1354 | unit: subscriptions |
| 1355 | chart_type: line |
| 1356 | dimensions: |
| 1357 | - name: total |
| 1358 | - name: agents_running |
| 1359 | - name: availability group |
| 1360 | description: These metrics refer to Always On Availability Groups. Auto-detected when HADR is enabled. |
| 1361 | labels: |
| 1362 | - name: ag_name |
| 1363 | description: Availability group name |
| 1364 | metrics: |
| 1365 | - name: mssql.ag_sync_health |
| 1366 | description: Availability Group Synchronization Health |
| 1367 | unit: state |
| 1368 | chart_type: line |
| 1369 | dimensions: |
| 1370 | - name: not_healthy |
| 1371 | - name: partially_healthy |
| 1372 | - name: healthy |
| 1373 | - name: mssql.ag_recovery_health |
| 1374 | description: Availability Group Recovery Health |
| 1375 | unit: state |
| 1376 | chart_type: line |
| 1377 | dimensions: |
| 1378 | - name: primary_online |
| 1379 | - name: primary_in_progress |
| 1380 | - name: secondary_online |
| 1381 | - name: secondary_in_progress |
| 1382 | - name: mssql.ag_threads |
| 1383 | description: Availability Group Threads (SQL Server 2019+) |
| 1384 | unit: threads |
| 1385 | chart_type: line |
| 1386 | dimensions: |
| 1387 | - name: capture |
| 1388 | - name: redo |
| 1389 | - name: parallel_redo |
| 1390 | - name: availability group replica |
| 1391 | description: >- |
| 1392 | These metrics refer to per-replica state within an Availability Group. |
| 1393 | Note: on secondary replicas, the replica states DMV returns only local information. |
| 1394 | labels: |
| 1395 | - name: ag_name |
| 1396 | description: Availability group name |
| 1397 | - name: replica_server |
| 1398 | description: Replica server name |
| 1399 | - name: availability_mode |
| 1400 | description: Availability mode (synchronous_commit or asynchronous_commit) |
| 1401 | - name: failover_mode |
| 1402 | description: Failover mode (automatic or manual) |
| 1403 | metrics: |
| 1404 | - name: mssql.ag_replica_role |
| 1405 | description: Availability Group Replica Role |
| 1406 | unit: state |
| 1407 | chart_type: line |
| 1408 | dimensions: |
| 1409 | - name: primary |
| 1410 | - name: secondary |
| 1411 | - name: resolving |
| 1412 | - name: unknown |
| 1413 | - name: mssql.ag_replica_connected_state |
| 1414 | description: Availability Group Replica Connected State |
| 1415 | unit: state |
| 1416 | chart_type: line |
| 1417 | dimensions: |
| 1418 | - name: connected |
| 1419 | - name: disconnected |
| 1420 | - name: unknown |
| 1421 | - name: mssql.ag_replica_sync_health |
| 1422 | description: Availability Group Replica Synchronization Health |
| 1423 | unit: state |
| 1424 | chart_type: line |
| 1425 | dimensions: |
| 1426 | - name: not_healthy |
| 1427 | - name: partially_healthy |
| 1428 | - name: healthy |
| 1429 | - name: availability group database replica |
| 1430 | description: These metrics refer to per-database synchronization within an Availability Group. |
| 1431 | labels: |
| 1432 | - name: ag_name |
| 1433 | description: Availability group name |
| 1434 | - name: replica_server |
| 1435 | description: Replica server name |
| 1436 | - name: database |
| 1437 | description: Database name |
| 1438 | metrics: |
| 1439 | - name: mssql.ag_db_sync_state |
| 1440 | description: AG Database Synchronization State |
| 1441 | unit: state |
| 1442 | chart_type: line |
| 1443 | dimensions: |
| 1444 | - name: not_synchronizing |
| 1445 | - name: synchronizing |
| 1446 | - name: synchronized |
| 1447 | - name: reverting |
| 1448 | - name: initializing |
| 1449 | - name: mssql.ag_db_log_send_queue |
| 1450 | description: AG Database Log Send Queue Size |
| 1451 | unit: bytes |
| 1452 | chart_type: line |
| 1453 | dimensions: |
| 1454 | - name: queue_size |
| 1455 | - name: mssql.ag_db_log_send_rate |
| 1456 | description: AG Database Log Send Rate |
| 1457 | unit: bytes/s |
| 1458 | chart_type: line |
| 1459 | dimensions: |
| 1460 | - name: send_rate |
| 1461 | - name: mssql.ag_db_redo_queue |
| 1462 | description: AG Database Redo Queue Size |
| 1463 | unit: bytes |
| 1464 | chart_type: line |
| 1465 | dimensions: |
| 1466 | - name: queue_size |
| 1467 | - name: mssql.ag_db_redo_rate |
| 1468 | description: AG Database Redo Rate (averaged over active redo time since startup) |
| 1469 | unit: bytes/s |
| 1470 | chart_type: line |
| 1471 | dimensions: |
| 1472 | - name: redo_rate |
| 1473 | - name: mssql.ag_db_filestream_send_rate |
| 1474 | description: AG Database Filestream Send Rate |
| 1475 | unit: bytes/s |
| 1476 | chart_type: line |
| 1477 | dimensions: |
| 1478 | - name: send_rate |
| 1479 | - name: mssql.ag_db_secondary_lag |
| 1480 | description: AG Database Secondary Lag (SQL Server 2016+) |
| 1481 | unit: seconds |
| 1482 | chart_type: line |
| 1483 | dimensions: |
| 1484 | - name: lag |
| 1485 | - name: mssql.ag_db_suspended |
| 1486 | description: AG Database Data Movement Suspended State |
| 1487 | unit: state |
| 1488 | chart_type: line |
| 1489 | dimensions: |
| 1490 | - name: active |
| 1491 | - name: suspended |
| 1492 | - name: mssql.ag_db_failover_readiness |
| 1493 | description: AG Database Failover Readiness |
| 1494 | unit: state |
| 1495 | chart_type: line |
| 1496 | dimensions: |
| 1497 | - name: ready |
| 1498 | - name: not_ready |
| 1499 | - name: mssql.ag_db_joined_state |
| 1500 | description: AG Database Joined State |
| 1501 | unit: state |
| 1502 | chart_type: line |
| 1503 | dimensions: |
| 1504 | - name: joined |
| 1505 | - name: not_joined |
| 1506 | - name: WSFC cluster |
| 1507 | description: These metrics refer to the Windows Server Failover Clustering quorum state. |
| 1508 | labels: [] |
| 1509 | metrics: |
| 1510 | - name: mssql.ag_cluster_quorum_state |
| 1511 | description: WSFC Cluster Quorum State |
| 1512 | unit: state |
| 1513 | chart_type: line |
| 1514 | dimensions: |
| 1515 | - name: normal |
| 1516 | - name: forced |
| 1517 | - name: unknown |
| 1518 | - name: WSFC cluster member |
| 1519 | description: These metrics refer to individual WSFC cluster members. |
| 1520 | labels: |
| 1521 | - name: cluster_member |
| 1522 | description: Cluster member name |
| 1523 | metrics: |
| 1524 | - name: mssql.ag_cluster_member_state |
| 1525 | description: WSFC Cluster Member State |
| 1526 | unit: state |
| 1527 | chart_type: line |
| 1528 | dimensions: |
| 1529 | - name: up |
| 1530 | - name: down |
| 1531 | - name: mssql.ag_cluster_member_quorum_votes |
| 1532 | description: WSFC Cluster Member Quorum Votes |
| 1533 | unit: votes |
| 1534 | chart_type: line |
| 1535 | dimensions: |
| 1536 | - name: votes |
| 1537 | - name: AG page repair |
| 1538 | description: These metrics refer to automatic page repair events per database in an Availability Group. |
| 1539 | labels: |
| 1540 | - name: database |
| 1541 | description: Database name |
| 1542 | metrics: |
| 1543 | - name: mssql.ag_page_repair |
| 1544 | description: AG Automatic Page Repair Events |
| 1545 | unit: repairs |
| 1546 | chart_type: line |
| 1547 | dimensions: |
| 1548 | - name: successful |
| 1549 | - name: failed |