Cause of preemptive_os_createfile wait ! Delayed DB recovery

Question

Hola Folks, I am running a sql server 2016 which is on aag. I see preemptive_os_createfile wait after failover which is causing the delay in database recovery. I verified that sql server account is added to perform volume maintenance tasks policy. I verified avg disk sec/reads and disk sec/writes are good. Could you please let me know if there is any other factors that can cause this

Answer 1

Hi @lohith ks chalam ,

Quote from this blog PREEMPTIVE_OS_CREATEFILE

This wait type is when a thread is calling the Windows CreateFile function. Note that this API is also used to open files for reading, not just for creating files.

This wait type is usually not a problem unless the workload involves creating many files, for instance, from using FILESTREAM storage. There are many posts on the Internet about performance issues that can happen in Windows that could make calls to CreateFile slow, including SMB, authentication failures, slow I/O subsystem, NTFS having to flush its caches, and none of these are SQL Server issues. Note that this API is also used to open files for reading, not just for creating files.

How did you confirm this wait type cause SQL server database recovery delay? Did you mean that you have SQL AG latency issue? Did you find any useful information from your SQL server error log?

If this is a SQL AG latency issue, you can refer to below MS blog.

Common Causes and Troubleshooting Solutions for SQL AG Data Synchronization latency

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".