How to Reduce PREEMPTIVE_OS_WRITEFILEGATHER Wait Type in SQL Server?
I was getting wait type PREEMPTIVE_OS_WRITEFILEGATHER during database size allocation. Yesterday, I was configuring database size to the value that was suggested by the vendor. We need to set database size to 700GB keeping 150 GB to log files. I have successfully configured data files sizes in less than 2 minutes because we had already enabled Instant File Initialization. As we know Instant File Initialization (IFI) speeds up the data file size allocation by skipping the zero-initialize operation. But when i started extending log file sizes, it took lot of time. One reason for taking longer time might be because of wait type PREEMPTIVE_OS_WRITEFILEGATHER that was showing for the session id that was running to allocate log file space.
What is PREEMPTIVE_OS_WRITEFILEGATHER?
As per MSDN, PREEMPTIVE_OS_WRITEFILEGATHER is a wait type that occurs while SQL Server is waiting for the Operating System to complete portions of write operations. This wait is commonly seen in databases with auto-growth not configured appropriately or during large size allocation to database files. If you are getting this wait type frequently then you should investigate your SQL Server instance. You can also observe this wait type along with BACKUPBUFFER and LOGBUFFER wait types.
Sometimes, we get this wait type during database restore operation as well. As we know backup operation captures details like database file sizes irrespective of how much data or logs filled inside the data or log file. When you execute restore operation, it will create that much big file and it will take more time because SQL Server will be busy in zeroing out the file equivalent to source data or log file size. If you have enabled IFI on your database server then you will not be getting this wait type for data files but still you might get it for log file size allocation.
Suppose your database has 100GB of log file and only 12 GB is filled. If you take backup of this database and restore it anywhere then restore operation will create a log file of 100GB that will take lot much time because of zero initialized operation to be performed for all 100GB size.
Solutions to Reduce Wait Type PREEMPTIVE_OS_WRITEFILEGATHER
Microsoft has given a feature Instant File Initialization to overcome or reduce this wait type but this feature works for only data files not log files. We hope this feature will work on both data as well as log files in future releases of SQL Servers.
If you are getting this wait type during extending data files or during data file autogrowth then you can simply enable Instant File Initialization on your SQL Server database server and this wait type will disappear from next time. This feature will skip zero-initialization of data files and directly allocate given size to the data files. Read attached article to learn Instant File Initialization and how to enable it on your SQL Server Instance?
But we know this feature does not work on log files so we cannot use this feature to reduce this wait type if it occurs during extending log file or during log file autogrowth. We faced same issue because we had already enabled Instant File Initialization for data files but stuck during extending log files. Here I will give you few best practices that you can apply to avoid and reduce this wait type.
- Increase auto-growth from the default value to some numeric value to reduce the frequent auto-growth occurrence for your database file. Thumb rule is to keep autogrowth size one eight of the corresponding database-file size. Read this article to know the Perfect value for database file Autogrowth?
- If possible avoid auto-growth occurrence by allocating expected size grown in future to the database.
- Monitor Storage subsystems and Investigate if any issues identified. Upgrade Storage if required.
- Have a look at the Windows Event Viewer for any system related issues.
- Enable Instant File Initialization to avoid zero-initialization for data files.
- Consolidate transactions from row-by-row statements to batch processing.
- If you have bigger log file having small number of logs filled inside. You can check log file size using DBCC SQLPERF command given in attached article. You can shrink the log file and take the backup again to perform the restore. This should be a quick process rather than waiting for zeroing to complete for previous size of log file.
- Stop any activity to free-up the target disk’s header so that it can dedicatedly work on zeroing out the LDF file.
- How to Speed up Huge Data Load in SQL Server?
- Understanding ASYNC_IO_COMPLETION Wait Type
- Manage Transaction Log File during Data Load
- Understanding SQL Server Wait Types
- How to reduce RESOURCE_SEMAPHORE wait type?
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018