Alexandros1
1 Nickel

MSSQL Restore runs very slowly

Jump to solution

Hello all.

I am starting with info:

Networker server 8.0 (OS win2008R2) physical

Networker client 8.0  (OS win2003 Enterprise) virtual

Networker module for MSSQL 5.2  (MSSQL 2005)

The backup is running through 10Gb ethernet

The full backup of MSSQL (db size 7,2TB) took about 5 hours with 16 stripes in 2 LTO4 tapes (throughput was about 450Mbps).

I am running the restore to another server (through a 10Gb ethernet) and the throughput is 30Mbps.

I understand that stripping the backup has an impact at restore but the difference is very big.

Does anyone has an idea how to speed up the restore procedure with the current settings?

Thank you in advanced

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
Alexandros1
1 Nickel

Re: MSSQL Restore runs very slowly

Jump to solution

0 Kudos
4 Replies
ble1
5 Iridium

Re: MSSQL Restore runs very slowly

Jump to solution

Performance here might only be affected in terms of multiplexing, but even so if you compare speed of individual stream it may suggest that you have TCP settings within the link that influence speed to be such.  I do not think this is related anyhow to NW or module version (even you do run unsupported and old version).

0 Kudos
shashank621
1 Copper

Re: MSSQL Restore runs very slowly

Jump to solution

I am no expert in EMC storage stuff but since SQL Server is involved I would like to help you here. The restore of a database can take much longer time as compared to backup. There are few reasons

1. The Log file of database is fragmented too much and there are too many Virtual Log Files. This would force recovery to take longer time. Run dbcc loginfo(db_name) on database and tell me how many rows it returns. Please see How a Log file Can affect database restore time

2. The SQL Server service account does not have "Perform Volume Maintenance Task" privilege or what you call as Instant File Initialization is enabled or not. If not the restore time can significantly increase

3. I suggest  you have a look at Optimizing Backup and Restore Performance in SQL Server

Next what do you mean when you say difference is very big ?

Use this blog to track status of the restore

PS: My reply is mostly centered if issue is with SQL Server. Also make sure your SQL Server 2005 is patches to Sp4

0 Kudos
Alexandros1
1 Nickel

Re: MSSQL Restore runs very slowly

Jump to solution

I know that the versions are unsupported but this not an issue.

When backup runs with 16 stripes i get a speed of almost 30Mbps per stripe directed to two physical tape drives.

When restore runs with one stripe per tape drive (total of 2) it gives ~17Mbps per drive.

This is not a TCP issue this is a stripe issue. Restore has to recreate the data files from 16 pieces so the tape has to move back and forth and this takes a lot of time.

Running the backup with only 2 stripes, one per tape, backup takes almost 10 hours but restore runs better giving a throughput of 108Mbps per drive which is much better.

0 Kudos
Alexandros1
1 Nickel

Re: MSSQL Restore runs very slowly

Jump to solution

0 Kudos