SQL Server Database Engine Performance Tuning Basics
Definition Description
https://blogs.msdn.microsoft.com/indrajit/2013/12/12/sql-server-database-engine-performance-tuning-basics/ As the market share of SQL Server grows over time, there has been a increasing demand for SQL Server performance tuning. There has been various approaches taken by different teams and individuals for improving the performance of the SQL Server, and I think it might be beneficial to the community to have the basic steps documented for troubleshooting and improving the SQL Server Performance for various applications. Disks: For SQL Server to perform optimally, monitoring and optimizing the SQL Server Disk sub-system is one of the important aspects. We have very specific disk performance requirements. Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. The following list shows ranges of possible values and what the ranges mean: Less than 10 ms – very good Between 10 – 20 ms – okay Between 20 – 50 ms – slow, needs attention Greater than 50 ms – Serious I/O bottleneck Identifying IO bottlenecks: You can use the following Performance Monitor counters (perfmon.exe) to correctly identify disk performance issues. PhysicalDisk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, you might have an I/O bottleneck. Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. The guidelines for the Avg. Disk Sec/Read values apply here. Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, there is an I/O bottleneck. Avg. Disk Reads/Sec is the rate of read operations on the disk. Ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity. Avg. Disk Writes/Sec is the rate of write operations on the disk. Ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
