Introduction to WINDOWS performance counters in sqlserver

Processer:

% Processor Time Processor is busy with task execution Time threshold <80% normal, <50% good, <30% excellent, sustained> 80%, CPU bottlenecks, insufficient memory, low query plan reuse rate, unoptimized statements, and so on.
SQLServer: Buffer Manager
Buffer cache hit ratio Adjust the Buffer hit rate. The threshold is greater than 97%. We recommend that you set it to 99%. If this threshold is lower than 97%, the memory is insufficient.
Checkpoint pages/sec SQL Checkpoint. This value is determined by the page lifecycle and inert write in the buffer pool. If the page lifecycle is short, the inert writer runs frequently, as a result, checkpoints are frequently run, increasing resource overhead. The longer the lifecycle of all pages
The smaller the source consumption is.
Page Life Expectancy Page lifecycle. The longer the lifecycle, the better. The minimum lifecycle is higher than 300 seconds. If the lifecycle is too short, the Page frequently interacts with the memory from the hard disk, it will increase a lot of physical I/O and waste resources.
Lazy Writes/sec Writes dirty pages not written to the hard disk for a period of time and Writes them to the hard disk in batches to reduce the resources occupied by frequent checkpoints.

SQLServer Plan Cache:

Cache Hit Ratio buffer pool plans the buffer Hit rate and adjusts the Ratio of buffer Hit times to the number of queries, that is, the plan reuse rate. If it is lower than 70%, the reuse rate is low, which increases resource overhead.

Parameters:
Extended Stored Procedures: Extended storage process reuse rate, typically 100%
Object Plans Object plan, create a stored procedure, and plan generated by functions and triggers. Generally, the plan is more than 90%, which is best if it is greater than 95%.
SQL Plans SQL temporary statement plan SQL SERVER automatically creates a temporary plan for a temporary T-SQL statement and saves it in the buffer pool. If the same statement exists, it is called from the buffer pool, no need to re-compile, reducing resource consumption.
Temporary Tables & Table Variables: cache information related to Temporary Tables and Table Variables. Due to the low Reuse Rate of Temporary Tables, the Table Variables cannot be used immediately after they are used. This value is usually low.

SQLServer SQL statistics:

Batch Requests/sec Batch Requests receive SQL Batch Requests per second, which is determined by (I/O, user data, high-speed cache size, complex request programs, the higher the value, the better the throughput.
SQL compilations/sec: the number of SQL compilations per second. When the user reaches the stable state, the value should be stable. If the value is unstable, it is a large number of users. Connection and disconnection are a waste of resources.
The number of times SQL Re-compilations/sec statements recompile per second. Generally, the smaller the value, the smaller the value. If the value is too large, it indicates that SQL statements are not reusable, optimize SQL statements. Multiple re-compilation will increase the CPU burden.

System:

Context Switches/sec Context switching of CPU threads. Different users and applications use threads. If a large number of users use a large number of threads, the thread needs to be frequently switched. Generally, the number of context switches per second is less than X.
The number of physical processes in the operating system. If the number exceeds, the CPU may experience a bottleneck.
Processor Queue Length refers to the Queue Length of the worker CPU processing task. Generally, the number of 5X physical processors is recommended, and the number of processors is X2.