Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - IO Requests taking longer than 15 seconds to complete

06 November,2010 by Jack Vamvas

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 -


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer