2

When restoring SQL Server 2008R2 DWH-DBs I always gave some memory back to the OS to speed it up.

Now I got performance problems restoring to SQLServer 2016:

Where does the restore buffer come from?

SQL Memory Manager I guess?

Does the SQL Server need more memory or the OS to restore big files over the network?

Backup is compressed and consist of 14 files each larger 2,5 TB => DBsize is lager 100 TB.

Parameter:

MAXTRANSFERSIZE = 4194300, BUFFERCOUNT = 5000, BLOCKSIZE default.

INSTANT FILE INITIALIZATION on, Network SMB, Server 2012R2

  • 1
    What is the hardware spec for the server - CPU, Total RAM and instance - cpu allocated, max memory ? – Kin Shah Feb 7 '17 at 15:51
  • 1
    When you say "Network SMB" - are you saying that the database is stored on an SMB share? Or that the backups are? Or both? What's the network connection (how many 10Gb Ethernet cables does the server have?) – Brent Ozar Feb 7 '17 at 16:26
  • 1
    A day of seaching - I found: INSTANT FILE INITIALIZATION is OFF DBCC TRACEON 3004 and 3605 log: Zeroing D:\DummyDB_log.ldf from page 0 to 128000 I had to change the SQL Service Accout to Local System. Used secpol.msc to add the ComputerName to 'Perform volume maintenance tasks' and restarted the SQL Server Service. But the SQL Server is zeroing out the pages. It is installed with INSTANT FILE INITIALIZATION on. Now I added SYSTEM and LOCAL SERVICE to Perform volume maintenance tasks and 5 minutes ago I restarted the SQL Server Service. IFI is still OFF What can I do? – TKR Feb 7 '17 at 17:41
  • 2
    You don't need to change the SQL Server Service Account to LocalSystem to get Instant File Initialization on. Simply add the SQL Server Service account to the "Perform volume maintenance tasks" policy (and "Lock pages in memory" policy, if nothing else is running on the server aside from SQL Server). – Max Vernon Feb 7 '17 at 17:50
  • 1
    Instant File Initialisation doesn't apply to log (ldf) files, test it again on a mdf/ndf file. You can also try copying the backup file locally and restoring from there to test if the bottleneck is the network. – Gareth Lyons Feb 7 '17 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.