SQL Server performance analysis example .txt

Source: Internet
Author: User
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.
 
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.