Microsoft SQL Server

Supported by Dynatrace

Expand visibility to improve the health and performance monitoring of your Microsoft SQL Servers.

Overview
Get started
Metrics
Versions
Screenshots

Microsoft SQL Server database monitoring is based on a remote monitoring approach implemented as a Dynatrace ActiveGate extension. The extension queries MS SQL databases for key performance and health metrics, extending your visibility, and allowing DAVIS AI to provide anomaly detection and problem analysis.

use cases

Understand all dependencies of your applications to the database, which database statements are executed and their performance.
Improve the performance of your application by reducing or optimizing typical database patterns like the 1+N query problem
Understand the resource impact that your applications have on your database.
Understand the impact of resource shortages, locks or other database issues have on your application by observing the database server itself.
Track health and performance of the MS SQL servers
Speed up the application problem diagnosis, where it’s caused by the database

Compatibility information

Supported types of SQL Server

SQL Server, Azure SQL Database, Azure SQL Managed Instance.

Supported version of SQL Server

Microsoft SQL Server with mainstream or extended support by Microsoft. Please, see the Microsoft documentation for end-of-life details.

UPGRADE NOTICE: When updating monitoring configurations to version 1.2.0+, feature sets need to be enabled for the monitoring to continue.

Get started

Activation

If your MS SQL database server is running on a virtual machine directly, you will need to install OneAgent on that virtual machine to get started.
Activate the remote MS SQL Server extension to get further metrics and events (see below).
Activate the local MS SQL Server extension to get the basic metrics, obtained via WMI, in case when you already have OneAgent installed on the DB server's host, and you're not interested in deep monitoring of the DB server statistics.

Ensure that you have log monitoring enabled to get full log insight.

Steps to activate remote monitoring (version 1.257+)

Activate Extension in the Hub by going to:
Manage → Dynatrace Hub → SQL Server → Add to environment
Add new monitoring configuration

Permissions required for remote monitoring

Permissions specified in the Microsoft documentation for sys.dm_os_performance_counters DMV, msdb.dbo.backupset, and master.dbo.sysdatabases are required.
Important: In order to monitor metrics for logical databases, such as master, tempdb, and user created databases, the msdb.dbo.backupset view must be present and accessible. Currently it's not supported on Azure SQL database (PAAS).
Following permissions are required: VIEW SERVER STATE, VIEW DATABASE STATE, VIEW ANY DEFINITION, VIEW ANY DATABASE (or CREATE DATABASE permission in the master database).
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required.

UPGRADE NOTICE: When updating monitoring configurations to version 1.2.0+, feature sets need to be enabled for the monitoring to continue.

Metrics

Name	Metric key	Unit	Description
Log files size	sql-server.databases.log.filesSize	KiloByte	Cumulative size of all the transaction log files in the database
Log files used size	sql-server.databases.log.filesUsedSize	KiloByte	The cumulative used size of all the log files in the database
Log flush waits	sql-server.databases.log.flushWaits.count	Count	Number of commits waiting for the log flush
Log growths	sql-server.databases.log.growths.count	Count	Number of times the transaction log for the database has been expanded
Log shrinks	sql-server.databases.log.shrinks.count	Count	Number of log shrinks for this database
Log truncations	sql-server.databases.log.truncations.count	Count	Number of times the transaction log has been truncated (in Simple Recovery Model)
Percent log used	sql-server.databases.log.percentUsed	Percent	Percantage of space in the log that is in use
Transactions	sql-server.databases.transactions.count	Count	Number of transactions started for the database
Database state	sql-server.databases.state	State	Database state

Name	Metric key	Unit	Description
CPU Count	sql-server.host.cpus	Unspecified	Detected CPU Count

Name	Metric key	Unit	Description
Release number	sql-server.releaseNumber	Unspecified	Release number of SQL Server
Physical memory	sql-server.memory.physical	KiloByte	Total amount of physical memory on the machine
Virtual memory	sql-server.memory.virtual	KiloByte	Total amount of virtual address space available to the process in user mode
Total server memory	sql-server.memory.total	KiloByte	Amount of memory the server has committed using the memory manager
Target server memory	sql-server.memory.target	KiloByte	Amount of memory that can be consumed by memory manager
Time spent in kernel mode	sql-server.cpu.kernelTime.count	MilliSecond	Time spent by all threads in kernel mode
Time spent in user mode	sql-server.cpu.userTime.count	MilliSecond	Time spent by all threads in user mode
Free list stalls	sql-server.buffers.freeListStalls.count	Count	Number of requests that had to wait for a free page
Page reads	sql-server.buffers.pageReads.count	Count	Number of physical database page reads that are issued
Page writes	sql-server.buffers.pageWrites.count	Count	Number of physical database page writes that are issued
Checkpoint pages	sql-server.buffers.checkpointPages.count	Count	Number of pages flushed to disk by a checkpoint or other operations that require all dirty pages to be flushed
Page life expectancy	sql-server.buffers.pageLifeExpectancy	Second	Number of seconds a page will stay in the buffer pool without references
Logins	sql-server.general.logins.count	Count	Number of logins started, excluding pooled connections
User connections	sql-server.general.userConnections	Count	Number of users currently connected to SQL Server
Processes blocked	sql-server.general.processesBlocked	Count	Number of currently blocked processes
Lock timeouts	sql-server.locks.timeouts.count	Count	Number of lock requests that timed out, including requests for NOWAIT locks
Deadlocks	sql-server.locks.deadlocks.count	Count	Number of lock requests that resulted in a deadlock
Lock waits	sql-server.locks.waits.count	Count	Number of lock requests that required the caller to wait
Lock wait time	sql-server.locks.waitTime.count	MilliSecond	Wait time for locks
Latch waits	sql-server.latches.waits.count	Count	Number of latch requests that could not be granted immediately
Average latch wait time	sql-server.latches.averageWaitTime.count	MilliSecond	Average latch wait time for latch requests that had to wait
Batch requests	sql-server.sql.batchRequests.count	Count	Number of Transact-SQL command batches received
SQL compilations	sql-server.sql.compilations.count	Count	Number of SQL statement compilations
SQL re-compilations	sql-server.sql.recompilations.count	Count	Number of SQL statement re-compilations
Connection memory	sql-server.memory.connection	KiloByte	Total amount of dynamic memory the server is using for maintaining connections
Memory grants outstanding	sql-server.memory.grantsOutstanding	Count	Number of processes that have successfully acquired a workspace memory grant
Memory grants pending	sql-server.memory.grantsPending	Count	Number of processes waiting for a workspace memory grant
Buffer cache hit	sql-server.buffers.cacheHitRatio	Percent	Percentage of pages found in the buffer cache without having to read from disk

Name	Metric key	Unit	Description
Availability mode	sql-server.always-on.ar.availabilityMode	State	The availability mode of the replica
Failover mode	sql-server.always-on.ar.failoverMode	State	The failover mode of the replica
Is replica local	sql-server.always-on.ar.isLocal	State	Whether the replica is local
Replica role	sql-server.always-on.ar.role	State	Current groups role of a local replica or a connected remote replica
Operational state	sql-server.always-on.ar.operationalState	State	Current operational state of the replica
Recovery health	sql-server.always-on.ar.recoveryHealth	State	Rollup of the database_state column
Synchronization health	sql-server.always-on.ar.synchronizationHealth	State	Rollup of the database synchronization state of all joined replicas and the availability mode of the replica
Connected state	sql-server.always-on.ar.connectedState	State	Whether a secondary replica is currently connected to the primary replica

Name	Metric key	Unit	Description
Hours since last backup	sql-server.databases.backup.hoursPassed	Hour	Number of hours passed since last backup

Name	Metric key	Unit	Description
Automated backup preference	sql-server.always-on.ag.automatedBackupPreference	State	Preferred location for backups in this availability group
Primary recovery health	sql-server.always-on.ag.primaryRecoveryHealth	State	Indicates the recovery health of the primary replica
Secondary recovery health	sql-server.always-on.ag.secondaryRecoveryHealth	State	Indicates the recovery health of a secondary replica
Synchronization health	sql-server.always-on.ag.synchronizationHealth	State	Reflects a rollup of the synchronization health of all replicas the group

Name	Metric key	Unit	Description
Synchronization state	sql-server.always-on.db.synchronizationState	State	Data-movement state
Synchronization health	sql-server.always-on.db.synchronizationHealth	State	Intersection of the synchronization state of a database that is joined to the group on the replica and the availability mode of the replica
Database state	sql-server.always-on.db.state	State	State of the availability database
Log send queue size	sql-server.always-on.db.logSendQueueSize	KibiByte	Amount of log records of the primary database not sent to the secondary databases
Log send rate	sql-server.always-on.db.logSendRate	KiloBytePerSecond	Average rate at which primary replica instance sends data
Redo queue size	sql-server.always-on.db.redoQueueSize	KiloByte	Amount of log records in the log files of the secondary replica that has not yet been redone
Redo rate	sql-server.always-on.db.redoRate	KiloBytePerSecond	Average Rate at which the log records are being redone on a given secondary database
Filestream send rate	sql-server.always-on.db.filestreamSendRate	KiloBytePerSecond	The rate at which the filestream files are shipped to the secondary replica

Versions

Keep up-to-date with newest releases

To have more information on how to install the downloaded package, please follow the instructions on this page.

UPDATE WARNING

SQL Server extension starting with version 1.2+ introduces feature sets. The feature sets need to be enabled for each updated monitoring configuration. Otherwise data collection for monitored endpoints stops after updating configuration.

Changes in this release

Improvements:

Instance is now visible on the Host screen if OneAgent is installed
Action buttons added on each entity screen
Improved entity listings on each screen
Uniform style for dashboard
Improved metric definitions with associations to source entities
Added properties for each monitored entity
Added relations to other entities inside property cards
Improved state charts
Added database state metric

Bugfixes:

Wrong count metric aggregations addressed
Proper identification of instances and replicas, separate from each other (duplicate instances)
Typos in screen charts
Enable monitoring of msdb and tempdb
Compatibility with SQL Server version <2019 for database metrics added back

Download