I have an Azure VM with Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition. I have enabled IFI (added SQL Server user
NTSERVICE\MSSQLSERVER to "Perform Volume Maintenance Tasks").
I have tried to restore a 3 GB database with files on C drive (SSD) which took about 40 minutes to complete. Checking restore progress, after about 20 minutes it was at 100% complete.
During the restore, I had
PREEMPTIVE_OS_WRITEFILEGATHER constantly showing up for my restore.
40 minutes for 3 GB database is too much.
Files being on C drive (not good) and with all the rest of activity that was on C drive - could have this impact my restore that bad taking it to 40 minutes, having in mind this is still a SSD drive?
Is there anything else I could look for in order to find what is causing it to take THAT long?
EDIT: I have checked the following so far:
1 - checked with
EXEC xp_cmdshell "whoami /priv" and I get
SeManageVolumePrivilege Perform volume maintenance tasks Enabled - so sql server user does have permissions
2 - restarted sqlservice, just to be sure, after the answers below
3 - checked creating a new database with trace flags
3004,3605 ON and it's only logging ldf zeroing, not MDF - so IFI is ON