Perfmon indexes for SQL Server Performance Optimization

Perfmon indexes for SQL Server Performance Optimization

Perfmon is a Windows system performance monitoring program. Monitors CPU usage, memory usage, hard disk read/write speed, and network speed.

Processor/% Privileged Time
Threshold Value: If the value exceeds 75%, the bottleneck exists.
Meaning: This counter indicates the time ratio used by a thread in privileged mode. When your program calls operating system methods (such as file operations, network I/O, or memory allocation), these operating system methods run in privileged mode.

Processor/% UserTime
The opposite of the % Privileged Time counter refers to the percentage of Time spent on operations in user State mode (that is, in non-Privileged mode.

Process(sqlservr.exe)/% Processor Time
CPU usage by SQLServer


Processor/% Interrupt Time
Threshold: Depends on the processor
Meaning: This counter indicates the time ratio used by the processor to receive and process hardware interruptions. This value indirectly indicates the activity of the hardware device that causes the interruption, such as network changes. If this counter is significantly increased, the hardware may be faulty.

System/Processor Queue Length
Threshold Value: if the average value is greater than 2, the CPU has a bottleneck.
Meaning: If the ready task exceeds the processing capability, the thread will be put into the queue. The set of threads in which the processor queue is ready but not executed by the processor. This is because another thread is in the running state. If more than two queues exist continuously or repeatedly, the processor bottleneck exists. You can also achieve higher throughput by reducing concurrency.
You can use Processor/% Processor Time to determine whether your program will benefit from CPU increase. Even on a multi-processor computer, the CPU time is also a single queue. Therefore, on a multi-Processor computer, the Processor Queue Length (PQL) value is divided by the number of CPUs used to process the load.
If the CPU usage is very busy (more than 90% of usage), the average PQL value will continue to be greater than 2/CPU, which should cause a CPU bottleneck and benefit from more CPUs. Alternatively, you can reduce the number of threads and increase the queue at the application layer. This will cause a small number of Context Switching, but a little Context Switching is good for reducing CPU load. A common reason for PQL being greater than 2 but with low CPU usage is that requests for CPU time arrive randomly and threads apply for Asymmetric CPU time from the processor. This means that the processor is not a bottleneck, and your thread logic needs to be improved.

SQLServer: SQL Statistics/Auto-Param Attempts/sec
The number of automatic parameterized attempts per second. The total number should be the sum of failed, safe, and insecure automatic parametric attempts. When an SQL Server instance attempts to parameterize A Transact-SQL request by replacing some text with parameters, automatic parameterization is performed, this allows you to re-use the execution plan stored in the cache for multiple similar requests. Note that in the updated version of SQL Server, automatic parameterization is also called simple parameterization. This counter does not include forced parameterization.

SQLServer: SQLStatistics/Failed Auto-params/sec
Number of failures in automatic parameterization attempts per second. The value should be small. Note that in SQL Server versions later, automatic parameterization is also called simple parameterization.

SQLServer: SQLStatistics/Batch Requests/sec
The number of batches of Transact-SQL commands received per second. This statistical information is affected by all constraints (such as I/O, number of users, cache size, and request complexity. A high number of batch processing requests means a good throughput.

SQLServer: SQL Statistics/SQLCompilations/sec
The number of SQL compilations per second. Indicates the number of times the compiled code path is entered. Including statement-level re-compilation in SQL Server. When SQL Server user activity is stable, this value is in the stable state.

SQLServer: SQL Statistics/SQLRe-Compilations/sec
The number of times the statement is recompiled per second. Calculates the number of times the trigger statement is re-compiled. Generally, this value should be smaller.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151203.htm