Backup Tune-up

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Sean McCown (Midnight DBAs)

Backup Tune-Up

Contact Info:
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)


  • 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

Comments powered by CComment

Related Articles

SQL Foundations - Day 1 2013

SQL Saturday - Baton Rouge # 234 - 2013

SSIS: ProTips - Performance Tuning

Thank you for visiting!