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 another IO-related wait type.

From Book On-Line:

Occurs when a task is waiting for I/Os to finish.

ASYNC_IO_COMPLETION Explanation:

Any tasks are waiting for I/O to finish. If by any means your application that’s connected to SQL Server is processing the data very slowly, this type of wait can occur. Several long-running database operations like BACKUP, CREATE DATABASE, ALTER DATABASE or other operations can also create this wait type.

Reducing ASYNC_IO_COMPLETION wait:

When it is an issue related to IO, one should check for the following things associated to IO subsystem:

• Look at the programming and see if there is any application code which processes the data slowly (like inefficient loop, etc.). Note that it should be re-written to avoid this  wait type.
• Proper placing of the files is very important. We should check the file system for proper placement of the files – LDF and MDF on 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 a 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 configuration of the HBA Queue Depth. In one of my recent projects, the SAN was performing really badly and so the SAN administrator did not accept it. After some investigations, he agreed to change the HBA Queue Depth on the development setup (test environment). 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 likely to happen that there are no proper indexes on the system and yet 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 following two articles I wrote that talk 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)

Read all the post in the Wait Types and Queue series.

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 Stats, SQL Wait Types, T SQL, Technology