Monitor your SQL server--let the bottleneck be exposed

Source: Internet
Author: User
Server when you suspect that computer hardware is the primary cause of the performance impact of SQL Server running, you can monitor the load of the appropriate hardware through SQL Server Performance Monitor to confirm 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 was competing for memory. If it continues to be high, memory may 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 Setup, if set working set size is placed to 0, Windows NT determines the size of the working set for SQL Server. If set working set size is placed to 1, the working set 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 value of this parameter persists over 95%, the bottleneck is the CPU. Consider adding a processor or switching to a faster processor.

Processor:%privileged time

If the value of the parameter and the "physical Disk" parameter values are high, it indicates that I/O is problematic. Consider replacing a faster hard drive system. Setting up tempdb in RAM, reducing max async io, and "Max lazy writer IO" will reduce the value.

Processor:%user time

Represents CPU-consuming database operations, such as sorting, executing aggregate functions, and so on. If the value is high, consider adding an index to reduce the value by using simple table joins, and horizontally dividing the large table.

Physical Disk:Avg.Disk Queue Length

This 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 you continue below 80%, you should consider adding more memory. Note the value of this parameter is cumulative since the start of SQL Server, so the value will not reflect the current value of the system after a period of time.



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.