8

I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored from a backup between each run, to make sure we're always starting from the same point.

My question is, the server currently has 12 cores and 92GB of RAM, with a RAID 5 disk subsystem that the database is on. What areas usually cause bottlenecks for SQL restore processes? Is it the disk, memory, or CPU?

  • 3
    What backup medium are you restoring from? By the way RAID 5 incurs a heavy write penalty when compared with most other RAID levels, so this may not be the best for performance testing. – Chris McKeown Jun 18 '13 at 21:29
  • The .bak's (8 of them split up) are on the same RAID 5 array they're being restored to, which does make me realize I can probably handle that better in the future. I don't have another array large enough to hold all the .bak's, but I might be able to split them up onto different direct attached drives. Also, good point about the RAID 5. I'm aware of that, but we're not doing stress testing yet so it's fine if it's bottlenecking at the disk drive right now during the actual load tests. Once we get a little further along we'll increase the disk performance via SAN, RAID 0 or RAID 1+0 – Sean Long Jun 18 '13 at 21:37
  • 2
    Certainly your suffering unduly from having the backups on the drive you're restoring too. How many disks in your current RAID5? – Mark Storey-Smith Jun 18 '13 at 21:40
  • So you are using compression, I'll assume. What other backup options are you using? How is your data partitioned? Are you able to intelligently distribute data across file groups (you could then just do file group backups and restores on the changed data)? – swasheck Jun 18 '13 at 21:43
  • The problem is that the tests touch a very large percentage of the database, so we would have to restore across multiple file groups (and the tests would change based on the needs and development of the workload). So, we would have to constantly look at the test makeup and restore the specific file groups. While that's an option, I'm not sure it would buy us a lot of saved time. – Sean Long Jun 18 '13 at 21:49
6

Your primary bottleneck on a restore is going to be the disk IO. To fix that you basically need either faster disks or a different configuration. I don't know enough about RAID or SANs to suggest anything there though. You might even consider SSDs. They are blindingly fast. I wouldn't want to use them on something that doesn't get re-created on a regular basis (tempdb is always a good candidate for this) but since you are restore it frequently it might be ok. On the other hand you probably want to make sure your test server is as close as possible to your production server if you are doing performance testing.

There are a couple of other things you can do to help yourself out. First compress your backups if you aren't already. This of course assumes SQL 2008 or higher. It will reduce not only the disk space to store the backup but the IO to read it in. There is a CPU cost involved so be aware. Also don't delete your database, just restore over it. This way the files are already in place and there is no overhead for creating them. You can turn on instant file initialization (It's a server level permission) to dramatically speed up file create/growth for your data file but it won't work for your log file.

  • Good information, I didn't realize that restoring over the existing is better than dropping/restoring from a backup. We're already using compression and I'm planning on verifying that instant file initialization is enabled for the account doing the restoring. I really appreciate the clarity of your answer, thanks! – Sean Long Jun 18 '13 at 21:56
  • Make sure that instant file initialization is turned on on the account running SQL Server also. For a small database it's probably not all that big a deal, but for something the size you are looking at it could make a big difference. – Kenneth Fisher Jun 18 '13 at 22:05
  • Good call. Also thanks for realizing that performance testing doesn't always mean stress testing (and that I'm pretty confined by the way my production configuration is set up, currently). – Sean Long Jun 18 '13 at 22:13
  • OT: " consider SSDs. ... I wouldn't want to use them on something that doesn't get re-created on a regular basis" ... why? – Martin Jun 19 '13 at 11:14
  • I would still be nervous about them failing. Everything I have read has said to use them for databases like tempdb which get recreated each time the instance starts, but not to use them for regular user databases. Although I'm sure that is changing over time. – Kenneth Fisher Jun 19 '13 at 11:30
7

Don't do a backup and restore; use SQL Server Snapshots. It takes a lot of disk space to store a sparse file the same size as the files you've snapshotted, but rolling back is hundreds of times faster.

They are available in SQL Server Enterprise and SQL Server Developer editions.

  • That's a good idea, and if this were any other server than a performance test server, that looks like a great way to go. However, it looks like DB snapshots won't work because it will cause additional overhead for the source DB, which I can't have. The testing being done is performance testing (load, stress, etc.) so we have to avoid anything external that would cause stress. – Sean Long Jun 18 '13 at 21:12
  • Personally I haven't noticed any performance differences with having a snapshot, but I guess copy-on-write does have some overhead; not knowing your workload I can't judge. – Mark Henderson Jun 18 '13 at 21:29
  • 2
    @SeanLong Mark's suggestion is probably the best option for your scenario. What I think you're misunderstanding is when and what you take the snapshot of. The plan on a test server would be to restore the test database from your live backup, then snapshot the test database, run your test cycle, then revert the snapshot, rinse & repeat. Periodically you can return to step 1 and restore the live backup to test again. – Mark Storey-Smith Jun 18 '13 at 21:38
  • Ah, I see. I thought that maintaining the snapshot required a constant amount of overhead from the test database, which would affect our (very write/read heavy) loadtests. I don't mind if our workload is causing the bottleneck at the disk drive, I just don't want an external factor (which I thought db snapshotting would be) to cause it. – Sean Long Jun 18 '13 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.