Dynatrace Hub

Extend the platform,
empower your team.

Microsoft SQL Server

Improve the health and performance monitoring of your Microsoft SQL Servers.

Extension by Dynatrace

Free trial Documentation

Product information
Release notes

Overview

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

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 if you prefer to leverage the OneAgent already installed on the DB server's host.
Ensure that you have log monitoring enabled to be able to read extension status logs and monitor longest running queries and database files.

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
Add new connection endpoint for each monitored instance

Feature sets selection

It is recommended to set up an individual endpoint for each monitored instance to retrieve full information about its status, resource consumption, and performance.
When connecting to an Always On cluster, the "Always On" feature set should only be enabled for primary replica. It is recommended to create a separate monitoring configuration to monitor Always On clusters.

Breaking changes

v2.0.0:
- All monitoring configurations need to be recreated because of change in feature sets.
- instance dimension changes its meaning to only contains an actual named instance name or MSSQLSERVER by default.
- hoursSinceBackup metric is removed and replaced by sql-server.databases.backup.age.
v1.2.0:
- When updating monitoring configurations to version 1.2.0+, feature sets need to be enabled for the monitoring to continue.

Compatibility information

Supported types of SQL Server:

SQL Server
- Enterprise
- Standard
- Developer
- Web
- Express
Azure SQL Database
Azure SQL Managed Instance

Supported versions of SQL Server:

Any version of SQL Server with active extended support by Microsoft is supported by this extension. Please refer to the official Microsoft documentation about lifecycle dates for SQL Server.

Resources

More Information

Support

Built and supported by Dynatrace

Dynatrace support center

Subscribe to new releases

copy to clipboard

Extension content

Content typeNumber of items included

alerts

screen actions

metric metadata

screen injections

list screen layout

screen properties

generic type

generic relationship

dashboards

screen entities lists

screen layout

screen chart groups

Feature sets

Below is a complete list of the feature sets provided in this version. To ensure a good fit for your needs, individual metrics can be activated and deactivated by your administrator during configuration.

Feature setsNumber of metrics included

Metric name	Metric key	Description	Unit
Uptime	sql-server.sessions	Number of active sessions per user	Count

Metric name	Metric key	Description	Unit
Latch waits	sql-server.latches.waits.count	Number of latch requests that could not be granted immediately	Count
Average latch wait time	sql-server.latches.averageWaitTime.count	Average latch wait time for latch requests that had to wait	MilliSecond

Metric name	Metric key	Description	Unit
Bytes sent to replica	sql-server.replica.bytesSent.count	Message bytes queued to be sent to replicas	Byte
Sends to replica	sql-server.replica.sends.count	Number of messages queued to be sent to replica	Count
Bytes sent to transport	sql-server.replica.bytesSentToTransport.count	Dequeued message bytes sent across network to replicas	Byte
Sends to transport	sql-server.replica.sendsToTransport.count	Number of messages sent across network to replicas	Count
Bytes received from replica	sql-server.replica.bytesReceived.count	Message bytes received from replica	Byte
Receives from replica	sql-server.replica.receives.count	Number of messages received from replica	Count
Resent messages	sql-server.replica.resentMessages.count	Number of messages that had to be resent to replica	Count

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

Metric name	Metric key	Description	Unit
Batch requests	sql-server.sql.batchRequests.count	Number of Transact-SQL command batches received	Count
SQL compilations	sql-server.sql.compilations.count	Number of SQL statement compilations	Count
SQL re-compilations	sql-server.sql.recompilations.count	Number of SQL statement re-compilations	Count

Metric name	Metric key	Description	Unit
Lock timeouts	sql-server.locks.timeouts.count	Number of lock requests that timed out, including requests for NOWAIT locks	Count
Deadlocks	sql-server.locks.deadlocks.count	Number of lock requests that resulted in a deadlock	Count
Lock waits	sql-server.locks.waits.count	Number of lock requests that required the caller to wait	Count
Lock wait time	sql-server.locks.waitTime.count	Wait time for locks	MilliSecond

Metric name	Metric key	Description	Unit
Minutes since last backup	sql-server.databases.backup.age	Number of minutes passed since last backup	Minute
Size of the last backup	sql-server.databases.backup.size	Size of the last backup of that type	Byte

Metric name	Metric key	Description	Unit
Database file size	sql-server.databases.file.size	Total size of files of specific types per database	KiloByte
Database file used size	sql-server.databases.file.usedSpace	Total size of space used in files of specific types per database	KiloByte
Database file empty size	sql-server.databases.file.emptySpace	Total size of empty space in files of specific types per database	KiloByte

Metric name	Metric key	Description	Unit
Automated backup preference	sql-server.always-on.ag.automatedBackupPreference	Preferred location for backups in this availability group	State
Primary recovery health	sql-server.always-on.ag.primaryRecoveryHealth	Indicates the recovery health of the primary replica	State
Secondary recovery health	sql-server.always-on.ag.secondaryRecoveryHealth	Indicates the recovery health of a secondary replica	State
Synchronization health	sql-server.always-on.ag.synchronizationHealth	Reflects a rollup of the synchronization health of all replicas the group	State
Availability mode	sql-server.always-on.ar.availabilityMode	The availability mode of the replica	State
Failover mode	sql-server.always-on.ar.failoverMode	The failover mode of the replica	State
Is replica local	sql-server.always-on.ar.isLocal	Whether the replica is local	State
Replica role	sql-server.always-on.ar.role	Current groups role of a local replica or a connected remote replica	State
Operational state	sql-server.always-on.ar.operationalState	Current operational state of the replica	State
Recovery health	sql-server.always-on.ar.recoveryHealth	Rollup of the database_state column	State
Synchronization health	sql-server.always-on.ar.synchronizationHealth	Rollup of the database synchronization state of all joined replicas and the availability mode of the replica	State
Connected state	sql-server.always-on.ar.connectedState	Whether a secondary replica is currently connected to the primary replica	State
Synchronization state	sql-server.always-on.db.synchronizationState	Data-movement state	State
Synchronization health	sql-server.always-on.db.synchronizationHealth	Intersection of the synchronization state of a database that is joined to the group on the replica and the availability mode of the replica	State
Database state	sql-server.always-on.db.state	State of the availability database	State
Log send queue size	sql-server.always-on.db.logSendQueueSize	Amount of log records of the primary database not sent to the secondary databases	KibiByte
Log send rate	sql-server.always-on.db.logSendRate	Average rate at which primary replica instance sends data	KiloBytePerSecond
Redo queue size	sql-server.always-on.db.redoQueueSize	Amount of log records in the log files of the secondary replica that has not yet been redone	KiloByte
Redo rate	sql-server.always-on.db.redoRate	Average Rate at which the log records are being redone on a given secondary database	KiloBytePerSecond
Filestream send rate	sql-server.always-on.db.filestreamSendRate	The rate at which the filestream files are shipped to the secondary replica	KiloBytePerSecond

Metric name	Metric key	Description	Unit
Free list stalls	sql-server.buffers.freeListStalls.count	Number of requests that had to wait for a free page	Count
Page reads	sql-server.buffers.pageReads.count	Number of physical database page reads that are issued	Count
Page writes	sql-server.buffers.pageWrites.count	Number of physical database page writes that are issued	Count
Checkpoint pages	sql-server.buffers.checkpointPages.count	Number of pages flushed to disk by a checkpoint or other operations that require all dirty pages to be flushed	Count
Page life expectancy	sql-server.buffers.pageLifeExpectancy	Number of seconds a page will stay in the buffer pool without references	Second
Connection memory	sql-server.memory.connection	Total amount of dynamic memory the server is using for maintaining connections	KiloByte
Memory grants outstanding	sql-server.memory.grantsOutstanding	Number of processes that have successfully acquired a workspace memory grant	Count
Memory grants pending	sql-server.memory.grantsPending	Number of processes waiting for a workspace memory grant	Count
Buffer cache hit	sql-server.buffers.cacheHitRatio	Percentage of pages found in the buffer cache without having to read from disk	Percent

Full version history

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

ReleaseDate

Major release with breaking changes

Breaking changes

All monitoring configurations must be recreated because of change in feature sets.
instance dimension changes its meaning to only contains an actual named instance name or MSSQLSERVER by default.
hoursSinceBackup metric is removed and replaced by sql-server.databases.backup.age.

New features

Query level monitoring: Top 100 longest running queries are collected every 5 minutes (enabled through Queries feature set).
Largest database files monitoring: Top 100 largest database files as monitored every 5 minutes (enabled through Database files feature set).
instance dimension is now retrieved from "InstanceName" server property but only contains an actual named instance name. Defaults to MSSQLSERVER for unnamed instances.
New server dimension is now retrieved from ServerName but only contains an actual machine or server name, without named instance.
Feature sets are not more granularly grouped allowing for fine grained control over which metrics are collected.
Detailed backup monitoring with backup size and backup age collected for backup type and individual database.
Detailed database files monitoring with average file size, used space, and empty space collected for each file type and individual database.
Entity screens are now adapted to the latest versions of SaaS and utilize DQL.
Monitoring of communication with availability replcia (bytes sent/received).
Conversion of database ID to human readable name for Azure SQL databases.
CPU and Memory monitoring.
Uptime monitoring.

Bug fixes

Incorrect detection of named instances in certain cases was addressed through better identification of instance and server dimensions.

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

Fix: missing column name for SQL Server versions below 2019

Always On support
Metric type fixed for Lock Wait Time and Latch Average Wait Time
Entities not being generated for hosts without a domain name
Entity topology updated
List of database entities is now available

Support for monitoring databases on named instances.

Release v1.0.1
Contains the same metrics that were collected by the previous generation of the SQL Server remote monitoring.