Home > Article > Backend Development > Monitor your SQL SERVER--Exposing bottlenecks_PHP Tutorial
Monitor your SQL SERVER--Exposing bottlenecks_PHP Tutorial
- WBOYOriginal
- 2016-07-13 17:02:101105browse
When you suspect that computer hardware is the main reason affecting SQL Server performance, you can monitor the load of the corresponding hardware through SQL Server Performance Monitor to confirm your guess and identify system bottlenecks. The following will introduce some commonly used analysis objects and their parameters.
Memory: Page Faults / sec
If this value occasionally goes high, it indicates that there are threads competing for memory at that time. If it's consistently high, memory may be the bottleneck.
Process: Working Set
This parameter of SQL Server should be very close to the memory value allocated to SQL Server. In the SQL Server settings, if "set working set size" is set to 0, Windows NT will determine the size of the SQL Server working set. If "set working set size" is set to 1, the working set size 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 parameter value continues to exceed 95%, it indicates that the bottleneck is the CPU. Consider adding a processor or changing to a faster processor.
Processor:%Privileged Time
If this parameter value and the "Physical Disk" parameter value are always high, it indicates that there is an I/O problem. Consider replacing the hard drive system with a faster one. In addition, setting Tempdb in RAM, reducing "max async IO", "max lazy writer IO" and other measures will reduce this value.
Processor:%User Time
Indicates CPU-consuming database operations, such as sorting, executing aggregate functions, etc. If the value is very high, consider adding indexes, and try to use simple table joins, horizontal splitting of large tables, etc. to reduce the value.
Physical Disk:Avg.Disk Queue Length
This value should not exceed 1.5~2 times the number of disks. To improve performance, add disks.
Note: A Raid Disk actually has multiple disks.
SQLServer:Cache Hit Ratio
The higher the value, the better. If it continues to be below 80%, you should consider adding more memory.
Note that this parameter value has been accumulated since SQL Server started, so after running for a period of time, the value will not reflect the current value of the system.