Thursday, March 5, 2015

Important counters in Performance Monitor tool to identify bottleneck.

LogicalDisk: Avg. Disk Queue Length:  If a logical disk gets very busy, then I/O requests have to be queued. The longer the queue, the more likely there is an I/O bottleneck. Again, be sure to select those counters for each logical drive that contains your mdf and ldf files
Memory: Available Mbytes: Measures how much RAM is currently unused, and so available for use by SQL Server and the OS. Generally speaking, if this drops below 5mb, this is a possible indication of a memory bottleneck.
Memory: Pages/sec: Measures how much paging the OS is performing. A high number may indicate a potential memory bottleneck.
Processor: % Processor Time: _Total: Measures the percentage of available CPUs in the computer that are busy. Generally speaking, if this number exceeds 80% for long periods of time, this may be an indication of a CPU bottleneck.
System: Processor Queue Length: If the CPUs get very busy, then CPU requests have to be queued, waiting their turn to execute. The longer the queue, the more likely there is a CPU bottleneck.
SQL Server Buffer Manager Object: Cache Hit Ratio: Percentage of pages that were found in the buffer pool without having to incur a read from disk. Higher the value good.

To measure processor utilization and context switching, you can use the following counters:
     1. Processor\% Processor Time
Threshold: The general figure for the threshold limit for processors is 65 percent.
Significance: This counter is the primary indicator of processor activity. High values many not necessarily be bad. However, if the other processor-related counters are increasing linearly such as % Privileged Time or Processor Queue Length, high CPU utilization may be worth investigating.
2. Processor\% Privileged Time
Threshold: A figure that is consistently over 75 percent indicates a bottleneck.
Significance: This counter indicates the percentage of time a thread runs in privileged mode. When your application calls operating system functions (for example to perform file or network I/O or to allocate memory), these operating system functions are executed in privileged mode.
     3. System\Context Switches/sec
Threshold: As a general rule, context switching rates of less than 5,000 per second per processor are not worth worrying about. If context switching rates exceed 15,000 per second per processor, then there is a constraint.


No comments: