SQL Server data and log files initialization, think multiple files, backup & restore performance!
Whenever you perform a database creation, add a data or log file to database, AUTOGROW process is intiated or restore of database will trigger the file initialization if that is created afresh.
In addition to this when you talk about files intialization you must consider the hardware such as RAID, as the software data block size is computed for optimal performance by SQL Server and should not be altered which is set to a maximum 64KB blocksize. Say if you are writing to disk or to tape (high-speed) drives should perform better if they have a dedicated SCSI bus for each tape drive used. Drives whose native transfer rate exceeds 50 percent of the SCSI bus speed must be on a dedicated SCSI bus to avoid loss in performance. In any case you must refer to the relevant Vendor's documentations about settings that affect might tape drive performance.
In general the backup operation should not affect the server's performance, but in many cases I have seen that raw I/O speed of the disk backup device affects disk backup device performance and allows SQL Server backup and restore performance operations to roughly scale linearly as multiple disk devices are added. This will occur if you are performing multiple BACKUP and RESTORE operations on a SQL Server instance. This is where RAID for a disk backup device needs to be carefully considered, for instance RAID 5 has low write performance, approximately the same speed as for a single disk (due to having to maintain parity information). Additionally, the raw speed of appending data to a file is significantly slower than the raw device write speed. So the performance is highly dependant on striping of that disk, such that backup performance can be increased by placing several backup media families on the same logical drive. This is the reason backup drives must be place on seperate disk device.
Coming the internals of how SQL Server 2005 initializes the files that, say data and log files are first initialized by filling the files with zeros when you create a database; add files, log or data, to an existing database; increase the size of an existing file (including autogrow operations); or restore a database or file group. File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros. The operation is instantaneous having the capability that allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros and the overwrite operation as new data is written to the files. The same will not work for database log files as they cannot be initialized instantaneously. A little catch here such an operation if instant file initialization is available on later versions of Windows Server 2003 & XP operating systems.
So when you have a large number of databases that are huge in size (more than 25GB) then deploying a better backup strategy is ideal by having differential and log backups on daily basis with full backups on weekly. By creating a differential backup is identical to creating a full backup, except only changed data is copied. Backing up a database file consists of simply copying the data from the file to the backup devices. There are more internals to how the reader and writer threads are initiated whenever a backup is initiated, similar to what restore operation is writer will take higher hand to perform than the reader on. This is where you may see the bottleneck on disks that will be either the database files or the backup devices. It is a bette practice to add more backup devices with necessary SCSI controllers dedicated to those devices can improve performance lot better, as if the total read throughput is greater than the total backup device throughput, then the bottleneck is on the backup device side. In other case of if the total backup throughput is greater than the total read throughput, then increase the read throughput; such as add more database files or devices (or by adding more disks to a RAID device) (source:Technet documentation).
To sumup its worth talking about Transaction log backup performance when you have large number of databases that are awaiting for point in time recovery mode. So whenever a transaction log backup is initiated on the disk side a writer thread is assigned to each backup device, this is the reason it is stressed to get more backup devices for transaction log backups or keep the backup on a seperate disk device. One cannot avoid bottleneck that can occur either on the disk device containing the transaction log files or the backup device, depending on their relative speed and the number of backup devices used. Why adding up more backup devices will perform better? The number of backup devices will scale linearly until the capacity of the disk device containing the transaction log files is reached, after which no further gains are possible without increasing the speed of the disk devices containing the transaction log, by using disk striping. So when you have multiple operations of BACKUP & RESTORE to/from tape then you can improve performance by specifying NOREWIND. This option causes SQL Server to keep the tape or tapes open after the backup operation. NOREWIND implies NOUNLOAD.
