Common metrics for monitoring SQL Server using Performance Monitor under Windows

This article focuses on common metrics for using Performance Monitor to monitor SQL Server under Windows, including the buffer Cache hit Ratio, pages/sec, Available Bytes, Disk Time, AVG. Disk Queue Length, Processor time, Processor queue Length, etc.

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 at least be above 90% and the ideal value is 99%. If this value is less 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 out of memory, but also other programs that 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 less than 55%, if it lasts more than 55%, that 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.

The following table shows the calculation formulas for I/O per disk:

calculation method
			RAID10	[reads+ (2*writes)]/number of Disks

Indicates the percentage of time that the disk drive is serving a read or write request, and if only%disk is large, the hard disk may be a bottleneck

Average Disk Queue Length

Represents the percentage of time that disk reads and write requests are served, and can increase performance by increasing the disk fabric array (twice times the number of <= disks)

Average Disk Read Queue Length

Indicates the average number of disk read requests

Average Disk Write Queue Length

Indicates the average number of disk write requests

Average Disk Sec/read

Average time to read data on disk in S

Disk BYTES/SEC provides the throughput rate of the drive system.

Common metrics for monitoring SQL Server using Performance Monitor under Windows