This post is more than 5 years old
20 Posts
0
3832
MSSQL Restore runs very slowly
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
PapadopoulosA
20 Posts
0
June 30th, 2016 05:00
shashank621 thank you for your answer
The issue here is the stripes that are very helpful in backup but they also significantly increase the restore time.
Here is the info for your question "Next what do you mean when you say difference is very big ?"
Backup MSSQL 2005 (db size 7,2 TB) with EMC Networker having 16 stripes and send the backup in 2 tape drives
Backup time 4h45m
Restore time N/A after 8 hours the restore data size was 700GB and i had to stop it
Backup MSSQL 2005 (db size 7,2 TB) with EMC Networker having 2 stripes and send the backup in 2 tape drives
Backup time 10h26m
Restore time still running but after 5h22m the restored data size is 4,2TB
ble1
14.3K Posts
0
June 29th, 2016 07:00
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).
shashank621
1 Message
0
June 29th, 2016 07:00
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
PapadopoulosA
20 Posts
0
June 30th, 2016 04:00
ble thank you for your answer
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.