SQL Server Customer Advisory Team - SQL Server Best Practices

Report Server Catalog Best Practices

denny.lee — Thu, 26 Jun 2008 21:15:00 GMT

Building and Deploying Large Scale SQL Server Reporting Services Environments—Technical Note Series

Authors: Denny Lee, Lukasz Pawlowski
Contributors: David Reinhold, John Gallardo, Dean Kalanquin, Robert Bruckner
Technical Reviewers: Tim Shea, Mike Ruthruff, Burzin Patel

Introduction

This technical note is part of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series which provides general guidance on how to set up, implement, and optimize an enterprise scale-out architecture for your Microsoft SQL Server Reporting Services (SSRS) environment. This note provides guidance for both SQL Server 2005 and 2008 Reporting Services. The focus of this technical note is the report server catalog—the underlying databases that provide the metadata (parameters, snapshots, history, and so on) that are used by Reporting Services to provide your reports.

Architecture

Figure 1 represents a typical scale-out Reporting Services environment; as indicated by the red box, the focus of this technical note is that of the report server catalog.

Figure 1: Reporting Services Scale Out Architecture

As you can see, all Reporting Services servers have connections to the report server catalog. As implied by this diagram, this single instance could be a possible bottleneck in the SSRS environment. Because of this, let’s focus on what these report server databases do and how to optimize them for your environment.

Report Server Databases

The report server catalog is comprised of two report server databases:

ReportServer (RSDB) stores all report metadata including report definitions, report history and snapshots, and scheduling information.

ReportServerTempDB (RSTempDB) stores all of the temporary snapshots while reports are running.

Figure 2: Breakdown of Report Server Catalog

These databases are responsible for containing the metadata that is needed by the Reporting Services engine so that it knows how to request data from the data source (such as a SQL Server database or an Analysis Services database). When a report is executed by a report server, SSRS loads report metadata from RSDB. This includes the data source information for the report, the default parameter values, and the report itself. Note that for ad-hoc reports, this data is not loaded from the RSDB catalog database because the report comes from the RDL that is executed, published, and bound to the user’s session. As well, all user session information and live or cached execution snapshots are stored in the RSTempDB database. History and execution snapshots are stored in the RSDB database. Subsequent report requests can use the stored state to view the report rather than re-executing the report.

Report execution state is represented in a snapshot. The snapshot contains the data that is retrieved from the report’s queries to the data source. SSRS keeps these snapshots so that subsequent requests for the same report can be satisfied by using the snapshot instead of re-executing the report and obtaining the data again from the original data source (thus reducing the load on your data source). By default, these snapshots are stored in the RSTempDB database. Therefore, commonly requested reports hit the RSTempDB database instead of querying the original data source (provided the snapshot is current). While this reduces the load against your original data source, it does increase the load on your SSRS catalog. The size of your snapshot is directly correlated to the size of the data returned by the queries of your reports. Because of this, it is important to reduce the size of your snapshots by including only the columns (and rows) you need.

In addition, this also implies that you must optimize the SSRS report server catalog to prevent it from being a bottleneck since every single report request hits RSDB. For frequently requested reports, many database queries will hit RSTempDB. Because these databases are hit for every single report request, the report server catalog has a lot of I/O and transactions in order to share state information across multiple SSRS servers. The RSTempDB tables and log will grow quickly because many transactions are performed to maintain report consistency. (For example, if you get the first page of a report, you need a snapshot of the second page that is consistent.) If you have an environment with many concurrent requests (typical of most enterprise Reporting Services environments), there can be a lot of write activity to the RSTempDB log.

For SQL Server 2005 Reporting Services, many of the insertions are performed to the ChunkData, SnapshotData, and SessionData tables. But SQL Server 2008 in general does not write to these tables. They exist in the catalog to support the upgrade of pre-2008 catalog. SQL Server 2008 Reporting Services uses a new snapshot storage mechanism that shreds chunks across multiple rows in a table named Segment. For SQL Server 2008 Reporting Services, this table generally takes on the majority of transactions of the RSTempDB database.

As implied by the above, there are differences in RSDB and RSTempDB access patterns between versions of SSRS. SQL Server 2008 Reporting Services makes use of RSTempDB significantly more than SQL Server 2005 Reporting Services does. This is because we process data incrementally during report execution rather than immediately when the report is executed. To make the report execution reliable, we store the data for the report in RSTempDB until it is needed to generate a particular page of the report. Clearly, this increases the number and size of queries executed against the RSTempDB database and can lead to bottlenecks.

Therefore, to optimize your report server catalog, we suggest the following best practices.

Use a Dedicated Server

As noted earlier, a heavy transaction load is placed on the SSRS report server catalog. Because of this, the SSRS report server catalog load can interfere with other processes and vice versa. For example, common environment setups include the following:

Same server as SSRS Windows/Web Services

The default one-box setup usually has the SSRS Windows/Web Services and the report server catalog on the same server. While this works great for small environments, in enterprise environments it causes far too much resource (CPU, memory, and disk) contention between the SSRS Windows/Web Services and the report server catalog. As well, when you scale out and add more SSRS servers, you want to have all of SSRS servers point to one dedicated report server catalog to reduce contention.

Same server as your data source relational database (SQL)

Another common approach is to place your SSRS report server catalog on the same server as your SQL Server data source. The issue here is that you will have SQL resource contention (tempdb, plan cache, memory, buffer pool, etc.) between your SQL Server data source and your SSRS report server catalog. As you have more concurrent users, you will have a lot of hits to the SSRS report catalog (RSDB for report metadata, RSTempDB for report snapshots) and transactions against the relational data source. As the load increases, it will become necessary to monitor things like CPU utilization, I/O response times, network resources, and buffer pool counters to ensure that adequate resources are still available. A common method to alleviate these issues is to separate the SSRS report server catalog from your SQL server data source.

As you can see, these two common scenarios create database resource contentions that slow down performance. Because of this, it makes sense to have a dedicated server for your SSRS report server catalog so that you can tune your report server databases separately from your relational data source and not run into SSRS Windows/Web Services and report catalog resource contention.

High-Performance Disk

Because your SSRS report server catalog has a lot of transactions, ultimately there will be a lot of disk I/O so that storage may be your resource contention. Because of this, you want to have a high‑performance disk such as a SAN or high‑RPM direct‑attach storage for your report server catalog. Some more specifics:

To optimize your disk I/O, see the SQL Server Best Practices white paper Predeployment I/O Best Practices, which provides great information on how storage works and how to use various tools to understand what performance you may be able to obtain for SQL Server from the storage you have.
Have more smaller sized disks with faster rotation speeds (e.g. >= 15,000 RPMs) rather than fewer larger sized disks with slower rotation speeds. That is, you should size your I/O based on IOS requirements instead of sizing based on capacity.
Maximize and balance your I/O across all of the available spindles.
Use separate disks for RSDB and RSTempDB. The profile for your RSDB is a lot of small transactions because it asks for report metadata. Your RSTempDB will have a lot of transactions as well, but they will be larger transactions because this database contains the report data. Having separate disks will enable you to tune your disk for the RSDB and RSTempDB databases separately.
Pre-grow your SSRS report server catalog databases instead of having SQL Server perform autogrow on these databases. RSTempDB can grow very quickly under load and the default autogrow options lead to a lot of file system fragmentation and blocking during the autogrow task.
For your SSRS server catalog databases, stripe your database files to the number of server cores at a ratio of 0.25 to 1.0 depending on how heavy your workload is. This enables you to minimize database allocation contention and makes it easier to rebalance your database when new LUNs become available.
If you are using RAID, like many other SQL implementations that are write-intensive, use RAID 10 to get the best performance. Do not use RAID 5 because of the write penalty that may be involved.
Monitor disk response times to ensure that disk latency is generally lower than 20ms, ideally 10ms, and log latency is no more than 1-5ms. To do this, look for high wait times on PAGEIOLATCH_xx or use sys.dm_os_virtual_file_stats to monitor response times specifically on SSRS‑related databases.

Move to 64-bit

For starters, if you need to stay with 32-bit and have >3 GB of memory because of the available hardware and OS, remember to use the /3GB and/or the /PAE (for systems with >4 GB of memory) switches (for the OS) and enable AWE in SQL Server so that it can use more than 3 GB of memory. Note that AWE can only be used for data cache. Do not forget that this involves both SQL Server changes (configure advanced options) and changes to the Windows OS boot.ini file.

We suggest moving to 64-bit because much of the hardware available right now is 64-bit and, as of SQL Server 2005, SQL Server itself natively supports 64-bit. With 64-bit, you have a much larger addressable memory space to use—especially if you increase the amount of memory. This means that you can handle larger queries (more data) and handle more connections to the server running SQL Server. Note that this does not result in higher throughput as that is typically bound to CPU. Nevertheless, the ability to handle more connections and larger reports minimize the chance that your report server catalog will be a bottleneck for your system. As well, the ability for 64-bit to scale is much higher than 32-bit and this is the platform of choice for SQL databases going forward.

Backup/Restore

The data in RSTempDB is highly volatile—typically one can expect its lifespan to be approximately equal to the SessionTimeout value configured for the SSRS server for most reports and viewing and usage time. The default SessionTimeout is 10 minutes, which is the report lifetime policy that defines when data can be cleaned up. The CleanupCycleMinutes value is the parameter that guides the background cleanup thread. Once the session timeout value is reached, we clean up the temporary snapshot from tempdb. We do that every cleanup cycle minutes, or continuously if the previous cleanup didn’t complete yet. The actual lifespan varies based on usage patterns but a lifespan longer than one day would be rare. As such, it is not necessary to protect RSTempDB data for data recovery purposes.

The data in RSDB is long lived—this data should be backed up following the standard guidance provided for SQL Server:

Backing Up and Restoring Databases in SQL Server

Optimizing Backup and Restore Performance in SQL Server

As well, do not forget to back up (and restore) the encryption key associated with these databases; you can find more information at Backing Up and Restoring Encryption Keys

Maintain Your SSRS Report Server Catalog Databases

Recall that the SSRS report server catalog databases are SQL Server databases specifically for Reporting Services usage. Therefore, the standard techniques to maintain SQL databases apply to the SSRS report server catalog databases. For example, periodically re-indexing the catalog tables and/or updating the database statistics may improve query performance.

As noted above, you may want to consider configuring the CleanupCycleMinutes setting in the RSReportServer.config file. This setting determines how frequently expired session content or unused snapshot data is removed from RSTempDB. The default setting is 10 minutes, which is similar to the default session timeout. RSTempDB generally stays more compact when using frequent cleanups, but at the cost of increasing the general load on the system. If the size of RSTempDB not a major concern and the system has high throughput user loads, you may want to considering slightly increasing the CleanupCycleMinutes configuration (such as setting it to 20 minutes).

Discussion

Since the SSRS Windows/Web Services interact with your report server catalog for almost all SSRS queries, it is important that you optimize your SSRS catalog databases so that they are not a point of contention. Standard SQL optimization techniques come into play here since SSRS report server catalogs are instances of SQL Server databases. Following the above suggested methods will make your SSRS environment easier to scale to enterprise data loads.

Recall that because of the concern for report consistency, it is important for Reporting Services to cache all report data. Therefore, to reduce the number of requests that are placed on your report server catalog, you may want to consider using the File System (FS) Snapshots feature, which is discussed in the next technical note of this technical series—SSRS Scale-Out Deployment Best Practices [work in progress].

Reporting Services Scale-Out Architecture

denny.lee — Thu, 05 Jun 2008 18:59:00 GMT

Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series

Authors: Denny Lee, Lukasz Pawlowski
Contributors: David Reinhold, Robert Bruckner
Technical Reviewers: Burzin Patel

Introduction

This technical note is the introduction to the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series, which provides general guidance on how to set up, implement, and optimize an enterprise scale-out architecture for your SQL Server Reporting Services (SSRS) environment. This note provides guidance for both SQL Server 2005 and 2008 Reporting Services. The focus of this technical note is the Reporting Services scale-out architecture, which is referenced throughout this technical note series.

Architecture

Figure 1 represents a typical scale-out Reporting Services environment.

Figure 1: Reporting Services Scale-Out Architecture

From this architecture, you will notice the following:

When your clients execute a query to Reporting Services, the query goes through a Network Load Balancer (NLB) to connect to the appropriate Reporting Services server. In order to balance the query load, your NLB can round-robin between the different SSRS servers so that the requests are evenly distributed amongst all the SSRS servers and a single server instance is not overburdened by too many requests.

The Reporting Services Windows/Web Services (denoted as RS Server) handle the client query request and perform the task of submitting the query to the report server catalog and/or the assigned relational data source, as well as resolving the data results back to the client. In enterprise environments, you will often want to scale out to multiple servers to handle many concurrent queries against your Reporting Services servers.

The RS Server makes a request to the report catalog databases initially to obtain the report metadata, which includes report definitions, report history and snapshots, and scheduling information from the ReportServer database (RSDB). If the report data desired is already available in a SSRS snapshot, the RS Server makes a request for this from the RSTempDB database, which stores all of the temporary snapshots while reports are being run. As noted in the above architecture design, in an enterprise environment you have multiple SSRS servers reading metadata information from the same report catalog instance. This is done to keep state information between SSRS servers; when a user requests a report from one SSRS server, if the subsequent query goes to another SSRS server, the state of that report (which report was used, what parameters were used, etc.) is kept so that the transition from one SSRS server to another is transparent to the user requesting the report.

If the report information is not available in the form of a SSRS snapshot, the SSRS server makes a request to the data source (denoted as Reporting Data). Whether your data source is a flat file or an SQL database, how this data is requested is defined by the parameters in the report catalog to enable the SSRS server to perform the task of requesting this data.

Keys to Success

To ensure a successful enterprise implementation of your Reporting Services environment, please note first that a lot of documentation is available online. Many mistakes in enterprise customer implementations can be avoided by reviewing the following white papers.

Upgrading Reporting Services (SQL Books Online): This a good reference to understand the issues related to upgrading from SQL Server 2000 Reporting Services to SQL Server 2005 Reporting Services. You can find the documentation for upgrading from SQL Server 2005 Reporting Services to SQL Server 2008 Reporting Services at Upgrade (Reporting Services), though this is pre-release documentation at this time.

Configuring a Report Server Scale-Out Deployment: Before starting a scale-out deployment for Reporting Services, please be sure to review this document, which includes deployment steps, creating and configuring SSRS instances, and other important configuration steps.

Planning for Scalability and Performance with Reporting Services: To plan for scalability and optimal performance with Reporting Services, this is a very important white paper for you to review. Before you design your scale-out solution, we definitely advise you to review this paper.

Customer Scenario

Below is an example customer scenario that has been shrunk down to its core components.

Figure 2: Customer Scenario

This customer currently has:

Four SSRS servers that keep state information by connecting to a separate server that contains both report server catalog databases (RSDB and RSTempDB).

The four SSRS servers connect to two SQL Server Analysis Services (SSAS) servers to provide pivot capability in the form of multidimensional reports.

Their two SSAS servers are connected to a single relational data source; they have two SSAS servers in order to handle the query loads placed on SSAS servers. For more information on scaling out SSAS servers, see:

To help handle the high query load, the customer also made use of:

Use of a dedicated RSDB server for the Reporting Services database instances to reduce database blocking issues due to resource contention. For more information, see Report Catalog Best Practices within this technical note series.

SSRS File System snapshots to make use of the cache; more information on this can be found in SSRS Scale-Out Deployment Best Practices [to be published] within this technical note series.

With this setup, they were able to use Visual Studio Test System to verify that the above configuration could handle 1,800 concurrent users with a 10-sec think time (time delay between the execution of each user’s reports) and an average transaction time of 35 sec.

Performance Testing

Another key component to ensuring a successful enterprise implementation is the ability to determine the query load prior to actually placing it into production. It is important that you understand your reporting scenarios and the data that needs to be delivered so that you can better plan capacity (hardware, resources, etc.). The above customer executed tests to make sure they could handle their expected maximum workload of 1,800 concurrent users. This allowed them to better plan for hardware capacity—and they realized that they did not need to build out more SSRS servers to handle their workload.

Your report scenarios are defined by user personas and usage patterns. For user personas, you need to understand the type of users who are using your reports, whether it is the director who typically wants high-level summary statistics or the analyst who needs detailed reports. Understanding usage patterns enables you to know if you have users who typically want to review a few sets of reports or want to heavily interact (drill down / up / through) with these reports. Once you understand your typical report scenarios, it is important for the purpose of performance to use actual reports when you test. If you do not have these, your test reports should closely reflect what users will actually be querying. As well, any and all tests that you do should isolate Reporting Services from other systems. This is implied from the above architecture diagrams, but a common issue is that customers run performance tests on shared systems causing both performance degradation and making it difficult to isolate problems.

To run performance tests, a good tool to use is Visual Studio Team System (VSTS)—and a great reference is Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server.

Customer Scenario Testing

Our customer was able to use and define within VSTS for their tests:

Mean transaction time range: 33-36 sec

Think time: 10 sec

The configuration of the tests involved the use of a single test controller (where VSTS would execute and control the tests) that used four 32-bit agents, which went against the SSRS servers. The SSRS servers all used the same hardware with 8-GB RAM with four cores.

To define the report query workflow, they used a predefined workload where 75% was simple and 25% was complex using a randomized selection of report parameters. An example of a workflow is:

Step 1

Open list of reports
Think time

Step 2

Open report by using default parameters
Report renders
Think time

Step 3

Change parameter
Report renders
Think time

Figure 3: VSTS workload to determine number of servers to maximum number of concurrent users for sustained time period (>= 15min) on the SSRS server

For this specific customer’s tests (i.e., their workload and their data), you can see from Figure 3 that one SSRS server could handle a maximum of 608 concurrent users for a sustained time period (>=15 minutes). By scaling out the SSRS servers, they also found that they could handle 1,218 concurrent users with two servers and approximately 2,300 concurrent users with four servers.

From this specific test scenario (four servers with this specific workload and tests), they also noted that with >2,000 concurrent users there was a higher rate of failed tests (SSRS queries executed with no results or errors returned). But at 1,800 concurrent users (the expected maximum workload), the rate of failed tests was at an acceptable level. If they wanted to handle a larger number of concurrent users, they would only need to scale out the number of SSRS servers further. Also of interest in their environment was that doubling the memory and CPU of their hardware (increasing to 16-GB RAM and eight cores) resulted in only a 1/3 increase in load capacity. Note, SQL Server 2008 Reporting Services should make better use of resources when adding more cores and memory. Therefore, it made sense for them to scale out (by increasing the number of servers) instead of scaling up (by adding more memory and CPUs to the servers) to handle higher workloads.

Please note, the results of these tests might not apply to your environment. These test results are specific for this customer scenario according to their workload, hardware, and scenarios. Nevertheless, you can see the importance of doing performance testing within your Reporting Services environment so you can more accurately predict the load it can handle.

Next Notes

As noted in the above section, this is the first technical note of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series. While this technical note provides the high level architecture, we also provide some additional guidance:

Report Catalog Best Practices: Provides guidance and best practices on the report server catalogs—the underlying databases that provide metadata (parameters, snapshots, history, etc.) used by Reporting Services to provide your reports.

SSRS Scale-Out Deployment Best Practices [link provided when published]: Provides guidance and best practices on deployment details for scaling out your Reporting Services environment including configurations and the use of File System snapshots.

SSRS Performance Optimization Configurations [link provided when published]: Provides guidance and best practices on using specific Reporting Services features and configurations to optimize the performance of your Reporting Services environment.

SSRS Troubleshooting Tips [link provided when published]: Provides various troubleshooting tips that are helpful to better understand issues that may occur within your Reporting Services environment.

Special Thanks

We’d also like to thank Craig Utley, Brian Welcker, Jim Carroll, Bill Emmert, and Nicholas Dritsas for their original contributions.

How Microsoft IT Leverages SQL Server 2008 SSIS Dataflow Engine Enhancements

denny.lee — Wed, 04 Jun 2008 05:47:00 GMT

The Information Security Consolidated Event Management (ICE) system is a more than 30-terabyte data warehouse used by the Microsoft Information Security team to analyze network utilization events captured by various sources, including over 100 proxy servers, mail servers, Net logon servers, etc. The ICE database processes approximately 1 terabyte of log data each day and it has become a key component in the incident response process, in addition to forensics investigations. Analysis of the proxy data has empowered the Microsoft Information Security team to identify and remediate numerous security issues that would have gone undetected otherwise.

ICE version 4.0 is an ambitious project set to deliver almost real-time data and high-query performance to the security team using Microsoft SQL Server 2008. Moreover, ICE 4.0 is also designed to perform all sorts of data filtering and transformation during the data-loading process, so the schema of data stored in ICE is tailored for investigation analysis/reporting needs. An Online Analytical Processing (OLAP) cube is built on top of the ICE data warehouse to facilitate aggregated queries. Join this session to learn how enhancements to the Microsoft SQL Server Integration Service (SSIS) 2008 dataflow engine have significantly improved the performance of loading, filtering and transforming 1 terabyte of network log data into the ICE data warehouse.

You can download the interviews here: WMA | MP3 Hi | MP3 Low

Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

CarlRabeler — Sat, 03 May 2008 19:29:00 GMT

Brief Description

This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique.

Overview

Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group).

http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

Tuning the Performance of Backup Compression in SQL Server 2008

SanjayMishra — Mon, 21 Apr 2008 19:37:00 GMT

Authors: Mike Ruthruff, Sanjay Mishra

Contributor: Steve Schmidt

Technical Reviewers: Kevin Farlee, Sunil Agarwal, Burzin Patel, Lindsey Allen, Mark Souza, Mark Tomlinson, Denny Lee, Prem Mehra, Alexey Yeltsov, Yuriy Toropov, Glenn Berry (SQL Server MVP)

Overview

Backup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. This document provides guidance related to tuning options for backup performance. All of the information and test results presented here were done specifically by using the backup compression feature of SQL Server 2008; however, they apply broadly to any backup scenario whether backup compression is used or not. They also apply to restore operations; however, restore will not be covered in depth in this document. For an introduction to the backup compression feature, see Backup Compression, in SQL Server Books Online.

Benefits of Backup Compression

One major benefit of backup compression is space saving. The size of the compressed backup is smaller than that of the uncompressed backup, which results not only in space savings, but also in fewer overall I/O operations during backup and restore operations. The amount of space you save depends upon the data in the database, and a few other factors, such as whether the tables and indexes in the database are compressed, and whether the data in the database is encrypted. To determine the effectiveness of backup compression, the following query can be used:

SELECT
b.database_name 'Database Name',
CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)'
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
ORDER BY
b.backup_finish_date DESC

Table 1 shows the output of the above query after taking an uncompressed and a compressed backup.

Database Name	Uncompressed Backup Size (MB)	Compressed Backup Size (MB)	Compression Ratio	Backup Time (Seconds)	Comments
BCTEST	292705	95907	3.05	1705	Compressed backup
BCTEST	292705	292705	1	3348	Uncompressed backup

Table 1: Comparing compressed and uncompressed backup

For an uncompressed backup, the compression ratio is 1 and the Compressed Backup Size reports the same value as the Uncompressed Backup Size. The higher the compression ratio, the greater the space saving. For our test database, we achieved a compression ratio of 3.05 and saved approximately 67% space for the backup. Figure 1 illustrates the benefits of backup compression in terms of space and time. Databases using the Transparent Database Encryption (TDE) feature of SQL Server 2008 may not see as high backup compression ratios (in most cases, it will be close to 1) due to the fact that encrypted data does not lend itself well to compression.

Figure 1: Benefits of backup compression in terms of space and time

As illustrated in Figure 1, a compressed backup is smaller in size, and hence requires fewer write I/Os to the backup media. This results in reduced backup time. Backup is an I/O intensive operation so reduction in I/O will be beneficial to performance.

Test Workload and Test Environment

Our test environment for these results consisted of a database representing an OLTP stock trading application with a database size (excluding free space) of approximately 300 GB. The hardware used for the testing was a 4-socket dual core DELL 6950 server and an EMC Clariion (CX700) storage array with 4GB of cache allocated 80% to write operations. The volume/LUN layout is shown in Table 2.

Volume	Purpose	RAID Level	Number of Disks
E:	Data files	1+0	8
F:	Data files	1+0	8
G:	Data files	1+0	8
H:	Data files	1+0	8
L:	Log files	1+0	8
M:	Backup files	1+0	8
P:	Backup files	1+0	2
Q:	Backup files	1+0	2
R:	Backup files	1+0	2
S:	Backup files	1+0	2

Table 2: Disk volume layout for backup compression tests

In each of the disk volumes shown in Table 2 there was no sharing of physical disks between any of the volumes. There were 32 total disks for the data, 8 for the log and 16 for the backup files.

SQL Server data files were striped such that each filegroup contained four data files—each file of a filegroup was placed on a separate data volume.

Determining Achievable Throughput of Backup Compression

Whenever you attempt to tune the performance of backup compression, it is important to first understand the achievable theoretical throughputs of the given hardware configuration, specifically the storage. The throughput of backup compression is bound by the CPU resources, the throughput of the input devices, or the throughput of the output devices.

One method of determining the throughput of a given configuration is to perform backup to a NUL device multiple times by varying the BUFFERCOUNT setting. Use the default BUFFERCOUNT first, and then explicitly specify BUFFERCOUNT in subsequent tests—each time increasing BUFFERCOUNT to a higher value. For example:

BACKUP DATABASE [BCTEST] TO DISK = 'NUL' WITH COMPRESSION

BACKUP DATABASE [BCTEST] TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 50

Default BUFFERCOUNT is determined dynamically by SQL Server based on the number of database volumes and output devices. Default values for BUFFERCOUNT are chosen so they will apply to a broad range of environments which is why tuning these on high end systems may be necessary to obtain optimal performance.

More information on tuning the BUFFERCOUNT parameter will be given later in this document. The BACKUP section in SQL Server Books Online also contains related information.

At some point during the above tests, you will observe one of the following, which will determine the limits of the hardware:

The total CPU utilization will be near 100%.
The disk throughput will remain constant, while the latency increases.

If total CPU utilization nears 100% across all cores, this is your bounding resource. If the total CPU is less than 100%, the observed throughput of the reads is your maximum achievable throughput. Once you have this value, you can then perform a backup to a real output device. If you do not achieve the same throughput attained by using BACKUP to NUL, write throughput to the output device is your bottleneck.

Figure 2 illustrates the throughput we achieved as we increased BUFFERCOUNT during our BACKUP to NUL tests.

Figure 2: Backup throughput to NUL device with varying BUFFERCOUNT

Similarly, the performance monitor graph in Figure 3 illustrates the fact that I/O on the read device is our bounding resource. This performance monitor log was run continuously across all iterations of our tests. Our achieved throughput was approximately 400 MB/sec before the read operations became I/O bound.

Figure 3: Disk throughput and latency during backup compression

Notice that near the intersection of the black straight lines there is a leveling off in the throughput (Disk Read Bytes/sec counter), while the latency (Avg. Disk sec/Read counter) continues to increase. This indicates a bottleneck on the I/O resources.

Ultimately, in our tests with a real output device this was the throughput we achieved, which means the bottleneck was on the I/O performance of our input devices.

Considerations for Tuning Backup Performance

Several considerations come into play when you attempt to increase the performance of compressed backup operations. The goal of our testing was to determine the highest throughput obtainable with the simplest configuration. Performance of backup operations in SQL Server is influenced by the following:

Database disk volume and backup device layout
Size of I/Os and number of outstanding I/O requests
Hardware configuration

Database Volume and Backup Device Layout

Database volume and the device layout have an impact on backup performance, irrespective of whether the backup is compressed. For backup operations, there is a single reader thread per database volume (drive letter or mount point volume), and a single writer thread per backup device. Figure 4 illustrates the performance differences of backup compression between the following configurations. In this article, a backup device is synonymous with a file.

Single backup volume (M) with a single backup device
Single backup volume (M) with four backup devices
Four backup volumes (P, Q, R, and S) with four backup devices

Figure 4: Backup compression performance versus number of backup devices

In Figure 4, BC refers to BUFFERCOUNT and MTS refers to MAXTRANSFERSIZE. These settings are discussed in more detail later in this document.

As shown in Figure 4, having multiple backup devices improves backup performance. We observed very slight improvement by having the multiple backup devices on separate volumes. This is likely because our test was bound by read throughput as illustrated in Figure 3.

BUFFERCOUNT and MAXTRANSFERSIZE were left at the default values for the test results in Figure 4. By default we observed MAXTRANSFERSIZE to be an average of 512 KB, and BUFFERCOUNT was 13 and 28 for 1 device and 4 devices respectively. SQL Server determines the default setting for BUFFERCOUNT based on the number of reader volumes and output devices. Using multiple backup devices results in an implicit increase in the BUFFERCOUNT value.

The increase in BUFFERCOUNT, in addition to the increased number of threads writing to the backup files explains why the performance of multiple devices on a single volume increases (described more later). Results using other values are shown later in Figure 6.

Figure 5: Parallelism influence on CPU utilization

The number of threads used for compression operations is dynamically determined and comes from the SQLOS thread pool. In our case, having either 1) increased BUFFERCOUNT with a single output device, or 2) multiple output devices, resulted in greater throughput and more concurrent compression operations. As shown in Figure 5, either of these increased parallelism and overall CPU usage across all CPUs in our server. A greater number of backup devices improves backup performance. However, it adds a management complexity. You have now multiplied the number of backup files to track. If you lose one of these files, the entire backup is useless.

Using backup compression results in higher CPU utilization when compared to uncompressed backups; this is due to the compression of the data. This cost is generally offset by the benefits of backup compression discussed earlier in this paper.

For backups that are executed concurrently with user workload, reducing the number of backup devices or BUFFERCOUNT may reduce the overall CPU used by the backup operation, leaving more CPU resources for the workload. This trade-off can be made to accommodate scenarios where CPU is a constrained resource that must be preserved for user workload.

Size of the I/Os and the Number of Buffers Used by Backup

In some scenarios, tuning the size of the I/O issued by backup / restore operations as well as the number of internal buffers used for the transfer of data may help increase throughput independently of the underlying volume / device layout. These are controlled by the MAXTRANSFERSIZE and BUFFERCOUNT options of the backup and restore commands. When tuning these, keep in mind the following:

Memory used for backup buffers comes from virtual address space, which resides outside the buffer pool. The potential amount of memory used for these operations is equal to MAXTRANSFERSIZE * BUFFERCOUNT. Care should be taken on 32-bit systems because specifying values for these settings that are too high may result in out-of-memory errors since there is a limited amount of virtual address space available for this. On 32-bit systems, the default amount of virtual address space outside of the buffer pool is 256 MB.
Choosing an initial value for BUFFERCOUNT can be difficult if you do not know the default value. This default value is dynamic and determined by SQL Server at run time. The default value is influenced by the number of backup devices and the number of database volumes. Testing is necessary to determine an optimal value. Running with trace flags 3605 & 3213 will result in output to the ERRORLOG containing the number of buffers used for backup operations.
With the exception of very small databases and snapshot backups of VLDB using VDI technologies, the default MAXTRANSFERSIZE is 1 MB.
Note: Snapshot backups taken by using VDI integrated storage solutions cannot use backup compression.
I/O size issued for backup / restore operations ranges from 64 KB up to MAXTRANSFERSIZE, based on the allocation of data within a data file. MAXTRANSFERSIZE refers to the size of the I/O operation issued to read data from the database files. Only contiguous regions of data on the disk are read. The continuity of the underlying data pages has an impact on the size of the I/Os. Therefore, the actual I/O size you observe may be less than the value of MAXTRANSFERSIZE specified. The default value for MAXTRANSFERSIZE is 1 MB.

Figure 6 extends the data in Figure 4 by including tuning of MAXTRANSFERSIZE and BUFFERCOUNT. Figure 6 shows backup time and CPU utilization for six different configurations. As illustrated in Figure 6, throughput comparable to that attained by using multiple devices can be achieved when using a single device on a single LUN by adjusting the MAXTRANSFERSIZE and BUFFERCOUNT parameters.

Figure 6: Backup time and CPU utilization (average for all CPU’s) versus device/volume configuration, MAXTRANSFERSIZE and BUFFERCOUNT

Although tuning MAXTRANSFERSIZE may result in slightly higher throughput, it also results in much larger I/O sizes. Larger I/O sizes of the backup operation when mixed with a concurrent OLTP workload may result in slower overall I/O response times. The recommended tuning approach is to adjust BUFFERCOUNT first, and then determine if tuning MAXTRANSFERSIZE will provide any additional performance advantage. Keep in mind when tuning this parameter that increasing the value may increase the size of the I/O with limited increase in overall throughput. Using multiple output devices as opposed to tuning these parameters may be a simpler approach to achieving greater throughput. However, this also may introduce the additional management overhead of having to maintain multiple backup files. Due to time constraints, we did not test all configurations in our scenario.

Figures 7 shows the read and write throughput attained during these tests. The write throughput is less than the read throughput because of compression.

Figure 7: Read and write throughput during backup operations

Hardware Configuration

Backup compression operations are parallelized dynamically up to the number of CPUs on the machine so there is a potential performance benefit to a machine with more physical CPUs. Similarly, the physical characteristics of the I/O configuration including the number of disks, cache memory available for I/O operations, and the throughput attainable on the I/O path, have a significant effect on performance. I/O operations related to backup / restore may ultimately be bound by the limits of the hardware. It is important to understand the potential throughput of your specific configuration in order to know when you are exhausting the capabilities of the hardware.

Recommendations

This article provides a good amount of detail into the internals of backup compression. A summary of our recommendations for tuning backup compression and restore are:

Use the method of backing up to NUL to determine the potential throughput attainable on your particular configuration.
Performance of backup operations may be increased by utilizing multiple backup devices and/or increasing BUFFERCOUNT. Using multiple devices over a single device with increased BUFFERCOUNT may offer slight additional benefit; however, use of a single device with increased BUFFERCOUNT may provide nearly the same throughput without the need to manage multiple backup files.
Keep in mind that adjusting backup parameters to increase the performance of backup may have adverse impact to user workload running on the system. Always consider what is best for your particular scenario.
MAXTRANSFERSIZE should be considered a secondary tuning approach. In our tests this provided little practical benefit over the default used by BACKUP
Consider the parallelism benefits of volume design on backup / restore operations at database creation time. This is one of the many things to consider when determining how many volumes to use for a SQL Server database.
Monitor your I/O and CPU utilization during tests to ensure that any performance bottlenecks you encounter are not hardware related.
Be careful while increasing the value of the BUFFERCOUNT parameter on 32-bit systems because specifying too high values for this may result in out-of-memory errors, since there is a limited amount of virtual address space available for this. On 32-bit systems, the default amount of virtual address space outside of the buffer pool is 256 MB.

Keep in mind that the results described here indicate the performance that is attainable in one specific configuration. Performance may be much higher or lower in different configurations. As an additional data point, Backup More Than 1GB per Second Using SQL2008 Backup Compression on the SQLCAT blog illustrates the performance attainable on very high-end systems.

Summary

There are many approaches to tuning the performance of backup in SQL Server 2008. Understanding how volume and device layout, and number of CPUs influence parallelism, as well as understanding other tuning options can provide you with the ability to significantly increase the performance of backup and restore. The decisions on which approach to use depend largely on what is optimal for your given environment.

Appendix A: Test Hardware and Software

Server

DELL PowerEdge 6950

4 socket dual core
AMD Opteron 2.8 GHz
x64
32 GB RAM

Storage

EMC Clariion CX700

10K SCSI drives
4 GB cache (80% write, 20% read)
2 HBA’s

Software

Windows Server 2003 Enterprise Edition Service Pack 2 x64
SQL Server 2008 February CTP x64

Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart

SanjayMishra — Fri, 18 Apr 2008 23:21:00 GMT

Author: Sharon Bjeletich

The following flowchart will address the majority of the most common performance and scalability issues that can arise in SQL Server-based applications.

http://sqlcat.com/files/folders/280/download.aspx

However, working through the flowchart is also recommended for applications that do not appear to have issues in those areas, as it will set a baseline for a system with a known performance and scalability characteristic. The flowchart is designed to be worked from the top down, left to right. It addresses the most impactful issues first, such as table scans, out of date statistics, etc. and the suggested readings are considered the basics for any DBA.

Please note that to be able to accurately use this flow chart you should put your system under load, and it should only be done in a test environment. For more information on how to load test SQL Server see Visual Studio Team System documentation.

A Look Inside SQLCAT (Customer Advisory Team)

denny.lee — Fri, 18 Apr 2008 22:10:00 GMT

On this episode of TechNet Radio, we learn more about SQL CAT (Customer Advisory Team). With the launch of Microsoft SQL 2008 on the way, the SQL CAT team has been working hard preparing lists of best practices, recommendations, technical white papers and technical end-to-end case studies on customer implementations.

You can download the interviews here: WMA | MP3 Hi | MP3 Low

Technet Radio Interview with Mark Souza

denny.lee — Fri, 18 Apr 2008 17:43:00 GMT

A Look at TechNet Edge

On this episode of TechNet Radio, we get the scoop on the new forum for IT Pros – TechNet Edge. We also feature highlights from the recent Vista SP1 Roundtable hosted by Mark Russinovich. Plus, we have a conversation with Mark Souza from SQL CAT (Customer Advisory Team).

You can download the interviews here: WMA | MP3 Hi | MP3 Low

Data Mining applied to medical research

denny.lee — Fri, 18 Apr 2008 17:36:00 GMT

During the PASS 2007 Community summit in Denver, Zach Owens had interviewed Denny Lee from the SQL CAT team to discuss data mining tools and techniques being applied to academic research conducted on Asthma patients. For more information, check out the webcast on Channel 8.

Analysis Services Distinct Count Optimization

denny.lee — Thu, 17 Apr 2008 21:40:00 GMT

SQL Server Best Practices Article

Published: April 17, 2008
Writer: Denny Lee
Contributors: Richard Tkachuk, Akshai Mirchandani, Sasha (Alexander) Berger, Thomas Kejser, Stuart Ozer
Technical Reviewers: Steve Pontello, Lindsey Allen, Lubor Kollar, Nicholas Dritsas, Ricardo Silva, Dave Wickert, Anne Zorner

Applies To: SQL Server 2005 SP2
Note: The tests in this white paper were performed using SQL Server 2005 SP2; however, they also apply to SQL Server 2008.

Distinct count (such as unique visitor counts on a Web site) calculations provide valuable information but come with a number of performance challenges. This white paper describes tests that were performed to determine how best to optimize these calculations and includes best practices based on the test results.

For more information, please refer to the Analysis Services Distinct Count Optimization whitepaper.