Authors: Sanjay Mishra, Mike Ruthruff
Technical Reviewers: Steve Schmidt, Mike Purtell, Mark Souza, Juergen Thomas, Peter Scharlock, Burzin Patel, Prem Mehra, James Podgorski, Joseph Sack, Alexey Yeltsov, Tom Davidson, Thomas Kejser, Lindsey Allen, Yuriy Toropov, Glenn Berry (SQL Server MVP)
This is the second part of the article Tuning the Performance of Backup Compression in SQL Server 2008. In the first part we described the benefits of backup compression, a methodology on how to tune backup compression for best performance, and shared some best practices. In this second part, we describe some more considerations in tuning backup compression, and how backup compression interacts with other important features in Microsoft SQL Server 2008. Specifically, we will discuss the following:
Understanding the tuning techniques and the interoperability of backup compression with other features discussed in this article can help you get the best out of the backup compression feature.
As described in Part 1 of the article, default BUFFERCOUNT is determined by SQL Server based on the number of database volumes and backup devices. If the database files are spread across several disk volumes and/or there are a large number of backup devices, the default BUFFERCOUNT value may provide optimal backup performance, and you may not need to tune BUFFERCOUNT further. As discussed in Part 1, you can use the trace flags 3605 and 3213 to find out the default BUFFERCOUNT value used in your backup. However, if the database files are spread across too few disk volumes and/or there are a small number of backup devices, the default BUFFERCOUNT value may not provide optimal backup performance. Tuning BUFFERCOUNT explicitly may improve backup performance.
Figure 1: Backup time and CPU utilization with varying BUFFERCOUNT
As illustrated in Figure 1, increasing BUFFERCOUNT results in reduced backup time, at the cost of higher CPU utilization. Your results may vary depending upon your database size, storage layout, and server capacity; however, you will notice that the impact of increasing BUFFERCOUNT on backup performance tends to reduce as BUFFERCOUNT is increased beyond a certain value. In our test, the curve tends to flatten out as we increase BUFFERCOUNT beyond 50.
BUFFERCOUNT value impacts the amount of memory used for backup (discussed later in the section “Memory Utilization by Backup”). Keep this in mind if you explicitly specify the BUFFERCOUNT value.
MAXTRANSFERSIZE refers to the size of the I/O operation issued to read data from the database files. The default value of MAXTRANSFERSIZE is 1 MB. Performance of sequential I/O operations generally benefit from larger block sizes, which is the reason the value is set to 1 MB by default. One drawback of larger I/O sizes is the potential impact on performance of the smaller I/Os being issued concurrently by an OLTP workload. Because I/O queue structures are shared, intermixing large I/O sizes with smaller concurrent I/O requests results in increased latency for both. In today’s shared storage network environments, there is potential for these operations to also impact other hosts sharing the same physical devices. Tuning this parameter is likely unnecessary in hardware configurations using dedicated storage, and it may be necessary only if it is determined that the backup operations impact concurrent workloads. As recommended in Part 1 of the article, tuning MAXTRANSFERSIZE should be considered as a secondary tuning option, and it should only to be utilized when it is determined to be beneficial through testing. In the majority of deployments, the default value will be acceptable.
Figure 2 illustrates observations of backup performance using various transfer sizes.
Figure 2: Backup time and throughput with varying MAXTRANSFERSIZE (BUFFERCOUNT = 50, 1 Backup Device)
As illustrated in Figure 2, smaller MAXTRANSFERSIZE (64 KB) results in lower backup throughput and hence longer backup time, and as you increase MAXTRANSFERSIZE, you observe reduced backup time and higher throughput. However, the impact of increasing MAXTRANSFERSIZE on backup performance tends to reduce as MAXTRANSFERSIZE is increased beyond a certain value. In our tests, we observed optimal backup performance when MAXTRANSFERSIZE was between 128 KB and 512 KB. Your results may vary based on your I/O configuration (throughput and latency of your I/O subsystem).
Similar to the BUFFERCOUNT setting, the value chosen for MAXTRANSFERSIZE will also impact the amount of memory used for backup operation (discussed later).
As discussed in Part 1 of the article, memory used by backup buffers comes from virtual address space outside the buffer pool. On 32-bit systems, there is a fixed amount of virtual address space set aside outside of the buffer pool (default of 384 MB); increasing BUFFERCOUNT and MAXTRANSFERSIZE options to high values may fail because the memory for backup operations is calculated and set aside at the beginning of the operation.
On 64-bit systems, the virtual address space for any process can be up to 8 TB which is far beyond the physical memory supported on current 64-bit version of Windows. As a result, memory for allocations outside the buffer pool does not have to be set aside at the time SQL Server is started and is potentially unlimited. Setting an appropriate value for ‘max server memory’ using SP_CONFIGURE is recommended on 64-bit deployments of SQL Server to ensure that enough physical memory will be available to support allocations outside the buffer pool for backup operations.
Trace flags 3605 and 3213 can be used to report the number of buffers used for backup operations to the SQL Server ERRORLOG. The following example shows the information that is reported in the ERRORLOG when these trace flags are enabled.
LogDate ProcessInfo Text
2009-01-16 12:42:13.700 spid53 Backup/Restore buffer configuration parameters
2009-01-16 12:42:13.700 spid53 Memory limit: 32761MB
2009-01-16 12:42:13.700 spid53 BufferCount: 100
2009-01-16 12:42:13.700 spid53 MaxTransferSize: 2048 KB
2009-01-16 12:42:13.700 spid53 Min MaxTransferSize: 64 KB
2009-01-16 12:42:13.700 spid53 Total buffer space: 200 MB
2009-01-16 12:42:13.700 spid53 Tabular data device count: 1
2009-01-16 12:42:13.700 spid53 Fulltext data device count: 0
2009-01-16 12:42:13.700 spid53 Filestream device count: 0
2009-01-16 12:42:13.700 spid53 TXF device count: 0
2009-01-16 12:42:13.700 spid53 Filesystem i/o alignment: 512
2009-01-16 12:42:13.700 spid53 Media Buffer count: 100
2009-01-16 12:42:13.700 spid53 Media Buffer size: 2048KB
2009-01-16 12:42:13.700 spid53 Encode Buffer count: 100
2009-01-16 12:45:41.820 Backup Database backed up. Database: TESTPART, creation date(time): 2008/11/06(12:31:03), pages dumped: 1295126, first LSN: 158:223:37, last LSN: 158:239:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'R:\Backup\TESTPART_Compressed.bak'}). This is an informational message only. No user action is required.
For an uncompressed backup, the total memory used by the backup buffers can be computed as BUFFERCOUNT multiplied by MAXTRANSFERSIZE. Compressed backup needs three sets of buffers – one set of buffers are used to read from the database files, the second set of buffers are used to compress the data, and the third set of buffers are used to write to the backup media. Therefore, a compressed backup will utilize three times as much memory as the uncompressed backup.
You can observe the memory used by the backup task by monitoring the “Process:Private Bytes” counter for the “sqlservr” process in the Reliability and Performance Monitor (also known as Perfmon). Figure 3 displays this Perfmon counter for a compressed backup, with no other workload. As illustrated in Figure 3, you will see an increase in the Private Bytes counter during the backup task. This increase is equal to (BUFFERCOUNT * MAXTRANSFERSIZE) for uncompressed backups, and equal to (3 * BUFFERCOUNT * MAXTRANSFERSIZE) for compressed backups.
Figure 3: Memory used by a backup compression operation, as measured by Perfmon
Another observation from Figure 3 is that this memory is allocated at the beginning of the backup operation, and it is released when the backup is complete.
Performance of log shipping will also benefit from compressed backups. Log shipping sends transaction log backups from a primary server to a secondary server by copying log backup files to a network share to be applied to the secondary server. When backup compression is used, transaction log backups are compressed. The reduction in file size for log backups improves performance of all the operations performed by log shipping:
The transaction log backups during log shipping can be compressed in one of the following two ways:
Figure 4: Compressing transaction log backups for log shipping
Figure 5 provides a data point from a customer deployment. The compressed log backup is significantly smaller and faster.
Figure 5: Compression of transaction log backup for log shipping (default values for BUFFERCOUNT and MAXTRANSFERSIZE, 1 backup device)
Data compression is a feature of SQL Server 2008 that can save disk space by compressing data pages within the database. A commonly asked question is “Are there additional benefits realized by the use of compressed backup operations when data compression is used (both compression ratio and performance of the backup operation)?”
In this test, all tables and indexes were compressed in the database, and then the performance of backup compression was measured. Separate tests were run with no compression, ROW compression applied to all tables/indexes, and PAGE compression applied to all tables/indexes. Figure 6 compares the size of the compressed backup, backup compression ratio, and backup time for databases that contain NONE, ROW, and PAGE compressed tables and indexes.
Figure 6: Backup compression with data compression (BUFFERCOUNT = 50, MAXTRANSFERSIZE = default, 4 backup devices)
Some observations from the results of Figure 6:
Transparent data encryption (TDE) is another very useful feature in SQL Server 2008. TDE provides encryption of data in a database at the storage level without requiring any application changes. A common question related to this is “How does backup compression perform against an encrypted database?”
In the example below, backup compression was performed against a database with TDE enabled. Figure 7 compares the size of the backup, CPU consumption, and backup time for compressed and uncompressed backups on the TDE-enabled database.
Figure 7: Backup compression with TDE (BUFFERCOUNT = default, MAXTRANSFERSIZE = default, 1 backup device)
We made the following observations when we performed compressed backup against TDE-enabled databases:
For these reasons, we do not recommend the use of backup compression on a TDE-enabled database.
Backup compression is one of the most popular features in SQL Server 2008 Enterprise. Most SQL Server deployments will benefit from this feature; it can reduce both the time taken to perform the backup operation and the disk space required to store database backups. Understanding the tuning techniques and considerations described in Part 1 of the article as well as the interoperability of backup compression with other features discussed in this article can help you get the best out of the backup compression feature.
All tests (except those in Figure 4 and Figure 6) were performed on the following hardware and software environment.
HP DL380G5 with:
EMC Symmetrix DMX-4 2500
Pingback from SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA
Author : Sanjay Mishra Contributors : Mark Scurrell, Richard Waymire Technical Reviewers : Denny Lee
I noticed that the backup file size is doubled during the backup operation. For example, a database sized 100 GB, the final compression backup is 40 GB. However, during the backup process, the backup file and the space used show 80 GB.
Is this true in your test? Does that mean we need to have the space doubled to have a successful backup? What is the story behind? Any link/document related?
Pingback from How to increase SQL Database Full Backup speed using compression and Solid State Disks | Henk's tech blog
Pingback from ?????? ???????????????? ???? SSWUG, ?????? ?????????? ???????????? ???????????????? ??-DB, ???? ?????????????????? ???????? ??-Oracle Open World ???????? ?????????? - SQLServer.co.il
Today’s post is a continuation of yesterday’s post How Many Checkpoints are Issued During a Full Backup?
Pingback from Sql server backups very slow Drija
Pingback from Entendendo e Melhorando a performance de seus backups | Vladimir M. B. Magalh??es – SQL Server DBA
Pingback from BISQL #21:All White Paper For SQL Server R2 Review and Better Understanding « SQL Server Mentalist
"for these reasons, we do not recommend the use of backup compression on a TDE-enabled database."
Not to sound ungrateful but why can MS simply fix the backup executable to un encrypt the stream of data on backup compress it then take the compressed data which is now a bakup file and then re encrypt that output with the same key? That way the backup file cant be stolen and restored onto the same machine unless the key exists? I'm sure that there be more at play then this but I believe compression is as important if not more then TDE.... Next release right your working on it correct?
1) Please note that setting BUFFERCOUNT can cause memory issues, even if only set to 50. " There is insufficient system memory in resource pool 'internal' to run this query."
2) I'm with Mudluck on this - surprised we can't make a compressed backup. And surprised no third-party has done this, either. It went from "we need to do this" to "we can't do this"