Sql-server – PREEMPTIVE_OS_WRITEFILEGATHER wait on database RESTORE with IFI on

azure-vmrestoresql serversql server 2014

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

Best Answer

wBob is right on the money, you need a service restart. I wanted to enhance his answer and give a script that will also verify that IFI is indeed enabled.

This will determine whether or not you need to kick the service to allow the local policy to take.

USE MASTER;

SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --
--Exit if a database named DummyTestDB exists
IF DB_ID('DummyTestDB') IS NOT NULL
BEGIN
    RAISERROR (
            'A database named DummyTestDB already exists, exiting script'
            ,20
            ,1
            )
    WITH LOG
END

--Temptable to hold output from sp_readerrorlog
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL
    DROP TABLE #SqlLogs
GO

CREATE TABLE #SqlLogs (
    LogDate DATETIME2(0)
    ,ProcessInfo VARCHAR(20)
    ,TEXT VARCHAR(MAX)
    )

--Turn on trace flags 3004 and 3605
DBCC TRACEON (
        3004
        ,3605
        ,- 1
        )
WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog
CREATE DATABASE DummyTestDB
GO

--Turn off trace flags 3004 and 3605
DBCC TRACEOFF (
        3004
        ,3605
        ,- 1
        )
WITH NO_INFOMSGS

--Remove the DummyDB
DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File
--This can take a while if you have a large errorlog file
INSERT INTO #SqlLogs (
    LogDate
    ,ProcessInfo
    ,TEXT
    )
EXEC sp_readerrorlog 0
    ,1
    ,'Zeroing'

IF EXISTS (
        SELECT *
        FROM #SqlLogs
        WHERE TEXT LIKE 'Zeroing completed%'
            AND TEXT LIKE '%DummyTestDB.mdf%'
            AND LogDate > DATEADD(HOUR, - 1, LogDate)
        )
BEGIN
    PRINT 'We do NOT have instant file initialization.'
    PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'
END
ELSE
BEGIN
    PRINT 'We have instant file initialization.'
END
Related Question