Performance indicators of SQL Server

1. I/O

Counter: physicaldisk: avg. Disk Queue Length

Description: avg. Disk queue length refers to the average number of Read and Write requests (queued for the selected disk in the instance interval.

Bottleneck: If the queue length is greater than 2 during peak hours, the I/O bottleneck may exist.

　　

Counter: physicaldisk: avg. Disk SEC/read

AVG. Disk SEC/Write

Note: avg. Disk SEC/read refers to the average time required to read data on this disk in seconds.

AVG. Disk SEC/write indicates the average time required to write data to this disk in seconds.

Bottleneck: a bottleneck may occur when the average read/write time exceeds 20 ms.

Counter: physicaldisk: disk reads/sec

Disk writes/sec

Description: The number of reads and writes to the disk per second. Check the specified transfer speed of the disk to verify that the speed does not exceed the specification.
Generally, Ultra Wide SCSI disks can process 50 to 70 I/O operations per second.
Note that whether I/O is sequential or random, the read/write rate per second of the disk will be greatly affected.

Bottleneck: when the number of reads and writes per second exceeds 58, the I/O bottleneck occurs.

2. Memory

Counter: Paging file: % usage

Note: displays the number of page file examples in use in percentages. See Process \ page file bytes.

Bottleneck: over 70%

3. CPU

Counter: SQL server: SQL statistics: Batch requests/sec

Description: Number of SQL batch requests received by the server.

Bottleneck:

Counter: SQL server: SQL statistics: SQL compilations/sec

Description: Number of compiled SQL statements.

Bottleneck:

Counter: SQL server: SQL statistics: SQL recompilations/sec

Description: Number of SQL recompilation tasks.

Bottleneck: