For any good system three things are vital: CPU, Memory and IO (disk). Among these three, IO is the most crucial factor of SQL Server. Looking at real-world cases, I do not see IT people upgrading CPU and Memory frequently. However, the disk is often upgraded for either improving the space, speed or throughput. Today we will look at an IO-related wait types.

From Book On-Line:

Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.

IO_COMPLETION Explanation:

Any tasks are waiting for I/O to finish. This is a good indication that IO needs to be looked over here.

Reducing IO_COMPLETION wait:

When it is an issue concerning the IO, one should look at the following things related to IO subsystem:

• Proper placing of the files is very important. We should check the file system for proper placement of files – LDF and MDF on a separate drive, TempDB on another separate drive, hot spot tables on separate filegroup (and on separate disk),etc.
• Check the File Statistics and see if there is higher IO Read and IO Write Stall SQL SERVER – Get File Statistics Using fn_virtualfilestats.
• Check event log and error log for any errors or warnings related to IO.
• If you are using SAN (Storage Area Network), check the throughput of the SAN system as well as the configuration of the HBA Queue Depth. In one of my recent projects, the SAN was performing really badly so the SAN administrator did not accept it. After some investigations, he agreed to change the HBA Queue Depth on development (test environment) set up and as soon as we changed the HBA Queue Depth to quite a higher value, there was a sudden big improvement in the performance.
• It is very possible that there are no proper indexes in the system and there are lots of table scans and heap scans. Creating proper index can reduce the IO bandwidth considerably. If SQL Server can use appropriate cover index instead of clustered index, it can effectively reduce lots of CPU, Memory and IO (considering cover index has lesser columns than cluster table and all other; it depends upon the situation). You can refer to the two articles that I wrote; they are about how to optimize indexes:
  • Create Missing Indexes
  • Drop Unused Indexes
• Checking Memory Related Perfmon Counters
  • SQLServer: Memory Manager\Memory Grants Pending (Consistent higher value than 0-2)
  • SQLServer: Memory Manager\Memory Grants Outstanding (Consistent higher value, Benchmark)
  • SQLServer: Buffer Manager\Buffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)
  • SQLServer: Buffer Manager\Page Life Expectancy (Consistent lower value than 300 seconds)
  • Memory: Available Mbytes (Information only)
  • Memory: Page Faults/sec (Benchmark only)
  • Memory: Pages/sec (Benchmark only)
• Checking Disk Related Perfmon Counters
  • Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
  • Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
  • Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussions of Wait Stats in this blog are generic and vary from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Wait Types, SQL White Papers, T SQL, Technology