Documentation forSQL Sentry

Azure SQL Database Performance Metrics

This article covers the various Azure SQL Database performance metrics displayed by the Performance Analysis Dashboard and Performance Analysis Overview, and how to interpret different metric values and combinations of values across different Azure SQL DB metrics for SQL Sentry.

Note:  For Mode: S = Sample and H = History.

Azure SQL Database Metrics

Section Metric Description
DTU Usage Total DTU percent A DTU represents the power of the database engine as a blended measure of CPU, memory, and read and write rates. This measure helps you assess the relative power of the SQL Database performance levels. Each service tier, which sets pricing and usage limits for an Azure SQL Database, expresses the amount of resource limits as a number of DTUs. The more DTUs an Azure SQL Database is allocated the more resources the database will have to service the workload.

If your database is seeing high total DTU percentage usage it may benefit from adjusting to the next highest service tier to improve performance. If you're consistently seeing very low total DTU percentage usage you may save some money by scaling down to the next lower service tier.

Additional Information: For more information regarding DTUs, see the documentation on Azure.com. For variable performing workloads and sharing resources across multiple Azure SQL Databases, see Elastic Database Pools.
Mode: S, H
Type:Percent
DTU Usage CPU This metric is the average CPU percentage based on the limit of the service tier. This is one of the metrics that makes up DTU.
Mode: S, H
Type:
Percent
DTU Usage Data IO This metric is the average Data I/O percentage based on the limit of the service tier. This is one of the metrics that makes up DTU.
Mode: S, H
Type:
Percent
DTU Usage Log IO This metric is the average log I/O percentage based on the limit of the service tier. This is one of the metrics that makes up DTU.
Mode: S, H
Type:
Percent
Memory Usage Allocated Memory Usage Each service tier has a maximum amount of memory allowed for the Azure SQL Database to use. This metric provides the percentage of the allowed memory being used for the database.

It will be very common for this metric to be high. If much of the data your applications need is in memory it means better performance because the database doesn't have to read from the physical disk to return the data.
Mode: S, H
Type:
Percent
Database Size Size in GB Each service tier has a maximum size allowed for the Azure SQL Database. This metric provides the size of the database, not including the transaction log.

If you're approaching the maximum size allowed you may need to increase your service tier size or look to scale out your database using sharding techniques.

Additional Information: For more information about sharding with Azure SQL Database, see Elastic Database tools.
Mode: S, H
Type:
Percent, GB
SQL Server Activity User Connections The total number of connections established to the Azure SQL Database. Details for each connection can be viewed by querying the sys.dm_exec_connections DMV.
Mode: S
Type:
Last value
SQL Server Activity Blocked Processes The total number of blocked processes detected on the Azure SQL Database. The Dashboard is designed to give you at a glance information about the number of blocked processes.
Mode: S, H
Type:
Last value
SQL Server Activity Transactions The average number of transactions per second for the Azure SQL Database. A transaction can be either a user-defined statement block surrounded by a BEGIN TRAN and END TRAN, or an individual DML statement (insert, update or delete).
Mode: S, H
Type:
Avg/sec
SQL Server Activity Backup MB/sec The data rate in MB/sec for any backup operations taking place on the server.

In Azure SQL Database the platform creates backups automatically for you. While you don't have direct control over the backup times, this does allow you to help determine if backups are negatively affecting your performance.
Mode: S, H
Type:
MB/sec
SQL Server Waits CPU Percent of Total Waits CPU percentage of Total Waits represents the percentage of all waits which are signal waits. Signal wait time is the time a thread has spent waiting on the CPU after being signaled that its resource is available. A high CPU percentage of Total Waits percentage may indicate CPU pressure.
Mode: S
Type:
Percent
May correlate: High CPU
SQL Server Waits Wait Time: by Category The average wait time (in milliseconds) per second for all processes on the database, broken down by major physical resource category (CPU, Memory, etc.). Only the wait types that can definitively be attributed to one of the physical resource categories are included in the calculations for this chart. The Other category is for a few other important wait types that can either affect performance in more than one major category, or can't be directly attributed to any category with absolute certainty, such as backups and parallelism respectively.

Waits by category is one of the most important charts on the dashboard, because it provides an instant profile of the SQL Database and where it's spending the most time waiting for physical resources. If waits by category are consistently low, then what the other dashboard charts are showing is less important.

To view the specific wait classes involved for a particular interval, choose the interval by selecting on the chart, and then click the Sample button on the toolbar.

If you hover over a category, the detailed wait types for that time sample are shown in a tooltip.

Additional Information: See also: 
Mode: H
Type:
Avg ms/sec
SQL Server Waits Wait Time: by Class The average wait time (in milliseconds) per second for all processes on the server, broken down by wait class. There are approximately 30 different wait classes, and each represents a particular SQL Server functional area or type of activity. Meaningless or innocuous wait types such as timer and queue waits are pre-filtered from view. Each class is further broken down into resource and CPU waits.

If you hover over a class column, the detailed wait types are shown in a tooltip.

Mode: S
Type:
Avg ms/sec
SQL Server Memory Buffer Cache Size The current size of the buffer cache, in MB. You want this to be as large as possible for maximum performance.
Mode: S, H
Type:
MB
SQL Server Memory Page Life Expectency The average lifespan of a data page. If this value is less than 600, it's an indicator of memory pressure. Ideally, it should be much higher than 600 if ample memory is available. In general, the larger the buffer cache size, the higher it should be. This is the best universal indicator of memory pressure.

Azure SQL Database is a multi-tenant environment and this metric represents more than just your database. Use this metric more as an indicator of overall memory pressure and trends.
Mode: S
Type:
Last value
Database IO Read latency by database file The average time in milliseconds each physical disk read is taking for a particular database file. For Azure SQL Database the log and data file are shown.

The log and data files in Azure SQL Database are handled for you as part of the Platform as a Service offering.
Mode: S, H
Type:
Avg ms/Read
Database IO Write Latency by Database File The average time in milliseconds each physical disk write is taking for a particular database file.

The log and data files in Azure SQL Database are handled for you as part of the Platform as a Service offering.
Mode: S, H
Type:
Avg ms/Write
Database IO Log Flushes Log flushes occur with every DML operation, and are a normal part of SQL Server activity. It's important to note that log writes to physical disk from updates to buffer pages happen immediately upon transaction commit, whereas writes to physical disk from the changed buffer pages is delayed until the next checkpoint occurs. It's critical that the physical disk system where the transaction log resides is fast enough to keep up with activity. If not, it can slow down all DML operations occurring in the database.
Mode: S, H
Type:
Avg/sec