Monitor common metrics for SQL Server with Windows Performance Monitor

The above article mentions that win's performance Monitor is a necessary tool to monitor database performance, and then I will introduce some common monitoring indicators, in fact, the disk, CPU, memory and other hardware operating indicators and the database itself like the lock Ah, the number of user connections Ah, There are parameter indicators that need attention according to their own business decisions.

1.SQL Server buffer:buffer Cache hit Ratio

This is a very important parameter to see if the memory is insufficient. The counters in SQL Server buffer buffer cache hit ratio are used to indicate how often SQL Server obtains data from the cache instead of the disk. SQL Server caches some of the queried data in memory for later query use. When a query a comes in, the database compiles the SQL to see what data is needed, and then executes the plan to look in memory for the data that is needed for this query, and if the same SQL has just been executed or the table's data has been cached in memory, but the data is not found in memory, It is possible that memory squeeze is caused by insufficient memory to write the cached data back to the hard disk or to release it to provide additional database requests to use. In general OLTP systems, this value should be at least 90% above, the ideal value is 99%. If this value is lower than 90%, it is recommended that you add memory.

2.memory:pages/sec

This is also a more important parameter to monitor whether memory is insufficient. This counter records the number of pages exchanged between memory and disk per second. Frequent switching of pages consumes more IO, which can affect the performance of the server. For example, the supermarket has a shelf full of new products A, B, C, when you go to the supermarket to buy a when you go directly to the shelves can get a, convenient, when the customer into the supermarket after a circle to tell you how I did not find the old goods D, I would like to buy this d, Then the staff will go to the warehouse to take the goods d out to the shelves for the next customer to buy. But the shelves are full of what to do, can only long time no one to ask for a shelf in the warehouse and then empty place to put D, but the next time another customer came and want to buy a intention, the staff will have to take a out again to replace the D on the shelves. In fact, memory is the shelf, the hard disk is the warehouse. Because the shelves are too small, so that only frequent replacement of goods on the shelves to provide normal operation, want to reduce the back and forth to move the resulting IO overhead, can only be replaced by a larger shelf to meet demand.

If only SQL Server running on the servers, the ideal range of this indicator should be between 0-20 , occasionally more than 20 words, if the value of more than 20, it means that your server may need to add memory.

Of course, this indicator to match the previous indicator buffer Cache hit Ratio , if the last indicator buffer hits have been 99% or higher, and during this period your page exchange has been over 20, that means not only memory shortage, and other programs consume system memory.

3.memory:available Bytes

This is the other counter that monitors the memory situation. This value is at least as much as 5M, because SQL Server needs to always maintain 5-10m free memory for allocation, and when this value is less than 5m, SQL Server may have a performance bottleneck due to lack of memory.

4.Physical Disk:% Disk time

This counter records how busy the disk is (the entire disk array or the physical disk is busy). Theoretically this value should be lower than 55%, if it persists above 55%, it indicates that there may be an IO bottleneck on this server.

If only occasionally appear several times, that does not need to worry, but can correspond to find this time point, the database is doing what the statement, corresponding optimization.

5.physical disk:avg. Disk Queue Length

This is a more important indicator for viewing disk IO conditions. In theory, the value of each physical disk should not exceed 2. Of course, this value is calculated, for example, with 4 physical disk to do a raid10, at this time in a monitoring cycle the average disk queue is 10, then the queue value of each disk is 10/4=2.5, then you can say that this disk array has an I/O bottleneck. This is the same as the previous Disktime indicator, occasionally do not have to worry about, if a long time, then you have to start to consider solving the disk IO performance issues.

6.Processor:% Processor time

This is an indicator for monitoring CPU conditions (similar to disk Time). This is a key parameter for observing CPU utilization. If the value of the processor Time counter continues to exceed 80%, there is a bottleneck on the CPU. If it only happens occasionally, it may indicate that there is a particular CPU-consuming query at this point in time, and you can try to grab SQL and optimize it the next time this point is coming. If the CPU continues to soar after a certain point in time, the common scenario is: 1. Sudden high Concurrency 2. Index reorganization 3. Suddenly a particularly large index of frequently used data is invalidated by 4. Deadlock 5. A lot of other things. Find out where the problem is and dispose of it.

7.system:processor Queue Length

This indicator is similar to disk Queue length and is counted as a single CPU. A single CPU can not exceed 2, for example, you are a 2u machine, that value should not exceed 4, if the duration of a monitoring cycle of more than 4, then there may be a CPU bottleneck.

Basically commonly used is so many, there are many can cooperate with you to detect the performance of SQL Server counters, interested can own Baidu under.

　　

　　

　　

　　

Monitor common metrics for SQL Server with Windows Performance Monitor