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

Question

in SQL 2014 SP3 i got quite a few "Long Sync IO: Scheduler x had 1 Sync IOs in nonpreemptive mode longer than 1000 ms". what is the size for the sync IO, is it 8K as per standard or varies based on data read/transaction write/or lazy writes or checkpoint activities?

Thank you,

Answer 1

Hi @rodrag ,

...My question was be which are the activities that generate syncIOs AND what are the max size values.

Activitiese: Page Reads/Page Writes/Lazy Writes/Checkpoints/Log Writes...

Max Size: No absolut values, based your situation.

For different types of I/O, SQL Server has a small amount of difference in the actual I/O size each time. For example, for table index queries, most of them use 8KB as the unit, while for some whole table queries, they may use 64KB or even larger units. As for when and how much I/O will be used, there is no absolute value.

Reference information: I/O Request Size,SQL Server Best Practices Article,how-to-use-the-sqliosim-utility-to-simulate-sql-server-activity-on-a-d

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

If memory serves, log writes can be up to 60 K. In any case, this message suggests that your I/O subsystem is not up to par. 1000 ms is quite long time for a disk write.

See also https://bobsql.com/how-it-works-sync-ios-in-nonpreemptive-mode-longer-than-1000-ms/

Answer 3

See this document for various SQL Server IO patterns. Below are some of my old notes gleaned from this and various other sources. Actual IO sizes may differ by SQL version and edition (e.g. I've seen 4MB read-ahead IO in EE). For a demanding workload, I proactively run diskspd tests for the IO patterns I expect to determine actual IO subsystem capabilities as part of a new server commissioning process, before installing SQL Server.

Data files:

• 8K Random read (index seek, key lookup, scan of fragmented table, file stream T-SQL access, sparse file reads, buffer pool extension reads)
• 64K Random read (read-ahead scan of fragmented table, T-SQL blob reads, buffer pool extension reads)
• 128K Random read (read-ahead scan of fragmented table, T-SQL blob reads, buffer pool extension reads)
• 256K Random read (T-SQL blob reads)
• 512K Random read with 32 outstanding IOs per thread (T-SQL blob reads)
• 8K Sequential read (non-read-ahead scan, columnstore reads)
• 64K Sequential read (read-ahead, columnstore reads)
• 128K Sequential read (read-ahead, columnstore reads)
• 256K Sequential read (read-ahead, columnstore reads)
• 512K Sequential read (columnstore reads)
• 1M Sequential read (columnstore reads)
• 2M Sequential read (columnstore reads)
• 4M Sequential read (backup/restore, columnstore reads)
• 8M Sequential read (columnstore reads)
• 16M Sequential read (columnstore reads)
• 8K Random write (lazywriter, checkpoint, eager writes, buffer pool extension writes)
• 64K Random write (lazywriter, checkpoint, eager writes, buffer pool extension writes)
• 128K Random write (lazywriter, checkpoint, eager writes, buffer pool extension writes)
• 256K Random write
• 512K Random write
• 8K Sequential write (columnstore, database initialization)
• 64K Sequential write (columnstore, database initialization)
• 128K Sequential write (columnstore, database initialization)
• 256K Sequential write (columnstore, database initialization)
• 512K Sequential write (columnstore, database initialization)
• 1M Sequential write (columnstore, database initialization)
• 2M Sequential write (columnstore, database initialization)
• 4M Sequential write (backup/restore, columnstore, database initialization)
• 8M Sequential write (columnstore, database initialization)
• 16M Sequential write (columnstore)

Log files:

• 512 bytes to 60K Sequential writes
• 512 bytes to 480K Sequential reads

Answer 4

Hi @rodrag ,

what is the size for the sync IO, is it 8K as per standard or varies based on data read/transaction write/or lazy writes or checkpoint activities?

Based on read/transaction write or layzy writes or checkpoint activities.

Firstly, let's summarize the main disk read and write behavior of SQL Server.

For SELECT operations, as long as the data is cached in memory, there should not be any hard disk reads and writes (ideally)

SQL Server is basically a read-only data file. The amount of reading is directly related to the cache capacity of the memory, and it is also related to the amount of data that users need to read.

The writing of SQL Server is divided into two parts, the data file and the log file. The number of data file writes is mainly related to the amount of data modification, and Lazy Writer is also related to whether the memory is under pressure. The number of log files written is completely determined by the amount of data modification, and has nothing to do with whether the memory is under pressure. You can roughly think that the log file is Write Through.

So in a more general sense, the number of reads in SQL Server is directly related to whether the memory is under pressure. The less the memory is under pressure, the less the number of reads will be (note that it is not writing, this is very important, and many people have such a misunderstanding). The number of writes in SQL Server is directly related to the amount of data modification that the user has completed. If most operations of a database are queries, the write operations of SQL Server will be very few. This has nothing to do with memory pressure.

Next, let us take a look at what factors affect the behavior of SQL Server I/O operations:

1.SQL Server Recovery Interval (sp_configure)

Recovery Interval controls how often SQL Server performs a checkpoint. With the same amount of modification, if SQL Server frequently does checkpoints, the hard disk writes generated by each checkpoint will not be too much, and the impact on the hard disk will not be too great. If checkpoint is done only after a relatively long period of time, when the checkpoint is not done, SQL Server does not have hard disk writes, and the performance may be faster. But when doing Checkpoint, because a large number of changes have been accumulated, a large number of writes may be generated. At this point in time, the performance of SQL Server may be affected. So extending the checkpoint interval is not necessarily a good thing. On most SQL Servers, the default setting is better, and there is no need to modify it.

2.Automatic growth and automatic shrinkage of data/log files

The growth and shrinkage of the file will have a serious impact on the speed of SQL Server reading and writing to this file. Therefore, for a production database, automatic growth and automatic shrinkage should be avoided.

3.The degree of page fragmentation in the data file

SQL Server's operations on data are based on pages as the smallest unit. If the page is fragmented and each page does not store much data, then SQL Server may need to read and write many more pages. This will cause additional hard disk reads and writes.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Hi @rodrag ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.