How It Works: Sync IOs in nonpreemptive mode longer than 1000 ms

Have you encountered the following in your SQL Server error log?

      Long Sync IO: Scheduler 95 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms

There are two general types of I/O performed by SQL Server.

• Async – Vast majority of SQL Server I/Os, as outlined in the provided link: https://technet.microsoft.com/en-us/library/aa175396(v=sql.80).aspx
• Sync 

The message I am blogging on today is of the Sync variety.    Simply stated the thread waits for the I/O to complete.  For example:

1. Build message text for the SQL Server error log
2. Call WriteFile  -  Until the I/O operation completes the API does not return control to the calling thread

Basically, what SQL Server does is wrap the sync I/O call (WriteFile, ReadFile, FlushFileBuffers, etc.) with a timer.

1. Build message text for the SQL Server error log
2. Start Timer
3. Call WriteFile  -  Until the I/O operation completes the API does not return control to the calling thread
4. Stop Timer
5. If Elapsed Time > 1000 ms and the worker is in non-preemptive mode – Report I/O stall problem

What this means is the SQL Server thread (worker), that owns the scheduler, performed the API call that took longer than 1000 ms.  Since only a single worker can own the scheduler at any given point in time this means the scheduler encountered a stall condition.   The target for a SQL Server scheduler context switch is around 4 ms so 1000 ms could have an impact on SQL Server processing.

SQL Server is telling you about an I/O bottleneck that you should address.   You should follow the same troubleshooting steps as outlined for async I/O stall reports in order to resolve the situation.

• https://blogs.msdn.microsoft.com/psssql/2008/03/03/how-it-works-debugging-sql-server-stalled-or-stuck-io-problems-root-cause/
• https://blogs.msdn.microsoft.com/psssql/2010/03/24/how-it-works-bob-dorrs-sql-server-io-presentation/

Bob Dorr - Principal Software Engineer SQL Server