Use Performance Monitor to monitor common metrics of SqlServer in Windows, and monitor sqlserver

Use Performance Monitor to monitor common metrics of SqlServer in Windows, and monitor sqlserver

As mentioned in the above article, win's Performance Monitor is an essential tool for monitoring database performance. Next I will introduce some common monitoring metrics, which are nothing more than disks, CPUs, memory and other hardware operating indicators include database locks, user connections, and parameter indicators that need to be paid attention to based on your own business decisions.

1. SQL Server Buffer: Buffer Cache Hit Ratio

This is a very important parameter to check whether the memory is insufficient. 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. Sqlserver caches Some queried data in the memory for future query. When A query A comes in, the database will compile the SQL statement to check the required data. Then, the execution plan will first go to the memory to check whether the data required for this query exists, if the same SQL statement has just been executed or the table data is cached in the memory, but no data is found in the memory, this may be because the memory is insufficient, which causes the memory to squeeze and write the cached data back to the hard disk or release it to provide other database requests for use. In general, this value should be at least 90% for oltp systems, and the ideal value is 99%. If the value is lower than 90%, we recommend that you add memory.

2. Memory: Pages/sec

This is also an important parameter for monitoring whether the memory is insufficient. This counter records the number of pages exchanged between memory and disk per second. Frequent page switching will consume more io, which will affect the server performance. For example, a supermarket has a shelves filled with new products a, B, and c. When you go to the supermarket to buy a, you can directly go to the shelves to get, it's very convenient. When a Customer walks around the supermarket and tells you why I didn't find the old product d, I want to buy it, then the staff will go to the warehouse and place the item d on the shelf for the next customer to buy. But what should I do if the shelves are full? I can only put a, which has no one to worry about for a long time, in the warehouse, and then leave it empty for d. But next time another customer comes, he wants to buy, the staff had to take a out again and replace d on the shelf. In fact, the memory is the shelf, and the hard disk is the warehouse. Because the shelves are too small, we can only change the items on the shelves frequently to provide normal operation. To reduce the io overhead produced by repeated back-and-forth operations, we can only change a larger shelf to meet the requirements.

If SQL server runs only on the server, the ideal range of this indicator should be between 0 and 20. If the value occasionally exceeds 20, the impact will not be significant. If the value frequently exceeds 20, that means you may need to add memory to this server.

Of course, this indicator should be combined with the Buffer Cache Hit Ratio of the previous indicator. If the Hit rate of the previous indicator Buffer is always 99% or higher, during this period, your page is always more than 20, that means not only the memory is insufficient, but other programs occupy the system memory.

3. Memory: Available Bytes

This is another counter that monitors memory conditions. This value must be at least 5 MB, because sqlserver always needs to maintain 5-10 MB of free memory for allocation. When this value is less than 5 MB, sqlserver may cause performance bottlenecks due to the lack of memory.

4. Physical Disk: % Disk Time

This counter records the disk busy (the entire disk array or physical disk busy ). In theory, this value should be lower than 55%. if it continues to be higher than 55%, it indicates that this server may have an io bottleneck.

If it appears only a few times occasionally, you don't have to worry about it. However, you can find out what statements are being executed by the database at this time point and optimize them accordingly.

5. Physical Disk: Avg. Disk Queue Length

This is an important metric for viewing disk I/O information. In theory, the value of each physical disk should not exceed 2. Of course, this value needs to be calculated. For example, if a raid 10 is created on four physical disks, the average value of the disk queue in a monitoring period is 10, the queue value of each disk is 10/4 = 2.5. Therefore, the disk array has an I/o bottleneck. This is the same as the previous disktime indicator. You don't have to worry about it occasionally. If it appears for a long time, you have to consider solving the disk I/O performance problem.

6. Processor: % Processor Time

This is an indicator for monitoring cpu usage (similar to disk time ). This is a key parameter for observing cpu usage. If the Processor Time counter value exceeds 80%, it indicates a cpu bottleneck. If it only appears occasionally, it indicates that there may be a cpu-consuming query at this time point. You can try to capture the SQL statement and optimize it next time at this time point. If the cpu usage remains high after a certain time point, the common situation is: 1. suddenly high concurrency 2. index reorganization 3. suddenly an index with a large amount of data frequently used fails. 4. deadlock 5. there are many others. Locate the problem and delete it.

7. System: Processor Queue Length

This metric is similar to the disk queue length and is also counted as a single cpu. A single cpu cannot exceed 2. For example, if you are a 2u machine, the value should not exceed 4. If you continuously exceed 4 in a monitoring cycle, a cpu bottleneck may occur.

Basically, there are so many commonly used counters that can work with you to check sqlserver performance. If you are interested, you can use Baidu.