SQL Server performance analysis parameters
Keywords: SQL Server
When you suspect that computer hardware is the main reason for the performance of SQL Server, you can monitor the load of the corresponding hardware via SQL Server Performance Monitor to confirm your guess and find the system bottleneck. Some commonly used analysis objects and its parameters will be described below. Memory: Page faults / sec If this value occasionally higher, it indicates that there is a thread competition memory. If the continuation is high, the memory may be a bottleneck. Process: The parameter of Working Set SQL Server should be very close to the memory value assigned to SQL Server. In SQL Server settings, if "SET WORKING SET SIZE" is set to 0, Windows NT will determine the size of the work set of SQL Server. If "SET WORKING SET SIZE" is set to 1, the force set size is the allocation memory size of SQLServer. 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 CPU. You can consider adding a processor or for a faster processor. Processor:% Privileged Time If the parameter value and "Physical Disk" parameter value have been high, I / O has problems. Consider replacing the faster hard disk system. In addition, TEMPDB IN RAM is set to reduce the value of "Max async IO", "Max Lazy Writer IO", and the value will be reduced. Processor:% User Time indicates a database operation of a CPU, such as sorting, executing aggregate functions. If this value is very high, consider adding an index, try to use a simple table connection, horizontal splitting large form, etc. to reduce this value. Physical Disk: avg.disk queue length This value should not exceed 1.5 to 2 times the number of disks. To improve performance, you can increase the disk. Note: A RAID DISK actually has multiple disks. SQLServer: Cache Hit Ratio This value is, better. If you continue less than 80%, you should consider adding memory. Note that the parameter value is started from SQL Server, and the number of times is accumulated, so the value will not reflect the current value of the system after a period of operation.
