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

Community
  • 1
TKR
  • 21
  • 1
  • 1
    What is the hardware spec for the server - CPU, Total RAM and instance - cpu allocated, max memory ? – Kin Shah Feb 07 '17 at 15:51
  • 4 NUMA Nodes - 72cores, RAM 3TB, one instance, max mem 1,5TB – TKR Feb 07 '17 at 15:58
  • 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 07 '17 at 16:26
  • Backups on SMB: 2x10Gb - in restore state network utilization is low on 2016 high on 2008R2 from the same source server, I/O subsystem: local discs tested with Diskspd and it is much faster than the restore operation. – TKR Feb 07 '17 at 16:59
  • 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 07 '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). – Hannah Vernon Feb 07 '17 at 17:50
  • I have to use LocalSystem – that is not based on my decision. I did a reboot Instant File Initialization is still off. How can I get it running for LocalSystem? – TKR Feb 07 '17 at 18:08
  • 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 07 '17 at 18:22

0 Answers0