Share via


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.

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

Bob Dorr - Principal Software Engineer SQL Server