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.
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.
