June 29, 2009 at 1:33 pm
Hey folks - for those of you who are interested, or have experienced slowness for your transaction log restores, you may want to take a look at your runtime setting for Affinity Mask, especially if you have upgraded the instance from SQL 2000.
We recently experienced extemely slow restore times for our transaction log backup files (some files were taking over 2 hours to restore, and these files were less than 100MB in size!), and found that altering the runtime setting for Affinity Mask to 0 allowed us to have restore times that were acceptable.
June 29, 2009 at 7:41 pm
What was it set to before, and why? I'd be wary of altering this unless you know why it is not 0. Or that you make sure that you're not affecting other applications on that server.
June 30, 2009 at 2:56 am
Great point Steve - one should not make changes unless one tests out or understands why the change is being made and knows what the effects will be. In our case, no one knows "why" it was set differently than 0 - the default setting, which allows SQL Server to dynamically allocate which CPU's it will use (no restrictions) other than it may have be a "test" a previous Admin did long ago and this change was either not rolled to the production systems and/or was not documented. As for the "what" behind this setting - the restrictive setting was 65535 (a NUMA - a hardware based Non-Uniform Memory Access - setting, which is not used in our environment). Once we changed it to 0 (allowing SQL Server to manage affinity), we saw a significant improvement in our restore times. We double checked with PSS and received a "thumbs up" on this change and the good news is we are now able to process log shipping the way it was intended: backup/restore times for our transaction logs are now processing as expected. (check this Technet article, it provides some details on testing done back in 2004: http://technet.microsoft.com/en-us/library/cc917532.aspx). As always, good feed back!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply