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

List of SQL Server instances associated with a host monitored by OneAgent

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.

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.

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.

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

screen injections

screen chart groups

screen layout

list screen layout

screen properties

generic type

generic relationship

dashboards

screen entities lists

metric metadata

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
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
Transactions	sql-server.databases.transactions.count	Number of transactions started for the database	Count
Database state	sql-server.databases.state	Database state	State

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

Metric name	Metric key	Description	Unit
Release number	sql-server.releaseNumber	Release number of SQL Server	Unspecified
Physical memory	sql-server.memory.physical	Total amount of physical memory on the machine	KiloByte
Virtual memory	sql-server.memory.virtual	Total amount of virtual address space available to the process in user mode	KiloByte
Total server memory	sql-server.memory.total	Amount of memory the server has committed using the memory manager	KiloByte
Target server memory	sql-server.memory.target	Amount of memory that can be consumed by memory manager	KiloByte
Time spent in kernel mode	sql-server.cpu.kernelTime.count	Time spent by all threads in kernel mode	MilliSecond
Time spent in user mode	sql-server.cpu.userTime.count	Time spent by all threads in user mode	MilliSecond
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
Logins	sql-server.general.logins.count	Number of logins started, excluding pooled connections	Count
User connections	sql-server.general.userConnections	Number of users currently connected to SQL Server	Count
Processes blocked	sql-server.general.processesBlocked	Number of currently blocked processes	Count
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
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
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
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

Metric name	Metric key	Description	Unit
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

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

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

Metric name	Metric key	Description	Unit
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

Full version history

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

ReleaseDate

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.

Dynatrace Hub

Build your own app

All (670)Log Management and Analytics Kubernetes Infrastructure Observability Automations Application Observability Application Security Digital Experience Business Analytics

Dynatrace Developer