Microsoft SQL Server
This integration is disabled by default. Please refer to Enable and Disable Integrations to enable it in your account.
This integration has 28 metrics.
Timeseries generated: ~500TS per instance and ~100TS per database
The metrics in this integration are scraped from the Windows Exporter in the Windows integration. To enable the SQL Server metrics in the Windows Exporter, add the mssql collector to its config.yaml file.
List of Alerts
| Alert | Description | Format |
|---|---|---|
| [Microsoft SQL Server] Low Free Memory | SQL instance running low on free memory. | Prometheus |
| [Microsoft SQL Server] High Log File Usage | Log file will be full in 12h. | Prometheus |
| [Microsoft SQL Server] Low Buffer Cache Hit Ratio | Buffer Cache Hit Ratio is low in the SQL instance. | Prometheus |
| [Microsoft SQL Server] Deadlocks detected | Deadlocks detected in the SQL instance. | Prometheus |
List of Dashboards
Microsoft SQL Server
The dashboard provides information on SQL Server instances and databases.
List of Metrics
| Metric name |
|---|
| windows_logical_disk_free_bytes |
| windows_logical_disk_size_bytes |
| windows_mssql_accessmethods_full_scans |
| windows_mssql_accessmethods_worktables_from_cache_hits |
| windows_mssql_bufman_buffer_cache_hits |
| windows_mssql_bufman_buffer_cache_lookups |
| windows_mssql_databases_data_files_size_bytes |
| windows_mssql_databases_log_cache_hits |
| windows_mssql_databases_log_cache_lookups |
| windows_mssql_databases_log_files_size_bytes |
| windows_mssql_databases_log_files_used_size_bytes |
| windows_mssql_databases_log_flushed_bytes |
| windows_mssql_databases_transactions |
| windows_mssql_genstats_active_temp_tables |
| windows_mssql_genstats_logical_connections |
| windows_mssql_genstats_logins |
| windows_mssql_genstats_logouts |
| windows_mssql_genstats_mars_deadlocks |
| windows_mssql_genstats_sql_trace_io_provider_lock_waits |
| windows_mssql_locks_deadlocks |
| windows_mssql_locks_lock_requests |
| windows_mssql_memmgr_connection_memory_bytes |
| windows_mssql_memmgr_database_cache_memory_bytes |
| windows_mssql_memmgr_free_memory_bytes |
| windows_mssql_memmgr_lock_memory_bytes |
| windows_mssql_memmgr_target_server_memory_bytes |
| windows_mssql_memmgr_total_server_memory_bytes |
| windows_mssql_transactions_tempdb_free_space_bytes |
Prerequisites
None.
Installation
Installing an exporter is not required for this integration.
Monitoring and Troubleshooting Microsoft SQL Server
This document describes important metrics and queries that you can use to monitor and troubleshoot SQL Server.
Server Stats
Login Activity
Use the following query to check the login / logout activity in the SQL Server instance:
rate(windows_mssql_genstats_logins[5m])
Transaction activity
You can monitor transaction activity with this query:
rate(windows_mssql_databases_transactions[5m])) by (hostname, mssql_instance))
Database Stats
Database Size
Use the following query to check databases size:
avg(windows_mssql_databases_data_files_size_bytes) by (hostname,mssql_instance, database)
Log File Size
Use the following queries to check Log File Size and Used Size:
avg(windows_mssql_databases_log_files_size_bytes) by (hostname,mssql_instance, database)
avg(windows_mssql_databases_log_files_used_size_bytes) by (hostname,mssql_instance, database)
Calculate the % Log File Used Size with this query:
avg(windows_mssql_databases_log_files_used_size_bytes) by (hostname,mssql_instance, database) / avg(windows_mssql_databases_log_files_size_bytes) by (hostname,mssql_instance, database) * 100
Transactions by Database
Use the following query to check number of transactions per second in a database:
rate(windows_mssql_databases_transactions[5m])) by (hostname, mssql_instance, database)
Log Flushes
Check the number of log flushes per second in a database:
rate(windows_mssql_databases_log_flushed_bytes[5m])) by (hostname, mssql_instance, database)
Full scans
Check the number of full scans per second in a database:
rate(windows_mssql_accessmethods_full_scans[5m])) by (hostname, mssql_instance, database)
A high number could explain high values in CPU usage. Check for queries doing full table scans.
Cache Stats
Buffer Cache Hit Ratio
Use the following query to check the Buffer Cache Hit Ratio:
windows_mssql_bufman_buffer_cache_hits / windows_mssql_bufman_buffer_cache_lookups
This value should be close to 98% because reading from the cache is much less expensive than reading from the disk. Check the amount of memory available in the SQL Server if you have a low value.
Log Cache Hit Ratio
Use the following query to check the Log Cache Hit Ratio:
windows_mssql_databases_log_cache_hits / windows_mssql_databases_log_cache_lookups
Access Methods Worktables Cache Hit Ratio
Use the following query to check the Access Methods Worktables Cache Hit Ratio:
windows_mssql_databases_log_cache_hits / windows_mssql_databases_log_cache_lookups
Lock Stats
Locks Requested By Resource
Use the following query to check the number of locks requested by resource per second:
rate(windows_mssql_locks_lock_requests[5m])
Deadlocks by Resource
Use the following query to check the number of locks requested by resource per second:
rate(windows_mssql_locks_deadlocks[5m])
Prevent the occurrence of SQL deadlocks. This value should be 0/s. If there are deadlocks check xml_deadlock_report in the SQL Server Management Studio and review affected queries.
Memory Stats
Total / Target Memory
Use the following queries to check total/target memory:
windows_mssql_memmgr_target_server_memory_bytes
windows_mssql_memmgr_total_server_memory_bytes
Memory pressure might appear if total memory is close to target memory.
Free Memory
Use the following query to check free memory:
windows_mssql_memmgr_free_memory_bytes
Agent Configuration
This integration has no default agent job.
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.