When you suspect that computer hardware is the primary cause of SQL Server performance, you can monitor the load on the appropriate hardware through SQL Server Performance Monitor to verify 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 is competing for memory at that time. If it continues to be high, then memory can 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 settings, if you set set working set size to 0, Windows NT determines the size of the working set of SQL Server. If set working set size is placed at 1, the working set size is forced to 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 CPU. Consider adding a processor or swapping it for a faster one.
Processor:%privileged time
If the parameter value and the "physical Disk" parameter values are always high, there is a problem with I/O. Consider replacing a faster hard drive system. In addition, the tempdb in RAM is set to reduce the max async IO, and all measures such as Max lazy writer IO will decrease this value.
Processor:%user time
Represents CPU-intensive database operations, such as sorting, executing aggregate functions, and so on. If the value is high, consider increasing the index, using a simple table join, and horizontally splitting the large table to reduce the value.
Physical Disk:Avg.Disk Queue Length
The 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 it lasts below 80%, you should consider increasing the memory.
Note that the value of this parameter is incremented after starting SQL Server, so the value will not reflect the current value of the system after a period of time has elapsed.
http://www.bkjia.com/PHPjc/631093.html www.bkjia.com true http://www.bkjia.com/PHPjc/631093.html techarticle when you suspect that the computer hardware is the primary cause of the performance of SQL Server, you can monitor the load on the appropriate hardware through SQL Server Performance Monitor to verify your guess and identify the system ...
