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.
Source:Jack Vamvas (http://www.sqlserver-dba.com)
