Backup Tune-up

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Sean McCown (Midnight DBAs)

Backup Tune-Up

Contact Info:
webshow.midnightdba.com
DBAs@midnight - webshow

This post is going to be a copy of my notes which arent going to make a lot of sense, but when I review them at different times, I am hoping to pick up on maybe what he was saying when it is applicable. There is a lot of information on their website, including the demo that he performed

Restore >> Execution Plans >> log zeroing
- fill a full file with zeros
- then fills it in with SQL data

IFI - data files only

• instant file initialization
• turn on - local security policy >> user rights assignments >> 1) perform volume maintenance task and 2) restart SQL service

Trace Flags - change the normal behavoiur of SQL

• 3014 - never gonna use - troubleshooting HEX info
• 1806 - turns off IFI - current session
• 3605 - send to SQL log
• 3004 - zeroing data came from here
• 3604 - send to client (not log)
• 3213 - tells how many buffers using by default
• 3212 - close to 3213 (sister to it)
• There is a SQL Server Central post that displays a lot the known ones

Increase the speed of backups - best ways

• compression - only works 2008 and above - enterprise and above
• buffer count (# of files) - striping to separate drives (or same drive)

More things discussed/shown

• buffers - memory to use
• striping - threads to use
• max transfer size - amount of data to write at a time - chunks of 64bytes (2MB is a bit excessive)
• cant stop zeroing log files - growing log files (1 GB is ok)

Benchmarking

• read and write
• NUL - goal - cant do any better - goal is to get as close to these numbers as possible
• Find out how fast you can do it with all the resources available
• Good to know you can do it BUT - if you do it you will hurt production resoruces
• But - if things are stopped due to issues, its nice to know the smallest amount of time it will take if all resources are available