Question :
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?
Answer :
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.
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.