Piecemeal restore takes forever

Question

Piecemeal restore takes forever

stakes

Ten Centuries

Points: 1376
More actions
July 11, 2013 at 5:42 am

#280221

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stakes Ten Centuries Points: 1376 More actions · Answer 2

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 SSC Guru Points: 233821 More actions · Answer 3

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 SSC Guru Points: 1003851 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stakes Ten Centuries Points: 1376 More actions · Answer 5

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 SSC Eights! Points: 862 More actions · Answer 6

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 Ten Centuries Points: 1376 More actions · Answer 7

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 SSC Eights! Points: 862 More actions · Answer 8

DominantDBA

SSC Eights!

Points: 862

September 25, 2013 at 6:55 am

#1653117

Thanks...back to the drawing board!