Documentation forDatabase Performance Analyzer

ASMI metrics collected by DPA

The following sections list the metrics that DPA collects for Azure SQL managed instances (ASMIs).

  • Learn how to view these metrics and change the thresholds.
  • For detailed information about resolving issues, click the Information link next to the metric on the Resources tab. The Information link is not available for all metrics.

CPU

Metric Description
Signal Waits

The percentage of overall time that sessions are waiting for a CPU to become available. Anything over 20% indicates a possible CPU resource bottleneck.

Examine the overall wait events for the server as a whole. A high signal wait percentage could be due to an increased number of sessions, so examine the overall workload for the server as well. Take steps to either reduce the overall runtime for queries or reduce the total number of sessions.

CPU Utilization The amount of CPU being used as a percentage of the limit of the service tier.

Memory

Metric Description
XTP Storage Utilization The percentage of available XTP Storage being used.
Page Life Expectancy The number of seconds a page will stay in the buffer pool without references. A lower value (for example, under 300) indicates the buffer pool is under memory pressure and you should add more memory to the system (enable AWE on 32-bit systems) or find the process in Task Manager that is consuming outside of SQL Server.
O/S Memory Utilization The percentage of memory being used for the entire system (includes all instances on this machine). If this is high and the Memory Paging Rate metric is high, you might need to increase the amount of physical RAM in the server, reduce the load on the server, or change the server memory configuration. Run sp_configure and review settings for "max server memory" and "min server memory" to determine amount of memory allocated to SQL Server.
Plan Cache Size The current size of the SQL Server Plan Cache.
Buffer Cache Hit Ratio

The rate at which SQL Server finds the data blocks it needs in memory rather than having to read from disk for this instance. By itself, the buffer cache hit ratio is not very meaningful except for servers with undersized memory settings. Tuning queries and performing index optimization is the best way to increase buffer cache hit ratios. To see the current metrics for the buffer cache, run the following query:

select * from master..sysperfinfo where object_name like 'Buffer Manager'

Buffer Cache Size The current size of the SQL Server Buffer Cache.
Procedure Cache Hit Ratio

The percentage of time when SQL Server looks for an execution plan in the procedure cache and finds it for this instance. If this is low, try to write more reusable code or consider increasing the size of the procedure cache. To see current metrics for the procedure cache, run the following query:

select * from master..sysperfinfo where object_name like '%Plan Cache%';

Log Bytes Flushed The number of bytes of information being flushed per second.
Log Flushes The number of log flushes that occur per second.
SQL Compilations

The number of compilations performed by SQL Server per second. Compilations are a natural part of SQL Server operations but do utilize 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 re-compilations performed by SQL Server per second. Re-compilations occur for many reasons but this number should typically be low.

Disk

Metric Description
Total I/O Wait Time

The sum of all I/O activity for all database files. If this is high:

  1. Examine the current physical structure of databases on the server to see if it is possible to reduce I/O load by redistributing the database files to distinct disks.
  2. Examine queries and database design to determine if they can be tuned to reduce I/O.
Total Read I/O Wait Time The sum of all read I/O activity for all database files.
Total Write I/O Wait Time The sum of all write I/O activity for all database files.
Data I/O Utilization The average data I/O utilization as a percentage of the service tier limit.
Log Write Utilization The average transaction log writes as percentage of the service tier limit.
O/S Disk Queue Length The number of I/O operations waiting for disk drives to become available for the entire system (includes all instances on this machine). Spikes of high disk queue length may be normal, but if this is high for an extended period, you could have an I/O bottleneck. Drill in to DPA Trends charts to examine queries with I/O wait types during the timeframe.
Page Reads The number of SQL Server physical reads from disk to memory. OLTP workloads are typically about 80-90 per second with higher values (or spikes) being an indication of insufficient storage performance, insufficient indexing, or not enough memory.
Page Writes The number of SQL Server physical writes from memory to disk. OLTP workloads are typically about 80-90 per second. If this is high (or spikes) it needs to be cross checked with lazy-writes/sec and checkpoints in order to determine if the issue might be due to low memory.
SQL Disk Read Latency Disk read latency from dm_io_virtual_file_stats DMO.
SQL Disk Write Latency Disk write latency from dm_io_virtual_file_stats DMO.

Network

Metric Description
Round-trip Time The round-trip time when running "select 1" against this instance (includes network time but not connect time). If this is high, contact your network administrator to understand network latency.

Sessions

Metric Description
Transaction Rate The number of transactions being executed every second in this instance (the Transactions/sec statistic from sysperfinfo for the instance).
Blocked Sessions The number of sessions that are blocked in this instance because another session is using a needed resource.
Max Worker Utilization Maximum concurrent workers (requests) as a percentage of the limit of the database's service tier.
Active Sessions The number of sessions in this instance actively performing work or waiting for a resource (excludes idle sessions).
Max Session Utilization Maximum concurrent sessions as a percentage of the limit of the database's service tier.
Batch Requests The number of batches being executed by SQL Server every second.

Waits

Metric Description
Total Instance Wait Time The total wait time for the database instance.

License Compliance

Metric Description
Sessions The number of sessions connected to this instance (even if the connection is idle). This value is typically used for licensing based on number of concurrent connections.
Connected Users The number of distinct users (that is, login names) connected to this instance (even if the connection is idle). This value is typically used for per-user licensing.
Core Count The number of cores used by the instance. This value is typically used for per-core licensing.
Connected Devices The number of distinct client machines connected to this instance (even if the connection is idle). This value is typically used for per-device licensing. It can also be used to approximate per-user licensing.