Microsoft SQL ServerMicrosoft SQL Server
Improve the health and performance monitoring of your Microsoft SQL Servers.
Extension
- Product information
- Release notes
Overview
Microsoft SQL Server database monitoring is based on a remote monitoring approach implemented as a Dynatrace ActiveGate extension. The extension queries MS SQL databases for key performance and health metrics, extending your visibility, and allowing DAVIS AI to provide anomaly detection and problem analysis.
Use cases
- Understand 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.
Extension content
Content typeNumber of items included
alerts
3
screen injections
1
list screen layout
6
document dashboard
2
screen actions
14
alert template
3
generic type
7
screen entities lists
22
screen custom cards
2
generic relationship
7
screen properties
6
dashboards
1
screen chart groups
25
screen layout
10
metric metadata
77
screen dql table
2
Feature sets
Below is a complete list of the feature sets provided in this version. To ensure a good fit for your needs, individual feature sets can be activated and deactivated by your administrator during configuration.
Feature setsNumber of metrics included
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Database state | sql-server.databases.state | Database state | State |
| Transactions | sql-server.databases.transactions.count | Number of transactions started for the database | Count |
| CPU Count | sql-server.host.cpus | Detected CPU Count | Count |
| Time spent in kernel mode | sql-server.cpu.kernelTime.count | Time spent by all threads in kernel mode | MilliSecond |
| Time spent in user mode | sql-server.cpu.userTime.count | Time spent by all threads in user mode | MilliSecond |
| Physical memory | sql-server.memory.physical | Total amount of physical memory on the machine | KiloByte |
| Virtual memory | sql-server.memory.virtual | Total amount of virtual address space available to the process in user mode | KiloByte |
| Total server memory | sql-server.memory.total | Amount of memory the server has committed using the memory manager | KiloByte |
| Target server memory | sql-server.memory.target | Amount of memory that can be consumed by memory manager | KiloByte |
| Uptime | sql-server.uptime | Time in seconds since instance was last started | Seconds |
| Logins | sql-server.general.logins.count | Number of logins started, excluding pooled connections | Count |
| User connections | sql-server.general.userConnections | Number of users currently connected to SQL Server | Count |
| Processes blocked | sql-server.general.processesBlocked | Number of currently blocked processes | Count |
| Active workers | sql-server.worker.activeWorkers | Number of workers that are actively executing tasks | gauge |
| Max workers | sql-server.worker.maxWorkers | Maximum number of workers that can be active | gauge |
| Worker threads percentage | sql-server.worker.threadsPercent | Percentage of worker threads in use | Percent |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Minutes since last backup | sql-server.databases.backup.age | Number of minutes passed since last backup | Minute |
| Size of the last backup | sql-server.databases.backup.size | Size of the last backup of that type | Byte |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Buffer cache hit | sql-server.buffers.cacheHitRatio | Percentage of pages found in the buffer cache without having to read from disk | Percent |
| Free list stalls | sql-server.buffers.freeListStalls.count | Number of requests that had to wait for a free page | Count |
| Page reads | sql-server.buffers.pageReads.count | Number of physical database page reads that are issued | Count |
| Page writes | sql-server.buffers.pageWrites.count | Number of physical database page writes that are issued | Count |
| Checkpoint pages | sql-server.buffers.checkpointPages.count | Number of pages flushed to disk by a checkpoint or other operations that require all dirty pages to be flushed | Count |
| Page life expectancy | sql-server.buffers.pageLifeExpectancy | Number of seconds a page will stay in the buffer pool without references | Second |
| Connection memory | sql-server.memory.connection | Total amount of dynamic memory the server is using for maintaining connections | KiloByte |
| Memory grants outstanding | sql-server.memory.grantsOutstanding | Number of processes that have successfully acquired a workspace memory grant | Count |
| Memory grants pending | sql-server.memory.grantsPending | Number of processes waiting for a workspace memory grant | Count |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Latch waits | sql-server.latches.waits.count | Number of latch requests that could not be granted immediately | Count |
| Average latch wait time | sql-server.latches.averageWaitTime.count | Average latch wait time for latch requests that had to wait | MilliSecond |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Database file size | sql-server.databases.file.size | Total size of files of specific types per database | KiloByte |
| Database file used size | sql-server.databases.file.usedSpace | Total size of space used in files of specific types per database | KiloByte |
| Database file empty size | sql-server.databases.file.emptySpace | Total size of empty space in files of specific types per database | KiloByte |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| - | sql-server.sql.agent.status | - | - |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Synchronization state | sql-server.always-on.db.synchronizationState | Data-movement state | State |
| Synchronization health | sql-server.always-on.db.synchronizationHealth | Intersection of the synchronization state of a database that is joined to the group on the replica and the availability mode of the replica | State |
| Database state | sql-server.always-on.db.state | State of the availability database | State |
| Log send queue size | sql-server.always-on.db.logSendQueueSize | Amount of log records of the primary database not sent to the secondary databases | KibiByte |
| Log send rate | sql-server.always-on.db.logSendRate | Average rate at which primary replica instance sends data | KiloBytePerSecond |
| Redo queue size | sql-server.always-on.db.redoQueueSize | Amount of log records in the log files of the secondary replica that has not yet been redone | KiloByte |
| Redo rate | sql-server.always-on.db.redoRate | Average Rate at which the log records are being redone on a given secondary database | KiloBytePerSecond |
| Filestream send rate | sql-server.always-on.db.filestreamSendRate | The rate at which the filestream files are shipped to the secondary replica | KiloBytePerSecond |
| Automated backup preference | sql-server.always-on.ag.automatedBackupPreference | Preferred location for backups in this availability group | State |
| Primary recovery health | sql-server.always-on.ag.primaryRecoveryHealth | Indicates the recovery health of the primary replica | State |
| Secondary recovery health | sql-server.always-on.ag.secondaryRecoveryHealth | Indicates the recovery health of a secondary replica | State |
| Synchronization health | sql-server.always-on.ag.synchronizationHealth | Reflects a rollup of the synchronization health of all replicas the group | State |
| Availability mode | sql-server.always-on.ar.availabilityMode | The availability mode of the replica | State |
| Failover mode | sql-server.always-on.ar.failoverMode | The failover mode of the replica | State |
| Is replica local | sql-server.always-on.ar.isLocal | Whether the replica is local | State |
| Replica role | sql-server.always-on.ar.role | Current groups role of a local replica or a connected remote replica | State |
| Operational state | sql-server.always-on.ar.operationalState | Current operational state of the replica | State |
| Recovery health | sql-server.always-on.ar.recoveryHealth | Rollup of the database_state column | State |
| Synchronization health | sql-server.always-on.ar.synchronizationHealth | Rollup of the database synchronization state of all joined replicas and the availability mode of the replica | State |
| Connected state | sql-server.always-on.ar.connectedState | Whether a secondary replica is currently connected to the primary replica | State |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Sessions | sql-server.sessions | Number of active sessions per user | Count |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Lock timeouts | sql-server.locks.timeouts.count | Number of lock requests that timed out, including requests for NOWAIT locks | Count |
| Deadlocks | sql-server.locks.deadlocks.count | Number of lock requests that resulted in a deadlock | Count |
| Lock waits | sql-server.locks.waits.count | Number of lock requests that required the caller to wait | Count |
| Lock wait time | sql-server.locks.waitTime.count | Wait time for locks | MilliSecond |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Log files size | sql-server.databases.log.filesSize | Cumulative size of all the transaction log files in the database | KiloByte |
| Log files used size | sql-server.databases.log.filesUsedSize | The cumulative used size of all the log files in the database | KiloByte |
| Log flush waits | sql-server.databases.log.flushWaits.count | Number of commits waiting for the log flush | Count |
| Log growths | sql-server.databases.log.growths.count | Number of times the transaction log for the database has been expanded | Count |
| Log shrinks | sql-server.databases.log.shrinks.count | Number of log shrinks for this database | Count |
| Log truncations | sql-server.databases.log.truncations.count | Number of times the transaction log has been truncated (in Simple Recovery Model) | Count |
| Percent log used | sql-server.databases.log.percentUsed | Percentage of space in the log that is in use | Percent |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Bytes sent to replica | sql-server.replica.bytesSent.count | Message bytes queued to be sent to replicas | Byte |
| Sends to replica | sql-server.replica.sends.count | Number of messages queued to be sent to replica | Count |
| Bytes sent to transport | sql-server.replica.bytesSentToTransport.count | Dequeued message bytes sent across network to replicas | Byte |
| Sends to transport | sql-server.replica.sendsToTransport.count | Number of messages sent across network to replicas | Count |
| Bytes received from replica | sql-server.replica.bytesReceived.count | Message bytes received from replica | Byte |
| Receives from replica | sql-server.replica.receives.count | Number of messages received from replica | Count |
| Resent messages | sql-server.replica.resentMessages.count | Number of messages that had to be resent to replica | Count |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Failed Distributed Transactions | sql-server.databases.failedDistributedTransactions.count | The number of failed distributed transactions | Count |
| - | sql-server.locks.byWaitType | - | - |
| Batch requests | sql-server.sql.batchRequests.count | Number of Transact-SQL command batches received | Count |
| SQL compilations | sql-server.sql.compilations.count | Number of SQL statement compilations | Count |
| SQL re-compilations | sql-server.sql.recompilations.count | Number of SQL statement re-compilations | Count |
| Percent of Elapsed time for Requests wait type | sql-server.locks.elapsedTimeRequestsPercent | Percentage of elapsed time for requests wait type performance counter_name | Percent |
Related to Microsoft SQL Server
Full version history
To have more information on how to install the downloaded package, please follow the instructions on this page.
ReleaseDate
Full version history
New Features
- Added ALERT_TEMPLATES for anomaly detectors in the Davis Anomaly Detection app.
Full version history
New Features
-
Added Worker Metrics to the Default Feature Set
- Active Workers: Displays the number of workers actively executing tasks.
- Max Workers: Shows the maximum number of workers that can be active.
- Worker Threads Percentage: Indicates the percentage of worker threads currently in use.
-
Added Query Performance Metric to the Queries Feature Set
- Percent of Elapsed Time for Requests Wait Type: Represents the percentage of elapsed time for requests categorized by wait type performance counter.
-
Added @@servername as a New Attribute
- Added as an additional attribute to Server Instances.
Improvements
- Updated {device.name} to {device} on database instances, enabling the database name to appear in the naming string.
Bug Fixes
- Failed Distributed Transactions Query Updated
- Resolved an issue where a temporary duplicate could occur if an orphaned query was recorded.
Future Breaking Change
In a future update Transaction Logs and Transaction logs FeatureSets will be merged into one.
Full version history
Potential Breaking Change affecting Availability Replica Entities
Bug Fix:
- Availability Replica would not handle name instances correctly. This has been fixed and now relationship mapping will happen as expected. The previously incorrect entities may no longer appear.
Dimension Changes:
Product VersionDimension has now been changed toDB Instance Versionon SQL Server Instance Entity. This is now a standardized value across Database Extensions.
Attribute Changes
- All Entities created by the SQL Server Extension now have the
security_contextattribute which is entered via the Monitoring Configuration.
Full version history
Bugfix
failed_jobspreviously would ingest job failures older than 5 minutes depending on the time of hour. This has been corrected.
Warning
Transaction LogsfeatureSet to be removed in a future update.Transaction logswill continue to exist
Full version history
Improvements
- Added new alert
SQL Server is unreachableto monitor database availability, based onUptime metric - Uptime visualization updated from single value display to graph chart visualization for better alert investigation
Full version history
Bug fixes:
- Current Jobs query syntax fix. This was causing a breakage of the extension
Warning:
- "Transaction Logs" featureSet to be removed in a future update. "Transaction logs" will continue to exist.
- Minimum EEC version increased (1.303) for Query Interval which was causing breakage issues when updating to 2.7.0 and above
Full version history
Bug fixes:
- buffer_cache_hit bundled alert now fixed.
- Minimum EEC version increased to 1.303 for Query Interval which was causing breakage issues when updating to 2.7.0 and above
Improvements:
- event.group added for agent and application queries log ingest
- Added "Job Category id" and "Job Category name" to Current and Failed Jobs
Features:
- Now able to see the
locks and waitsof impacted requests. - Added Gen 3 dashboard to visualize the
locks and waitsdata
Warning:
- "Transaction Logs" featureSet to be removed in a future update. "Transaction logs" will continue to exist.
Full version history
New Feature:
- Configurable frequency for the SQL queries
Full version history
Adds the SAME_AS relationship between SQL Instances and related Process Group Instances on OneAgent monitored hosts.
Full version history
Features:
- Added a built-in dashboard for 3rd gen SaaS tenants.
Full version history
Fixed in this version:
- Sorting SQL statements in Databases app would produce duplicate results
Full version history
New Entity
- SQL Agent added to topology
New Metrics
- Agent Status
- Failed Distributed Transactions Count
New Log Ingests
- Top 25 Agent Queries running longer than 60 seconds
- Top 25 Application Queries running longer than 60 seconds
Bug Fixes
- Fixed an issue where Databases App did not display Uptime Hours due to syntax error.
- Fixed Arithmetic Overflow Error that had a chance to occur in large environments on the Largest Files Managed calculation.
Full version history
Improvements
- Execution Plan fetching now done in Databases App
- Instance entity parsing from log records
Changes
- Removed query plan from longest_queries query
- Show latest back-up age
Full version history
Improvements:
-
Added default value for
availability.replica.instancedimension:MSSQLSERVER.This enables the SAME AS relationship between the
sql:sql_server_instanceandsql:sql_server_availability_replicaentities based on the equality of their respective attributes:server = ar_server_nameandinstance = ar_instance_name.Previously, if the replica was running on an instance that had a default name, the relationshp wasn't being established.
Full version history
Bug fixes:
- Fixed typo in the metric description of the
sql-server.databases.log.percentUsed.
Full version history
Bug fixes:
- Added
SAME_ASrelationship fromsql:sql_server_availability_replicatosql:sql_server_instancebased on the matchingar_server_name->serverandar_instance_name->instanceattributes.
Full version history
Bug fixes:
- Hotfix for backup age retrieval in metric
sql-server.databases.backup.age. Release 2.5.0 introduced a bug where age of the oldest backup instead of the latest one was retrieved.
Full version history
Bug fixes:
- Only the age of the oldest observable backup was reported in the
sql-server.databases.backup.agemetric. This has now been fixed and the age of the most recent backup is being reported instead.
Improvements:
- Additional dimensions in for backup metrics:
device_type,recovery_model,software_name. - Better granularity for
backup_typedimension: "Full", "Full copy-only", "Differential", "Transaction log", "File or filegroup", "Differential file", "Partial", "Differential partial".
Features:
- Backup monitoring for Azure SQL Database is now available through "Backups" feature set. The
sys.dm_database_backupsview is queried every 5 minutes and a record about each finished backup is ingested as a log. Can be filtered using thebackups_azurevalue forevent.groupattribute. - Monitoring of each individual backup for SQL Server and Azure SQL Managed Instances is now available through "Backups" feature set. Information about every backup finished within the last 5 minutes is retrieved and ingested as a log record.
Full version history
Improvements:
- Default timeout for
longest_queriesis increased to 120 seconds. Allowing query to have more time to complete and fetch the data about top busiest queries on the target database.
Features:
- New variable "Longest queries timeout" that can be specified per monitoring configuration. Default is 120 seconds. Can be increased to values up to 290 seconds.
Warning:
- Any value specified in the "Longest queries timeout" variable field must be a valid integer.
Full version history
Bug fixes:
- Fixed incorrectly detected relationship between Availability Databases and Availability Groups. Previously only one availability database was visible per group.
Warning:
- Direct topology relationship between Availability Group and Availability Database entity types was removed. Instead, the relationship between them is now derived through Availability Replica.
- Direct topology relationship between Database and Availability Database is removed. It wasn't detected properly in the past and has been removed as redundant.
Full version history
Bug fixes:
- Fix retrieval problems in "Queries" and "Jobs" feature sets for instances in non-UTC time zones or running on hosts in non-UTC time zones.
Full version history
Bug fixes:
- Fixed:
Arithmetic overflow error / An expression of non-boolean type specifiedfor Database Files feature set
Full version history
Improvements:
- Top queries and failed jobs are now correctly retrieved for instances with a timezone other than UTC.
Full version history
New features:
- Monitoring of current and failed jobs (enabled through
Jobsfeature set).
Addressed issues:
- Improved compatibility with new Databases App.
Full version history
- Number of longest queries collected every minute is reduced from 100 to 10 to comply with existing log ingestion limitations (to be increased back in the future release).
- Monitoring of backups is now supported on Azure SQL Managed Instance and Analytics Platform System (PDW).
- The
backup_typedimension now contains full backup type names instead of a single letter.
Full version history
Major release with breaking changes
Breaking changes
- All monitoring configurations must be recreated because of change in feature sets.
instancedimension changes its meaning to only contains an actual named instance name orMSSQLSERVERby default.hoursSinceBackupmetric is removed and replaced bysql-server.databases.backup.age.
New features
- Query level monitoring: Top 100 longest running queries are collected every 5 minutes (enabled through Queries feature set).
- Largest database files monitoring: Top 100 largest database files as monitored every 5 minutes (enabled through Database files feature set).
instancedimension is now retrieved from "InstanceName" server property but only contains an actual named instance name. Defaults toMSSQLSERVERfor unnamed instances.- New
serverdimension is now retrieved fromServerNamebut only contains an actual machine or server name, without named instance. - Feature sets are not more granularly grouped allowing for fine grained control over which metrics are collected.
- Detailed backup monitoring with backup size and backup age collected for backup type and individual database.
- Detailed database files monitoring with average file size, used space, and empty space collected for each file type and individual database.
- Entity screens are now adapted to the latest versions of SaaS and utilize DQL.
- Monitoring of communication with availability replcia (bytes sent/received).
- Conversion of database ID to human readable name for Azure SQL databases.
- CPU and Memory monitoring.
- Uptime monitoring.
Bug fixes
- Incorrect detection of named instances in certain cases was addressed through better identification of
instanceandserverdimensions.
Full version history
- Fix: missing column name for SQL Server versions below 2019
Full version history
- Always On support
- Metric type fixed for Lock Wait Time and Latch Average Wait Time
- Entities not being generated for hosts without a domain name
- Entity topology updated
- List of database entities is now available
Full version history
- Support for monitoring databases on named instances.
Full version history
- Release v1.0.1
- Contains the same metrics that were collected by the previous generation of the SQL Server remote monitoring.
