My user database MDF file resides on separated disk. There is nothing else on the disk than this MDF file. I can see average write stalls higher then 20ms on the file using appropriate DMV. I assume that writes are only made assynchroniously by checkpoints or lazy writer.

I doubt whether there is any direct negative impact on the workload performance because of these write stalls? Or is there any good reason we should try to increase write performance of the disk?

  • Did you add the secondary MDF file later per chance after the first one was already filling data? It's unlikely, but the algo used to fill files is 'round robin with proportional fill'. It will write all data to the new files until they are the same size as the others. – Ali Razeghi Feb 26 '19 at 18:28

The occasional disk write of 20ms or more on a volume hosting a database file is not normally a big deal.

The server-level Recovery Interval, or the database-level Target Recovery Time (aka Indirect Checkpoints, and the default for SQL 2016+) can trigger aggressive flushing, which can increase the total write IO cost of a workload by eliminating the write-coalescing behavior of the page cache.

If you set the Target Recovery Time and the "recovery writer" starts to fall behind, it will hijack your schedulers to help it catch up. This will create "backpressure" to reduce your workload throughput to maintain your Target Recovery Time. See, eg:

" If recovery writer starts falling behind resulting into long DPLists, the individual worker threads running on the scheduler starts playing the role of recovery writer to collect, sort and post write for dirty pages. This allows recovery writer to scale dynamically on heavy OLTP systems to ensure target_recovery_time interval set is met all the time. While multiple recovery writer routine running aggressively allows us to meet the target_recovery_time for predictable recovery, the overall throughput of workload may degrade under these circumstances due to additional overhead caused from each running worker performing recovery writer activity."

Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler

Also if your workload requires data file reads, these will contend with the writes for IO.

  • But what if it is not occasional but in average above 20 ms? – jericzech Feb 27 '19 at 9:57
  • Unless you see IO waits or the data file disks are consistently at 100% disk time, making the data disk faster is probably not going to have a big impact on your transaction time or total throughput. – David Browne - Microsoft Feb 27 '19 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.