Azure SQL database metrics collected by DPA
The following sections list the metrics that DPA collects for Azure SQL database instances.
- Learn how to view these metrics and change the thresholds.
- For detailed information about resolving issues, click the
next to the metric on the Resources tab. The Information link is not available for all metrics. - If the database instance runs on a virtual machine (VM), metrics collected for the VM are described in VM metrics collected by DPA
Connections
| Metric | Description |
|---|---|
| Connected Machines | The number of distinct client machines connected to this database (even if the connection is idle). |
| Connected Users | The number of distinct users (that is, login names) connected to this database (even if the connection is idle). |
| Sessions | The number of sessions connected to this database (even if the connection is idle). |
CPU
| Metric | Description |
|---|---|
| Core Count |
The number of cores used by the instance. Use this value to assess computing capacity and scaling. |
| CPU Utilization |
The percentage of CPU being used based on the DTU limit. If CPU is near the upper limit, use DPA to determine which queries are contributing to high CPU usage, and determine if the queries can be tuned. |
| Instance CPU Utilization |
The CPU being used for this specific SQL Server instance. This is a subset of the CPU Utilization metric. High values could indicate performance bottlenecks. |
Disk
| Metric | Description |
|---|---|
| Data I/O Utilization | The percentage of data I/O usage based on the DTU limit. |
| Database Size | The size of the database in GB (rounded up to the nearest GB). |
| Database Storage Consumption | The percentage of the storage available to the database that is currently used. |
| Log Write Utilization | The percentage of log write usage based on the DTU limit. |
| Page Reads |
The number of physical reads from disk to memory. High values (or spikes) could indicate insufficient storage performance, insufficient indexing, or not enough memory. |
| Page Writes |
The number of physical writes from memory to disk. This value can help you diagnose disk I/O pressure. |
DTU
| Metric | Description |
|---|---|
| DTU Consumption |
The total number of DTUs (Database Transaction Units) being used. A DTU represents a combination of CPU, memory, data I/O and transaction log I/O. Microsoft places limits on these resources based on what service tier a database is in. When a database exceeds its limit for any resource, Microsoft restricts throughput, which slows performance. |
| DTU Limit | The DTU limit for this database instance. |
| DTU Utilization | The percentage of available DTUs being used. Use this value to determine the appropriate service tier for your needs. |
Memory
| Metric | Description |
|---|---|
| Buffer Cache Size |
The current size of the SQL Server buffer cache. |
| Instance Memory Utilization |
The memory usage of the database instance. Monitor this value for memory saturation or leaks. |
| Log Bytes Flushed |
The number of bytes of log data being flushed per second. High values could indicate frequent transaction commits. |
| Memory Usage Utilization |
The percentage of memory being used. It is not unusual for this metric to be high. Data being used by applications is stored in memory to improve performance. |
| Page Life Expectancy |
The number of seconds a page stays in the buffer pool without references before it is flushed. A lower value (for example, under 300) indicates the buffer pool is under memory pressure and you should add more memory to the system or find the process in Task Manager that is consuming memory outside of SQL Server. |
| Plan Cache Size |
The current size of the SQL Server Plan Cache. This value can help you assess query plan reuse and memory usage. |
| SQL Compilations |
The number of compilations performed by SQL Server per second. Compilations are a natural part of SQL Server operations but do use CPU and other resources. Compare this to the Batch Requests/sec metric to understand if this metric is too high. Minimizing compilations will help overall performance. For more information, see the following Microsoft Knowledgebase article: http://support.microsoft.com/kb/243588. |
| SQL Re-Compilations |
The number of query re-compilations performed by SQL Server per second. Re-compilations occur for many reasons but this number should typically be low. High values could indicate schema changes. |
| XTP Storage Utilization | The percentage of XTP storage usage based on the DTU limit. This resource is available only for databases running on the Premium service tier. Zero percent is returned for the Basic and Standard service tiers. |
Network
| Metric | Description |
|---|---|
| Round-trip Time | The round-trip time when running "select 1" against this database (includes network time but not connect time). If this is high, contact your network administrator to understand network latency. |
Sessions
| Metric | Description |
|---|---|
| Active Sessions | The number of sessions in this database actively performing work or waiting for a resource (excludes idle sessions). |
| Batch Requests |
The number of batches being executed by SQL Server every second. This value indicates workload intensity and is useful for capacity planning. |
| Blocked Sessions | The number of sessions in this database that are blocked because another session is using a needed resource. |
| Max Session Utilization | The percentage of Max Session Utilization based on the database limit. |
| Max Worker Utilization | The percentage of Max Worker Utilization based on the database limit. |
| Transaction Rate |
The number of transactions being executed every second in this instance. This value helps you identify transaction throughput and bottlenecks. |
TempDB
Space required by the tempDB database fluctuates based on the number of queries running and the nature of those queries. Use tempDB metrics to monitor the amount of space required.
| Metric | Description |
|---|---|
| TempDB % Free Space |
The percentage of unused space in tempDB. If tempDB fills up and cannot autogrow, the performance of all queries will be affected as they wait for access to tempDB. |
| TempDB Free Space |
The amount of unused space in tempDB. |
| TempDB Size |
The amount of space currently allocated for tempDB. Use this value to track the amount of space tempDB typically uses and plan for storage requirements. Each time SQL Server is restarted, tempDB is recreated using the default size. By default, tempDB grows automatically as needed. However, the files cannot be used during that process, and excessive autogrowth can lead to fragmentation. If tempDB typically needs to grow by a large amount, consider increasing the initial size. |
| TempDB Log File % Free Space |
The percentage of space allocated to the tempDB log file that is currently unused. Space in the tempDB log file is critical for transaction logging and rollback operations. Insufficient space can lead to transaction failures. |
| TempDB Log File Free Space |
The amount of space allocated to the tempDB log file that is currently unused. |
| TempDB Log File Size |
The amount of disk space currently allocated for the log file in the tempDB database. Each time SQL Server is restarted, tempDB is re-created, and the log file is created using the default size or the size specified by the DBA. If the tempDB log file requires more space, by default it autogrows as needed. However, autogrowth can affect performance because the tempDB log file cannot be used during autogrowth, and because autogrowth can lead to file fragmentation. Use the TempDB Log File Size metric to:
|
Waits
| Metric | Description |
|---|---|
| Total Instance Wait Time | Total wait time for the database instance. |