Detecting when hardware resources are a limiting factor

As larger and larger workloads are placed on a database, performance is typically limited by CPU cycles, memory space, or disk I/O bandwidth. An inefficient application or database server could be the cause. If you cannot detect any inefficiencies, you may need to add additional hardware resources. To view a list of common inefficiencies and recommendations for solving them, see Troubleshooting performance problems.

Adding resources may not resolve scalability problems or improve computer performance. For example, if a database server is fully using all of its allotted CPUs, it may indicate that you should assign more CPU resources. However, doubling the number of CPUs available to the database server may not double the amount of work the database server can perform.

Use the Statistics tab in the Application Profiling Details area to detect whether hardware resources are a limiting factor for performance.

Detecting whether CPU is a limiting factor To detect whether CPU as a limiting factor, check the ProcessCPU statistic. If this statistic is not present on the graph, click the Add Statistics button and select ProcessCPU. If the graph shows ProcessCPU increasing at a rate of nearly 1 point per second per CPU assigned to the database server, then the CPU is a limiting factor. For example, for a database server running on two CPUs, if the Process CPU counter increased from 2220 to 2237 in ten seconds, this indicates that CPU usage over that twelve second period was (2237-2220) / 10s * 100 % = 170%, meaning that each CPU is running at 170% / 2 = 85% of its capacity.
Detecting whether memory is a limiting factor To detect whether memory (buffer pool size) is a limiting factor, check the CacheHits and CacheReads database statistics. If these statistics are not present on the graph, click the Add Statistics button and select CacheHits And CacheReads. If CacheHits is less than 10% of CacheReads, this indicates that the buffer pool is too small. If the ratio is in the range of 10-70%, this may indicate that the buffer pool is too small—you should try increasing the cache size for the database server. If the ratio is above 70%, the cache size is likely adequate. Note that this strategy only applies while the database server is running at a steady-state—that is, it is servicing a typical workload and has not just been started.
Detecting whether I/O bandwidth is a limiting factor To detect whether I/O bandwidth is a limiting factor, check the CurrIO database statistic. If this statistic is not present on the graph, click the Add Statistics button and select CurrIO. Look for the largest sustained number for this statistic. For example, look for a high plateau on the graph; the wider it is, the more significant the impact. If the graph has sustained values equal to, or greater than 3 + the number of physical disks used by database server, it may indicate that the disk system cannot keep up with the level of database server activity.

Detecting when hardware resources are a limiting factor

See also