The other day I was running a script I’d written to expand out some tables in the AdventureWorks sample database for some testing. The script was taking a long time, and at first I thought I was just spoiled because I usually run my tests on my laptop, whose storage is backed by SSDs. In this case I was running my test against a SQL Server instance up in AWS.
After a delicious fifteen minute coffee break, I realized something was wrong– it STILL wasn’t done. I grabbed sp_BlitzFirst® to do a quick check and see if my storage was really THAT slow.
I ran sp_BlitzFirst® with a 60 second sample, like this:
|
1 2 |
exec sp_BlitzFirst @seconds=60, @ExpertMode=1; GO |
Here’s what I saw (click for a larger version):
PREEMPTIVE_OS_WRITEFILEGATHER means slow file growths
In a 60 second sample, I’d been waiting for PREEMPTIVE_OS_WRITEFILEGATHER for 36 seconds. Whoops!
The script I was running was making tables larger. The database was out of room, and was growing the data file in small autogrowth increments. Each of these automatic growths was stopping to “zero out” the files, because I hadn’t enabled Instant File Initialization in SQL Server.
How I enabled Instant File Initialization
My test instance is set up a bit uniquely– it uses UNC paths for the SQL Server databases. This is supported in SQL Server 2012, and I was testing it out in a Windows Failover Cluster. (If you use shares for files, you need to be very careful about redundancy and availability of course, but that’s a topic for another post.)
I’d forgotten to enable Instant File Initialization for the SQL Server Service on the server that hosted the file shares! Wherever Windows will actually grow the files, the SQL Server Service account needs the “Perform Volume Maintenance” tasks right.
Using the Local Security Policy to Grant “Perform Volume Maintenance Tasks”
Here’s how to grant the rights:
First, open the local security. I usually do this by running the command secpol.msc, but you can also find this in the “Administrative Tools” menu.
Then navigate to Local Policy -> User Right Assignment -> Perform Volume Maintenance Tasks.
Add the SQL Server Service account. The change will take effect the next time you restart SQL Server.
So, did it improve my performance?
Heck yeah, it did! On the first run I got tired of waiting and killed my expansion script after 20 minutes and just dealt with the rollback. After enabling Instant File Initialization on fileserver that holds the shares where my database files live, the expansion script finished in four minutes and 48 seconds. Shazam!
33 Comments. Leave new
Hi Kendra
Great article, Have you ever seen this wait type when doing backups to a unc disk?
Hi Paul,
I haven’t personally observed it — I believe I primarily usually see waits that have ‘backup’ in the name when those occur. (Just going from memory, haven’t reproduced.)
Kendra
Hi, i have just observed that kind of wait type in a restore process. It was waiting in PREEMPTIVE_OS_WRITEFILEGATHER event. DB size is about 10 TB so i thought that it was not normal after waiting about one hour. Then i ‘ve checked sys.dm_exec_requests i’ve seen percent_complete is still 0. I’ve checked the disk activity from resoruce monitor. I’ve seen only two disk activities called “D:\$LogFile (NTFS Volume Log) and D:\$Mft (NTFS Master File Table)” There was nothing in my restore location so i understand that there was a problem in my OS and i’ve asked mr google 🙂 thant i ‘ve find that article. My mssql server service is running under NT Service\MSSQLSERVER. I’ve added that into policy and BINGO:)
Hello Kendra
Nice article. Sure Instant File Initialization is a great thing since SQL Server 2005 / Windows 2003 Server, even greater if it worked also for the log files, not just the mdf/ndf data files. Some how, this great feature seems to be not very popular (imho), perhaps due to some security implications.
Interesting! What security concerns have folks cited?
Only security issue I’ve seen specifically called out is the lack of 0 initialization in conjunction with dbcc page. Specifically, if the data file grows into space previously allocated, but was soft deleted (where the address pointers were merely deleted and the space was added back into available storage), you can use dbcc page to interrogate the previously stored contents. Not sure if I buy it as a realistic security concern unless the storage is shared and unencrypted. Additional forewarning, I have not played with this in a true SAN environment (especially VM environment) to see if you can do this with unallocated blocks at that level. My gut feeling is that it would only be relevant in a thin provisioned scenario/dynamic vhd(x) – which I would personally argue against in the first place.
If you’re looking for an extra excuse to not allow application accounts the sysadmin right, I guess this adds yet another thing to the long list of reasons why that’s a terrible idea. 🙂
Really only one security concern with Instant File Initialization. Which is the reason it’s not enabled by default–
If you enable IFI, and thus don’t have it zero-out all new storage being allocated, your new space can include data that was sitting there from before, like say old Emails or whatever else you had on disk. Which is visible when the file (or its backup) is examined with say a hex tool. For a disk pool that’s never been used for anything but your SQL data, I don’t usually see a problem. For mixed-usage disk, or say in HIPAA compliance situation, it could matter.
I’m wondering if AWS disk might be a special case; do they already zero it for you when it gets presented to you?
I’ve never found any customers who were actually *concerned* about the security risks of IFI when it was all explained– hence me wondering if there really are folks out there for whom this is a big concern.
Perhaps the people who would be concerned about instant file initialization’s security implication would tend to use Transparent Data Encryption (which means IFI doesn’t work anyway)?
Excellent point– Anyone who’s likely to be concerned with IFI data leakage SHOULD be using TDE.
I have no comment…. just trying to sign up for the newsletter
If you scroll down just a LITTLE bit more, there’s a bar to input your email address at the very bottom of every page– that’ll get you signed up for the newsletter.
Nice Archer reference.
We encountered issue in restoring databases. This article fixed that. Good points made from Tim Plas.
Thanks Kendra. Saved me a lot of time today.
Great post Kendra. Curious minds want to know if there’s a way of granting the Perform Volume Maintenance Task to the SQL engine service account via PowerShell.
Never mind, you have a blog about that on http://www.littlekendra.com
http://www.littlekendra.com/2009/11/12/automating-sql-local-security-policy-rights-posh-and-ntrights/
I have seen this same wait type when Perform Volume Maintenance tasks is in fact allowed for the service account, but the autogrowth size is too large. I was doing some testing on my local machine and purposefully made to autogrowth 100GB to see what I may get. No errors, but the wait type was this PREEMPTIVE_OS_WRITEFILEGATHER. I believe that this wait specifically indicates autogrowth of the file, though I cannot find an exact definition in BOL. Is this your experience as well?
It’s related to growth, whether automatic or manual. Just to confirm, are you talking about data file growth, or log file growth? Perform Volume Maintenance tasks only applies to data files.
Yep, I was specifically referring to data file growth. I suppose I should have realized it would be any growth, whether auto or manual 🙂 Thanks!
Here’s a handy script by Tibor to test if IFI is enabled (via T-SQL / whoami):
http://sqlblog.com/blogs/tibor_karaszi/archive/2014/03/12/using-whoami-to-check-for-instant-file-initialization.aspx
or
Via the sql log:
http://sqlblog.com/blogs/tibor_karaszi/archive/2013/10/30/check-for-instant-file-initialization.aspx
Is it the data file or the log file that’s growing? I’ve observed this wait type during a delete operation. No version store / snapshot isolation on my database. So how can the data file be growing during delete?
Kendra, what a great post – here I was waiting on a restore into a new environment, giving up on enjoying a sunny Saturday morning, thinking that it’s just the large file size causing the issue – then found the wait and found your post – 35 minutes into the restore (which of course hadn’t progressed) I killed it, added the permissions and restarted – now 15 minutes into the restore I’m 10% done.
Thanks for taking the time out to make our lives a little easier!
Truly gutted that I didn’t get to see you speak at SQLBits this week (the other guy based in Liverpool went) – hoping you’ll be in London some time soon so I can scribble some notes and save a future weekend!
Hi, Kendra,
Do you know how to add the “NT Service\MSSQLServer” to that policy?
Ra – for questions, head on over to https://dba.stackexchange.com.
Will do. Looks like I either need to improve my search criteria, ask the question there, or give up, LOL.
Could this wait type occur in case of deleting large number of rows from a table in batches. I am trying to delete 10 million rows in 4000 rows batch to avoid lock escalation and to avoid waits for insert on the same table. I also have filegrowth set to 11MB for data and log. Does this setup cause this wait?
Udit – for questions, head on over to https://dba.stackexchange.com.
Error:
Autogrow of file ‘(DB NAME)’ in database ‘(DB NAME)’ was cancelled by user or timed out after 23966 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Is this something i can resolve with the settings that you have mentioned?
Kirtan – yep!
Turned on IFI as instructed and restore DB worked perfectly (31GB).
Before Turing it on the restore was waiting for preemptive_os_writefilegather wait type.
Thank you so much Brent
Said
It is an absolute joke how good you guys and this thread is right now, here am I sitting after years of experience and 1st time seeing this during a critical restore, I added permissions while doing the restore, 15 odd seconds later the waits dissapeared and database 60% restore one minute later after waiting 10 minutes with no progress.
Freaken lifesavers!!!!!!!!!
Woohoo! Glad we could help.