SQL Server performance analysis example .txt

SQL Server Performance Analysis
 
　
SQL Server performance analysis Author: tofu chinaasp
If you suspect that computer hardware is the main cause of affecting the performance of SQL Server, You can monitor the load of the corresponding hardware through SQL server performance monitor to confirm your guesses and identify system bottlenecks. The following describes some common analysis objects and their parameters.
Memory: Page faults/sec
If this value increases occasionally, it indicates that there were threads competing for memory. If it continues 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 SQL Server settings, if "set working set size" is set to 0, Windows NT determines the working set size of SQL Server. If you set "set working set size" to 1, the size of the work set is the size of memory allocated by sqlserver. 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 continues to exceed 95%, the bottleneck is the CPU. You can consider adding a processor or changing a faster processor.
Processor: % privileged time
If the value of this parameter and the value of physical disk remain high, it indicates that I/O is faulty. Consider replacing a faster hard drive system. In addition, you can set tempdb in Ram to reduce "Max async Io" and "Max lazy writer Io.
Processor: % USER time
Indicates CPU-consuming database operations, such as sorting and executing Aggregate functions. If the value is very high, you can consider increasing the index and try to reduce the value by using simple table join and horizontal table segmentation methods.
Physical Disk: avg. Disk Queue Length
This value should not exceed 1.5 of the number of disks ~ 2 times. To improve performance, you can add disks.
Note: A raid disk actually has multiple disks.
Sqlserver: cache hit ratio
The higher the value, the better. If the duration is lower than 80%, consider increasing the memory. Note that the value of this parameter is accumulated after SQL Server is started. Therefore, after running for a period of time, this value cannot reflect the current value of the system.