However, when the amount of transaction data sent in large quantities increases, "occurrence(s)" will appear in the SQL Server record file from time to time. ) of I/O requests taking longer than 15 seconds to complete on file [X:\XXX\XXX.mdf] in database id XX. The OS file handle is 0x0000000000001AF0. The offset of the latest long I/O is: 0x00000a9ef02000 .
The duration of the long I/O is: It takes longer to enter the database, and even causes timeout errors when the platform executes SQL syntax. Research-on-the-Causes-of-IO-Delay-in-SQL-Server-01 In order to find out the cause of this warning message, we bot dataset repeatedly queried the preceding and following messages in the SQL Server log file and found that "FlushCache: cleaned up XXXXXX bufs with XXXXXX" would appear several times before the I/O write delay exceeded 15 seconds. writes in XXXXXX ms (avoided XXXXX new dirty bufs) for db XX:0" (see Figure 2 below).
The correlation is that the occurrence of message 2 may not necessarily cause the occurrence of message 1, but the occurrence of message 1 must have a message 2 appears. After a period of observation, we found that message 2 usually occurs during database maintenance work - index rebuilding or when a large amount of transaction data is written. Research-on-the-Causes-of-IO-Delay-in-SQL-Server-01 Then I searched for FlushCache’s documentation (Ref 1, Ref 2) on the Internet and came to the following conclusions: