Here’s a quick tip for you:
During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you’ve installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE=1048576, it’ll use 1MB buffers.
I’m going to create about 1000-5000 VLFs, test the restore time which should be pretty dang long and then try this! I’m curious what the curve will look like on a RAID 0 array and a RAID 6 array. Thanks Kevin! I’ll post my results.
Let me know what you find out, Ali. Btw, you might want to check out Kimberly Tripp’s blog at SQLSkills.com. She’s done a similar test arlready, though not with varying RAID types IIRC. Looking forward to your results! -Kev
If you use this option on your backups – it’s automatically used (by default) when doing your restores. What also makes a huge difference is the blocksize – so max both
BLOCKSIZE=65536,
MAXTRANSFERSIZE=4194304
*we get ~10x throughput over sql server defaults
however you do need to be carefull with the BUFFERCOUNT (this works with the above settings and determines how much memory will be used for the backup/restore process)
Excellent info, Rob! Thanks very much for the added information. I’m impressed with your 10x speed improvements. I’ll have to test that out myself as well. ^_^
-Kev