Microsoft SQL Server

Metrics, Dashboards, Alerts and more for Microsoft SQL Server Integration in Sysdig Monitor.
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

AlertDescriptionFormat
[Microsoft SQL Server] Low Free MemorySQL instance running low on free memory.Prometheus
[Microsoft SQL Server] High Log File UsageLog file will be full in 12h.Prometheus
[Microsoft SQL Server] Low Buffer Cache Hit RatioBuffer Cache Hit Ratio is low in the SQL instance.Prometheus
[Microsoft SQL Server] Deadlocks detectedDeadlocks detected in the SQL instance.Prometheus

List of Dashboards

Microsoft SQL Server

The dashboard provides information on SQL Server instances and databases. Microsoft SQL Server

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.