Poor Restore Times

Question

Post reply

Poor Restore Times

csoloway

Ten Centuries

Points: 1087
More actions
November 21, 2010 at 6:15 am

#100331

After suffering through a 13 hour restore yesterday for an old server I need to understand the factors in a restore that make it so slow. I think I know, but can someone point me to a microsoft (technet or msdn or other) article that talks about the factors that make a restore so slow.
Here's the scenario:
DB size 212G
SQL Server 2000 SP4 Standard Edition
CPU 8
Memory 4G on Server, but SQL is limited to it's annoying 2G
Slow network

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Di you have other things running on the server, as well?

--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)

ps. One Orange Chip Points: 29422 More actions · Answer 2

csoloway (11/21/2010)
Memory 4G on Server, but SQL is limited to it's annoying 2G
Slow network

You can increase SQL Server memory usage to upto 3 GB by using /3gb switch.

You mentioned slow network. Is your backup file placed on a network folder or local to the server?

Pradeep Singh

csoloway Ten Centuries Points: 1087 More actions · Answer 3

It's Windows 2003 Standard which is limited to 4G and SQL Server 2000 Standard which is limited to 2.

I'm just looking for some documentation that speaks to what happens during a restore so I can make them understand as long as it stays on those versions, it's going to be slower than dirt.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

I have not seen any tech document regarding that, but When using SQL Server 2008, the back up size is small(compressed back up). That means less IO on the back up file. This will increase both the back up and restore speed.

Other than that, back up and restore time is based on the HW and its configuration. (As far as I know)

-Roy

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

csoloway (11/22/2010)
It's Windows 2003 Standard which is limited to 4G and SQL Server 2000 Standard which is limited to 2.
I'm just looking for some documentation that speaks to what happens during a restore so I can make them understand as long as it stays on those versions, it's going to be slower than dirt.

Does this help?

http://msdn.microsoft.com/en-us/library/ms191455.aspx

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 6

You may also want to check the number of virtual log files in your transaction log. If there are many (meaning your transaction log has autogrown many times), restores will be slow. See http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/43105b39-0fb3-4276-a0c3-468277c37941 for info. Step 8 of Kimberly Tripp's post here tells how to manage your VLFs.

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 7

Just out of curiosity, I'd love to hear how many VLFs your database has. Run DBCC LOGINFO(0) in the database. The number of rows returned equals the number of VLFs you have. With a database that big, there's a pretty good change you have lots.

csoloway Ten Centuries Points: 1087 More actions · Answer 8

csoloway

Ten Centuries

Points: 1087

December 6, 2010 at 11:31 am

#1259142

Actually I was very surprised to only find 9 VLFs

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 9

Are you restoring across the network?

While the backup/restore should be faster in SQL 2005+, I'm not sure it would make a huge difference. Compressing the backups (reducing i/O) and having better hardware should help the most.

What type of times are you getting for what size data?

csoloway Ten Centuries Points: 1087 More actions · Answer 10

We did get it done. It was SQL 2000 on the same server. Took 7 hours to restore 387G.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 11

I don't think that you'd get substantially better performance on SS2K8, but if you want (and have time) install Dev edition on the same hardware and test that.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 12

We have a 400 GB DB on SS2k8 and it takes 40 minutes for back up and restores in 1 hr. But our server is bit powerful.. 😉

-Roy