The following error messages are appearing within the SQL Server logs.

 SQL Server has encountered 99 occurrence(s) of IO requests taking longer than 15 seconds to complete on file H:\MSSQLSERVER\MSSQL$MYINST\Data\MyDB.mdf] 

in database [MyDB] (13).  The OS file handle is 0x000004BC.  The offset of the latest long IO is: 0x000000d83e0000

SQL Server uses asychronous IO  with the operating system ,meaning the SQL Server , utilises a type of IO that allows other processing to progress prior to transmission finishing. i.e releases the thread to allow futher threads. Usually, this  takes 10ms , but  under certain circumstances delays will occur.If the above error is reported possible reasons are:

I/O subsystem problems

I/O subsystem misconfiguration

Fragmentation.

Excessive I/O requested by SQL Server

Inefficient placement of data files on disk

 The asynchronous IO is managed through various APIs, involved in buffer management . And suitable for writing large amounts of data to large files. The method is similar for all the APIs , dealing with handles, memory,block sizes and sending instructions on how the kernel will handle IO.

The IO moves into kernel mode , in the process converting into a interrupt requests packet (IRP). The OS commonly packages most I/O requests to drivers using IRPs. The reason being they can be processed asynchronously,and useful for I/O that must use more than one driver.

 The IRP moves through :firewalls, virus scanners, filter drivers, device drivers , filesystem drivers and  to disk

 The challenge with this type of error  is to locate where the delay is occuring. Initially, try to investigate the relevant Perfmon counters, and establish if IO is being serviced efficiently by the disk. If it is , then begin to investigate the various filter drivers such as virus scanners. In the case of virus scanners – consider adding the SQL Server file types in the exclusion list, and turning off real time scanning.

Read More

SQL Server – Pageiolatch_sh and how to reduce it

SQL Server Latency

Powershell and SQL Error Logs

SQL Server – IO_COMPLETION and how to reduce it - SQL Server ...

IO_completion on disk solved with Wait Stats and Perfmon -