SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Piecemeal restore takes forever


Piecemeal restore takes forever

Author
Message
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
Hi Everyone

I have a 1.1TB database with 700GB of data on a SQL Server 2008 R2 SP1 instance. Most data is row compressed and in 2 partitioned tables. A full backup is taken daily to 4 files on a NAS as part of the daily maintenance routine (which also includes index fragmentation, updating statistics and an integrity check directly before the full backup and in that order). The backup is also compressed.

On a testserver I want to do a piecemeal restore, starting with the primary filegroup (the partitioned tables are in other filegroups). The primary filegroup file is 9GB and has about 3GB of data. The transaction logfile is 16GB. I know, with a restore, the transaction logfile is being zeroed which could take some time. On the other hand, the empty space in the primary data file is not being zeroed during a restore operation when Instant File Initialization is enabled. This is the case on my testserver.

The problem is that the piecemeal restore takes a very long time, about 6 hours. I investigated and tried a few things to speed up the restore. Here they are:

- Check if Instant File Initialization is enabled, it is.
- Checking the performance of the whole chain (NAS, network, SAN), by restoring another backup with approximately the same size (3GB) from the same NAS to the same testserver over the same network. The restore was done in 2-4 minutes.
- Checking the number of virtual logfiles, which is 41 and thus ok.
- Take a partial backup and restore that one partially. This restore took 2-4 minutes. My conclusion: creating the mdf and ldf files is not the problem.
- Altered the daily maintenance routine by adding a transactionlog backup after the integrity check and before the full backup, hoping that the transactions from the transactionlog caused by the earlier operations, are left out from the full backup. I’m not sure this makes sense, but to my understanding when SQL Server takes a backup, the transactions in the transaction log are also included for the redo/undo fase when restoring a the backup.

Also, I opened the Performance Monitor to see disk activity during the restore operation. There is only disk activity during the beginning of the restore operation. Furthermore, when I execute sp_WhoIsActive during the restore operation, I see that the restore session is suspended (wait_info: BACKUPTHREAD) while network traffic stays at maximum. The CPU's are also processing 'something'. I’m beginning to suspect the restore operation is scanning every page in the full backup files somehow.

Is there someone who knows if this is true or, if not, knows the true reason why the piecemeal restore is so slow?

Thanks
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86652 Visits: 41098
Do you know how long a full restore (non-piecemeal) of all filegroups takes?

Also, are the filegroups of the larger tables that you've partitioned "Read_Only"? If not and IIRC, all read/write filegroups will be restored with the PRIMARY filegroup during piecemeal restores.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
Hi Jeff, thanks for replying. I don't know how long a full restore on this particular test machine takes, but I suspect longer than 2 hours. The machine is in a seperate virtual testenvironment with old hardware and I don't have the space to restore the entire database. In another testenvironment, with better h/w, it takes about 1-2 hours.

The filegroups are all read/write. Indeed all read/write filegroups will be restored with the PRIMARY filegroup during piecemeal restores, if the database is in simple recovery mode. But it isn't, it's in full recovery mode.

From BOL:
"Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups. "
(http://msdn.microsoft.com/en-us/library/ms177425(v=sql.105).aspx)
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20058 Visits: 17244
stakes (7/11/2013)
The machine is in a seperate virtual testenvironment with old hardware and I don't have the space to restore the entire database. In another testenvironment, with better h/w, it takes about 1-2 hours.

can you supply more info on the virtual disks for this VM and the storage that is provisioned for the underlying data store.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86652 Visits: 41098
I guess I don't know the answer to your problem then. I was thinking that a full restore was takeing you 6 hours and, although a piecemeal restore bring the "guts" of the system online more quickly, it would still take about the same amount of time to ultimately peicemeal restore all of the filegroups.

Thanks for the BOL tip. I'd forgotten that peicemeal restores were handled differently for the SIMPLE recovery model.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
Hi Perry, what info exactly do you want? I want to give it to you, but I don't think it's the virtual disk or the VM itself, because other restores on the same machine do go fast.
DominantDBA
DominantDBA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 463
Im having the same issue where I thought picemeal was going to be quicker

I am developing a DR solution and one of the things Im testing is peacemeal restore as a means to get us up and running and sadly Im finding
that it doesnt perform.

A full restore takes about 20 second on my test database but when I do a peacemeal restore it takes 20 minutes!!!

Ive also checked instatant file intialization et al and its setup correctly but it just takes ages to create the files of the filegroups

not sure what else I can try
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
Hi DominantDBA and all others who encountered this problem. For piecemeal restores to work as fast as they are suppose to, you have to backup your filegroups seperately. That way you can restore them 'piece-by-piece' in a timely manner. This is how piecemeal restores are meant to be, if you read the MS documentation carefully. Of course, your backup strategy will become more complex by backing up your filegroups seperately and that's why MS should make piecemeal restores from full backup's run faster as they do now. At least, in my opinion.
DominantDBA
DominantDBA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 463
Thanks...back to the drawing board!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search