When you suspect that your computer hardware is the primary reason for the performance impact of SQL Server running, you can monitor the load of the appropriate hardware through SQL Server Performance Monitor to confirm your guesses and identify system bottlenecks. Some of the commonly used analysis objects and their parameters are described below.
Memory:page faults/sec
If the value is occasionally higher, it indicates that the thread was competing for memory. If it continues to be high, memory may be a bottleneck.
Process:working Set
This parameter of SQL Server should be very close to the memory value assigned to SQL Server. In SQL Server Setup, if set working set size is placed to 0, Windows NT determines the size of the working set for SQL Server. If set working set size is placed to 1, the working set is forced to be the allocated memory size of SQL Server. In general, it is best not to change the default value of set working set size.
Process:%processor time
If the value of this parameter persists over 95%, the bottleneck is the CPU. Consider adding a processor or switching to a faster processor.
Processor:%privileged time
If the value of the parameter and the "physical Disk" parameter values are high, it indicates that I/O is problematic. Consider replacing a faster hard drive system. Setting up tempdb in RAM, reducing max async io, and "Max lazy writer IO" will reduce the value.
Processor:%user time
Represents CPU-consuming database operations, such as sorting, executing aggregate functions, and so on. If the value is high, consider adding an index to reduce the value by using simple table joins, and horizontally dividing the large table.
Physical Disk:Avg.Disk Queue Length
This value should be no more than 1.5~2 times the number of disks. To improve performance, you can increase the disk.
Note: A RAID disk actually has more than one disk.
Sqlserver:cache Hit Ratio
The higher the value, the better. If you continue below 80%, you should consider adding more memory.
Note the value of this parameter is cumulative since the start of SQL Server, so the value will not reflect the current value of the system after a period of time.
