Microsoft SQL Server extension

Latest Dynatrace
Concept
4-min read
Published Jun 27, 2025

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

Compatibility information

Supported types of SQL Server

SQL Server (editions: Enterprise, Standard, Developer, Web, Express) on Windows servers.
Azure SQL Database.
Azure SQL Managed Instance.

Important note: The extension is reported to work with other types of SQL Server, such as AWS RDS or SQL Server on Linux, but they are not officially supported.

Supported types of HA or replication

Always On

Important note: Other types of replication and HA monitoring, including publisher/subscriber model, are not supported yet.

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.

Simultaneous use of different versions of extension

Running two or more different versions of the extension against the same SQL Server is not supported.
Running different major versions (for example, version 1 and version 2) of the extension on the same tenant is highly discouraged and is not supported. This will break the topology model.

Compatibility with OneAgent

In order for SQL Server Instance entity to be linked to the Host entity, they must share the same IP address. If the monitoring configuration for SQL Server is configured with a different IP address, two instances will not be linked with each other.

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.

See the largest files in your SQL Server Environment via logs ingested by the extension.

See the longest running queries in your SQL Server environment via logs ingested by the extension.

1 of 6Get an overview of your SQL Server monitoring using New Dashboards, which are bundled with the extension upon activation.

Get started

Activation

Activate Extension in the Hub: Dynatrace Hub → SQL Server → Add to environment.
Add a monitoring configuration with endpoints that connect to individual SQL Server instances.
- For more quick start information, please see the documentation.
Enabling log monitoring will activate extension status logs and allow for monitoring of the longest running queries and largest database files.

Use cases

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

Compatibility information

Supported types of SQL Server

SQL Server (editions: Enterprise, Standard, Developer, Web, Express) on Windows servers.
Azure SQL Database.
Azure SQL Managed Instance.

Important note: The extension is reported to work with other types of SQL Server, such as AWS RDS or SQL Server on Linux, but they are not officially supported.

Supported types of HA or replication

Always On

Important note: Other types of replication and HA monitoring, including publisher/subscriber model, are not supported yet.

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.

Simultaneous use of different versions of extension

Running two or more different versions of the extension against the same SQL Server is not supported.
Running different major versions (for example, version 1 and version 2) of the extension on the same tenant is highly discouraged and is not supported. This will break the topology model.

Compatibility with OneAgent

In order for SQL Server Instance entity to be linked to the Host entity, they must share the same IP address. If the monitoring configuration for SQL Server is configured with a different IP address, two instances will not be linked with each other.

Using DQL and Logs

Top queries

How to enable?

Collection of top queries ordered by total duration can be enabled using the Queries feature set.

Prerequisites

Query Store must be enabled on SQL Server instance.
The database from which queries are collected is determined by:
- Explicit database name specified in the endpoint for monitoring configuration; or
- Default database configured for the connected user.

How often is the information updated?

Top queries are fetched by extension every 5 minutes.

How to list top queries?

The query below, when executed in Logs and Events, displays top queries, as observed within the most recent 5 min timeframe, using DQL:

fetch logs, from:now()-60m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "longest_queries")
| fields total_duration, avg_duration, content, server, instance, num_executions, query_plan
| sort asDouble(total_duration) desc

Description of fields:

total_duration field represents a sum of all executions of this query over the given 5 min timeframe in seconds
avg_duration represents an average execution time of this query of the given 5 min timeframe in seconds
content field contains the SQL text of the query

On Managed tenants: log records can be retrieved by filtering logs using 2 attributes

dt.extension.name: com.dynatrace.extension.sql-server; and
event.group: longest_queries.

Largest files

How to enable?

Collection of largest database files by size can be enabled using the Database files feature set.

How often is the information updated?

Top database files by size are fetched by extension every 5 minutes.

How to list the largest database files by size?

The query below, when executed in Logs and Events, displays the largest database files, as observed within the most recent 5 min timeframe, by size using DQL:

fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "largest_files")
| fields content, file_size, file_type_desc, file_state_desc, database, server, instance, file_used_space, file_empty_space
| sort asDouble(file_size) desc

Description of fields:

content field represents the physical name of the file as handled by host OS
file_size is reported in KB
file_used_space is reported in KB and represents amount of space occupied by allocated pages within a specific file
file_empty_space is reported in KB and represents amount of space that is still empty within a specifc file

On Managed tenants: log records can be retrieved by filtering logs using 2 attributes

dt.extension.name: com.dynatrace.extension.sql-server; and
event.group: largest_files.

Current jobs

How to enable?

Monitoring of current jobs can be enabled using the Jobs feature set.

How often is the information updated?

Current jobs are fetched by extension every 5 minutes.

How to list current jobs?

The query below, when executed in Logs and Events, displays current jobs, as observed within the most recent 5 min timeframe, using DQL:

fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "current_jobs")
| fields job_name, job_status, content, enabled, last_run_outcome, duration, instance, server, start_execution_date, stop_execution_date, job_category, category_name
| sort asDouble(duration) desc

Description of fields:

content field represents the last execution outcome message.
job_status and last_run_outcome are identical, except for two situations:
- When the job was not executed yet, the job_status equals Idle
- When the job is currently being executed, the job_status equals In Progress
duration represents complete job duration in seconds after execution is finished
category_id represents the category id of the job
category_name represents the Name assigned to the category id

On Managed tenants: log records can be retrieved by filtering logs using 2 attributes

dt.extension.name: com.dynatrace.extension.sql-server; and
event.group: current_jobs.

Failed jobs

How to enable?

Monitoring of failed jobs can be enabled using the Jobs feature set.

How often is the information updated?

Failed jobs are fetched by extension every 5 minutes.

How to list failed jobs?

The query below, when executed in Logs and Events, displays failed jobs, as observed within the most recent 5 min timeframe, using DQL:

fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "failed_jobs")
| fields job_name, step_name, outcome, content, duration, instance, server, sql_severity, retries_attempted, start_execution_date, stop_execution_date
| sort stop_execution_date desc

Description of fields:

content field represents the message of the last executed step and usually contains the error.
outcome represents the final job status message as composed by SQL Server Agent.
duration represents complete job duration in seconds after execution is finished

On Managed tenants: log records can be retrieved by filtering logs using 2 attributes

dt.extension.name: com.dynatrace.extension.sql-server; and
event.group: failed_jobs.

Permissions

Each available Feature Set is supported by a corresponding set of SQL Server types. For details on the individual permissions that must be granted to the extension user for each Feature Set, please refer to the Involved Views and Tables section and the granular permission details for each system view provided below.

Supported systems and involved system views per feature set

default

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

Views and tables involved:

sys.dm_os_sys_info
sys.dm_os_performance_counters
sys.databases

Memory

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

Involved Views and Tables:

sys.dm_os_performance_counters

Locks

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

Involved Views and Tables:

sys.dm_os_performance_counters

Latches

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

Involved Views and Tables:

sys.dm_os_performance_counters

Queries

Monitoring query performance stats

Supported on:
- SQL Server (all versions)
Involved Views and Tables:
- sys.dm_os_performance_counters
Monitoring TOP longest queries

Supported on:
- SQL Server (2016 and later)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
Involved Views and Tables:
- sys.query_store_runtime_stats
- sys.query_store_plan
- sys.query_store_query
- sys.query_store_query_text

Replication

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

Involved Views and Tables:

sys.dm_os_performance_counters

Sessions

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric

Involved Views and Tables:

sys.dm_exec_sessions

Transaction logs

Supported on:

SQL Server (all versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

Involved Views and Tables:

sys.dm_os_performance_counters

Backups

Monitoring age of latest backup and individual backups per database

Supported on:
- SQL Server (all versions)
- Azure SQL Managed Instance
Involved Views and Tables:
- sys.databases
- msdb.dbo.backupset
- msdb.dbo.backupmediafamily
- msdb.dbo.backupmediaset
Monitoring backup files size per database

Supported on:
- SQL Server (all versions)
Involved Views and Tables:
- sys.databases
- msdb.dbo.backupset
- msdb.dbo.backupmediafamily
- msdb.dbo.backupmediaset
- msdb.dbo.backupfile
- sys.master_files
Monitoring individual Azure SQL Database backups

Supported on:
- Azure SQL Database
Involved Views and Tables:
- sys.db_database_backups

Database files

Monitoring database files stats

Supported on:
- SQL Server (all versions)
- Azure SQL Managed Instance
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.master_files
Monitoring largest database files on Azure SQL Database

Supported on:
- Azure SQL Database
Involved Views and Tables:
- sys.database_files
Monitoring largest database files on other SQL Server types

Supported on:
- SQL Server (all versions)
- Azure SQL Managed Instance
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.master_files

Always On

Supported on:

SQL Server (2016 and later)

Involved Views and Tables:

sys.availability_groups
sys.availability_replicas
sys.availability_databases_cluster
sys.dm_hadr_availability_group_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_database_replica_states

Jobs

Supported on:

SQL Server (all versions)

Involved Views and Tables

msdb.dbo.sysjobs
msdb.dbo.sysjobsteps
msdb.dbo.sysjobhistory
msdb.dbo.sysjobservers
msdb.dbo.sysjobactivity
msdb.dbo.systargetservers
msdb.dbo.syscategories

Required permissions:

Official SQL Server documentation

Specific permissions required per system view

sys.dm_os_sys_info

SQL Server (2022 and later)
- VIEW SERVER PERFORMANCE STATE permission.
SQL Server (up to 2019)
- VIEW SERVER STATE permission.
Azure SQL Database (Basic, S0, S1 service objectives and for databases in elastic pools)
- Server admin account; or
- Azure Active Directory admin account; or
- Membership in the ##MS_ServerStateReader## server role.
Azure SQL Database (All other service objectives)
- VIEW DATABASE STATE permission on the database; or
- ##MS_ServerStateReader## server role.
Azure SQL Managed Instance
- VIEW SERVER STATE permission.

sys.dm_os_performance_counters

SQL Server (2022 and later)
- VIEW SERVER PERFORMANCE STATE permission.
SQL Server (up to 2019)
- VIEW SERVER STATE permission.
Azure SQL Database (Basic, S0, S1 service objectives and for databases in elastic pools)
- Server admin account; or
- Azure Active Directory admin account; or
- Membership in the ##MS_ServerStateReader## server role.
Azure SQL Database (All other service objectives)
- VIEW DATABASE STATE permission on the database; or
- ##MS_ServerStateReader## server role.
Azure SQL Managed Instance
- VIEW SERVER STATE permission.

sys.databases

Azure SQL Database
- Connect to master database for all databases to be visible.
- When connecting to a user database, only the current database and the master database are visible.
Other supported types of SQL Server
- To see just the database extension is connected to:
  - No additional permissions are required.
- To see all ONLINE databases:
  - VIEW ANY DATABASE (default permission for the public role)
- To see all OFFLINE databases as well:
  - ALTER ANY DATABASE on server level; or
  - CREATE DATABASE permission in the master database.

sys.query_store_runtime_stats

All supported types of SQL Server
- VIEW DATABASE STATE permission.

sys.query_store_plan

All supported types of SQL Server
- VIEW DATABASE STATE permission.

sys.query_store_query

All supported types of SQL Server
- VIEW DATABASE STATE permission.

sys.query_store_query_text

All supported types of SQL Server
- VIEW DATABASE STATE permission.

sys.dm_exec_sessions

To see the sessions of the user extension connects with:
- No additional permissions are required.
To see all sessions within the database extension is connected to:
- VIEW DATABASE STATE permission.
To see all sessions on the server:
- SQL Server (2022 and later)
  - VIEW SERVER PERFORMANCE STATE permission.
- SQL Server (up to 2019)
  - VIEW SERVER STATE permission.

msdb.dbo.backupset

Available as read-only to any user with public level access to the instance.

msdb.dbo.backupfile

Available as read-only to any user with public level access to the instance.

msdb.dbo.backupmediafamily

Available as read-only to any user with public level access to the instance.

msdb.dbo.backupmediaset

Available as read-only to any user with public level access to the instance.

sys.master_files

All supported types of SQL Server:
- VIEW ANY DEFINITION; or
- CREATE DATABASE; or
- ALTER ANY DATABASE.

sys.database_files

All supported types of SQL Server:
- Requires membership in the public role, see Metadata Visibility Configuration.

sys.availability_groups

All supported types of SQL Server:
- VIEW ANY DEFINITION permission.

sys.availability_replicas

All supported types of SQL Server:
- VIEW ANY DEFINITION permission.

sys.availability_databases_cluster

All supported types of SQL Server:
- If the user with which extension makes the calls is the owner of the database, no additional permissions are required.
- Otherwise:
  - VIEW ANY DATABASE; or
  - ALTER ANY DATABASE; or
  - CREATE DATABASE permission in master is required.

sys.dm_hadr_availability_group_states

SQL Server (2022 and later)
- VIEW SERVER PERFORMANCE STATE permission.
SQL Server (up to 2019)
- VIEW SERVER STATE permission.

sys.dm_hadr_availability_replica_states

SQL Server (2022 and later)
- VIEW SERVER PERFORMANCE STATE permission.
SQL Server (up to 2019)
- VIEW SERVER STATE permission.

sys.dm_hadr_database_replica_states

SQL Server (2022 and later)
- VIEW SERVER PERFORMANCE STATE permission.
SQL Server (up to 2019)
- VIEW SERVER STATE permission.

sys.db_database_backups

Azure SQL Database (Basic, S0, S1 service objectives and for databases in elastic pools)
- Server admin account; or
- Microsoft Entra ID admin account; or
- Membership in the ##MS_ServerStateReader## server role.
Azure SQL Database (All other service objectives)
- VIEW DATABASE STATE permission on the database; or
- ##MS_ServerStateReader## server role.

License Consumption

DDUs are consumed at 0.001 DDU from your available quota for each ingested data point. Each enabled feature set increases DDU consumption. The "default" feature set cannot be turned off.

DDU consumption for each metric (per hour) is calculated as follows: number of unique associated entities * retrieval frequency per hour * 0.001 DDUs per data point

Example:

Metric: sql-server.databases.backup.size
Associated entity: SQL Server Database
Number of unique associated entities:
- Let's assume we monitor 2 instances with 20 databases in each.
- Therefore, there are 2 (SQL Server Instances) * 20 (SQL Server Databases in each) = 40 unique databases in total.
Retrieval frequency per hour: 60 (metric is retrieved every minute).
Total DDU consumption for this metric over the environment (per hour): 40 * 60 * 0.001 = 2.4 DDUs.
Total DDU consumption for this metric over the environment (per year): 2.4 * 24 * 365 = 21,024 DDUs.

Feature Sets

default

sql-server.memory.target
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.memory.physical
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.databases.state
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.uptime
- Associated entity: SQL Server Instance
- Frequency: 12 times per hour (every 5 minutes)
- Data points per hour: number of SQL Server Instances in environment * 12
sql-server.databases.transactions.count
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.memory.total
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.cpu.kernelTime.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.general.userConnections
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.general.processesBlocked
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.general.logins.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.cpu.userTime.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.memory.virtual
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.host.cpus
- Associated entity: SQL Server Host
- Frequency: 12 times per hour (every 5 minutes)
- Data points per hour: number of SQL Server Hosts in environment * 12

Always On

sql-server.always-on.ag.secondaryRecoveryHealth
- Associated entity: SQL Server Availability Group
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Groups in environment * 60
sql-server.always-on.ag.primaryRecoveryHealth
- Associated entity: SQL Server Availability Group
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Groups in environment * 60
sql-server.always-on.ar.failoverMode
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.ag.synchronizationHealth
- Associated entity: SQL Server Availability Group
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Groups in environment * 60
sql-server.always-on.ar.operationalState
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.ar.connectedState
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.db.filestreamSendRate
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.db.state
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.db.synchronizationHealth
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.db.logSendQueueSize
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.ar.role
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.db.synchronizationState
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.db.redoRate
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.db.redoQueueSize
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.ar.synchronizationHealth
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.db.logSendRate
- Associated entity: SQL Server Availability Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Databases in environment * 60
sql-server.always-on.ar.availabilityMode
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.ag.automatedBackupPreference
- Associated entity: SQL Server Availability Group
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Groups in environment * 60
sql-server.always-on.ar.isLocal
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60
sql-server.always-on.ar.recoveryHealth
- Associated entity: SQL Server Availability Replica
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Availability Replicas in environment * 60

Backups

sql-server.databases.backup.age
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.backup.size
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60

Database files

sql-server.databases.file.emptySpace
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.file.size
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.file.usedSpace
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
largest_files
- Associated Entity: SQL Server Instance
- Frequency: 12 times per hour (Every 5 minutes)
- Data points per hour: Up to 100 (num of files) * 12 * avg log size

Latches

sql-server.latches.waits.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.latches.averageWaitTime.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60

Locks

sql-server.locks.timeouts.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.locks.waits.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.locks.waitTime.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.locks.deadlocks.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60

Memory

sql-server.buffers.checkpointPages.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.memory.grantsOutstanding
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.memory.connection
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.buffers.pageWrites.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.buffers.pageLifeExpectancy
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.memory.grantsPending
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.buffers.cacheHitRatio
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.buffers.freeListStalls.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.buffers.pageReads.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60

Queries

sql-server.sql.recompilations.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.sql.compilations.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.sql.batchRequests.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
longest_queries
- Associated Entity: SQL Server Instance
- Frequency: 12 times per hour (Every 5 minutes)
- Data points per hour: Up to 100 (num of queries) * 12 * avg log size
instance_locks_wait_time_type
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60

Replication

sql-server.replica.bytesSentToTransport.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.replica.sends.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.replica.sendsToTransport.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.replica.bytesReceived.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.replica.bytesSent.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.replica.resentMessages.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60
sql-server.replica.receives.count
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60

Sessions

sql-server.sessions
- Associated entity: SQL Server Instance
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Instances in environment * 60

Transaction logs

sql-server.databases.log.flushWaits.count
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.log.filesUsedSize
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.log.growths.count
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.log.truncations.count
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.log.shrinks.count
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.log.filesSize
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60
sql-server.databases.log.percentUsed
- Associated entity: SQL Server Database
- Frequency: 60 times per hour (every 1 minute)
- Data points per hour: number of SQL Server Databases in environment * 60

Jobs

current_jobs
- Associated Entity: SQL Server Instance
- Frequency: 12 times per hour (Every 5 minutes)
- Data points per hour: Number of currently enabled jobs * 12 * avg log size
failed_jobs
- Associated Entity: SQL Server Instance
- Frequency: 12 times per hour (Every 5 minutes)
- Data points per hour: top 100 failed jobs * 12 * avg log size

Locks and waits

all_requests
- Associated Entity: SQL Server Instance
- Frequency: 60 times per hour (Every 1 minute)
- Data points per hour: avg Number of active requests * 60 * avg log size
note on current_jobs, failed_jobs, longest_queries, all_requests, and largest_files: These metrics are based on Log data. As every environment is different the calculation needs to be estimated on the client side. Then calculate the data size ingested. Currently, 100 DDUs are consumed per GB ingested. Please refer to the DDU consumption model for Log Management and Analytics in the documentation. If you are on Log Monitoring Classic: each log record (line, message, entry) deducts 0.005 DDU from your available qouta. Please refer to the DDUs for Log Monitoring Classic in the documentation.

Limitations

Aggregated metrics for database files

The two metrics below

sql-server.databases.file.usedSpace
sql-server.databases.file.emptySpace

are only reported for the database the extension is currently connected to. This is due to sys.allocation_units only containing information about used pages of the database that is currently used inside the connection.

Top busiest queries

Top queries are only collected for a single database.
Top queries cannot be collected for master database (limitation of SQL Server itself).

Azure backups

Azure backups are monitored by querying the sys.db_database_backups view which is currently available for all Azure SQL Database service tiers except Hyperscaler.

Always On

To obtain information about every replica in a given availability group, connect the extension to the server instance that is hosting the primary replica. When connected to a server instance that is hosting a secondary replica of an availability group, the extension returns only local information for the availability group.

When connected to a secondary replica, the extension retrieves states of every secondary database on the server instance. On the primary replica, the extension returns data for each primary database and for the corresponding secondary database.

Depending on the action and higher-level states, database-state information may be unavailable or out of date. Furthermore, the values have only local relevance. See limitations of sys.dm_hadr_database_replica_states.

When a database is added to an availability group, the primary database is automatically joined to the group. Secondary databases must be manually prepared on each secondary replica before they can be joined to the availability group.

If the local server instance cannot communicate with the WSFC failover cluster, for example, because the cluster is down or quorum has been lost, only rows for local availability replicas are returned. These rows will contain only the columns of data that are cached locally in metadata.

Troubleshooting

To troubleshoot this extension, use the guide(s) in the Dynatrace Community

FAQ

How does the extension affect the target database?

The extension only executes SELECT queries to obtain monitoring data. The database is never modified or locked.
The extension only queries sys.* system views and msdb database (when applicable). User databases and objects are never affected.
All executed queries are static and are cached within the target database after their first execution.
Even with all Feature Sets enabled, the effect the extension has on each target database is negligible.

How to size ActiveGates for this extension?

Each monitoring configuration is automatically assigned to an ActiveGate within the assigned ActiveGate group.
All of the endpoints within a single monitoring configuration are executed on a single ActiveGate.
Failover migration of monitoring configuration is automatically performed in case an ActiveGate is brought down. Migration is only performed within a single ActiveGate group.
Each monitoring configuration can handle hundreds of active endpoints simultaneously on a single ActiveGate with 2vCPU and 4GiB RAM.
The number of monitoring configurations that can be created is limited. It is much more performance and resource-efficient to have many endpoints inside a monitoring configuration instead of creating too many monitoring configurations.

Are there any special considerations when monitoring Always On clusters?

It is recommended to create two distinct monitoring configurations when monitoring an Always On cluster:
- First monitoring configuration with only the "Always On" feature set enabled and connected exclusively to primary replicas within the cluster.
- Second monitoring configuration with every feature set enabled except for "Always On" (disabled within the second monitoring configuration) with a connection to all instances within the cluster.
- This configuration will ensure that full infrastructure observability is enabled for every instance within the cluster while the data related to Always On is reliably collected from the primary replicas.
It is recommended to create a separate monitoring configuration to monitor Always On clusters and only create endpoints to primary replicas. Due to built-in limitations of Always On, the secondary replicas do not have full information about the entire Always On cluster they belong to.
Connecting to both the primary and secondary replica in the same Always On cluster while having the "Always On" feature set enabled for both of them will result in duplicate metrics as well as distorted monitoring and is highly discouraged.

What authentication schemas are supported?

The following authentication types are supported
- Basic authentication
- Kerberos
- NTLM

Are self-signed SSL certificates and PKCS12 truststores supported?

Yes, certificates signed with a non-public signing chain must be added to a truststore.
When an encryption certificate is generated using a non-publicly verifiable certificate authority, that CA must be made known to the ActiveGate.
See instructions on adding a truststore for a step-by-step guide.

What is the Endpoint Metadata field for?

When you add text into this field, each SQL Server Instance created by that monitoring configuration with have their properties section modified to have this information added to the entity.

How do I add custom intervals?

The 2 fields query interval and heavy query interval have been added. If you want the 1-minute queries to run at intervals greater that 1 minute. e.g. every 10 minutes you can enter the number 10 in this box.
The heavy query interval input box functions the same. Except you are changing the frequency of queries that run every 5 minutes.
For more information the description under each input box explains which queries are affected.

How do I view my Locks and Waits?

Enable the Locks and waits featureSet. If you are on SaaS you will have a new Dashboard to view this data in an organized single pane of glass. If you are on Managed you will have the logs ingested and you can view requests with locks or waits.

Explore in Dynatrace Hub

Explore Microsoft SQL Server in Dynatrace Hub.

Explore supported Extensions