Quick Tip – Speed a Slow Restore from the Transaction Log

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.

If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO.  You may also wish to keep an eye on LOGBUFFER wait stats.
I’d love to hear your feedback.  Have you tried this technique?  Did it work as advertised?  Did it require some changes to work on a specific version or edition?
Many thanks,
-Kev

Comments

  1. 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.

  2. 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

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

  4. 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

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.