SQL Server performance counters

CPU:
1. System: % Total processor time average CPU usage (you can also treat a program such as sqlserver process % processor time)
2. System: % processor queue length number of threads waiting for processing by the processor (the normal range is 1 ~ 3 times)
3. Processor: % USER time occupied by non-kernel-level applications (such as sqlserver or anti-virus software)
4. Processor: % interrupts/sec processor interruptions per second (sustained over 1000 indicates a problem, which may be caused by poor driver, hardware overuse, or hardware inventory problems)

Disk (physical disk ):
Queue Length counter
1.% avg. Disk queue length trace the number of requests waiting for the disk and the number of requests obtaining the service in the queue during the sampling period
2.% current disk queue length: Number of requests waiting for and obtaining services
Throughput counter
3.% disk Bytes/sec is an important indicator of Disk Throughput.
4.% disk read Bytes/sec
5.% disk write Bytes/sec
Utilization counter
6.% disk Time
7.% disk read time and % disk write time percentage of time the disk drive was used to process read/write requests
8.% idle time percentage of time when the disk system does not process requests and no requests in the queue
9. Disk transfers/sec disk reads/sec disk writes/sec
Page counters
10. SQL SERVER: Buffer Manager: cache hit ratio
11. SQL SERVER: Buffer Manager: Page life expectancy
12. SQL SERVER: Buffer Manager: Checkpoint pages/sec
13. SQL SERVER: Buffer Manager: lazy writes/sec
14. Memory: page/sec
15. Memory: Page reads/sec page writes/sec
Others
16.% avg. Disk SEC/Transfer Report data transmission speed (in seconds), measure the average time of each transmission (all round-trip time)
17.% avg. Disk SEC/read % avg. Disk SEC/Write report speed of data reading/writing from disk (in seconds)
18.% avg. Disk time report the percentage of time the selected disk drive was used to process read and write requests
19.% avg. Disk Bytes/transfer measure input/output operations (avg. Disk Bytes/read avg. Disk Bytes/write)
(Disk bottleneck: High Disk activity, persistent disk queue length, and a large number of page exchanges)

Memory:
1. How much memory is available for available Mbytes (the zeroed/free/standby space in the memory list is added together)
2. Pages/sec must be read from the disk or written to the disk to free up space for other pages.
3. Process: % working set the amount of submitted memory allocated to the corresponding process. It may include the shared memory and private content currently in the physical memory.
4. SQL SERVER: Buffer Manager: cache hit ratio cache hit rate
5. SQL SERVER: Buffer Manager: Total pages total pages of the cache pool
6. SQL SERVER: Buffer Manager: Total server memory (KB)
Specifies the memory used by the sqlserver server. Target Server
Memory (Kb) indicates the amount of memory required for SQL Server to run efficiently. It is based on the number of buffer zones reserved by SQL Server.

Certificate ----------------------------------------------------------------------------------------------------------------------------------

Parsing of % disk time exceeds 100%-zee

Symptom:

On LR, we can see that % disk time exceeds 100%.
If the machine is busy, the value may exceed 100%, for example, copying a large number of files.

Cause:

This problem occurs because the processor allows the operating system to use overlapping I/O. The disk performance counter uses a 100-nanosecond precision counter to measure the disk Time, And then displays the data according to the sampling frequency.
The subsequent time may exceed 100%. For example, 10 requests are generated within 2 ms, and the sampling time is 10 ms. if it is a raid disk, the operating system can read and write to multiple disks, resulting in overlap.
I/O, this will make % disk time greater than 100%.

Actually:

To measure the disk Time and latency, you can use the following counters. Generally, if the value of one of the following Counters is greater than 20 milliseconds, the disk is overloaded.
• Avg. Disk SEC/read
• Avg. Disk SEC/Write
• Avg. Disk SEC/Transfer