Introduction to Windows performance counters in SQL Server _mssql

Processer:

The%processor time processor is busy performing tasks on the <80% normal condition, <50% condition, <30% condition, continuous >80%, the CPU has bottlenecks or insufficient memory or low query plan reuse rate, not optimized statements and so on.
Sqlserver:buffer Manager
Buffer cache Hit ratio buffers to adjust the buffer hit rate, the valve value >97% normal condition, recommended >99%, if this item is less than 97% memory is not enough.
Checkpoint pages/sec SQL checkpoint, this value is determined by the lifetime of the page in the buffer pool and lazy write, and if the page has a short life cycle, the lazy writer runs frequently, thus running checkpoints frequently, increasing the cost of the resource. The longer the life cycle of all pages, the
The smaller the source consumption.
Page life expectancy, this lifetime is better, at least 300 seconds, if the cycle is too short, the page frequently from the hard disk and memory interaction, will increase a lot of physical I/O, waste resources.
Lazy writes/sec Lazy Write lazy writer will not write a period of time to the hard disk of dirty pages, batch write to the hard disk, reduce the frequently run checkpoints occupy resources.

SQL Server Plan Cache:

Cache Hit Ratio Buffer pool plan buffer hit rate, adjust the ratio of hit times and lookup times, that is, plan reuse rate, if less than 70%, reuse rate is low, will increase resource cost.

Parameters:
Extended Stored Procedures Extended stored procedure reuse rate, typically 100%
Object plans objects plan to create stored procedures, functions and triggers generated by the plan, the general situation in more than 90%, more than 95% best.
SQL plans SQL temporary statements plan SQL Server to automatically create temporary plans for temporary T-SQL statements and save it in a buffer pool, and if you have the same statement, call from the buffer pool without recompiling, reducing resource consumption.
Temporary tables & Table Variables cache information related to temporary tables and table variables, because of the low rate of reuse of temporary tables, the table variables cannot be used soon after, and this value is often low.

SQL Server Statistics:

Batch requests/sec batch request, receive SQL batch request per second, this value is determined by (I/O, user data, cache size, request complex program), the higher the number indicates the better the throughput.
SQL Compilations/sec SQL compile times per second, when the user reaches a stable state, the value should be stable, if not stable, is a large number of users, connectivity and disconnection, waste of resources.
SQL RE-COMPILATIONS/SEC the number of statements per second, in general, the smaller the value, the smaller, if the value is too large, indicating the reusability of the SQL statement is not good, please optimize the SQL statements, multiple recompile will increase the CPU burden.

System:

Context Switches/sec CPU threads, different users, different applications, to use the thread, if a large number of users use a large number of threads, then the thread needs frequent context switching, in general, the number of context switches per second, less than 5000X
Operating system physical processing number, if more than, then the CPU bottleneck state.
Processor Queue Length of the CPU processing task, in general, the number of 5X physical processors, recommended number of processors X2