Perfmon metrics for SQL Server performance optimization

Perfmon is Windows System Performance Monitor. Used to monitor CPU usage, memory usage, hard disk read and write speed, network speed, and more.

Processor/%privileged time
Threshold: A bottleneck is indicated if the value continues to be greater than 75%.
Meaning: This counter represents the percentage of time that a thread uses in privileged mode. When your program calls the operating system's methods (such as file manipulation, network I/O, or allocating memory), the methods of these operating systems are run in privileged mode.

processor/%usertime
This is the opposite of the%privileged time counter, which refers to the percentage of times spent in user state mode (that is, non-privileged mode) operations.

Process (sqlservr.exe)/%processor time
CPU usage by SQL Server


processor/% Interrupt Time
Threshold: Depends on processor
Meaning: This counter indicates the percentage of time that the processor is receiving processing hardware interrupts. This value indirectly indicates the activity of the hardware device that generated the interruption, such as network changes. The significant increase in this counter indicates that there may be a problem with the hardware.

system/processor Queue Length
Threshold: Average duration greater than 2 indicates a CPU bottleneck
Meaning: If the ready task exceeds the processing power, the thread is put into the queue. The processor queue is a collection of threads that are ready but failed to be executed by the processor because another thread is executing the state. Continuous or repeated occurrence of more than 2 queues is a clear indication of the presence of a processor bottleneck. You can also achieve greater throughput by reducing concurrency.
You can combine processor/% Processor time to decide whether your program will benefit if you increase the CPU. Even on multiprocessor computers, the CPU time is a single queue. Therefore, on multiprocessor computers, the value of Processor Queue Length (PQL) is divided by the number of CPUs used to handle the load.
If the CPU is very busy (more than 90% usage), the average value of the PQL also continues to be greater than 2/CPU, which is a CPU bottleneck and can benefit from more CPUs. Alternatively, you can reduce the number of threads and increase the queue for the application tier. This can cause a small amount of context switching, but a little context switching is good for reducing CPU load. The common cause of pql greater than 2 but not high CPU usage is that requests for CPU time arrive randomly and threads request from the processor to the asymmetric CPU time. This means that the processor is not a bottleneck, and your threading logic needs to be improved.

Sqlserver:sqlStatistics/auto-param attempts/sec
The number of automatic parameterization attempts per second. The total number should be the sum of failed, secure, and unsafe automatic parameterization attempts. When an instance of SQL Server attempts to parameterize a Transact-SQL request by replacing some text with a parameter, it is automatically parameterized so that the execution plan saved in the cache can be reused for multiple similar requests. Note that in newer versions of SQL Server, automatic parameterization is also known as simple parameterization. This counter does not include forced parameterization.

Sqlserver:sqlstatistics/failed auto-params/sec
Number of failed automatic parameterization attempts per second. the value should be very small. Note that in later versions of SQL Server, automatic parameterization is also known as simple parameterization.

Sqlserver:sqlStatistics/batch requests/sec
Number of Transact- SQL command batches received per second. This statistic is affected by all constraints, such as I/O, number of users, cache size, complexity of the request, and so on. a high number of batch requests means good throughput.

sqlserver:sql statistics/sqlcompilations/sec
The number of SQL builds per second. Indicates the number of times the compiled code path was entered. Includes compilation caused by statement-level recompilation in SQL Server. When the SQL Server user activity is stable, the value will reach a stable state.

Sqlserver:sql statistics/sqlre-compilations/sec
the number of times per second the statement was recompiled. calculates the number of times the trigger statement is recompiled. Generally speaking, this value is preferably smaller.

Perfmon metrics for SQL Server performance optimization