Moved from: bobsql.com
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:
- Build message text for the SQL Server error log
- 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.
- Build message text for the SQL Server error log
- Start Timer
- Call WriteFile – Until the I/O operation completes the API does not return control to the calling thread
- Stop Timer
- 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/
