Try it free

Microsoft SQL Server extension

  • Latest Dynatrace
  • Extension

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

Monitor SQL Server instances, databases, and Always On clusters remotely.

Get started

Overview

Monitor Microsoft SQL Server health and performance with the Dynatrace extension.

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

Use cases

  • Understand the impact that resource shortages, locks, or other database issues have on your application by observing the database server itself.
  • Track the health and performance of the Microsoft SQL Server servers.

Requirements

  • Designate an ActiveGate group or groups that will remotely connect to your Microsoft SQL Database server to pull data. All ActiveGates in each group must connect to your Microsoft SQL Database server.
  • A corresponding set of SQL Server types supports each available feature set. For the individual permissions the extension user needs, see the Views and tables involved section. Granular permission details for each system view follow below.

Supported systems and involved system views per feature set

default

Supported on:

  • Microsoft 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.dm_os_schedulers
  • sys.databases
Memory

Supported on:

  • Microsoft 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_performance_counters
Locks

Supported on:

  • Microsoft 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_performance_counters
Latches

Supported on:

  • Microsoft 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_performance_counters
Queries

  • Monitor query performance stats

    Supported on:

    • SQL Server (all versions)

    Involved views and tables:

    • sys.dm_os_performance_counters

  • Monitor lock wait time by wait type

    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_exec_requests

  • Monitor failed distributed transactions

    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_tran_locks
    • sys.databases

  • Monitor 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_runtime_stats_interval
    • sys.query_store_plan
    • sys.query_store_query
    • sys.query_store_query_text
Replication

Supported on:

  • Microsoft 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:

  • Microsoft 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:

  • Microsoft 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

  • Monitor the age of the 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

  • Monitor backup file 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.dm_database_backups
Database files

  • Monitor database file stats

    Supported on:

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

    Involved views and tables:

    • sys.master_files

  • Monitoring the largest database files on Azure SQL Database

    Supported on:

    • Azure SQL Database

    Involved views and tables:

    • sys.database_files

  • Monitoring the 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.sysjobhistory
  • msdb.dbo.sysjobservers
  • msdb.dbo.sysjobactivity
  • msdb.dbo.systargetservers
  • msdb.dbo.syscategories
Agent

Supported on:

  • SQL Server (all versions)

    Not supported on Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse Analytics.

Involved views and tables:

  • sys.dm_server_services
Locks and waits

Supported on:

  • Microsoft SQL Server (all versions)
  • Azure SQL Database
  • Azure SQL Managed Instance
  • Azure Synapse Analytics

Involved views and tables:

  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.databases
  • sys.dm_exec_sql_text

Required permissions:

  • Official SQL Server documentation

Specific permissions required per system view

sys.dm_os_sys_info
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER PERFORMANCE STATE permission.
  • Microsoft 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
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER PERFORMANCE STATE permission.
  • Microsoft 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_schedulers
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER PERFORMANCE STATE permission.
  • Microsoft 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 only the database the 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.dm_exec_requests
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER PERFORMANCE STATE permission.
  • Microsoft SQL Server (up to 2019)
    • VIEW SERVER STATE permission; otherwise only the current session is visible.
  • Azure SQL Database
    • VIEW SERVER STATE can't be granted; results are always limited to the current connection.
sys.dm_exec_sql_text
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER PERFORMANCE STATE permission.
  • Microsoft SQL Server (up to 2019)
    • VIEW SERVER STATE permission.
sys.dm_tran_locks
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER PERFORMANCE STATE permission.
  • Microsoft 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.query_store_runtime_stats
  • All supported types of SQL Server
    • VIEW DATABASE STATE permission.
sys.query_store_runtime_stats_interval
  • 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 the 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.
    • Microsoft 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.dm_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.
sys.dm_server_services
  • Microsoft SQL Server (2022 and later)
    • VIEW SERVER SECURITY STATE permission.
  • Microsoft SQL Server (up to 2019)
    • VIEW SERVER STATE permission.

Compatibility information

Supported types of Microsoft SQL Server

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

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

Supported types of HA or replication

  • Always On

Other types of replication and HA monitoring, including the publisher/subscriber model, aren't supported yet.

Supported versions of SQL Server

This extension supports any version of SQL Server with active extended support by Microsoft. See 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 on the same SQL Server isn't supported.
  • Running different major versions (for example, version 1 and version 2) of the extension on the same tenant is highly discouraged and isn't supported. Mixed major versions break the topology model.

Compatibility with OneAgent

  • For a SQL Server Instance entity to link to its Host entity, both must share the same IP address. If the monitoring configuration for SQL Server uses a different IP address, the two entities won't link.

Activation and setup

Follow these steps to configure your device for Microsoft SQL Server database monitoring.

Add DB instance

Required permission: Change monitoring settings

  1. Go to Hub Dynatrace Hub.

  2. Select and install Microsoft SQL Server extension. This enables the extension in your monitoring environment.

  3. Select Add DB Instance in Databases Databases. This opens the Add DB Instance wizard.

  4. Select Microsoft SQL Server section in the wizard.

Select hosting type

Select a hosting type from the options. This choice determines which script generates the necessary database objects later in the process.

  1. From the Add DB Instance wizard, select the host type that matches your requirement.
  2. Select Next.

Select ActiveGate group

  1. Select the ActiveGate group to determine which ActiveGates will run the extension.
  2. Select Next step.

Create a connection

Set up the connection to your database instance. Provide the required credentials directly in the wizard or use secure alternatives:

  1. Name the connection, so you can identify it later.
  2. Add the details in the Configure connection section.
    1. Select connection. Use Select from existing hosts or Enter manually to add connection details.
    2. Add Database name
  3. Provide the Authenticate credentials for the dynatrace monitoring user you have created directly or use secure alternatives.
    • Basic credentials: Authentication details passed to Dynatrace when activating monitoring configuration are masked to prevent them from being retrieved.
    • Credential vault: Use vault credentials to securely store and retrieve database credentials.
    • You can enable SSL to establish a secure connection for your configuration.
  4. Select Next.

Install instance

  1. Add manual configurations based on the monitoring requirements.
  2. Select Create DB instance monitoring.

You can enable log monitoring to activate extension status logs. Log monitoring covers the longest running queries and the largest database files.

Details

Breaking changes

  • v3.1.2:
    • Requires a minimum Dynatrace version of 1.338.0 and a minimum EEC version of 1.337.0.
    • On Dynatrace on Grail, metrics and logs ingested by the extension are now processed by dedicated OpenPipeline pipelines instead of dynamic routes or the classic log pipeline. Migrate any custom routing or classic log processing rules to OpenPipeline rules, and opt in to OpenPipeline configuration via the Settings API. OpenPipeline also handles some field names differently, for example, capitalization. Review any custom dashboards and alerts.
    • Feature sets were refactored, with some added and others removed. Most monitoring configurations need to be recreated, and existing feature set selections should be reviewed so the same insights remain enabled.
    • SQL Agent was removed from Classic Topology.
    • The agent_requests and application_requests log event groups were removed in favor of the generic all_requests event group. Use the program_name attribute to filter for agent or application requests.
  • v2.7.0:
    • If you're not on ActiveGate 1.303 and newer, your monitoring configurations will error upon running.
  • v2.0.0:
    • All monitoring configurations need to be recreated because of a change in feature sets.
    • The instance dimension now only contains the name of the actual named instance or MSSQLSERVER by default.
    • The 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.

Licensing and costs

There is no charge to use the extension. You are only charged for the data that the extension ingests.

The Microsoft SQL Server extension ingests custom metrics, which consume Davis data units (DDUs) (Dynatrace Classic license) or Metrics powered by Grail (DPS), according to your license model.

Each enabled feature set increases consumption. The default feature set can't be turned off.

The number of metric data points produced per minute for a given metric is calculated as follows:

number of unique associated entities / minutes between retrievals = metric data points per minute

Example:

  • Metric: sql-server.databases.backup.size
  • Associated entity: SQL Server Database
  • Number of unique associated entities:
    • Assume 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: every minute.
  • Metric data points per minute for this metric: 40 / 1 = 40.

Dynatrace Platform Subscription

In the Dynatrace Platform Subscription, metric ingestion consumes Metrics powered by Grail according to the number of ingested metric data points.

To calculate the approximate yearly consumption, apply the following calculation: <metric data points per minute> * 60 minutes * 24 hours * 365 days.

For the example above: 40 * 60 * 24 * 365 = 21,024,000 metric data points per year.

Dynatrace Classic license

In the classic licensing model, metric ingestion consumes Davis data units (DDUs) at the rate of .001 DDUs per metric data point. Multiply the above formula for annual data points by .001 to estimate annual DDU usage.

For the example above: 40 * 60 * 24 * 365 * 0.001 = 21,024 DDUs per year.

The DDU cost doesn't include any log events or custom events the extension triggers. For more information, see DDU events.

DQL and logs

Individual backups

Enable individual backups

You can enable the collection of individual backup details using the Backups feature set for SQL Server and Azure SQL Managed Instance, or the Backups Azure feature set for Azure SQL Database. Only one of these two feature sets should be enabled at a time for a given SQL Server type.

Update frequency

Individual backups are fetched by the extension every five minutes.

List individual backups (SQL Server and Managed Instance)

The following query, when executed in Logs and Events, displays individual backups as observed within the most recent 5-minute timeframe using DQL:

fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "backups_managed")
| fields content, database, backup_type, device_type, recovery_model, compatibility_level, backup_size, compressed_backup_size, backup_start_date, backup_finish_date, physical_device_name, server, instance
| sort backup_finish_date desc

Description of fields:

  • content field contains the backup type (same value as backup_type)
  • backup_size and compressed_backup_size are reported in bytes
  • physical_device_name represents the physical path or name of the backup device
List individual backups (Azure SQL Database)

The following query, when executed in Logs and Events, displays individual Azure SQL Database backups as observed within the most recent 5-minute timeframe using DQL:

fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "backups_azure")
| fields content, logical_database_name, physical_database_name, logical_server_name, backup_type, in_retention, backup_start_date, backup_finish_date, server, instance
| sort backup_finish_date desc

Description of fields:

  • content field contains the backup type (same value as backup_type)
  • in_retention indicates whether the backup is still within the configured retention period

Top queries

Enable top queries

You can enable a collection of top queries ordered by total duration using the Queries feature set.

Prerequisites
  • Query Store must be enabled on the 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.
Update frequency

Top queries are fetched by the extension every 60 minutes. Unlike other queries in the Queries feature set, this interval is fixed and isn't affected by the query interval or heavy query interval configuration fields.

List top queries

The following query, when executed in Logs and Events, displays top queries as observed within the most recent 60-minute 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, avg_cpu_time, content, server, instance, database, count_executions, last_execution_time, sql_id
| sort asDouble(total_duration) desc

Description of fields:

  • total_duration field represents a sum of all executions of this query over the given 60-minute timeframe in milliseconds
  • avg_duration represents the average execution time of this query over the given 60-minute timeframe in milliseconds
  • avg_cpu_time represents the average CPU time consumed by this query over the given 60-minute timeframe in milliseconds
  • content field contains the SQL text of the query
  • count_executions represents the number of times this query was executed over the given 60-minute timeframe
  • sql_id represents the query execution plan ID

Largest files

Enable largest file collection

You can enable collection of the largest database files by size using the Database files feature set.

Update frequency

Top database files by size are fetched by the extension every five minutes.

List the largest database files by size

The following query, when executed in Logs and Events, displays the largest database files as observed within the most recent 5-minute 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 the host OS.
  • file_size is reported in KB
  • file_used_space is reported in KB and represents the amount of space occupied by allocated pages within a specific file
  • file_empty_space is reported in KB and represents the space still empty within a specific file

Current jobs

Enable current job monitoring

You can enable monitoring of current jobs using the Jobs feature set.

Update frequency

Current jobs are fetched by the extension every five minutes.

List current jobs

The following query, when executed in Logs and Events, displays current jobs as observed within the most recent 5-minute 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 hasn't run 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

Failed jobs

Enable failed job monitoring

You can enable monitoring of failed jobs using the Jobs feature set.

Update frequency

Failed jobs are fetched by the extension every five minutes.

List failed jobs

The following query, when executed in Logs and Events, displays failed jobs as observed within the most recent 5-minute 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 the complete job duration in seconds after execution is finished

Feature sets

default

  • sql-server.memory.target
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every 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 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 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 five 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 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 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 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 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 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 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 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 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 five minutes)
    • Data points per hour: number of SQL Server Hosts in environment * 12
  • sql-server.worker.activeWorkers
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Instances in environment * 60
  • sql-server.worker.maxWorkers
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Instances in environment * 60
  • sql-server.worker.threadsPercent
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Instances in environment * 60

Always On

  • sql-server.always-on.ag.secondaryRecoveryHealth
    • Associated entity: SQL Server Availability Group
    • Frequency: 60 times per hour (every 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 minute)
    • Data points per hour: number of SQL Server Databases in environment * 60
  • backups_managed
    • Associated entity: SQL Server Instance
    • Frequency: 12 times per hour (every five minutes)
    • Data points per hour: Up to number of individual backups taken in the interval * 12 * avg log size

Backups Azure

  • backups_azure
    • Associated entity: SQL Server Instance
    • Frequency: 12 times per hour (every five minutes)
    • Data points per hour: Up to number of individual backups taken in the interval * 12 * avg log size

Database files

  • sql-server.databases.file.emptySpace
    • Associated entity: SQL Server Database
    • Frequency: 60 times per hour (every 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 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 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 five minutes)
    • Data points per hour: Up to 100 (num of files) * 12 * avg log size

Database files Azure

  • largest_files
    • Associated entity: SQL Server Instance
    • Frequency: 12 times per hour (every five 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 minute)
    • Data points per hour: number of SQL Server Instances in environment * 60
  • sql-server.locks.elapsedTimeRequestsPercent
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Instances in environment * 60
  • sql-server.databases.failedDistributedTransactions.count
    • Associated entity: SQL Server Database
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Databases in environment * 60
  • sql-server.locks.byWaitType
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Instances in environment * 60
  • longest_queries
    • Associated entity: SQL Server Instance
    • Frequency: once per hour (every 60 minutes)
    • Data points per hour: Up to 100 (num of queries) * 1 * avg log size

Replication

  • sql-server.replica.bytesSentToTransport.count
    • Associated entity: SQL Server Instance
    • Frequency: 60 times per hour (every 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 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 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 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 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 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 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 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 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 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 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 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 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 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 minute)
    • Data points per hour: number of SQL Server Databases in environment * 60

Agent

  • sql-server.sql.agent.status
    • Associated entity: SQL Server Agent
    • Frequency: 60 times per hour (every minute)
    • Data points per hour: number of SQL Server Agents in environment * 60

Jobs

  • current_jobs
    • Associated entity: SQL Server Instance
    • Frequency: 12 times per hour (every five 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 five 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 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. Because every environment is different, estimate the calculation on the client side, and then calculate the data size ingested. Currently, 100 DDUs are consumed per GB ingested. See the DDU consumption model for Log Management and Analytics in the documentation. If you're on Log Monitoring Classic, each log record (line, message, entry) deducts 0.005 DDU from your available quota. See 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. The FILEPROPERTY(..., 'SpaceUsed') function only returns used-space information for the database of the current connection.

Top busiest queries

  • Top queries are only collected for a single database.
  • Top queries can't be collected for the master database (limitation of SQL Server itself).

Azure backups

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

Always On

To obtain information about every replica in a given availability group, connect the extension to the server instance hosting the primary replica. When connected to a server instance 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 the 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 can't communicate with the WSFC failover cluster, only rows for local availability replicas are returned. This happens when the cluster is down or quorum has been lost. These rows contain only the columns cached locally in metadata.

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 endpoints within a single monitoring configuration are executed on a single ActiveGate.
  • Failover migration of the monitoring configuration is automatically performed when 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 2 vCPU and 4 GiB RAM.
  • The number of monitoring configurations that can be created is limited. It's 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?
  • We recommend creating 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" (turned off in the second monitoring configuration), with a connection to all instances within the cluster.
    • This split gives full infrastructure observability for every instance within the cluster while the data related to Always On is reliably collected from the primary replicas.
  • We recommend creating a separate monitoring configuration to monitor Always On clusters and only create endpoints for primary replicas. Due to the built-in limitations of Always On, the secondary replicas don't have complete information about the entire Always On cluster they belong to.
  • Don't enable the "Always On" feature set on both the primary and the secondary replica in the same cluster. Doing so produces duplicate metrics and distorted monitoring.
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.
  • For a step-by-step guide, see instructions on adding a truststore.
What is the Endpoint Metadata field for?
  • When you add text to this field, every SQL Server instance created by that monitoring configuration adds the text to its properties.
How do I add custom intervals?
  • Two fields, query interval and heavy query interval, control how often queries run. To run the 1-minute queries at a longer interval, for example every 10 minutes, enter 10 in the query interval field.
  • The heavy query interval field works the same way, except it changes the frequency of queries that run every five minutes.
  • For more details, the description under each field explains which queries are affected.
  • Top queries (longest_queries) run on a fixed 60-minute interval and aren't affected by either the query interval or heavy query interval setting.
How do I view my Locks and Waits?
  • Enable the Locks and waits feature set.You'll have a new dashboard to view this data in a single pane of glass.

Troubleshooting

  • To troubleshoot this extension, use the guides in the Dynatrace Community.
Hub

Explore in Dynatrace Hub

Monitor Microsoft SQL Server health and performance remotely with an ActiveGate extension that collects metrics, backups, jobs, and Always On data.

Related topics

  • Dynatrace blog - Intelligent observability for Oracle and SQL databases
Related tags
DatabaseSQLMSSQLMicrosoftApplication Observability