Basic performance for SQL Server databases

It's been a long time since you've written, and DBAs typically have to test server performance before the system is officially online.

For example, you have a lot of servers, some do Web server, some do cache server, some do file server, some do database server

Database server to do the server performance is relatively good, disk, memory, CPU and so on,

Then you need to test the performance of each server before choosing one of the servers as the database server

And you need to set some hardware parameters, such as setting the disk controller parameters, reference article: Writeback and Writethrough difference

So what exactly is the test? How do we get the test metrics?

You can refer to this article: SQL Server Database Engine Performance Tuning Basics

Body

As the market share of SQL Server evolves over time, there is a growing demand for performance tuning for SQL Servers.

Different teams and individuals are using a variety of methods to improve the performance of SQL Server servers,

And I think these basic steps for documenting SQL Server troubleshooting and documents that improve the performance of various programs are meaningful to the SQL Server community

Disk

For SQL Server to function effectively, monitoring and optimizing the disk subsystem of SQL Server is an important aspect

We need to be very specific about the performance requirements of the disk

AVG. Disk Sec/read This counter is the average of the data read from disk per second

The following list shows the range of this counter value and indicates what the range of this counter means

Less than MS- very good between 10-20 MS- can also be between 20-50 ms- slow, requiring attention greater than ms– severe I/O bottlenecks

Disk Performance Test Tool

(1) CrystalDiskMark

(2) Hdtune HDD detection and Repair Tool

(3) ATTO Disk Benchmark

Identify I/O bottlenecks

PhysicalDisk Object:avg. Disk Queue: The average read-write request for the selected physical disk that was queued during sampling

If your disk queue length often exceeds twice times the peak of SQL Server disk usage, that means there may be an I/O bottleneck

Avg. Disk Sec/read: Average of data read from disk per second

Avg . Disk Sec/write: Average time to write data to disk,avg. disk Sec/read Reference index

physicalDisk:%disk Time disk is the percentage of time that the selected disk drive spends processing a read-write request, one metric is that if this value is greater than 50%, there is an I/O bottleneck

Avg. Disk reads/sec: The ratio of read operations on disk. Make sure that this number is less than 85% of the disk throughput. When this value exceeds 85% disk access time will grow exponentially

AVG. Disk writes/sec C: The rate of write operations on disk. Make sure that this number is less than 85% of the disk throughput. When this value exceeds 85% disk access time will grow exponentially

For more information, you can refer to "How to create a performance counter set": http://technet.microsoft.com/en-us/library/cc722148.aspx