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

Published Feb 24 2022 07:01 AM 933 Views
Microsoft

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:

  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.

 

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

 

%3CLINGO-SUB%20id%3D%22lingo-sub-3209756%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20Sync%20IOs%20in%20nonpreemptive%20mode%20longer%20than%201000%20ms%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3209756%22%20slang%3D%22en-US%22%3E%3CP%3EMoved%20from%3A%20bobsql.com%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20encountered%20the%20following%20in%20your%20SQL%20Server%20error%20log%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23c0504d%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Long%20Sync%20IO%3A%20Scheduler%2095%20had%201%20Sync%20IOs%20in%20nonpreemptive%20mode%20longer%20than%201000%20ms%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20two%20general%20types%20of%20I%2FO%20performed%20by%20SQL%20Server.%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAsync%20%E2%80%93%20Vast%20majority%20of%20SQL%20Server%20I%2FOs%2C%20as%20outlined%20in%20the%20provided%20link%3A%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Faa175396(v%3Dsql.80).aspx%22%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Faa175396(v%3Dsql.80).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Faa175396(v%3Dsql.80).aspx%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3ESync%3C%2FSTRONG%3E%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSTRONG%3EThe%20message%20I%20am%20blogging%20on%20today%20is%20of%20the%20Sync%20variety.%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3B%20Simply%20stated%20the%20thread%20waits%20for%20the%20I%2FO%20to%20complete.%26nbsp%3B%20For%20example%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EBuild%20message%20text%20for%20the%20SQL%20Server%20error%20log%3C%2FLI%3E%0A%3CLI%3ECall%20WriteFile%26nbsp%3B%20%E2%80%93%26nbsp%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3E%3CSTRONG%3EUntil%20the%20I%2FO%20operation%20completes%20the%20API%20does%20not%20return%20control%20to%20the%20calling%20thread%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EBasically%2C%20what%20SQL%20Server%20does%20is%20wrap%20the%20sync%20I%2FO%20call%20(WriteFile%2C%20ReadFile%2C%20FlushFileBuffers%2C%20etc.)%20with%20a%20timer.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EBuild%20message%20text%20for%20the%20SQL%20Server%20error%20log%3C%2FLI%3E%0A%3CLI%3E%3CEM%3EStart%20Timer%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3ECall%20WriteFile%26nbsp%3B%20%E2%80%93%26nbsp%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3E%3CSTRONG%3EUntil%20the%20I%2FO%20operation%20completes%20the%20API%20does%20not%20return%20control%20to%20the%20calling%20thread%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3EStop%20Timer%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20color%3D%22%23c0504d%22%3EIf%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EElapsed%20Time%20%26gt%3B%201000%20ms%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%20the%20worker%20is%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Enon-preemptive%20mode%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%E2%80%93%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EReport%20I%2FO%20stall%20problem%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CLI-WRAPPER%3E%3C%2FLI-WRAPPER%3E%3C%2FP%3E%0A%3CP%3EWhat%20this%20means%20is%20the%20SQL%20Server%20thread%20(worker)%2C%20that%20owns%20the%20scheduler%2C%20performed%20the%20API%20call%20that%20took%20longer%20than%201000%20ms.%26nbsp%3B%20Since%20only%20a%20single%20worker%20can%20own%20the%20scheduler%20at%20any%20given%20point%20in%20time%20this%20means%20the%20scheduler%20encountered%20a%20stall%20condition.%26nbsp%3B%26nbsp%3B%20The%20target%20for%20a%20SQL%20Server%20scheduler%20context%20switch%20is%20around%204%20ms%20so%201000%20ms%20could%20have%20an%20impact%20on%20SQL%20Server%20processing.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%20is%20telling%20you%20about%20an%20I%2FO%20bottleneck%20that%20you%20should%20address.%26nbsp%3B%26nbsp%3B%20You%20should%20follow%20the%20same%20troubleshooting%20steps%20as%20outlined%20for%20async%20I%2FO%20stall%20reports%20in%20order%20to%20resolve%20the%20situation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20title%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fpsssql%2F2008%2F03%2F03%2Fhow-it-works-debugging-sql-server-stalled-or-stuck-io-problems-root-cause%2F%22%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fpsssql%2F2008%2F03%2F03%2Fhow-it-works-debugging-sql-server-stalled-or-stuck-io-problems-root-cause%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblogs.msdn.microsoft.com%2Fpsssql%2F2008%2F03%2F03%2Fhow-it-works-debugging-sql-server-stalled-or-stuck-io-problems-root-cause%2F%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20title%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fpsssql%2F2010%2F03%2F24%2Fhow-it-works-bob-dorrs-sql-server-io-presentation%2F%22%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fpsssql%2F2010%2F03%2F24%2Fhow-it-works-bob-dorrs-sql-server-io-presentation%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblogs.msdn.microsoft.com%2Fpsssql%2F2010%2F03%2F24%2Fhow-it-works-bob-dorrs-sql-server-io-presentation%2F%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3209756%22%20slang%3D%22en-US%22%3E%3CH3%20class%3D%22single-title%22%20id%3D%22toc-hId--1409506191%22%3EHow%20It%20Works%3A%20Sync%20IOs%20in%20nonpreemptive%20mode%20longer%20than%201000%20ms%3C%2FH3%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3209756%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBobSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 24 2022 07:01 AM
Updated by: