Documentation forDatabase Performance Analyzer

SQL Server metrics collected by DPA

The following sections list the metrics that DPA collects for SQL Server databases.

  • 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 total waits that are runnable and waiting for an available CPU. Anything over 20% indicates that there is 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.

O/S CPU Queue Length via WMI The number of O/S threads waiting to access the CPU for the entire system (includes all instances on this machine).
O/S CPU Utilization The percentage of CPU being used for the entire system (includes all instances on this machine). Potential solutions to a CPU bottleneck are to reduce the server load by tuning the queries waiting on CPU, get faster CPUs, or get more CPUs.
Instance CPU Utilization The CPU Utilization for this specific SQL Server instance. This is a subset of the O/S CPU Utilization metric.

Memory

Metric Description
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.
Memory Paging Rate via WMI The number of pages read from or to the disk to resolve memory references to pages that were not in memory at the time of the reference. This metric is for the entire system (includes all instances on this machine). High rates may indicate excessive memory contention (thrashing).
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'

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%';

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.
Buffer Cache Size The current size of the SQL Server Buffer Cache.
Plan Cache Size The current size of the SQL Server Plan Cache.
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.
Log Flushes The number of log flushes that occur per second.
Log Bytes Flushed The number of bytes of information being flushed per second.

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.
Physical Read Rate via WMI The number of kilobytes being read from disk every second for the entire system (includes all instances on this machine). If this is high, drill in to the DPA Trends charts and review queries waiting on physical read wait types, such as PAGEIOLATCH_SH or PAGEIOLATCH_EX.
Physical Write Rate via WMI The number of kilobytes being written to disk every second for the entire system (includes all instances on this machine). If this is high, drill in to the DPA Trends charts and review queries waiting on write wait types, such as IO_COMPLETION or PAGEIOLATCH.
Physical I/O Rate via WMI The number of kilobytes being read and written to disk every second for the entire system (includes all instances on this machine). If this is high, drill in to the DPA Trends charts and review physical read and write wait types.
O/S Disk Queue Length via WMI The number of I/O operations waiting for disk drives to become available for the entire system (includes all instances on this machine).
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.
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.
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.

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.
Active Sessions The number of sessions in this instance actively performing work or waiting for a resource (excludes idle sessions).
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 instance.

License Compliance

Metric Description
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.
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.
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.
Core Count The number of cores used by the instance. This value is typically used for per-core licensing.