Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Piecemeal restore takes forever Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 5:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 201, Visits: 387
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
Post #1472515
Posted Thursday, July 11, 2013 6:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1472545
Posted Thursday, July 11, 2013 8:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 201, Visits: 387
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)

Post #1472590
Posted Thursday, July 11, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 5,951, Visits: 12,822
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"
Post #1472592
Posted Thursday, July 11, 2013 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1472596
Posted Thursday, July 11, 2013 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 201, Visits: 387
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.
Post #1472601
Posted Wednesday, September 25, 2013 4:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:42 AM
Points: 104, Visits: 359
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
Post #1498241
Posted Wednesday, September 25, 2013 5:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 201, Visits: 387
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.
Post #1498262
Posted Wednesday, September 25, 2013 6:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:42 AM
Points: 104, Visits: 359
Thanks...back to the drawing board!
Post #1498314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse