Use Performance Monitor to identify SQL server hardware bottlenecks

Use Performance Monitor to identify SQL server hardware bottlenecks

The best way to start sqlserver performance tuning is to start with the Performance Monitor (system monitor. You can monitor some key counters within a 24-hour period. You will be familiar with the hardware bottlenecks of your sqlserver server.

Generally, Performance Monitor is used to create a 24-hour monitoring log for some key counters. When you decide to create this log, you need to select a typical 24-hour cycle, for example, to select a typical busy date, rather than Sunday or holiday.

Once you log the captured data, the recommended counter value is displayed in the graphic interface of the Performance Monitor. Note the average, minimum, and peak values in the preceding table. After that, use your results to compare with the following analysis. By comparing your results with the following recommended values, you can quickly find the potential hardware bottlenecks your sqlserve is experiencing.

Key performance counters

The following is a discussion of different key performance counters, their recommended values and some options to help solve hardware bottlenecks. Note that I have limited some key counters that the performance monitor needs to monitor. I am doing this because in this article we aim to easily find obvious performance problems, and many other performance monitor counters can be found elsewhere on this website.


Memory: Pages/sec

This counter records the number of pages exchanged between memory and disk per second. Switching more pages and more I/O than your server will reduce your SQL server performance in turn. Your goal is to minimize the number of pages, rather than eliminate them.

If sqlserver is the most important application on your server, the ideal value range is 0 ~ Between 20. Most of the time, you may see more than 20 values. Generally, the average number of pages per second must be below 20.

If the average value is always greater than 20, the biggest one may be the memory bottleneck problem and the memory needs to be increased. Generally, more memory means less pages to be executed.
In most cases, the server determines the size of the appropriate memory used by sqlserver. The average page size is less than 20. Giving sqlserver proper memory means that the server's cache hit rate (buffer hit cache ratio, which will be discussed later) reaches 99% or higher. If your sqlserver cache hit rate reaches 99% or higher in a 24-hour period, but during this period your page count always exceeds 20, this means that you may have run other programs. If so, we recommend that you remove these programs so that sqlserver is the most important program on your server.

If your sqlserver server does not run other programs and the number of pages always exceeds 20 in a 24-hour cycle, you should modify your memory settings. Set it to "dynamically configure sqlserver memory" and set the maximum memory to a higher value. To achieve the best, sqlserver will obtain as much memory as possible to complete its work, rather than competing with other programs for memory.

Memory: available bytes

Another way to check whether sqlserver has enough physical memory is to check the memory object: available bytes counter. This value must be at least 5 MB; otherwise, more physical memory needs to be added. On a dedicated sqlserver server, sqlserver tries to maintain 4-10 MB of free physical memory, and the remaining physical memory is used by the operating system and sqlserver. When the available physical memory is close to 5 MB or lower, sqlserver is most likely to encounter performance bottlenecks due to the lack of memory. In this case, you need to increase the physical memory to reduce the load on the server, or configure a suitable memory for sqlserver.

Physical Disk: % disk Time

This counter measures how busy the disk array is (not an independent disk in a logical partition or disk array ). It provides a relatively good measurement of the disk array's busy degree. In principle, the value of counter % disk time should be less than 55%. If the duration exceeds 55% (about 10 minutes in your 24-hour monitoring cycle), your sqlserver has an I/O bottleneck. If you only see it occasionally, you don't have to worry too much. However, if this happens frequently (that is, several times in an hour), you should start looking for solutions to increase server I/O performance or reduce server load. Generally, it is to add disks to the disk array, or better and faster disks, or add cache to the Controller Card, or use raid of different versions, or replace a faster controller.

Before using this counter on NT4.0, make sure to enter diskperf-y at the NT command prompt to restart the server so that it can be opened manually. This counter must be enabled for the first time in NT4.0, and in Windows2000, this counter is enabled by default.

Physical Disk: avg. Disk Queue Length

In addition to observing the % disk Time counter of the physical disk, you can also use the avg. Disk queue length counter. If the value of each disk in the disk array exceeds 2 (about 10 minutes in your 24-hour monitoring period), your disk array has an I/O bottleneck. Like the counter % disk Time, you don't have to worry too much if you only see it occasionally. However, if this happens frequently, you should find a solution to increase the I/O performance of the server. As mentioned above.

You need to calculate this value because Performance Monitor does not know how many physical disks are in your disk array. For example, if you have a disk array consisting of six physical disks, its avg.
If the disk queue length value is 10, the actual value of each disk is 1.66 (10/6 = 1.66), which is within the recommended value of 2.

Before using this counter on NT4.0, make sure to enter diskperf-y at the NT command prompt to restart the server so that it can be opened manually. This counter must be enabled for the first time in NT4.0, and in Windows2000, this counter is enabled by default.

Using these two counters together will help you identify the I/O bottleneck. For example, if the value of % disk time exceeds 55% and the value of AVG. Disk queue length exceeds 2, the server has an I/O bottleneck.

Processor: % processor time

Processor object: The % processor time counter is available for each CPU and can detect each CPU. It is also available for all CPUs. This is a key counter for observing CPU usage. If the value of the % Total processor time counter exceeds 80% (about 10 minutes in your 24-hour monitoring cycle), there is a CPU bottleneck. If it only happens occasionally and you think it has little impact on your server, then no problem. If this happens frequently, you should reduce the server load, replace the CPU at a higher frequency, increase the number of CPUs, or increase the level 2 cache (L2 cache) of the CPU ).

System: processor Queue Length

Based on the % processor time counter, you can monitor the processor queue length counter. If the value of each CPU lasts for more than 2 (about 10 minutes in your 24-hour monitoring cycle), your CPU has a bottleneck. For example, if your server has 4 CPUs, the total processor queue length counter value should not exceed 8.

If the processor queue length counter value regularly exceeds the recommended maximum but the CPU usage is not very high, reduce the "Max worker threads" configuration value of sqlserver. The high value of the processor queue length counter may be caused by too many worker threads waiting for processing. Reduce the value of "Maximum worker threads" and force the thread pool to kill some threads to maximize the utilization of the thread pool.

When using the counter processor queue length and counter % Total process time together, you can find the CPU bottleneck. If both show that they exceed their recommended values, you can be sure that there is a CPU bottleneck problem.

SQL Server Buffer: buffer cache hit ratio

The counter buffer cache hit ratio in SQL Server Buffer is used to indicate the frequency at which sqlserver obtains data from the cache rather than from the disk. In an OLTP program, the ratio should exceed 90%, and the ideal value is over 99%. If your buffer cache hit ratio is lower than 90%, you need to increase the memory immediately. If the ratio is between 90% and 99%, you should carefully consider buying more memory. If it is close to 99%, your sqlserver performance is faster. In some cases, if your database is very large, you cannot reach 99%, even if you have configured the maximum memory on the server. All you can do is add the memory as much as possible.

In OLAP programs, this ratio is greatly reduced due to its working principle. In any case, more memory can always improve the performance of sqlserver.

SQL Server General: user connections

Since the number of users of sqlserver affects its performance, you need to focus on sqlserver's general statistics object: user connections counter. It displays the current number of sqlserver connections, not the number of users.
If the counter exceeds 255, you need to set the "Maximum worker threads" configuration value of sqlserver to 255 higher than the default value. If the number of connections exceeds the number of available threads, sqlserver will share the thread, which will affect the performance. "Maximum worker threads" needs to be set to be higher than the maximum number of connections that your server has ever reached.