Common metrics for monitoring SQL Server using Performance Monitor under Windows _mssql

The above article mentioned win Performance Monitor is a necessary tool for monitoring database performance, Next I will give you some common monitoring indicators, in fact, is nothing more than disk, CPU, memory and other hardware operating indicators as well as the database itself, such as lock Ah, the number of user connections Ah, There is the need to pay attention to the parameters of their own business indicators.

1.SQL Server buffer:buffer Cache Hit Ratio

This is a very important parameter to see if the memory is low. The counters in SQL Server buffer buffer Cache Hit ratio are used to indicate how often SQL Server obtains data from the cache rather than from the disk. SQL Server will cache 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, then the execution plan goes first in memory to see if there is any data needed for this query, 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. That may be due to a lack of memory that causes memory squeeze to write the cached data back to the hard drive or release it to provide additional database requests. In general, OLTP systems, this value should be at least 90% above, the ideal value is 99%. If this value is below 90%, it is recommended that you add memory.

2.memory:pages/sec

This is also a relatively important parameter to monitor the lack of memory. This counter records the number of pages exchanged between memory and disk per second. Frequent swap pages consume more IO, which can affect the performance of the server. For example, the supermarket has a shelf top 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, very convenient, when customers go shopping in the supermarket after a circle to tell you how I did not find the old product D, I want to buy this d, Then the staff will go to the warehouse and put the product D out on the shelves for the next customer to buy. But the shelves are full of how to do it, can only be a long time no one wants to put a shelf to the warehouse and then empty out place D, but next time another customer came and want to buy a intention, the staff will have to take a out again to replace the shelves of D. In fact, memory is the shelf, hard drive is the warehouse. Because the shelves are too small, resulting in frequent replacement of the goods on the shelves to provide normal operations, to reduce the repeated back and forth from the IO overhead, can only change to a larger shelf to meet demand.

If only SQL Server running on the servers, the ideal range of this metric should be between 0-20, occasionally more than 20 of the impact is not significant, 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 previous indicator buffer hit 99% or higher, and this period of your page exchange has been more than 20, which means not only insufficient memory, and other programs occupy the system memory.

3.memory:available Bytes

Another counter that monitors memory conditions is this. This value is at least as high as 5M, because SQL Server needs to always maintain 5-10m free memory for allocation, and when this value is below 5m, SQL Server may have a performance bottleneck due to a lack of memory.

4.Physical Disk:% Disk Time

This counter records how busy the disk is (whether the entire disk array or physical disk is busy). Theoretically this value should be less than 55%, and if it continues above 55%, that means 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 point in time, the database is doing what the statement, corresponding optimization.

5.Physical disk:avg. Disk Queue Length

This is a more important metric for looking at disk IO. Theoretically, the value of each physical disk should not exceed 2. Of course, this value needs to be calculated, such as using 4 physical disk to do a raid10, at this time in a monitoring cycle, the average disk queue is 10, the queue value of each disk is 10/4=2.5, then it can be said that the disk array has I/O bottleneck. This is the same as the previous disktime indicators, occasionally do not have to worry about, if a long time, you have to start to consider the problem of troubleshooting disk IO performance.

6.Processor:% Processor Time

This is a metric 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%, the CPU has a bottleneck problem. If it's just occasional, it might be a CPU-intensive query at this point in time, and try to grab the SQL and optimize it the next time this point comes up. If the CPU has been soaring after a certain point in time, the common scenario is: 1. Suddenly the high Concurrency 2. Index reorganization 3. Suddenly an index that is often used with a very large amount of data is invalidated by 4. Deadlock 5. Many others. Find out where the problem is and get rid of it.

7.system:processor Queue Length

This metric is similar to disk Queue length and is also counted as a single CPU. A single CPU can not exceed 2, such as you are 2u machine, the value should not be more than 4, if in a monitoring period of more than 4 continuous, then there may be CPU bottlenecks.

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