Technical Notes

Initializing a Transactional Replication Subscriber from an Array-Based Snapshot

premmehra — Mon, 04 May 2009 18:46:00 GMT

Author: Benjamin Wright-Jones
Contributors: Ken England, Prem Mehra, Mike Ruthruff, Greg Yvkoff, Qun Guo and Gopal Ashok
Technical Reviewers: Joseph Sack, Jimmy May, Paul Randal (SQLskills.com), Dennis Tighe and Glenn Berry (SQL Server MVP)

Overview

This article describes how to initialize a transactional replication Subscriber from an array-based snapshot rather than using the native SQL Server snapshot mechanism. Initializing the Subscriber using a SAN-based restore solution is particularly beneficial for very large databases. In this context, I use the term VLDB to mean a database that is typically multi-terabyte and requires specialized administration and management This is primarily because the standard transactional replication initialization process, which is typically restricted by either the network or storage I/O bandwidth, could take longer than the business service-level agreement (SLA) permits because of the time needed to initialize or recover the Subscriber. In contrast, initializing a Subscriber using an array-based snapshot utilizes the Virtual Device Interface (VDI) freeze and thaw mechanism, thereby minimizing recovery time. This procedure is also particularly beneficial in non-production environments that use transactional replication and require repeatable tests with large volumes of data.

Scope

This procedure was performed using Microsoft SQL Server 2005 Enterprise Edition IA64 with Service Pack 2 (SP2) and cumulative update 9 running on Windows Server 2003 Datacenter Edition IA64. The procedure is expected to be identical in SQL Server 2008; however, this was not tested during the exercise. The storage array was provided by Hitachi Data Systems (HDS), and HDS Split Second was used to manage the array-based snapshots (backup and restore of the databases). VERITAS Storage Foundation HA software was used for volume management.

It should be emphasized that even though the hardware listed above was used, the principle of initializing a Subscriber from an array-based snapshot can be performed using other storage array network (SAN) vendor technologies – specific implementation details will vary. Microsoft recommends that customers attempting this procedure work closely with an engineer from the storage vendor to ensure the solution are implemented correctly.

When Is This Technique Useful?

There may be situations where the Publisher, Distributor, and Subscriber need to be restored after data loss. This technique:

·

Minimizes the transactional replication setup time for the Subscriber through the use of the underlying Virtual Device Interface (VDI) storage mechanisms, which reduce the time required to back up and restore large volumes of data.

· Supports repeated benchmark tests to re-establish a test baseline.

· Provides rapid recovery of the Subscriber if data loss has occurred and the database(s) need to be recovered from a point outside of the distribution retention period.

Background

Transactional replication has been available as a feature in SQL Server since version 6.0. Available functionality has grown since this time to include tracer tokens to measure latency, concurrent snapshot processing, and peer-to-peer replication. However, the general premise has remained the same: to replicate a copy or subset of the data to another database. The Publisher, Distributor, and Subscriber terminology is used to describe the nodes within the topology. For more information about replication, see SQL Server Books Online.

A transactional replication Subscriber can be initialized using one of the following mechanisms:

a) Transactional replication concurrent snapshot processing

b) Database snapshot (this requires SQL Server 2005 Enterprise Edition with Service Pack 2)

c) Initialize from log sequence number (LSN) (SQL Server 2008 only)

d) SQL Server backup (initialize from backup)

e) Copy of the data or array-based restore

A summary of the pros and cons for each technique is presented in a table below.

Option (a) – Transactional replication concurrent snapshot processing does not require an outage, and it allows production activity to occur on the Publisher while the initialization process is copying the schema and data to the Subscriber. Concurrent snapshot processing does not hold shared locks during snapshot generation, thereby allowing production activity to continue. However, a schema modification lock (Sch-M) is taken for a brief period. In contrast to option (d) and (e), this procedure also has the benefit of only copying the schema and data that is required rather than making a complete copy of the database.

Option (b) – SQL Server 2005 Enterprise Edition with Service Pack 2 introduced a new snapshot mechanism that permits the initialization of the Subscriber from a database snapshot. The database snapshot functionality was introduced in SQL Server 2005 to provide a read-only static view of the database using sparse files. Before a page is updated in the source database, the original page is copied to the sparse file. Subsequent updates to the same modified page do not prompt a repeat of this procedure. That is, the pre-change copy of a particular page only pushes into the database snapshot once after the database snapshot is created. It is possible to initialize a transactional replication Subscriber from a database snapshot as described above. This procedure is similar to the concurrent snapshot processing described in option (a) in that it permits transactional activity during the initialization procedure. However, although a database snapshot utilizes the sparse file capability of NTFS, with a lot of concurrent updates, the database snapshot may grow. It is important to ensure that sufficient storage space is allocated for the database snapshot sparse files to store data pages that have been updated during the Subscriber initialization period, because the database snapshot stores the pre-updated page images.

Option (c) – Initialize from LSN was introduced in SQL Server 2008 to aid the configuration of peer-to-peer transactional replication topologies. Initializing from an LSN can also be used in disaster recovery scenarios; instead of performing a full re-initialization of the Subscriber database, you can initialize a Subscriber from an LSN. This means that the Distribution Agent will apply transactions (after the supplied LSN), from the distribution database to the Subscriber, as long as the distribution retention period has been set appropriately.

Option (d) - A Subscriber can also be initialized from a SQL Server backup. For more information, see “Initializing a Transactional Subscription without a Snapshot” in SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms151705.aspx). This approach restores the complete dataset on the Subscriber and does not require an outage for the initialization, as long as the distribution database stores the in-flight transactions that were taken after the backup. Post-configuration administrative procedures may be required to remove unwanted objects and data on the Subscriber database.

Option (e) - It is also possible to initialize the Subscriber using a copy of the data. The database can be provisioned using any mechanism that will copy the Publisher schema and data to the Subscriber, such as a manual network file copy of the data and log files, a native SQL Server restore, or alternatively, an array-based restore. While this approach does require an outage during the initialization process, an array-based restore is particularly beneficial for very large databases (VLDBs) in either production or benchmark environments where the setup time can be minimized by using the VDI mechanisms. This technique was used to initialize the Subscriber from a HDS-array based snapshot, which is the focus of this article, and is discussed in more detail in the rest of this article. For more information about VDI, see “SQL Server 2005 Virtual Backup Device Interface (VDI) Specification” (http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

The table below summarizes the pros and cons of the transactional replication snapshot techniques.

Initialization technique

Online

Pros

Cons

Use when

Concurrent snapshot processing

Yes

Available in Workgroup, Standard, and Enterprise editions, allows online processing, generates a snapshot only for published objects.

Duration of Subscriber initialization is impacted by size of database, network, and so on. DML statements can be expensive.

Online processing is a requirement, storage space is limited on the Subscriber.

Database snapshot

Yes

No locking on the Publisher database, in comparison to concurrent snapshot processing.

Enterprise Edition only. Requires sufficient storage for the snapshot sparse files.

Online processing is a requirement, concurrency is essential, sufficient storage exists for the sparse file.

Initialize from LSN

Yes

Full snapshot not required, allows online processing.

SQL Server 2008 Enterprise only, must be able to determine correct LSN.

Only consider if a full snapshot is not feasible and not necessary. May also be suitable for environments that use database mirroring and transactional replication.

SQL Server Backup (initialize with backup)

Yes

Full snapshot not required, allows online processing.

Restores a complete copy of the database on the Subscriber (for example, requires more storage space) and persists copies of all objects unless removed.

Data volume is manageable and offline initialization is not an option. Speed of backup and restore is preferable to use of the BCP utility to copy data.

Copy of data /
array-based restore

Rapid backup/restore of large data volumes, replication snapshot is not required.

Offline processing, must stop transactional activity, requires a complete copy of the data on the Subscriber.

Volume of data is in the terabyte range and backup and restore times are a priority.

Pros and Cons for Transactional Replication Initialization Techniques

Setup Procedure – Initializing the Subscriber from an Array-Based Snapshot

The procedure to initialize the Subscriber from a copy of the data is similar to using the native SQL Server restore mechanism. However, the array-based approach is more useful for large databases, because hardware implementations of the VDI freeze and thaw mechanism allow large amounts of data to be copied quickly and in a transactionally consistent manner, thereby reducing the restore time.

High-Level Steps

The following procedure was used to initialize the Subscriber during a high-end benchmark with a multi-terabyte database. The backup and restore of the database was performed using the HDS array capabilities. This includes the use of HDS Split Second, which is a command-line utility developed by Hitachi Consultancy Services. We also tested a full database backup operation with SQL Server 2005. However, this was simply to benchmark the operation for a 17-terabyte database.

It is important to note that application activity must be paused during this operation, because any change in data at the Publisher will result in data inconsistency. This inconsistency will be raised as an alert in Replication Monitor. One can use tablediff, a command line utility that returns detailed information about the differences between two tables. It can also generate a Transact-SQL script to bring a subscription into convergence with data at the Publisher. This utility can be used to correct data inconsistencies; however, it is recommended that precautionary steps be put in place to ensure that the application or any other activity cannot write to the Publisher or Subscriber databases during this procedure. These steps are discussed later. Alternatives to this approach that allow transactional activity during the setup procedure include concurrent snapshot processing, initialization from backup, and initialization from a database snapshot. Generating a replication snapshot for this volume of data would take many hours, so we elected to initialize the Subscriber using an array-based restore in order to minimize the movement of data.

Initializing the Subscriber

The following steps were used to initialize the Subscriber from the array-based restore. The majority of the time was consumed by the backup and restore procedures using HDS Split Second. This was approximately two hours. The other activities were DBA operations to modify and validate the replication metadata.

1. Pause application activity.

2. Disable the application login(s) using ALTER LOGIN <login> DISABLE.

3. Ensure the database is in RESTRICTED_USER WITH ROLLBACK IMMEDIATE mode to clear user connections that may still be in the database. Please note that RESTRICTED_USER does not prevent access to applications run under the context of sysadmin, dbcreater, or sysadmin.

4. Back up the primary (Publisher) database using a storage array-based mechanism.

5. Restore the primary (Publisher) database on a separate instance using the storage array-based mechanism. This database will become the Subscriber.

6. Recover the Subscriber database and check the SQL Server error log to verify completion of the recovery operation.

7. Set the RESTRICTED_USER mode on the subscription database to prevent any user activity. This is simply an additional precautionary step to ensure that applications or users cannot access the database. Please refer to step 3 above, because the same caveat applies.

8. Enable the NOT FOR REPLICATION value on the Subscriber tables (and any other objects that will be published for replication). The NOT FOR REPLICATION option enables you to specify which database objects are treated differently if a replication agent performs a transactional operation. For example, the identity column value is not incremented if a replication agent performs an insert operation. For more information, see “Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION” in SQL Server 2005 Books Online (http://msdn.microsoft.com/en-us/library/ms152529(SQL.90).aspx) or SQL Server 2008 Books Online (http://msdn.microsoft.com/en-us/library/ms152529.aspx).

9. Create the publication(s) on the Publisher.

10. Drop any redundant columns on the subscription database using ALTER TABLE DROP COLUMN. For our tests, binary large object (BLOB) columns were dropped, because we did not replicate columns of this data type (primarily to reduce the storage requirement on the Subscriber). We did not drop any other objects, because there were scenarios where we wanted to be able to view or query copies of nonpublished tables on the Subscriber.

11. Optional: Reclaim storage space on the Subscriber by using DBCC CLEANTABLE in the subscription database and by specifying a batch size to reduce the impact on the transaction log. In previous smaller volume tests, we also reclaimed the BLOB storage space, using DBCC CLEANTABLE with a batch size of 100,000. If a batch size is not specified, DBCC CLEANTABLE processes the whole table in one transaction and the table is exclusively locked during the operation. This can require considerable transaction log space for very large tables.

12. Create the subscriptions using the replication support only sync_type of sp_addsubscription. This is a key component of the process, because it indicates that the Subscriber already has a copy of the schema and data and does not require initialization with a replication snapshot.

13. Perform DBA checks. For this exercise, we used Transact-SQL scripts to ensure that the number of objects marked for publication was consistent with the number of objects marked for replication at the Subscriber. For example:

-- Count the number of columns with the NOT FOR REPLICATION option set to 1.

-- Execute this on the Publisher and Subscriber to ensure the count is consistent.

-- Investigate further if there is a difference.

USE <Publisher or Subscriber>

GO

SELECT COUNT(*) NOT_FOR_REPL_IDENT_Tables

FROM sys.identity_columns

WHERE is_not_for_replication = 1

AND OBJECTPROPERTY(OBJECT_ID, 'IsMSShipped') = 0;

Similar statements can also be executed for foreign keys, triggers, and constraints that may require this property to be set.

14. Enable the publication and subscription databases for MULTI_USER activity.

15. Enable the application login(s); for example, use ALTER LOGIN <login> ENABLE.

It is advisable to launch Replication Monitor (Sqlmonitor.exe) to verify that the publications are healthy and that the Log Reader and Distribution agents are not reporting any problems. Similarly, posting a tracer token will also provide a good idea of latency between the databases.

Restoring the Databases for Repeated Benchmark Tests – Initializing the Subscriber from an Array-Based Snapshot

There may be situations where the transactional replication databases need to be restored to a previous point in time, for example, in a benchmark environment to re-establish the baseline for repeated tests. This eliminates the need to re-create the replication objects and also synchronise the Publisher and Subscriber, which can be time-consuming for large databases.

The procedure is similar to that shown above, with the added advantage that SQL Server transactional replication has already been configured and is functioning correctly.

The following steps were used to initialize the transactional replication Publisher, Distributor, and Subscriber from the array-based restore prior to each test cycle.

1. Pause application activity.

2. Disable the application login(s) by using ALTER LOGIN <login> DISABLE; on both the publication and subscription databases.

3. Ensure that the Publisher database is in RESTRICTED_USER WITH ROLLBACK IMMEDIATE mode to clear user connections that may still be in the database. Connections under the context of sysadmin, dbcreator, or db_owner will still be allowed.

4. Stop the SQL Server Agent services on both the Publisher and Subscriber, or disable the Log Reader and Distribution agent jobs to ensure that the array-based backup and restore mechanisms have exclusive access to the database.

5. Set RESTRICTED_USER mode on the subscription database to prevent any user activity.

6. Use the HDS array-based restore mechanisms to:

      a. Restore the Publisher database.

      b. Restore the distribution database.

      c. Restore the Subscriber database.

Note: For large OLTP databases with high transaction volume, you may observe intermittent I/O in System Monitor or Performance Monitor (perfmon.exe) after the restore and recovery. This is typically due to the GHOST_CLEANUP process, which is removing records that have been marked for deletion. This process can be observed in the sys.dm_exec_requests catalog view.

7. Start the SQL Agent Service on the Publisher, Distributor, and Subscriber.

8. Enable the Publisher and Subscriber databases for MULTI_USER activity.

9. Enable the application login(s); for example, use ALTER LOGIN <login> ENABLE.

As with the previous procedure, it is recommended that Replication Monitor be launched to verify the health of the publications. The Log Reader and Distribution agents should also be launched before the application logins are enabled.

The restore of the Publisher, Distributor, and Subscriber databases can be performed in parallel using the HDS array-based restore mechanism. This approach was used to re-establish the baseline for repeated benchmark tests.

Observations and Data Points

A number of observations were documented during the initialization of the Subscriber during the benchmark. This relates to both the initial setup as described above and also to repeated tests conducted during the benchmark. Figure 1.0 below provides a graphical illustration to accompany the following data points. Numbers denote phases in figure 1.0.

1. The data was first loaded into the primary OLTP database without using transactional replication.

2. During this data load phase, the data was shadowed to a second set of volumes. Owing to the volume of data, this was deemed the most appropriate mechanism to provision the Subscriber database due to the volume of data. Loading data with transactional replication enabled was not appropriate, because the operation is fully logged regardless of the database recovery model.

3. After the data load had completed, the volumes were dismounted from the Publisher and mounted on the Subscriber. Transactional replication was then configured using the ‘replication support only’ sync_type parameter to avoid the need for a replication snapshot.

4. The 17-terabyte Subscriber database was then paired with a set of volumes as a backup. This process took approximately 17 hours.

A full backup of the 17-terabyte OLTP database using the native SQL Server 2005 backup mechanism took approximately four hours using eight streams. Please note that this was conducted on SQL Server 2005 Enterprise Edition. SQL Server 2008 may offer additional gains due to the native backup compression. For the native SQL Server 2005 backup procedure, the following Transact-SQL script was executed.

USE [master]

DECLARE

@DateStr nvarchar(50),

@Stmt nvarchar(1000),

@Filepath nvarchar(50),

@Quote nvarchar(1),

@Databasename nvarchar(50)

SET @Quote = char(39)

SET @Filepath = 'DISK=' + @Quote + '<directory>'

SET @Databasename = '<database>'

SELECT @DateStr = N'_' +

CONVERT(nchar(8),

getdate(), 112) +

N'_' +

RIGHT(N'0' + rtrim(CONVERT

(nchar(2), datepart(hh, getdate()))), 2) +

RIGHT(N'0' + rtrim(CONVERT

(nchar(2), datepart(mi, getdate()))), 2) +

RIGHT(N'0' + rtrim(CONVERT

(nchar(2), datepart(ss, getdate()))), 2)

SELECT @Stmt =

'BACKUP DATABASE ' + @databasename + ' TO ' +

@Filepath + @databasename + @DateStr + '_1.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_2.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_3.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_4.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_5.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_6.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_7.BAK' + @Quote + ', ' +

@Filepath + @databasename + @DateStr + '_8.BAK' + @Quote +

' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 5'

EXEC (@Stmt)

You should modify the @Filepath and @Databasename variables if script reuse is intended.

5. New gold (baseline) backups were occasionally required due to schema changes like indexes or partitioning for performance reasons. This HDS Split Second backup took approximately 30 minutes to complete; this was also a function of how much data had changed since the last restore, because the data had changed during the test cycle. While this backup process typically occurs almost instantaneously in the background, we opted to wait for the completion signal from the array before commencing a new test. SQL Server 2005 differential database backup timings were comparable and took approximately the same amount of time.

6. Following a test run, a restore of the Publisher, Distributor, and Subscriber databases took approximately two hours. This was also a symptom of the data that had changed during the test. However, approximately one hour and thirty minutes of this duration was due to the import and export of the VERITAS volumes, which was sequential in nature.

The Publisher and Subscriber databases were restored in parallel, thereby reducing the restore time for the repeated benchmark tests. The Distributor could be restored in parallel; however, we opted to conduct this step separately, because we wanted more control over the procedure.

Figure 1.0. Benchmark Data Points

As previously mentioned, before commencing a test, ensure that the GHOST_CLEANUP process has completed on both the Publisher and Subscriber databases (following a restore), because it may affect I/O measurements taken during the test.

Summary

Initializing a transactional replication Subscriber from an array-based restore is beneficial when the data volume is very large and the restore and configuration time need to be minimized. Though this can still be achieved using any other restore or data copy mechanism, the benefit of an array-based VDI freeze and thaw, which allows large amounts of data to be copied quickly and in a transactionally consistent manner, significantly reduces backup and restore time.

Bulk Loading Data into a Table with Concurrent Queries

AlexeiK — Mon, 06 Apr 2009 12:52:00 GMT

Authors: Alexei Khalyako, Stuart Ozer

Contributor: Sanjay Mishra

Technical Reviewers: Mark Souza, Denny Lee, Burzin Patel

Introduction

This article describes load scenarios for the common data warehouse scenario in which many queries read data from a table while the table is loaded. The key question that administrator/developer has to answer before deciding on a strategy is whether the read queries can afford to wait. If they can wait, you won’t want to complicate the loading process by running queries simultaneously with the load. However, if you have a business need to run queries while you load data, read on.

For these tests, we used Microsoft® SQL Server® 2008. We created two identical tables, each containing 18 million rows totaling 2.6 GB. One table was organized as a heap, and the other was organized as a clustered index. These tables were used as targets into which data was bulk inserted. The input data file for the bulk insert had 65,535 rows. We were not concerned about performance differences between loading heaps or indexes; we were looking instead at the impact of queries concurrent with loads.

One of the test’s goals was to understand whether read committed snapshot isolation (RCSI) makes any difference in query concurrency during loads. Does it help keep readers “unblocked” from the loading process? And how does the WITH TABLOCK option affect the bulk loading process?

Bulk Insert into Heap Table

For our first test, we loaded data into the heap with RCSI turned off (that is, with the default READ COMMITTED isolation level).

BULK INSERT Without TABLOCK hint, Read Committed

To imitate a data warehouse workload, we had five concurrent connections executing random SELECT statements on the table. While the SELECT statements were running, we started the bulk load operation. We used the sys.dm_exec_requests dynamic management view (DMV) to monitor requests on the server, and we observed that the BULK INSERT statement was waiting in the queue with LCK_M_IX wait type. Figure 1. shows the output of sys.dm_exec_requests while the BULK INSERT statement was being blocked by the SELECT statement. As soon as the BULK INSERT operation started loading data (Figure 2), all SELECT statements were blocked by the BULK INSERT statement until it completed.

Figure 1 : BULK INSERT is waiting for the SELECT statement to complete

Figure 2 : BULK INSERT is loading data, and SELECT statements are blocked

BULK INSERT With TABLOCK hint, Read Committed

For this test, the same five SELECT statements were running concurrently when we started the bulk insert, but this time we specified the WITH TABLOCK option. This time the sys.dm_exec_requests DMV indicated that the BULK INSERT operation was waiting with the LCK_M_BU wait type (Figure 3). The LCK_M_BU wait type occurs in SQL Server when a task is waiting to acquire a Bulk Update (BU) lock. Just as with our first test, as soon as BULK INSERT started loading data, we saw that the SELECT statements (Figure 4) were blocked by the bulk insert operation.

Figure 3: BULK INSERT with TABLOCK waiting for the SELECT statement to complete

Figure 4: SELECT statements waiting for the BULK INSERT statement with TABLOCK to complete

For our second test, we did the same test but with RCSI enabled. We wanted to see whether it would produce different results.

BULK INSERT Without TABLOCK Hint, Read Committed Snapshot Isolation

When we loaded data into the heap table without using the TABLOCK hint, we did not observe any waits. We concluded that option provides the maximum amount of concurrency, if you need to load data into a table while you run SELECT queries.

Figure 5: BULK INSERT into HEAP table with no TABLOCK hint

BULK INSERT with TABLOCK Hint, Read Committed Snapshot Isolation

Despite the fact that we were using RCSI while we were loading data into the table, the sys.dm_exec_requests DMV indicated that the BULK INSERT operation was waiting for a LCK_M_IX lock and that it was being blocked by an active SELECT statement.

Two other SELECT queries, which were issued after the BULK INSERT command started, were observed waiting on the LCK_M_S lock type.

Figure 6: BULK INSERT into HEAP with the TABLOCK hint

As soon as the SELECT statement with session_id 59 (in Figure 6) was completed, BULK INSERT started loading data, with the selects in sessions 55 and 60 continuing to wait until the BULK INSERT command completed.

We did observe another interesting effect. So far we were dealing only with the BULK INSERT command. But SQL Server 2008 introduced bulk optimization the INSERT INTO … SELECT statement. This operation behaved like BULK INSERT in all the tests on the heap table. The only exception was that if we loaded data into the table using INSERT INTO … SELECT with the TABLOCK hint under RCSI mode, none of the readers was blocked.

Isolation level	TABLOCK specified?	TIME to load data (min:sec)	WAIT TYPE
Read Committed	NO TABLOCK	3:34	LCK_M_IX
Read Committed	WITH TABLOCK	1:28	LCK_M_BU*
Read Committed Snapshot Isolation	NO TABLOCK	0:04	NONE
Read Committed Snapshot Isolation	WITH TABLOCK	1:03	LCK_M_IX

*Very short; difficult to measure how long it was taken

Table 1: Duration of data loading into the heap table

BULK INSERT into a Table with Clustered Index

Next, we loaded data into the table with a clustered index and with RCSI turned off. This test didn’t show any surprises and behaved as expected: All read operations were blocked while BULK INSERT was loading data into the table.

If RCSI was enabled, no blocking was observed with BULK INSERT running with concurrent read operations. As we expected, enabling RCSI eliminated locks for read operations that allowed simultaneous loading of the data into the table.

Figure 7: BULK INSERT into a table with a clustered index, no TABLOCK hint, and RCSI turned off

Figure 8: BULK INSERT into a table with a clustered index, a TABLOCK hint, and RCSI turned off

Figure 9: BULK INSERT into a table with a clustered index and RCSI enabled

Figure 9 shows SELECT statements reading from the table while a BULK INSERT is running. BULK INSERT behaved similarly both with and without the TABLOCK hint. This helped us conclude that if you are loading data with RSCI enabled, the TABLOCK hint in the BULK INSERT statement doesn’t play a significant role, as far as concurrency is concerned.

Isolation level	TABLOCK specified?	TIME to load data (min:sec)	WAIT TYPE
Read Committed	WITH TABLOCK	6:49	LCK_M_IX
Read Committed	NO TABLOCK	10:18	LCK_M_X
Read Committed Snapshot Isolation	WITH TABLOCK	01:01	NONE
Read Committed Snapshot Isolation	NO TABLOCK	0:48	NONE

Table 2: Duration of data loading into the table with a clustered index

The only time that readers were blocked by bulk operations was when data was loaded into an empty table with the clustered index built on it. In that special case only, we observed that read operations were blocked by bulk load operations and read operations if they had the LCK_M_SCH_S wait type. This lock was released only after the loading batch was completed.

Conclusion

RSCI can provide great benefits, if you are bulk loading into the table with concurrent readers working on it . In most cases, it provides you with the ability to read while bulk loads are performed. Bulk loading does not affect the size of the version store in tempdb under RCSI; however, RCSI cannot be enabled at the table level. It can be enabled on the entire database only. Therefore you should carefully analyze your workload to ensure that other operations on your database will not cause the tempdb size to explode.

Note that RCSI will introduce 14 noncompressible bytes into every row in every table in the database (http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/overhead-of-row-versioning.aspx). An alternative strategy for concurrent readers is to execute queries using the READ UNCOMMITTED isolation level (also known as a dirty read), but this requires application changes, and it can deliver transitionally inconsistent results. RCSI requires no application change, and it guarantees consistent results.

For more information about data loading strategies and scenarios, see the Data Loading Performance Guide at http://msdn.microsoft.com/en-us/library/dd425070.aspx

Scheduling Sub-Minute Log Shipping in SQL Server 2008

SanjayMishra — Tue, 24 Feb 2009 23:27:00 GMT

Author: Sanjay Mishra

Contributors: Mark Scurrell, Richard Waymire

Technical Reviewers: Denny Lee, Prem Mehra, Richard Tkachuk, Burzin Patel, Kun Cheng, Peter Carlin, Juergen Thomas, Michael Thomassy, Thomas Kejser, Lubor Kollar, Alexei Khalyako, Peter Scharlock, Nicholas Dritsas, Tom Michaels, Mike Ruthruff, Mike Weiner

Overview

Log shipping allows you to automatically take transaction log backups on a primary server, send them to one or more secondary servers, and then restore the transaction log backups on each secondary server. Many Microsoft SQL Server customers have asked for the ability to schedule the log shipping jobs with less than 1 minute frequency. In SQL Server 2005, SQL Server Management Studio user interface allowed the frequency of the scheduled jobs to be 1 minute or more, which meant that the minimum latency of log shipping was as long as 3 minutes (1 minute each for the backup, copy, and restore jobs). Many customers have asked for this latency to be less than 1 minute.

In this paper we introduce the new sub-minute log shipping capability in SQL Server 2008, and we discuss some considerations you need to be aware of in scheduling frequent log shipping jobs.

Introducing Sub-Minute Log Shipping in SQL Server 2008

SQL Server 2008 enables log shipping jobs to be scheduled with frequency in seconds. In SQL Server 2008, SQL Server Management Studio and the stored procedures sp_add_jobschedule and sp_add_schedule allow frequency settings in seconds, minutes, and hours. The minimum frequency is 10 seconds.

Figure 1: SQL Server 2008 Log Shipping user interface enables scheduling the jobs in hour, minute, or second frequency

Considerations

There are some considerations you should be aware of when you set up too frequent log shipping jobs:

The next execution of the job will not start until the previous execution has completed. Let’s assume you have set the frequency interval of the log backup job to 10 seconds, but one execution of the log backup takes 12 seconds to complete. The next backup job will start at the next scheduled time, which is 20 seconds after the start of the previous backup job. One execution of the job is skipped in this case.
Every time a log backup is completed, a message similar to the following is shown in the SQL Server ERRORLOG:

2009-02-09 15:25:56.94 Backup Log was backed up. Database: Test_LS, creation date(time): 2009/02/09(14:27:24), first LSN: 19:145:1, last LSN: 19:145:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\PRIMARY_DL380\LSBackup\Test_LS_20090209232551.trn'}). This is an informational message only. No user action is required.

If you take a log backup every 10 or 15 seconds, the SQL Server ERRORLOG flooded with such messages.

If you don’t want these messages flooding the SQL Server ERRORLOG, you can enable trace flag 3226. This trace flag doesn’t alter the behavior of backup jobs; it just suppresses the backup completion messages, preventing them from getting into the SQL Server ERRORLOG. Note that this trace flag suppresses all backup messages – database backup as well as transaction log backup.

Information about each backup is also recorded in the msdb database (the msdb.dbo.backupset, msdb.dbo.backupmediaset, msdb.dbo.backupmediafamily, msdb.dbo.backupfile, and msdb.dbo.backupfilegroup system tables). If you back up too frequently, you can expect these tables to grow faster than usual. You should periodically check the size of these tables and delete or archive the old information as necessary. To delete the old backup history, use the stored procedure sp_delete_backuphistory.
The backup compression feature in SQL Server 2008 provides significant space and time savings. Backup compression results in smaller backups, and it helps improve the performance of all the operations performed by log shipping by providing the following:
- Faster backup of the transaction log on the primary server.
- Faster copying of the transaction log backup file to the secondary over network.
- Faster restore of the log backup on the secondary.

However, the benefits of backup compression come with the cost of higher CPU utilization. If your log backup jobs use compression and are scheduled too frequently, you may notice frequent spikes in CPU utilization on the primary. Restoring from a compressed backup uses more CPU, and you could see frequent spikes in CPU utilization on the secondary as well. For more information about backup compression, see Tuning the Performance of Backup Compression in SQL Server 2008 and Tuning Backup Compression Part 2.

Conclusion

SQL Server 2008 provides the ability to schedule log shipping jobs as frequently as 10 seconds, which results in reduced latency of log shipping. Reduced log shipping latency can result in reduced data loss in case of loss of primary.

Tuning Backup Compression Part 2

SanjayMishra — Mon, 16 Feb 2009 18:24:00 GMT

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)

Overview

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:

Tuning BUFFERCOUNT and MAXTRANSFERSIZE
Memory used by backup compression
Backup compression and log shipping
Backup compression and data compression
Backup compression and transparent data encryption

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.

Tuning BUFFERCOUNT

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.

Tuning MAXTRANSFERSIZE

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).

Memory Utilization by Backup

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.

Backup Compression and Log Shipping

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:

Backup the transaction log on the primary server.
Copy the transaction log backup file to the secondary server over the network.
Restore the log backup on the secondary server.

The transaction log backups during log shipping can be compressed in one of the following two ways:

If you are using SQL Server Management Studio to setup log shipping, set the backup compression setting by selecting Compress backup from the Set backup compression list, as shown in Figure 4. If you have set backup compression as the default server level setting (sp_configure option backup compression default), you can pick the “Use default server setting” option as well.
If you are using the stored procedures to set up log shipping, set the parameter @backup_compression of stored procedure sp_add_log_shipping_primary_database to 1. If you have set backup compression as the default server level setting, you can set this parameter to 2.

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)

Backup Compression and Data Compression

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:

Backup compression can result in additional disk space savings even on databases that contain ROW or PAGE compressed tables or indexes. The size of the compressed backup and the backup compression ratio depend upon the characteristics of the data in the database, and they can vary from the results shown in these examples.
Backup operations on databases that use ROW or PAGE compression will likely result in shorter backup times, because the smaller database size translates into less I/O.
CPU consumption during the backup operation for databases that use ROW or PAGE compression may be higher as a result of less I/O, which results in more CPU time for compression operations.

Backup Compression and Transparent Data Encryption

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:

On a TDE-enabled database, backup compression doesn’t help reduce the size of the backup. The backup compression ratio is nearly 1.0, independent of the data in the database. This is due to the fact that encrypted data does not lend itself well to backup compression.
CPU utilization for the compressed backup is higher than the uncompressed backup, even though the backup size is not much different. This is because CPU resources are wasted in the compressed backup operation, because it attempts to compress the data, even though the data is not very compressible.
On a TDE-enabled database, it takes longer to perform a compressed backup than it takes to perform an uncompressed backup. This is due to the fact that I/O operations are not reduced, because the data does not compress well. However, there is time spent attempting to compress the incoming data.

For these reasons, we do not recommend the use of backup compression on a TDE-enabled database.

Conclusion

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.

Appendix A: Test Hardware and Software

All tests (except those in Figure 4 and Figure 6) were performed on the following hardware and software environment.

Server

HP DL380G5 with:

2 socket quad core
Intel Xeon CPU E5345 @2.33 GHz
16 GB RAM

Storage

EMC Symmetrix DMX-4 2500

Data volumes

4 data volumes from a disk group with

32 disk drives, 300 GB each @15K RPM
RAID 1+0

Backup volume

1 backup volume from a disk group (separate from data volumes) with

32 disk drives, 300 GB each @15K RPM
RAID 1+0

~2 HBAs (4 Gb Fiber Channel)

Software

The 64-bit edition of Windows Server 2008 Enterprise
The 64-bit edition of SQL Server 2008 Enterprise

Reporting Services Performance Optimizations

denny.lee — Wed, 14 Jan 2009 18:47:00 GMT

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

Authors: Denny Lee, Lukasz Pawlowski
Contributors: Robert Bruckner, James Wu, John Gallardo, Dean Kalanquin

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 Reporting Services environment. This note provides guidance for Reporting Services in both Microsoft® SQL Server® 2005 and SQL Server 2008. The focus of this technical note is to optimize your Reporting Services architecture for better performance and higher report execution throughput and user loads.

Architecture

Figure 1 represents a typical scale-out Reporting Services environment; as noted by the red box, the focus of this technical note is that of Performance Optimization of your scale-out deployment.

Figure 1: Reporting Services Scale-Out Architecture

Should You Use 64-Bit?

Yes.

Why, you ask? The answer can be divided into two different sections – how 64-bit helps your report server catalog performance and how it helps your report server performance.

How 64-bit Helps Your Report Server Catalog Performance

Remember that your report server catalogs are SQL Server databases, so your standard database techniques for optimizing SQL Server databases come into play here, as noted in the Report Server Catalog Best Practices technical note. Since SQL Server 2005, the database has been written natively for 64-bit and it is able to make use of the additional addressable memory.

How 64-bit Helps Your Report Server Service

For your report servers, the answer is slightly more complicated. As a general statement, most of your Reporting Services reports are memory-intensive, and the additional addressable memory made available by 64-bit will provide you with more scale. Note that for some workloads, 32-bit can execute faster, especially in scenarios where you have many small reports. But if you have more memory made available by 64-bit, you can handle more concurrent report users. Because there is less memory contention, report throughput will be higher; that is, you can allow more users to view and export larger reports. In SQL Server 2005 Reporting Services, each report’s data set is placed into memory; the more concurrent reports, the more memory used. If you use 32-bit, you can easily hit the 3 GB ceiling, which can also result in Internet Information Services (IIS) process recycles, leading to report failure.

But as noted in the Reporting Services Scale-Out Deployment Best Practices technical note, SQL Server 2008 Reporting Services is not memory-bound. It is able to effectively use the file system in order to move data structures in and out of memory and the file system if the report server experiences memory pressure. These memory limits are explicitly configurable in SQL Server 2008 Reporting Services via RSReportServer.config, as noted in the Memory Configurations for SQL Server 2008 Reporting Services section below. When Reporting Services uses the file system, the reports run more slowly, because it is much more efficient to request data from the memory than from disk. The file system is used only if Reporting Services memory usage gets close to the configured memory limits. If you overload the report server in SQL Server 2008 Reporting Services with a large number of concurrent users and/or very large reports, all of your reports can still complete processing, albeit more slowly, because Reporting Services can hold all of this data without running out of memory space. In enterprise environments, you will eventually run into situations where your servers will need to be able to handle many concurrent users and a large load – the optimization in SQL Server 2008 Reporting Services (in comparison to SQL Server 2005 Reporting Services) is that while the reports may run slower at times, they will complete.

Exceptions

Keep in mind that certain data providers are not available for 64-bit (for example, the Microsoft JET provider or certain third-party providers). In these cases, customers will need to continue using 32-bit for their Reporting Services environment.

Handling a Large Workload

As noted in the previous section, the two main issues concerning enterprise reporting environments are the ability to handle concurrent user load and the ability to handle a large workload (that is, large reports). To help mitigate the concurrency issue, your solution is to scale out to multiple report servers to handle the user query load, as noted in the Reporting Services Scale-Out Deployment Best Practices technical note.

To get the highest performance when handling large workloads that include user requests for large reports, implement the following recommendations.

Control the Size of Your Reports

You will first want to determine the purpose of these reports and whether a large multipage report is even necessary. If a large report is necessary, how frequently will it be used? If you provide users with smaller summary reports, can you reduce the frequency with which users attempt to access this large multipage report? Large reports have a significant processing load on the report server, the report server catalog, and report data, so it is necessary to evaluate each report on a case-by-case basis.

Some common problems with these large reports are that they contain data fields that are not used in the report or they contain duplicate datasets. Often users retrieve more data than they really need. To significantly reduce the load placed on your Reporting Services environment, create summary reports that use aggregates created at the data source, and include only the necessary columns. If you want to provide data feeds, you can do this asynchronously using more appropriate tools, such as SQL Server Integration Services, to provide the file data feed.

Use Cache Execution

As noted in the Reporting Services Scale-Out Deployment Best Practices, if you have reports that do not need to have live execution, enable the cache execution setting for each of your appropriate reports. This setting causes the report server to cache a temporary copy of those reports in memory.

Configure and Schedule Your Reports

For your large reports, use the Report Execution Timeouts setting to control how long a report can execute before it times out. Some reports simply need a long time to run, so timeouts will not help you there, but if reports are based on bad or runaway queries, execution timeouts ensure that resources are not being inappropriately utilized.

If you have large reports that create data processing bottlenecks, you can mitigate resource contention issues by using Scheduled Snapshots. Instead of the report data itself, a regularly scheduled report execution snapshot is used to render the report. The scheduled snapshot can be executed during off-peak hours, leaving more resources available for live report users during peak hours.

Deliver Rendered Reports for Nonbrowser Formats

The rendering performance of nonbrowser formats such as PDF and XLS has improved in SQL Server 2008 Reporting Services, as noted in the Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned technical note. Nevertheless, to reduce the load on your SQL Server Reporting Services environment, you can place nonbrowser format reports onto a file share and/or SharePoint® team services, so users can access the file directly instead of continually regenerating the report.

Prepopulate the Report Cache by Using Data-Driven Subscriptions for Parameterized Reports

For your large parameterized reports, you can improve performance by prepopulating the report cache using data-driven subscriptions. Data-driven subscriptions enable easier population of the cache for set combinations of parameter values that are frequently used when the parameterized report is executed. Note that if you choose a set of parameters that are not used, you take on the cost of running the cache with little value in return. Therefore, to identify the more frequent parameter value combinations, analyze the ExecutionLog2 view as explained below. Ultimately, when a user opens a report, the report server can now use a cached copy of the report instead of creating the report on demand. You can schedule and populate the report cache by using data-driven subscriptions. For more information, see Report Caching in Reporting Services.

Back to the Report Catalogs

You can also increase the sizes of your report server catalogs, which allows the databases to store more of the snapshot data. For more information, see Report Server Catalog Best Practices.

Tuning the Web Service

IIS and Http.sys tuning helps get the last incremental performance out of the report server computer. The low-level options allow you to change the length of the HTTP request queue, the duration that connections are kept alive, and so on. For large concurrent reporting loads, it may be necessary to change these settings to allow your server computer to accept enough requests to fully utilize the server resources.

You should consider this only if your servers are at maximum load and you do not see full resource utilization or if you experience connection failures to the Reporting Services process. To do this:

For SQL Server 2005 Reporting Services, tune IIS.
For SQL Server 2008 Reporting Services, tune Http.sys within the operating system: Windows® 2003 or Windows 2008.

Monitoring by Using ExecutionLog2

The Reporting Services ExecutionLog2 view is a good starting point from which to analyze your current workloads and understand its dataset size, performance, and complexity characteristics. For more information, see Robert Bruckner’s blog, which provides extensive details on the ExecutionLog2 view (http://blogs.msdn.com/robertbruckner/archive/2009/01/05/executionlog2-view.aspx). For more information about query and reporting on report execution log data, see SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms155836.aspx).

In particular, this view contains a new AdditionalInfo column. ExecutionLog2.AdditionalInfo contains information related to the size of memory-pressure responding data structures. One way this information can be useful is to check whether you have reports with high values (10s, or 100s of MBs) – these reports might be candidates for further review, focusing on the design of those reports and the dataset query sizes.

Below are some tips on how to view the ExecutionLog2 view to quickly understand potential performance bottlenecks. Linked is the Review Execution Logs Reporting Services project, which creates summary and detail Reporting Services reports on the last 1,000 entries into the ExecutionLog2 view, with the sorting options noted below.

Figure 2: Review Execution Logs (ExecutionLog2) Summary Report

Figure 3: Review Execution Logs (ExecutionLog2) Details Report

Long-Running?

Sorting by ElapsedSec or RowCount helps you identify long-running reports. If the value for TimeDataRetrieval is high, the data source is your bottleneck, and you may want to optimize. If the there is a high value for RowCount, a lot of data is being retrieved and aggregated by Reporting Services – perhaps have your data source do this to reduce the load on your report server.

Subscriptions or Interactive?

Sorting by the RequestType field allows you to determine whether you have a lot of subscriptions; you can then determine the bottlenecks and stagger-schedule the reports (that is, schedule the subscription execution times of the reports at different times).

Live Data or Snapshots?

Sorting by the Source field allows you to determine whether your reports are typically live data or snapshots. If the reports can be snapshots (for example, yesterday’s report), create snapshots so you can avoid query execution, report processing, and report rending.

Load Balanced?

Sorting by the Instance field can help you see whether your network load balancer is handling report requests in a balanced fashion. You can also see if some nodes are down or not processing requests.

Discover Report Patterns

Sorting by ReportPath and TimeStart can help you to find interesting report patterns – for example, an expensive report that takes 5 minutes to run is executed every 10 minutes.

Report Health

You can sort by status to determine if you have a high number of failures that occurred before (for example, incorrect RDL) or after (for example, subscription delivery error) the report is processed. This can also help you identify reports where there is outdated information or settings (for example, expired data source passwords or missing subreports).

In addition, if ScalabilityTime > 0, Reporting Services is in scale mode, which means that it is under heavy memory pressure and will start pushing long-running reports to the file system to ensure enough memory to complete smaller query requests. If this happens frequently, consider trying one of the following:

Reduce the size of the dataset.
Simplify the grouping, filtering, or sorting logic in the report to use less memory.
Add more memory.
Add more servers to better handle the load.

Data-Driven Subscriptions

Based on all of this information, you can then create your own data-driven subscriptions that can sort, filter, and track your issues. For example, you can create a subscription that alerts you if Errors > 5%.

Memory Configurations for SQL Server 2008 Reporting Services

As alluded to above in the 64-bit section, memory in SQL Server 2008 Reporting Services is used more efficiently at the data structure level. Under intensive workload pressures, it uses a file system cache in order to reduce memory utilization. By being more efficient and writing to disk, it can process more report executions (even large reports) successfully. The administrator uses settings to determine when SQL Server 2008 Reporting Services starts to use the file system cache and how aggressively the cache is used. The administrator should consider configuring memory settings for the SQL Server 2008 Reporting Services to optimally use their computer resources. Fine-tuning these settings can provide an increase in performance under intensive workload over the default configuration.

For more information, see SQL Server 2008 Books Online: Configuring Available Memory for Report Server Applications . The key memory configurations are:

WorkingSetMinimum: This is the minimum amount of memory that Reporting Services makes available in order for it to perform its task; that is, it does not use the file system cache if SQL Server Reporting Services process memory utilization is below this limit. It is configured as a KB value within the RSReportServer.config file. After this threshold is hit, Reporting Services responds to memory pressure by having long-running requests use the file system cache and smaller queries use memory.

For many concurrent users, you can potentially increase this configuration value after it hits peak performance, enabling more process requests to be completed within memory instead of the file system.

WorkingSetMaximum: This is the maximum amount of physical memory Reporting Services will use; it is also configured as a KB value within the RSReportServer.config file. After this threshold is hit and exceeded for a period of time, Reporting Services recycles the app domains to reduce memory utilization. This will ensure that there is enough memory left for the operating system to complete its task. You can increase this value if you want to process more reports concurrently.

MemorySafetyMargin and MemoryThreshold: These values define how aggressively the file system cache is used. MemorySafetyMargin defines the boundary between low-pressure and medium-pressure scenarios, with a default value of 80%. MemoryThreshold defines the boundary between medium-pressure and high-pressure scenarios, with a default value of 90%. Both are configured within the RSReportServer.config file.

In general, if you are constantly hitting memory thresholds, it is recommended that you consider scaling up (adding more memory, altering these configuration values) and then scaling out. You should scale up first because resources are better managed in SQL Server 2008 Reporting Services, as noted above and in Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned.

Memory Configurations for SQL Server 2005 Reporting Services

As noted in the Reporting Services Scale-Out Architecture technical note, you can scale up by adding more memory as well. But the impact may not be as dramatic; one scenario might see a doubling of memory and CPU (to 16 GB RAM and 8 cores) with only 1/3 increase in capacity. Nevertheless, there are things you can still do to scale up SQL Server 2005 Reporting Services prior to scaling out.

As with SQL Server 2008 Reporting Services, you can use memory configurations to address memory threshold issues. There are two primary memory configurations in SQL Server 2005 Reporting Services that we recommend you change if you’re constantly hitting memory thresholds:

MemoryLimit: This configuration is similar to WorkingSetMinimum in SQL Server 2008. Its default is 60% of physical memory. Increasing the value helps Reporting Services handle more requests. After this threshold is hit, no new requests are accepted.
MaximumMemoryLimit: This configuration is similar to WorkingSetMaximum in SQL Server 2008. Its default is 80% of physical memory. But unlike the SQL Server 2008 version, when its threshold is met, it starts aborting processes instead of rejecting new requests.

While we do recommend memory configuration changes if you’re constantly hitting memory thresholds, note that changing these values might create or exacerbate other resource contentions.

Conclusion

This concludes our four-part “Building and Deploying Large Scale SQL Server Reporting Services Environments” technical note series. We hope that this set of technical notes will help you design, manage, and maintain your enterprise SQL Server Reporting Services environments.

Diagnosing Transaction Log Performance Issues and Limits of the Log Manager

Lindsey.allen — Tue, 09 Dec 2008 20:28:17 GMT

Author: Mike Ruthruff

Technical Reviewers: Benjamin Wright-Jones, Peter Byrne, Kun Cheng, Burzin Patel, Sanjay Mishra, Mark Souza, Michael Thomassy, Lubor Kollar, Prem Mehra

Overview

For transactional workloads I/O performance of the writes to the SQL Server transaction log is critical to both throughput and application response time. This document discusses briefly how to determine if I/O to the transaction log file is a performance bottleneck and how to determine if this is storage related; a limitation is due to log manager itself or a combination of the two. Concepts and topics described in this paper apply mainly to SQL Server 2005 and SQL Server 2008.

Monitoring Transaction Log Performance

To determine if I/O performance of transaction log writes is a problem there are several tools which can help quickly isolate bottlenecks related to the log writes. These are:

1. SQL Server Dynamic Management Views (DMV’s).

a. sys.dm_os_wait_stats: This DMV exposes a number of wait types documented here. The wait type most relevant the current discussion is WRITELOG. WRITELOG waits represent the time waiting on a log I/O to complete after a COMMIT has been issued by the transaction. When observed these should be an indication I/O performance and characteristics of the log writes should be pursued.

b. sys.dm_io_pending_io_requests: This DMV exposes outstanding I/O requests at the individual I/O level. Documentation for this DMV can be found here. In scenarios where the SQL Server transaction log file is not on a dedicated volume this DMV can be used to track the number of outstanding I/O’s at the file level. If the transaction log is on a dedicated logical volume this information can be obtained using Performance Monitor counters. More details on both are given below.

2. Window Performance Monitor “SQL Server:Databases” Object. This performance monitor object contains several counters specific to performance of a transaction log for a specific database. In many cases these can provide more detailed information about log performance as the granularity is at the log level regardless of the logical storage configuration. The specific counters are:

a. Log Bytes Flushed/sec

b. Log Flushes/sec - (i.e. I/O operation to flush a log record to the transaction log)

c. Log Flush Wait Time

3. Windows Performance Monitor “Logical or Physical Disk” Objects. There are five counters that should be considered in any I/O analysis:

a. Current Disk Queue Length

b. Avg. Disk/sec Read, Avg. Disk/Write

c. Avg. Disk Bytes/Read, Avg. Disk Bytes/Write

d. Disk Reads/sec, Disk Writes/sec

e. Disk Read Bytes/sec, Disk Write Bytes/sec

The key counters to monitor with respect to log performance are the Current Disk Queue Length and Avg. Disk/sec Write.

These are the primary tools which are used to monitor I/O performance of the transaction log and diagnose any bottlenecks related to transaction log throughput. When troubleshooting any performance issue it is critical to look first at the complete overall system performance before focusing on any individual item. For the purpose of this discussion we will focus on diagnosing transaction log performance.

How Do I Determine if I Have a Log Bottleneck?

The quickest way to determine if you think performance issues are related to transaction log performance is to monitor sys.dm_os_wait_stats for high waits on WRITELOG. It is important to understand that this counter is cumulative since SQL Server was last restarted so monitoring deltas of these values over specific time periods is necessary to provide meaningful details.

There are two primary questions to answer when investigating a performance issue which is suspected to be log related.

1. Is the performance of the I/O subsystem adequate to provide healthy response times to I/O issued against the log?

2. Am I hitting any limits imposed by SQL Server related to transaction log behavior?

In the majority of experiences observed related to I/O performance issues, improperly sized or poorly configured storage is the primary contributor to I/O issues. This can be made worse by queries which are not tuned and issue more I/O than necessary affecting everyone using the I/O subsystem. In addition, there are other factors that will have some impact on log including things such as transactional replication, log backups, mirroring, storage level replication etc...

With respect to #1 our recommendation for response time on the log device should be in the range of <1ms to 5ms. It is important to keep I/O response time on log devices as low as possible which is the primary reason we recommend isolating logs from data files on separate physical spindles. Writes to the transaction log are sequential in nature and benefit by being isolated on to separate physical devices. In today’s modern storage environments there are many considerations which may make this impractical so in the absence of the ability to do this focus on keeping response times in the healthy range.

Limits of the Log Manager

Within the SQL Server engine there are a couple limits related to the amount of I/O that can be “in-flight” at any given time; “in-flight” meaning log data for which the Log Manager has issued a write and not yet received an acknowledgement that the write has completed. Once these limits are reached the Log Manager will wait for outstanding I/O’s to be acknowledged before issuing any more I/O to the log. These are hard limits and cannot be adjusted by a DBA. The limits imposed by the log manager are based on conscious design decisions founded in providing a balance between data integrity and performance.

There are two specific limits, both of which are per database.

1. Amount of “outstanding log I/O” Limit.

a. SQL Server 2008: limit of 3840K at any given time

b. Prior to SQL Server 2008: limit of 480K at any given time

c. Prior to SQL Server 2005 SP1: based on the number of outstanding requests (noted below)

2. Amount of Outstanding I/O limit.

a. SQL Server 2005 SP1 or later (including SQL Server 2008 ):

i. 64-bit: Limit of 32 outstanding I/O’s

ii. 32-bit: Limit of 8 outstanding I/O’s

b. Prior to SQL Server 2005 SP1: Limit of 8 outstanding I/O’s (32-bit or 64-bit)

Either of the above limits being reached will cause the suspension of the Log I/O until acknowledgments are received. Below are two examples which illustrate the above and provide guidance on how to determine how isolate whether or not these limits are being reached.

Example 1

This example illustrates the limit of 32 outstanding I/O’s on the 64-bit edition of SQL Server 2008. The Current Disk Queue Length counter is represented by the black line in the graph. The workload was executing approximately 14,000 inserts per second with each insert being a single transaction. The workload was generated using a multi-threaded client. The disk response time in this example is averaging 2ms. High wait times on WRITELOG were observed as this workload throughput level was approached suggesting investigation of disk throughput on the log device. This led us to investigate if the 32 outstanding I/O limit was the cause.

In Figure 1 below we can see the limit of 32 outstanding I/O’s clearly being reached. We can also determine it is the limit of outstanding I/O’s vs. the limit of 480K “outstanding log I/O” based on the following calculations (averages over this period of time as observed via Performance Monitor).

· (9.6MB Log Bytes Flushed/sec)/(5000 Log Flushes/sec) = ~1.9K per Flush

· (32 Outstanding I/O’s)*(1.9K per Flush) = ~60K “in-flight” at any given time (far below the limit)

Figure 1: Performance Monitor graph illustrating the limit of 32 outstanding I/O’s

In this particular scenario the Current Disk Queue Length could be utilized to diagnose the bottleneck because the log resided on its own logical volume (in this case H:\) . As discussed above, if the log did not reside on a dedicated logical volume, an alternative approach to diagnosing the limit being encountered would have been to use the sys.dm_io_pending_io_requests DMV discussed previously.

The following is a sample query that could be used to monitor a particular log file. This query needs to be run in the context of the database being monitored. It is worth noting that the results returned by this query are transient in nature and polling at short intervals would likely be needed for the data to be useful much in the same way performance monitor samples the disk queue.

select vfs.database_id, df.name, df.physical_name

,vfs.file_id, ior.io_pending

,ior.io_handle, vfs.file_handle

from sys.dm_io_pending_io_requests ior

inner join sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs on (vfs.file_handle = ior.io_handle)

inner join sys.database_files df on (df.file_id = vfs.file_id)

where df.name = '[Logical Log File Name]'

For this example, the best strategy to resolve or optimize the transaction log performance would be to isolate the transaction log file on a storage device which was not impacted by the checkpoint operations. In our storage configuration this was not possible and shared components (specifically array controllers) were the primary source of the problem.

Example 2

This example is a little more complicated but illustrates the limit of 480K “in-flight” data running SQL Server 2005 SP2 (64-bit edition). This was observed during a benchmark of a high throughput OLTP application supporting approximately 15,000 concurrent users. Characteristics of this application were a bit different in that the log flush sizes were much larger due to logging of larger transactions (including BLOB data). Again, high waits on the WRITELOG wait type were observed pointing in the direction of investigating I/O performance of the log device.

One important item to note when considering this data is that the log behavior over this period of time is spiky in nature and it is during the spikes the limits are encountered. If we look at averages over the problem time period we see the following

· (4,858,000 Log Bytes Flushed/sec)/(124 Log Flushes/sec) = 39,177 bytes per flush

· 5.4 ms per log flush but there are spikes. This is on the edge of health latency for the log.

· Current Disk Queue Length shows spikes and when Current Disk Queue Length * Bytes per flush is close to 480KB then we will start to see the Log Manager throttle the writes (observed as higher wait times on WRITELOG).

Figure 2 illustrates this although it is not as obvious to see as the pervious example. The correlation to watch for is (Average Bytes per Flush) * (Current Disk Queue Length) near the 480K limit.

Figure 2: Performance Monitor graph illustrating the limit of 480K outstanding log I/O data

A strategy for resolving this problem would be to work on the storage configuration to bring the latency on the log writes to a lower average. In this particular scenario, log latency was impacted by the fact that synchronous storage replication was occurring at the block level from the primary storage array to a second array. Disabling storage level replication reduces the log latency from the 5ms range to the range of <1ms however disaster recovery requirements for zero data loss made this not an option for the production environment.

It is worth noting that this was observed on SQL Server 2005 SP2 and migrating to SQL Server 2008 resolved the bottleneck since the limit in SQL 2008 is 8 times that of SQL Server 2005.

In either example reducing log latency is critical to increasing the transactional throughput. In very high throughput scenarios at the extreme end we have observed customers approaching this by alternative methods including 1) utilizing minimal logging capabilities of SQL Server when possible and 2) scaling out the transaction log through partitioning data into multiple databases.

Summary

The above provides information on limits imposed by the SQL Server Log Manager. These limits are based on conscious design decisions founded in providing a balance between data integrity and performance. When troubleshooting performance issues related to log performance, always consider optimizing the storage configuration to provide the best response time possible to the log device. This is critical to transactional performance in OLTP workloads.

Reintroducing Usage-Based Optimization in SQL Server 2008 Analysis Services

denny.lee — Tue, 18 Nov 2008 23:09:00 GMT

Author: Denny Lee
Contributors / Reviewers: Richard Tkachuk, Akshai Mirchandani, Robert Zare, Thomas Kejser, Gang Xiao, Cristian Petculescu

Introduction

From the early days of Microsoft® SQL Server® Analysis Services, one of the query performance optimization techniques was for developers to use the usage-based optimization feature. Developers would create their OLAP cube and cube partitions and then set the aggregations for the cube at a low percentage. It was common to see developers set the aggregations for their partitions at 0% - 5%. After the OLAP database was deployed to the production servers, there would be a beta test period for users to work with the cube and to store the components of the queries executed against the cube. Afterwards developers could build aggregations based on what users had queried, typically optimizing for the slowest queries. After this was done, these aggregations could significantly improve the performance of these initially slow queries. With the release of SQL Server 2008 Analysis Services, an improved usage-based optimization algorithm has been included that enhances Analysis Services’ ability to create better aggregations.

Customer Databases

To write this technical note, we performed some extensive testing on various customer databases to provide the lessons learned below. To do this, the query and database criteria were that the queries resulted in a lot of storage engine-heavy queries (as opposed to formula engine-heavy queries). This could be identified by viewing the Analysis Services profiler trace and noting that there were a lot of Event Class 12\Event SubClass 22 events (Query SubCube Verbose\Non Cache data). Specifically, we wanted queries that were not able to use the cache and needed to hit the file system.

Upon the application of usage-based optimization, we verified that aggregations were being used by verifying the Event Class 60 (Get Data from Aggregation) events in the Analysis Services profiler trace in SQL Server Profiler. For more information about the Analysis Services events, see Query Events in SQL Server Books Online. As well, you can find a translation of the events within the [Analysis Services Root Folder]\OLAP\bin\Resources\1033\tracedefinition100.xml file. For more information about how to use the Profiler to identify query performance bottlenecks, see Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services.

Recommendations

If usage-based optimization is used correctly, you can consistently reduce the length of long-running queries by using usage-based optimization to create smart aggregations, as shown in Figure 1.

Figure 1: Using usage-based optimization to improve query performance

We learned the following key lessons for using the usage-based optimization feature of SQL Server 2008 Analysis Services:

Usage-based opimtization should only be applied to long-running queries only. Application of usage-based optimization to any and all queries may have the inadvertent effect of reducing query improvement against your long-running queries and increasing processing costs as well.
Sometimes handmade aggregations will be more optimal in the situation where you want to improve the query performance of a specific query (as opposed to a set of queries). For more information about this technique, see the Analysis Services 2005 Aggregation Strategy technical note. As a general rule, you will still want to start with usage-based optimization to see whether you can get optimal query performance first. Then try applying your handmade aggregations.
Do not blindly apply and replace existing aggregations with usage-based optimization, because while you may improve one set of queries, you may slow down other queries.
When using usage-based optimization, you should set the aggregation level to 100% (which is now the default). You should lower it only if processing time is unacceptable or aggregation size increases above the threshold (for example, 1.5 times the size of the facts).
Test, test, test – make sure you test your queries with your original aggregations, new usage-based optimization aggregations, handmade aggregations, and/or no aggregations. It is important to do this to better understand the query characteristics of your cube.

For more information about the results and tests performed, please read-ahead – if you’re not interested – that’s it! :-)

Results

As noted above, we executed a large number of queries on various customer databases. Table 1 contains twelve queries that are representative of the larger customer query tests where we compared the query times before and after we applied usage-based optimization. (Times are in milliseconds.)

QueryID	No aggregations	Before usage-based optimization	After usage-based optimization	% difference	Factor (UBO is x times faster)
Q1		32,391	229	99%	141
Q2		37,377	256	99%	146
Q3		337	36,532	-10,740%	-108
Q4				30%	1.4
Q5	267,582	276,071	361,255	-31%	-1.3
Q6		4,679	3,870	17%	1.2
Q7		933	983	-5%	-1.1
Q8		2,627	2,746	-5%	-1.1
Q9		3,759	2917	22%	1.3
Q10		84,647	30,861	64%	2.7
Q11		350,652	9,339	97%	37.5
Q12		328,924	11,198	97%	29.4

All of the tests (including the values in Table 1) had the cache and file system cleared before query execution to ensure that the query results were not obtained from the cache.

Discussion

As can be seen in Figure 2, queries Q1 and Q2 show a two-order of magnitude improvement in query performance. But, in the case of query Q3, there is a two-order of magnitude performance degradation in query performance. This occurred because Q3 was already very fast(337 milliseconds) due to an existing set of aggregations. When the usage-based optimization aggregations were applied, they replaced the existing aggregations instead of adding to them, This replacement caused the performance degradation with Q3.

Figure 2: First set of usage-based optimization query tests

When we reapplied the aggregations such that both the usage-based optimization aggregations and the original aggregations were applied, all three queries had return times in the hundreds of milliseconds, that is, less than one second.

Lesson Learned: While this is not really specific to usage-based optimization aggretations, remember to be careful and ensure that any new aggregations that you create with usage-based optimization do not override existing aggregations that you may want to keep.

Figure 3 breaks down query Q5, which shows the same query execution times when no aggregations, handmade aggregations (Before UBO), and usage-based optimization aggregations (After UBO). As seen here, the application of usage-based optimization may sometimes result in the creation of aggregations that may slow down query performance.

Figure 3: Application of usage-based optimization may be slower

There are times when the creation of any aggregations may slow down query performance. There are any number of reasons that could cause this, ranging from cube design to the way the MDX statements were written. But as you can see in Figure 3, when aggregations were removed from the cube and the query was re-executed, query performance actually improved. As noted in the Table 2, even though the number of subcube requests to the storage engine were the same, the duration is significantly higher for usage-based optimization.

Event SubEvent Events Duration

Usage-Based Optimization No Aggregations Handmade Aggregations

Query Subcube Cache data 1329 2,960 464 512

Query Subcube Non-cache data 204 1,514 1,223 1,480

Table 2: Usage-based optimization has longer duration to query process the same number of events

Lesson Learned: Make sure to test all of your queries with your original aggregations (if you had any) and without any aggregations at all. There are times when the Analysis Services MOLAP engine can scan through the file system faster than aggregations can be gone through. This can be especially apparent in the case where the size of the aggregations is larger than the data itself.

As well, the “Before UBO” query in Figure 3 was against a cube where handmade aggregations were made following the guidelines within the Analysis Services 2005 Design Strategy technical note. If we discount the “No Agg” query, it becomes apparent that there are times where handmade aggregations can be faster. This is because when you use the Aggregation Manager to design your own aggregations, you can optimize for that specific query. Note that you can also use usage-based optimization to design aggregations for a single query. But the advantage of usage-based optimization is that it can create and combine aggregations for all of your select queries (for example,replace aggregations 5, 17, and 33 with a new one that covers them all).

Lesson Learned: To improve the performance of individual queries, sometimes you can get faster query performance by creating handmade aggregations because you can optimize for that specific query.

As can be seen in Figure 4, all of these queries here had relatively fast query times – that is, all of them were under 5s query time. In some cases, usage-based optimiation had improved performance (Q9, Q6) while in some other cases, there was a negligible performance degradation (Q7, Q8) that may or may not have anything to do with the use of aggregations.

Figure 4: Usage-based optimization query comparison for fast queries

Nevertheless, the key lesson learned here is that the application of usage-based optimization aggregations may have very little effect when you apply them to fast queries.

Lesson Learned: Make sure that when you apply usage-based optimization, you apply it to your long-running queries only. Usage-based optimiation is a complex algorithm that creates aggregations based on the queries that you select. If you end up selecting many queries that are already fast, the impact of these aggregations will only be slight and may even reduce the impact of your long-running queries.

After we take into account the above lessons learned, we can consistently reduce the length of long-running queries by using usage-based optimization to create smart aggregations, as shown in Figure 5.

Figure 5: Using usage-based optimization to improve query performance

Conclusion

A summary of the recommendations to optimize your use of usage-based optimization can be found in the Recommendation section earlier in this technical note. Ultimately, these tests and lessons learned provided us with some valuable lessons learned and affirmation of this feature.

Reporting Services Scale-Out Deployment Best Practices

denny.lee — Tue, 21 Oct 2008 06:21:00 GMT

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

Authors: Denny Lee, Lukasz Pawlowski
Contributors: John Gallardo, Robert Bruckner, Dean Kalanquin
Technical Reviewers: 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 environment. This note provides guidance for both SQL Server 2005 and 2008 Reporting Services. The focus of this technical note is Scale-Out Deployment Best Practices – the Reporting Services Windows®/Web Services servers that provide your reports.

Architecture

Figure 1 shows a typical scale-out Reporting Services environment; as noted by the red box, the focus of this technical note is scale-out deployment.

Figure 1: Reporting Services scale-out architecture

Report Catalog Sizing

As noted in the previous technical note of this series, Report Server Catalog Best Practices, the report server catalogs are an integral part of your Reporting Services solution. When you begin the process of building your Reporting Services scale-out deployment, you will need to appropriately scale your report server catalogs to handle the additional workload. In addition to the concepts discussed in the aforementioned technical note, a key consideration is Report Catalog sizing; the two report server catalogs (RSDB and RSTempDB) have different sizing requirements.

Report Server (RSDB) Database Size

The size of this database typically varies by the number of reports published and the number of history snapshots associated with each report. A general rule of thumb is that a moderate-sized report definition will take about 100-200 KB of disk space, which is larger than the actual size of the RDL. This is because Reporting Services will persist both the RDL and the compiled binary representation to improve report execution performance. In the case of history snapshot sizes, this is a function of the amount of data that is in the report (i.e., the larger your report and datasets, the larger your snapshot). A general rule of thumb is that Reporting Services has an approximate 5:1 compression ratio; therefore if your report has 10 MB of data, then the snapshot will be about 2 MB in size. Note that reports that do not perform aggregations will be slightly larger than reports that do.

Report Server TempDB (RSTempDB) Database Size

The size of this database varies depending on the number of users who are concurrently using the report servers. Each live report execution generates a report snapshot that is persisted in the RSTempDB for the duration of the user’s session. Because of the high degree of variability of the number of users who are accessing your report servers at any one time, your sizing estimate should be based on the maximum number of concurrent users that access your Reporting Services environment. A general rule of thumb here is that you will typically see a maximum of 10-20% concurrency of your user base.

For example, if you have 1,000 users, then you can have up to 200 concurrent users. If most of these users are accessing your 10-MB report, then you will need to have at least 400 MB of storage when taking compression into account. Of course, as users are no longer querying for reports and/or report sessions are timing out, the space will be reclaimed and made available for new users. But at the same time, to size properly, you will want to make your calculation based on the maximum number of concurrent users.

Enable File System Snapshots for SQL Server 2005 Reporting Services

Please note that this is applicable for SQL Server 2005 Reporting Services, not SQL Server 2008 SQL Server 2008 Reporting Services. As noted in Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned, this feature did not provide the scale of performance benefit (more on this later) for SQL Server 2008 Reporting Services.

Recall that the report server uses snapshot data stored in RSTempDB to render reports. Therefore RSTempDB gets filled up fast, because there are many transactions performed to keep report consistency (e.g., if I get the first page of a report, we need a snapshot of the second page that is taken from the same data as the first). Because of this concern for consistency, it is then important for Reporting Services to cache all of this information. To reduce the number of transactions being placed on your RSTempDB, use the File System (FS) snapshots feature instead. With it enabled, snapshot data persists on the local Reporting Services server. Therefore, this reduces the network traffic from the Reporting Services server to the catalog, which also reduces the number and size of transactions within the catalog. For more information about this feature, see the Planning for Scalability and Performance with Reporting Services white paper.

For Reporting Services to efficiently access this snapshot data, it is spread over a number of chunks – logical divisions of smaller size. During regular operations, the report server may need to access the chunks multiple times to satisfy user requests. For example, a particular chunk may have been removed from server memory, so the report server needs to fetch it again from RSTempDB. By default, the server must query RSTempDB each time it needs to get a snapshot chunk. As user load increases due to report complexity or concurrency, the number of transactions to RSTempDB also increases. This can cause performance degradation on RSTempDB. To mitigate this, Reporting Services provides configuration options to create file system chunks (i.e., file system snapshots), which act as a cache for the snapshot chunks on the node that created the chunk. This reduction in load on RSTempDB allows your Report Catalog to scale better.

Now that the report data is stored on the file system, it is a requirement to use a load balancing solution to affinitize user sessions to their report server node (more on this later). The slight drawback to this feature is that if you were to lose a reporting server, you would also lose the file system data cache that corresponds to that reporting server. However, the metadata is still available in RSDB and the original data is in the reporting data. Your users would simply connect to a different report server and make a new request for this report, experiencing slower initial query performance before the file system snapshots kick in.

Therefore, for large-scale SQL Server 2005 Reporting Services deployments, we recommend you turn on the file system storage of snapshot chunks. To do that, you must set WebServiceUseFileShareStorage and WindowsServiceUseFileShareStorage to True. You can control the location of the files using the FileShareStorageLocation configuration property. These settings are available in the file rsreportserver.config located under the report server installation folder.

Note that if you enable this feature, it will require more disk space than your standard Internet Information Services (IIS) configuration, because of the large amount of data that will be written onto disk. (For more information, see the Report Catalog sizing information in the previous section.)

Why Not File System Snapshots for SQL Server 2008 Reporting Services?

There are a number of changes and optimizations to SQL Server 2008 Reporting Services, including better utilization of memory and of the file system. Enabling file system snapshots in SQL Server 2005 Reporting Services was an advantage because unless this feature was enabled, a majority of the transactions required hitting the RSTempDB. But SQL Server 2008 Reporting Services caches a lot of this data into memory. Meanwhile the data is continually persisted to the Report Catalogs so that the local file system acts as a write-through cache, regardless of whether file system snapshots are enabled. This was done to ensure stability in case of incorrect load balancing affinity or gaps (e.g., secondary output streams such as CSS style sheets in HTML, report images retrieved in HTML) in report snapshots.

The design in SQL Server 2005 Reporting Services was that it generally would not materialize the original datasets in the report snapshot, except for particular cases. This was possible since the entire report was calculated in SQL Server 2005 Reporting Services when the first page was requested, and the report contents were persisted in a output-format independent way. On the other hand, SQL Server 2008 Reporting Services does not precalculate everything on the initial request – therefore, it has to always serialize all datasets so that it can calculate report contents as needed later. There are still certain parts in SQL Server 2008 Reporting Services that once calculated (e.g., group/sort/filter/aggregates) are persisted and not recalculated. However, many parts of a report (e.g., text box values, style values) are calculated on demand instead of precalculated and stored.

Another reason file system snapshots do not have as profound impact for SQL Server 2008 Reporting Services is that when reports are executed, the Reporting Services on-demand engine will initially retrieve all of the data for your report and write this data into the RSTempDB, to achieve a consistent data snapshot. It will then do the calculations and groups required for the first page. But subsequent calls are more efficient and have much less impact than SQL Server 2005 Reporting Services (e.g., page 2 of the report) because the on-demand engine will only access a subset of the data instead of requesting for the entire dataset. Note that the data is materialized in the initial request (e.g., render first page) when all dataset requirements are executed. Later session requests (e.g., navigate to second page, export to Microsoft Excel®) will use the data already materialized in the session snapshot.

This observation was also noted in the Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned technical note. Saying this, you may still want to test the efficacy of this feature within your environment, because there could be variables that allow this feature to help improve performance – but it certainly doesn’t always have benefits as it did for SQL Server 2005 Reporting Services.

Turn on Cache Execution

As you may have noticed, so far we have a recurring theme concerning the effective use of memory and desire to have minimal impact on the file system. After all, a key component for scalability is the ability to reduce I/O. Of course, CPU and memory are extremely important for scalability, but often it is the disk I/O that is slowing down your performance. A powerful feature to reduce I/O is to enable the cache execution feature of your reports. By default, when you deploy your Reporting Services reports, it will be a live execution report – i.e., upon report execution, Reporting Services will query the snapshot or your data source. At a minimum, this means that for every report execution some I/O occurs to provide your users with data. By using cache execution, Reporting Services caches a temporary copy of this report into memory that expires after a set number of minutes. Report executions during this time period request data from memory instead of disk, thus reducing I/O. Even if you set your cache timeout for your report to every five minutes, you will still see an 80% reduction in I/O for your report.

To configure cache execution, after you deploy your reports to your report server, you will need to configure each report (i.e., there is no global setting) to cache a temporary copy of the report. Note that you will want to set your reports to use stored credentials instead of Windows integrated security, in order for the cache execution to work effectively. Do not forget to set up your Reporting Services keys to ensure encryption of the stored credentials.

Load Balance Your Network

It is very important to load balance your many client connections to your Reporting Services servers. We recommend the use of a hardware network load balancer (NLB) that allows your client connections to balance across your Reporting Services servers, as noted in Figure 2.

Figure 2: Load balancing your network

We also recommend that you use cookie persistence (refer to your NLB guide to set this up) to preserve the client-to-Reporting Services server connection – i.e., to affinitize the client to the Reporting Services server. You can consider IP affinity, but this can overload under browser-based or proxy connections. Note that you typically will round-robin the initial connections to minimize the load on your NLB. Subsequent queries will have affinity to allow the clients to make use of the Reporting Services local file cache.

Because your Reporting Services servers are making heavy use of the network, you may want to consider also placing additional NIC cards for your Reporting Services servers. In enterprise Reporting Services environments, an important task will be to profile your network resources so you can determine where your network resource bottleneck is. If you are running into network bottlenecks, a setup to consider if you want to scale up your network load is to have two NIC cards, one for the browser traffic and one for the database traffic.

Isolate Your Workloads

Within your enterprise Reporting Services environment, you will typically have two types of users – one set that assesses scheduled reports and another set that reviews the reports in an interactive manner. A common profile differentiation between these two types of reports is that users accessing interactive reports will execute their reports often in order to drill up, drill down, filter, etc. On the other hand, scheduled reports typically will process against much larger sources of data (which involves a considerable hit on the system to produce the reports; hence scheduling will reduce the load) or have a much larger audience (where the load created by the concurrency of a large number of users to query the same report can be reduced by creating the report beforehand). Whether you are dealing with interactive users or scheduled report users, you have a situation where your users produce two different types of profiles as noted in Figure 3.

Figure 3: Isolating your workloads

Therefore, it is suggested that within your scale-out deployment, you also isolate your workloads so that you have one set of servers devoted to working with your interactive users (Interaction) while another set of servers work on scheduled reports (Scheduling). As implied in Figure 3, doing so will provide you with a more predictable workload because of the different resource profile that is used by these different report users. Workload isolation will also allow you to isolate any performance issues associated with these two types of reporting users; that way, interactive users and scheduled report users are not interfering with each other.

Note that ad-hoc report users (i.e., users who use Report Builder) are a different class of interactive users. Because these users can build whatever reports desired, there is a high degree of unpredictability in the amount of resources that this class of users will require. In extreme cases, you may want to consider creating a completely separate deployment for these ad-hoc users so that they do not bottleneck your interactive or scheduled report users.

To isolate your workloads, set your scheduling Reporting Services servers to Schedule Events and Report Delivery Enabled, and set your interactive Reporting Services servers to Web Service and HTTP Access Enabled. By default both options are enabled, so make sure only one is enabled on each server when you decide workload isolation is the path you will take.

Additional Notes on Workload Isolation

You can also make these workload isolation changes programmatically through WMI using the Reporting Services WMI Provider Library. You will change the SetServiceState method; for more information, see the SQL Server Books Online > SetServiceState Method.

To help isolate your reporting bottlenecks for scheduled reports (the Scheduled Events and Report Delivery), schedule your reports so they are not being processed in parallel. This way you will be able to isolate performance issues.

Report Data Performance Considerations

With all of these configuration and infrastructure considerations, you will be able to build an optimal scale-out environment for your Reporting Services solution. But while your Reporting Services environment is scaled out, do not forget the impact on your underlying reporting data – i.e., the data source. Here are some of the things you can do to reduce the impact on your report data:

Limit the dataset size by using query filters.
If you need to provide large dataset sizes, use alternate mechanisms such as Integration Services to provide an extract that is accessible from the file system, instead of providing a million-page report.
Limit the number of reports that users can access. This is not just a security concern. Limiting access reduces your maintenance cost by preventing users from creating multiple similar subscriptions and reports.

But even after making the above changes, you may still find yourself placing far too much load on your reporting data. To help resolve these issues, you may want to consider also scaling out your underlying data sources. Figure 4 is an example of a scale-out solution where a set of two Reporting Services servers are assigned to a single Analysis Services server. The underlying reporting data (in this case Analysis Services cubes) is replicated to multiple Analysis Services query servers so that every two Reporting Services servers query only a single Analysis Services instance. Note that to do this you will need to manually create local data source aliases on the reporting servers that will perform this type of server assignment.

Figure 4: Scaling out Analysis Services

These options are outside the scope of a Reporting Services specific technical note, but here are some resources that you may find helpful:

Deploying a Scalable Shared Database: This feature allows you to deploy multiple read-only versions of your SQL Server database for the purposes of reporting. Scalable shared databases offer a scale-out solution in which multiple servers hold read-only copies of your SQL Server database that users can query thus distributing the query load across multiple databases or servers.

SQL Server Replication: Providing High Availability using Database Mirroring: This white paper covers the use or database mirroring to increase availability of the replication stream.
Database Mirroring and Log Shipping: You can use database mirroring and log shipping together to provide both high availability and disaster recovery.
SQL Server Replication Features: Choose among transactional, merge, or snapshot replication to provide yourself with query replica databases.
Scale-Out Querying with Analysis Services: This white paper describes how to replicate a read-only Analysis Services database to multiple servers.
Scale-Out Querying with Analysis Services Using SAN Snapshots: If you have a SAN, you can make use of SAN snapshots to perform the task of metadata replication instead of physically copying the underlying Analysis Services database.
Scaling out an Analysis Services Solution: In addition to attaching or detaching an Analysis Services database, you can also attach a read-only copy of your Analysis Services database that places the Analysis Services database metadata locally and refers to a remote copy of the database. That is, one copy of a database has metadata distributed to multiple servers so that the formula calculation load can be scaled out to many servers.

Next Steps…

Now, that you’ve scaled out your Reporting Services deployment – there are additional features and configurations that may optimize your Reporting Services environment; for more information, see SSRS Performance Optimization Configurations [link provided when published].

The Many Benefits of Money…Data Type!

admin — Thu, 25 Sep 2008 18:20:00 GMT

Authors: Denny Lee, Thomas Kejser, Matt Neerincx
Contributors / Reviewers: Peter Carlin, Brandon Berg, Eric Jacobsen, Burzin Patel

Background

Our initial reason for looking at the money data type can be found within the Precision Considerations for Analysis Services Users white paper. In this white paper, we provide extensive examples of the types of precision issues when your SQL relational data source and your Microsoft® SQL Server® Analysis Services cube have different non-matching data types (e.g., if you query one way you get the value 304253.3251, but run the query in another way and you get the value 304253.325100001).

To avoid these types of problems, you need to ensure that your SQL relational data source and Analysis Services measure groups have matching data types. By default, when you create an Analysis Services measure on a money data type, Microsoft Visual Studio® Business Intelligence Development Studio will set the data type reference to double. To avoid precision loss and have faster performance, you should change the data type to currency within the Source Properties as noted in the screen shot below.

Show Me The Money! ...Data Type for Faster Processing Performance

Working on customer implementations, we found some interesting performance numbers concerning the money data type. For example, when Analysis Services was set to the currency data type (from double) to match the SQL Server money data type, there was a 13% improvement in processing speed (rows/sec). To get faster performance within SQL Server Integration Services (SSIS) to load 1.18 TB in under thirty minutes, as noted in SSIS 2008 - world record ETL performance, it was observed that changing the four decimal(9,2) columns with a size of 5 bytes in the TPC-H LINEITEM table to money (8 bytes) improved bulk inserting speed by 20%. Note that within SSIS, the equivalent of the money data type is DT_CY, which currently does not support fast parse. Hence, getting money out of text files may incur additional cost.

Note that these tests were performed on 64-bit systems. Relative performance may be different in the 32-bit editions of SQL Server because of differences in the way it performs 64-bit integer (or money) operations.

Money vs. Decimal vs. Float Decision Flowchart

Below is a high-level decision flowchart to help you decide which data type you should use. Note that this is a generalization that may not be applicable to all situations. For a more in-depth understanding, you can always refer to Donald Knuth’s The Art of Computer Programming – Volume 1.

As well, remember that different data types have different client API mappings. Some more in-depth references to this include SQL Server Data Types and ADO.NET and A Money type for the CLR.

Money (Data Type) Internals

The reason for the performance improvement is because of SQL Server’s Tabular Data Stream (TDS) protocol, which has the key design principle to transfer data in compact binary form and as close as possible to the internal storage format of SQL Server. Empirically, this was observed during the SSIS 2008 - world record ETL performance test using Kernrate; the protocol dropped significantly when the data type was switched to money from decimal. This makes the transfer of data as efficient as possible. A complex data type needs additional parsing and CPU cycles to handle than a fixed-width type.

Let’s compare the different data types that are typically used with money (data types).

Breakdown

money

decimal

float

Simple/complex data type:

Simple data types align more directly to native processor types. Complex data types require CPU to review type metadata and to perform branching.

Simple

Complex

Simple

Fixed/variable length writers: Because a variable-length data type may incur a memcpy when moving, causing additional CPU overhead, use a fixed 8-byte or 4-byte integer assignment if possible.

Fixed

Variable

Fixed

Storage format: Incurs less overhead if the data type is composed of native literals (e.g., int, uint, long, ulong) instead of approximate data types (e.g., float).

8-byte integer

Scaled integer (one sign byte plus one to four ulong depending on precision)

8-byte integer

Comments: This row lists other issues of concern.

None

TDS wire format is always packed, so extra overhead is required to pack and unpack this data type.

Approximate data are types more expensive to compare/convert than native literals; there may be precision issues on conversion.

The key here is that the money data type is a simple fixed-length integer-based value type with a fixed decimal point. Composed of an 8-byte signed integer (note that small money is a single 4-byte integer) with the 4-byte CPU alignment, it is more efficient to process than its decimal and floating point counterparts. The other side of the coin is that floating points (but not decimal) can be more quickly calculated in the floating point unit of your CPU than money. However, bear in mind the precision issues of float as noted above.

**Saving (Space for) Your Money!**

In the context of SQL Server data compression, the money and small money data types tend to compress well when the absolute value is low (e.g., values between -0.0128 and 0.0127 compress to 1 byte, while values between -3.2768 and 3.2767 compress to 2 bytes). It is the absolute value that matters for compression, not the number of significant digits; both 1,000,000 and 1,234,567.8901 will take 5 bytes compressed. On the other hand, decimal will compress better when there are fewer significant digits. For example, both 1,000,000 and .0001 will compress to 2 bytes, but 1,234,567.8901 will take several more bytes because it has more significant digits.

$ummary

There will be many scenarios where you preferred option will still be to use data types such as decimal and float. But before skipping over this detail, take a look at your data and see if you can change your schema to the money data type. After all, a 13% improvement in Analysis Services processing speed and 20% improvement in SSIS processing isn’t chump change.

…and that’s our $0.02.

SQL Server Service Broker: Maintaining Identity Uniqueness Across Database Copies

MichaelThomassy — Wed, 13 Aug 2008 02:53:00 GMT

Author: Michael Thomassy

Technical Reviewers: Sanjay Mishra, Burzin Patel, Stuart Ozer

A Deployment Method: Copying Databases

When dealing with many large customers, they often develop new and interesting ways of using technology and deploying it. One such case concerning service broker is with large, scale-out deployments consisting of hundreds of servers where the same database will be copied and deployed many times over. This database will contain all of this particular customer's service broker standards and custom built pieces for how broker will operate in their environment. As a package this database can be easily copied and deployed across many instances and servers through 1) "copy & attach," or 2) backup & restore. The caveat for this approach is that each of these databases will contain the same service broker identity. Therefore, we need to reset it. For more details on Managing Service Broker Identities see Books Online (BOL):

http://msdn.microsoft.com/en-us/library/ms166057(SQL.100).aspx

We will get to the relevance of having an actual globally unique service broker identity shortly. However, it's important to recognize the database properties that are modified when a database is attached or restored. For our discussion relevant to service broker, I'll point out that the following values are set to 0 when the database is attached or restored:

is_broker_enabled
is_honor_broker_priority_on
is_trustworthy_on

You can view database properties by querying sys.databases, see BOL:

http://msdn.microsoft.com/en-us/library/ms178534(SQL.100).aspx

SELECT
     name
     ,is_broker_enabled
     ,is_honor_broker_priority_on
     ,is_trustworthy_on
     ,service_broker_guid
FROM sys.databases

Just after detaching, making a couple copies and attaching the 3 databases, you’ll notice that all of my databases have the same service_broker_guid. There is no way to know the purpose, function or environment in which the database is attached or restored. This is why service broker is disabled by default. Now that the database has been duplicated, we need to reset the service broker identity as follows:

ALTER DATABASE [MySSBDBCopy1] SET NEW_BROKER
ALTER DATABASE [MySSBDBCopy2] SET NEW_BROKER
GO
SELECT
     name
     ,is_broker_enabled
     ,is_honor_broker_priority_on
     ,is_trustworthy_on
     ,service_broker_guid
FROM sys.databases

Giving the database a new service broker identity will enable service broker (is_broker_enabled=1) and will remove all service broker messages and conversations in the database with sending end dialog messages. Again, in our scenario since we are deploying a "standards" database we will assume there are no messages or conversations in the database.

Service Broker Diagnostic Utility

Having 1 or more databases with the same service broker guid may not seem problematic. However, service broker does assume that this guid is globally unique across "time and space" independent of the location, instance and server. When messages are sent and received i.e. the initiator and target are the same database, having multiple identical guids may not pose a problem. Unless you'd like to use the new service broker diagnostic tool called the ssbdiagnose Utility. See BOL for more detail: http://msdn.microsoft.com/en-us/library/bb934450(SQL.100).asp. SSBDiagnose is new in SQL Server 2008 however it can be used in SQL Server 2005 only environments or mixed. The utility will error when used against an instance where there are 2 or more databases having the same service broker guid. Yes, it is a problem even if the database being diagnosed has a guid that's not duplicated. The Service Broker Diagnostic Utility will generate output similar to:

Microsoft SQL Server 10.0.1442.23
Service Broker Diagnostic Utility
D 29997 MYSERVER\INST2 SSBReceiver1 Service Broker GUID is identical to that of database SSBReceiver on server MYSERVER\INST2
D 29997 MYSERVER\INST2 SSBReceiver2 Service Broker GUID is identical to that of database SSBReceiver on server MYSERVER\INST2
D 29905 MYSERVER\INST2 SSBReceiver Service Broker is not enabled in the database
3 Errors, 0 Warnings

To summarize, when making a copy of a database establish a new service broker identity. This will avoid any potential future conflicts and will guarantee that your service broker identity will truly be a G.U.I.D.!

Moving Large Amounts of Data Between Oracle and SQL Server: Findings and Observations

Nicholas Dritsas — Fri, 08 Aug 2008 22:04:00 GMT

Author: Nicholas Dritsas
Contributers: Hae Jin Park
Technical Reviewers: Denny Lee, Burzin Patel

Case

A large online gaming company tested a number of methods to quickly and efficiently move data between an Oracle 9i/UNIX system and SQL Server 2005. Their goal was to move 200‑GB of daily user activity data within two hours. The data was to move without the use of transformations from Oracle’s OLTP system to the SQL Server Operational Data Store (ODS) database. An ODS database is used in data warehouse architecture to hold an exact and intact copy of the source data, representing the only true data and history. The ODS data would be used later by SSIS packages to transform and load data into a staging database, a DW database, and an OLAP cube for reporting purposes.

Note This Technical Note does not describe a suggested method to fully migrate data from Oracle to SQL Server in all cases. Depending on the application, third-party tools and wizards may be a better fit for your migration.

Process

The company started by using a 64-bit server with 32 and 64 cores, testing both x64 and ia64. They installed SQL Server, Analysis Services and SSIS on that server. Since it was a 64-bit platform, the Microsoft OLE DB Provider for Oracle was ruled out because it does not yet support 64-bit environments. The project team decided to use Oracle’s 10.2.0.2.2 version provider. Here are the initial tests and findings:

Solution 1: The first solution they tried was using the SQL Server Integration Services (SSIS) OLE DB Source task to move the Oracle 200-GB data into SQL Server. However, the speed was very slow and they could not meet their goal of two hours so they stopped the test. In this scenario, they used the 64-bit server and the 64-bit provider from Oracle and the projected time of completion was about 18 hours.

Solution 2: The next solution was to use a separate 32-bit server (8 cores, 20-GB RAM) for SSIS only and 32-bit Oracle’s OLEDB Provider for Oracle. They downloaded the Oracle data into a binary file (using the network drive on the 64-bit server) by using the SSIS Raw File destination task. From there they uploaded the data into SQL Server staging tables, and then converted it into SQL ODS tables by using the SSIS Raw File source task.

Result: 2 hours 47 min elapsed.

Issue : Large staging file and staging DB (not ODS) size

Even though the size of the Oracle data was 200 GB, the binary file size was over 600 GB. Then, the 600 GB of data had to be uploaded into SQL Server tables without data conversion. After that, it was necessary to convert it into SQL Server target ODS table format. The process required at least 1.4 terabytes of additional disk space for the binary file and staging tables.

Note Less overall time would have been required if they had used Oracle SQL*Plus commands to spool the data into a file. That way, they would not have had to use the slow provider to extract into a raw file. They did not have time to test this scenario but it is worth mentioning. Here is a helpful link: http://www.orafaq.com/faq/is_there_a_sql_unloader_to_download_data_to_a_flat_file.

Solution 3: Next they tried using DataDirect Technologies’ Oracle provider with wire protocol on the x64 server (it seems DataDirect does not have an ia64 provider). The achieved speed results were very impressive. In a comparison test, they loaded 10,000 records from Oracle to SQL Server. In that test, the DataDirect provider took 2 seconds versus 22 seconds when using the Oracle provider. However, the overall test failed because of conversion errors.

Issue: They tried to retrieve the Oracle data by using the SSIS OLE component with DataDirect’s provider (x64) but there was a code page conversion error for the SQL Server code page CP949 (Korean character set). They tried to use Unicode conversion by using an output column (in SSIS), but this did not work either. They plan to open a support case with DataDirect. It seems there are some special Korean characters that do not get mapped properly in CP949 and the driver fails.

Solution 4: The company finally decided to use the SSIS OLE DB source and target components to move data from Oracle to SQL Server directly through an 8‑core 32‑bit server using Oracle’s32‑bit OLEDB provider (ver. 10.2.0.2.2) and Korean_90_Bin collation (with the SQL Server ODS database). When Korean_90_bin is used, they do not have to convert Korean data explicitly in Oracle, thus reducing response time.

Result: The response time was 2 hr 33m 32 sec (without the need of an additional 1.4 terabytes of disk space).

Findings

· Oracle OLEDB Provider performance appears to be better optimized on 32‑bit compared to x64 or IA64. (Oracle driver speed: x86 > x64 > IA64). So, the company had to use 32‑bit middle server to meet the target time.

· It seems that the 32-bit middle server's spec is important to the data move time. Based on this test result, if they used a 32-bit server with more than 8 cores, it is possible they could reduce the time even more. But, based on the tests, when they went from 8 cores to 16 cores, the difference was small. The total time went from 2h33min to 2h11min.

· To achieve even better performance, they used the Fast Load option under the Data Access mode of the OLE DB Destination task. They decided not to use the SQL Server destination, even though it is supposed to be faster, because it requires that SSIS and SQL Server be on the same server. By using the OLE DB Destination, they have the option to deploy the packages on a separate SSIS server.· Regarding the Oracle to SQL ODS step (using a direct load via OLEDB and an x86 server), they found that performance on a 16 dual-core x64 is better than on a 32 dual-core x64. (The gap is 10min.) The x64 server hosts SQL Server and the ODS database.Detailed Test Results

Following are the test results on an HP superdome IA64 with 16- and 32-way plus a Unisys ES7000 16‑way and 32-way x64 based on EMC and Hitachi storage. The SQL Database DW size is 47 GB and the Analysis Services database with 20 cubes is 19 GB. It appears that a Unisys x64 32-way EMC may be the right option for their requirements.

Unisys x64

Case	Step	Server	Elapsed Time	CPU
				DW	Middle
case1	Oracle ODS -> Staging ODS (With SSISOLE)	32way - HDS	2:53:34	AVG 7% MAX 11%	AVG 81% MAX 99%
		16way - HDS	2:52:02	AVG 13% MAX 27%	AVG 82% MAX 99%
		32way - EMC	2:43:35	AVG 7% MAX 11%	AVG 86% MAX 100%
		16way - EMC	2:33:32	AVG 15% MAX 27%	AVG 92% MAX 100%
case2	ETL (Fact, Mart)	32way - HDS	1:35:30	AVG 23% MAX 44%
		16way - HDS	1:42:57	AVG 36% MAX 76%
		32way - EMC	1:34:13	AVG 28% MAX 50%
		16way - EMC	1:40:34	AVG 37% MAX 74%
case3	Cube build	32way - HDS	0:53:40	AVG 13% MAX 42%
		16way - HDS	0:56:03	AVG 27% MAX 72%
		32way - EMC	0:52:29	AVG 16% MAX 44%
		16way - EMC	0:55:13	AVG 27% MAX 72%

HP IA64

Case

Step

Server

Elapsed Time

CPU

Middle

case1

Oracle ODS -> Staging ODS (with Raw files)

32way - HDS

2:53:00

AVG : 13%
MAX : 44%

AVG : 71%
MAX : 99%

16way - HDS

2:47:07

AVG : 25%
MAX : 87%

AVG : 75%
MAX : 99%

32way - EMC

2:51:58

AVG : 16%
MAX : 42%

AVG : 77%
MAX : 100%

16way - EMC

2:52:38

AVG : 27%
MAX : 85%

AVG : 73%
MAX : 99%

case2

ETL (Fact, Mart)

32way - HDS

1:52:14

AVG : 18%
MAX : 45%

16way - HDS

2:05:24

AVG : 30%
MAX : 82%

32way - EMC

1:55:19

AVG : 20%
MAX : 50%

16way - EMC

1:57:37

AVG : 34%
MAX : 81%

case3

Cube build

32way - HDS

1:03:07

AVG : 13%
MAX : 41%

16way - HDS

1:00:29

AVG : 25%
MAX : 84%

32way - EMC

1:01:01

AVG : 13%
MAX : 38%

16way - EMC

59:01

AVG : 25%
MAX : 96%

Conclusion

Based on these results for the specific requirements of this company it appears the best solution is Solution 4. The company plans to get the following servers to maximize price/performance ratio:

· A 4 dual-core 32‑bit server with 20‑GB of RAM using Oracle’s latest 32‑bit OLE DB Provider as a dedicated SSIS server

· An 8 dual-core x64 server with 32‑GB of RAM and EMC SAN to host SQL Server RBMS and Analysis Services

For more information about SSIS connectivity options, see Connectivity and SQL Server 2005 Integration Services.

Running Microsoft SQL Server 2008 Analysis Services on Windows Server 2008 vs. Windows Server 2003 and Memory Preallocation: Lessons Learned

CarlRabeler — Wed, 16 Jul 2008 16:56:00 GMT

Author: Carl Rabeler
Contributers: Eric Jacobsen, Thomas Kejser
Technical Reviewers: Brad Daniels, Lindsey Allen, John Desch, Wayne Robertson, Kevin Cox

Introduction

SQL Server 2005 Service Pack 2 introduced a new advanced configuration setting for Analysis Services that enables an administrator to specify that a certain percentage of memory be allocated to Analysis Services when the service starts (memory preallocation). This configuration setting was introduced because the Microsoft Windows Server 2003 operating system did not scale well with many small virtual memory allocations due to memory fragmentation and inefficiencies in retrieving information already located in memory. With the use of this configuration setting with 64-bit versions of Windows Server 2003 on multiprocessor computers (specifically computers with 16, 32, and 64 cores), the SQL Customer Advisor Team (SQL CAT) observed significant performance gains during Analysis Services processing and querying when memory use substantially exceeds 3 GB. Indeed, when Unisys and Microsoft achieved a record-setting benchmark for Analysis Services cube processing, the memory preallocation configuration setting for Analysis Services was set to 20 rather than to the default setting of 0 (resulting in 20% of physical memory being allocated to Analysis Services). For more information about this benchmark, see Data Warehouse Performance: Unisys and Microsoft Achieve Record-setting Benchmark.

The memory manager in Windows Server 2008 includes many performance and scalability enhancements, one of which is a change in the algorithm for scanning pages in memory. Additional memory manager improvements include the following:

· Kernel virtual address space is dynamically allocated to improve scalability when a greater number of processors and larger memory configurations are used. The sizes of paged and non-paged memory pools are no longer fixed, so manually reconfiguring systems to prevent resource imbalances is not required. Previously, the sizes of some resources, such as memory pools, were set by registry keys or by SKU.

· Kernel virtual address space is made more available in x86 architectures by more efficiently using space in kernel-mode stacks in recursive calls into the kernel.

· When a kernel-mode component allocates more than a page of memory, the memory manager now uses the pool memory between the end of that allocation and the next page boundary to satisfy other memory requests.

· SuperFetch, an adaptive page prefetch technique that analyzes data usage patterns and preloads data into memory according to those patterns, was added.

· Numerous enhancements that make writing to the page file faster and more efficient have been added. The memory manager now writes more data in each operation, aligns pages with their neighbors, and does not write pages that are completely zero.

· There is now greater coordination between the memory manager and the cache manager for write operations.

· Prefetch-style clustering of pages to satisfy page faults and populate the system cache was added.

· The capabilities of NUMA architectures are more fully utilized.

For more information about the improvements in the Windows Server 2008 memory manager, see:

· Advances in Memory Management for Windows

· Memory Management, Dynamic Kernel Addressing, Memory Priorities and I/O Handling

· Designing Applications for High Performance

· Inside Windows Server 2008 Kernel Changes

We decided to test and compare Analysis Services partition and dimension processing using SQL Server 2008 Analysis Services RC0 running on Windows Server 2008 and Windows Server 2003 to assess the impact of these memory manager improvements on the type of hardware and with the type of data sets with which we have previously seen significant performance benefits with the memory preallocation configuration setting.

Executive Summary

Due to the improvements in the Windows Server 2008 memory manager related to the change in the algorithm for scanning pages in memory, SQL Server 2008 Analysis Services performed equally well during both partition and dimension processing with or without memory preallocation when running on Windows Server 2008. However, SQL Server 2008 Analysis Services performed substantially better during both partition and dimension processing with the use of the memory preallocation configuration setting when running on Windows Server 2003. Specifically, the processing performance of Analysis Services 2008 without memory preallocation on Windows Server 2008 was virtually identical to the processing performance of Analysis Services 2008 on Windows Server 2003 with memory preallocation. With the hardware that we used in our tests and with the Analysis Services objects that we processed, we observed a performance benefit of approximately 100% during partition processing and a performance benefit of between 30-40% during dimension processing.

If you use memory preallocation with SQL Server 2008 (or SQL Server 2005), use a value that is low enough to ensure that sufficient memory remains for other processes on the computer (avoiding paging) and high enough for Analysis Services (use the peak value for the Process: Private Bytes counter for the msmdsrv instance to establish this value).

Note: While we did not specifically test SQL Server 2005 Analysis Services for this technical note, the results discussed here apply to Analysis Services 2005 as well as to Analysis Services 2008 because the underlying code base for the portion of Analysis Services 2008 that we tested has not changed.

Test Environment

For our testing, we used the following:

· A Unisys ES7000/one Enterprise Server with 32 dual-core x64 processors and 128 GB of RAM that was connected to two HP EVA 8000 arrays

· Windows Server 2003 Datacenter Edition and Windows Server 2008 Datacenter Edition

· SQL Server 2008 RC0

· An internal Microsoft sales database, containing approximately 1 terabyte of relational data

· An Analysis Services cube based on this relational database, which resulted in a cube that was approximately 180 GB in size. The dimensions were approximately 60 GB in size and the partitions were approximately 120 GB in size (and contained no aggregations).

Note: We used the Microsoft Enterprise Engineering Center, with its extensive hardware inventory and team expertise to build the test system for this test.

Database Engine Configuration

For our processing tests, we set the minimum and maximum memory for the relational engine to 20 GB. We left all other settings at their defaults. We located the data and log files for the relational database on different LUNs from the Analysis Services dimension and partition files.

Analysis Services Configuration

In Analysis Services, we set memory preallocation to 40 (<PreAllocate>40</PreAllocate>) in the msmdsrv.ini file (which preallocates 40% of the 128 GB of memory on our test computer to Analysis Services at startup). We experimented with enabling large page support, but found no measurable performance benefit. Large pages require that the Analysis Services service account have the lock pages in memory user directly on the local computer. Large page support enables server applications to establish contiguous large-page memory regions. For more information about large page support and restrictions when using large pages, see Large Page Support.

Caution: When we enabled large page support for Analysis Services on Windows Server 2003 after the computer had been in operation for some time, and then restarted Analysis Services, this service took longer to restart than without large page support (from several minutes to occasionally much longer, sometimes requiring a system restart due to the inability of this service to acquire the specified amount of contiguous large pages because of memory fragmentation). However, we rarely saw this symptom with Windows Server 2008. When we enabled large page support for Analysis Services with Windows Server 2008, this service would restart quickly.

Test Methodology

For our processing tests, we processed the following Analysis Services objects:

· A single measure group containing 158 partitions. The size of these partitions on disk was approximately 9 GB. We fully processed this measure group after unprocessing all of the partitions in the measure group.

· A dimension containing 89 attributes, with the key attribute containing approximately 26 million distinct members. The size of this dimension on disk was approximately 38 GB. We fully processed this dimension after unprocessing the dimension.

· A dimension containing 47 attributes, with the key attribute containing approximately 75,000,000 distinct members. The size of this dimension on disk was approximately 18 GB. We incrementally processed this previously processed dimension (process update).

With each processing test, we always worked with warm file system cache (we did not reboot the computer between tests). That is, for each test, we tested once with the cold file system cache and then tested three times with the warm file cache to confirm that our test results were reproducible.

Note: We chose large dimensions and measure groups to attempt to maximize the potential impact of memory preallocation. The performance benefit of memory preallocation with smaller dimensions and partitions was not tested.

Test Results

In our tests, we observed the following:

· Using default settings, the processing performance of Analysis Services 2008 on Windows Server 2008 was always superior to the processing performance of Analysis Services 2008 on Windows Server 2003 (see Figure 1).

· Using memory preallocation with Analysis Services 2008 on Windows Server 2003 enabled us to match the processing performance of Analysis Services 2008 on Windows Server 2008 with default settings (See Figure 2).

· Using memory preallocation with Analysis Services 2008 on Windows Server 2008 provided no measurable performance benefit (See Figure 3).

Note: The results that we saw may not apply across all hardware platforms or with all data sets. We verified our test results on an HP Superdome with 32 IA64 single-core processors and 64 GB of RAM to compare the x64 and IA64 platforms with respect to this configuration setting. We did not verify our test results with SQL Server 2005, but do expect that we would see very similar results with this version of SQL Server. We also did not verify our results on any other hardware platforms.

Caution: If you choose to preallocate memory with Analysis Services 2008 (or Analysis Services 2005) when running on Windows Server 2003, test thoroughly to determine if there is a performance benefit in your environment. If you do preallocate memory, you must ensure that sufficient memory remains for the operating system and other services on your computer.

Partition Processing

During our partition processing tests, we observed performance improvements with memory preallocation of approximately 100% (see Figure 1).

Figure 1: Analysis Services 2008 Partition Processing

Dimension Processing

During our dimension processing tests, we observed performance improvements with memory preallocation of between 30-40% (see Figures 2 and 3).

Figure 2: Analysis Services 2008 Dimension Processing—Full

Figure 3: Analysis Services 2008 Dimension Processing—Incremental

Note: While we did not include MDX querying in this memory preallocation test, we observed the benefit of memory preallocation with SQL Server 2005 Analysis Services on Windows Server 2003 when we tested the performance of MDX queries involving many-to-many dimensions. For more information, see Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques. We expect the results that we saw in this test when running Analysis Services 2008 on Windows Server 2008 to apply equally to MDX queries, but more testing is required in this area.

Determining the Appropriate Value for Memory Preallocation

· If you set the memory preallocation value too high, you will starve other processes on the server and the result will be memory paging. If you are running the SQL Server relational engine on the same server as Analysis Services, you should also limit the maximum amount of memory used by the SQL Server relational engine. While there is no hard limit for how much memory you should leave for the operating system, 6-8 GB is generally sufficient if no other services are running on the same server.

· Set the memory preallocation to a value that is high enough to ensure that Analysis Service preallocates sufficient memory for Analysis Services query and processing activity on the server such that it does not need to request any additional address space. To determine this value, monitor the peak value for the Process: Private Bytes counter for the msmdsrv instance. The peak value establishes the maximum value for memory preallocation that you need to set. When you cannot set the preallocation value this high, set it as high as possible without starving other processes and the operating system.

Conclusion

For optimum performance with SQL Server 2008 Analysis Services default settings, use Windows Server 2008. To match the performance of SQL Server 2008 Analysis Services running on Windows Server 2008 when running SQL Server 2008 Analysis Services on Windows Server 2003, consider using memory preallocation. These conclusions apply to SQL Server 2005 as well as to SQL Server 2008.

Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned

CarlRabeler — Wed, 09 Jul 2008 20:24:00 GMT

Author: Carl Rabeler
Test Contributers: Jim Carroll, David Raskino, Bill Emmert, Dan Manrique, Jim Dugan
Technical Reviewers: Robert Bruckner, David Raskino, Lukasz Pawlowski, Burzin Patel, Kun Cheng, Thierry D'Hers, Prem Mehra, Kevin Cox

Introduction

Microsoft SQL Server 2008 Reporting Services contains many improvements designed to improve performance and capacity. The rearchitecture of the Reporting Services 2008 server as a single server with embedded HTTP capabilities enables Reporting Services 2008 to exercise more control over resource management (thread, memory, and state management) to improve performance and scalability, as well as configurability. The new report engine architecture includes a new on-demand processing model designed to ensure that sufficient memory is always available to execute very large reports and heavy workloads from a large number of concurrent users (by paging and releasing memory in response to memory pressure). Using an early RC0 build (which was not an optimized build), we tested the performance of SQL Server 2008 Reporting Services versus SQL Server 2005 Reporting Services, SP2 (which was optimized) on several scale-up hardware platforms. This technical note discusses our tests and our learnings.

Executive Summary

Reporting Services 2008 was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer. In stark contrast, Reporting Services 2005 generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer.

Our tests clearly show that the new memory management architecture of the report server enables Reporting Services 2008 to scale very well, particularly on the new four-processor, quad-core processors. With our test workload, Reporting Services 2008 consistently outperformed SQL Server 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput. Furthermore, with these renderers on this hardware platform, Reporting Services dramatically outperformed other hardware platforms regardless of Reporting Services version, responding to 3–5 times the number of requests than when running on either of the other hardware platforms. As a result, we recommend that you scale up to four-processor, quad-core servers for performance and scale out to a two-node deployment for high availability. Thereafter, as demand for more capacity occurs, add more four-processor, quad-core servers.

Finally, with all renderers and with all hardware platforms using our test workload, the performance bottlenecks were the processor on the front-end server and the disk subsystem on the data source with Reporting Services 2008, whereas the Reporting Services front-end Web service was the performance bottleneck with Reporting Services 2005.

Note: This technical note assumes that you are familiar with the following articles:

· Planning for Scalability and Performance with Reporting Services

· Reporting Services Scale-Out Architecture

Test Environment

We used a standard remote catalog environment for all testing. Figure 1 illustrates the test environment for our scale-up performance tests. For our Reporting Services 2008 tests, SQL Server 2008 components were installed on all servers and for our Reporting Services 2005 tests, SQL Server 2005 components were installed on all servers. Windows Server 2003 was used for all our tests.

Figure 1: Reporting Services Scale-Up Architecture with Remote Catalog and Remote Data Source

Reporting Services “Front-End” Servers

For the Reporting Services front-end server, we tested Reporting Services 2008 and Reporting Services 2005 on the following three hardware platforms:

· Hardware Configuration #1 (2x2): One HP Proliant BL 460c G1 computer with two x64 dual core processors and 16 GB RAM

· Hardware Configuration #2 (4x2): One Dell 2970 computer with four x64 dual core processors and 16 GB RAM

· Hardware Configuration #3 (4x4): One HP Proliant BL 460c G5 computer with four x64 quad core processors and 16 GB RAM

Reporting Server Catalog Servers

For the Report Server catalog server, we used one HP Proliant BL 460c G1 computer with two x64 dual core processors and 8 GB RAM for all tests. The Report Server catalog server was connected to an EMC Clarion CX3 Model 40 SAN.

Reporting Data Source Server

For the Report Server data source server, we used one HP Proliant BL 460c G5 computer with four x64 quad core processors and 16 GB RAM for all tests. The Report Server data source server was connected to an EMC Clarion CX3 Model 40 SAN.

Important: We used the same configuration for the Report Server catalog server and the Report Server data source server for all tests. Our testing with Reporting Services 2008 revealed that disk queuing was a performance bottleneck and that even closer attention must be paid to disk configuration for optimum performance with Reporting Services 2008 than with Reporting Services 2005. This close attention includes the disk configuration for the source database(s) as well as the ReportServer and ReportServerTempDB databases. For more information about optimizing storage for best performance, see Storage Top 10 Best Practices and Predeployment I/O Best Practices.

Test Harness

For our performance tests, we used Visual Studio Team System 2008 Test Edition (VSTS) to test a report load based on the Adventure Works sample relational database. For our tests, we created 50 separate reports of varying complexity and size and designed for different user personas, such as Executive, Territory Manager, Product Manager, and Sales Manager. The report load consisted of:

· A mix of execution modes with 95% live and 5% snapshot

· A mix of data region types with 80% table and 20% chart

· A mix of query data sizes with 60% small, 20% medium, and 20% large

All tests were executed using the same mix of these reports. These reports were executed with a think time of 20 seconds between the execution of each report and we ramped up to a specified number of users. While we conducted our tests with different numbers of users, this technical note focuses primarily on our 5,000-user test as the most representative of the differences and similarities between Reporting Server 2008 and Reporting Services 2005 (Ramp Up Test). At the end of this technical note, we discuss the results of a push test to determine how far we can push Reporting Services 2008 compared to SQL Server 2005, and to observe where the bottleneck occurs with each version of Reporting Services (push test). We executed test runs for the following report renderers:

· HTML

· PDF

· XLS

· Mix (approximately 33% of each renderer)

Note: We modified the MaxActiveReqForOneUser configuration setting for this test to enable us to test with this number of users.

Test Results

The following sections discuss the results that we observed with each report renderer on each hardware platform.

Ramp Up Test with a Mix of Report Renderers

With a mix of report renderers, Reporting Services 2008 outperformed Reporting Services 2005 on the 4x4 and 4x2 hardware platforms, with the 4x4 hardware platform substantially outperforming the 4x2 and 2x2 hardware platforms regardless of the Reporting Services version. Figures 2, 3 and 4 graphically display the errors/sec, average response time, and total requests counters for these hardware platforms for each version of Reporting Services.

Errors/Sec

When we analyzed the errors/sec counter values from our ramp up test with a mix of report renderers, we discovered that excessive HTTP 503 Service Is Unavailable errors occurred during the Reporting Services 2005 tests on all hardware platforms at approximately 4,500 users (see Figure 2). During the Reporting Services 2008 tests on the three hardware platforms with the mix of report renderers, we observed that no such hard limit was hit. We observed that this same limit was hit with all report renderers during our Reporting Services 2005 tests and was not hit with any of our Reporting Services 2008 tests. Reporting Services 2008 manages memory under heavy load much better than Reporting Services 2005, and it avoids overloading the Reporting Services front-end server. The excessive HTTP 503 errors with Reporting Services 2005 indicate that the heavy load coming from the clients overloaded the Reporting Services front-end server to the point where the server is simply starved for resources, and reveal that memory management under load is clearly a problem with Reporting Services 2005.

Note: The Errors/sec counter value includes HTTP errors (HTTP 500 and HTTP 503 errors). HTTP 503 errors with Reporting Services tell you that the Web service itself is unable to respond (frequently due to insufficient memory). HTTP 500 errors with Reporting Services tell you that the Web service was unable to complete the request for some unknown reason (generally due hardware bottlenecks on the front-end server, the network or the back-end server). During the push test discussion (later in this technical note), we analyze in more detail the bottlenecks we saw with Reporting Services 2008 that resulted in HTTP 500 errors.

Figure 2: Mix of Renderers—Errors/Sec

Average Response Time

When we analyzed the average response time counter values from our ramp up test with a mix of report renderers, we discovered that the average response time with Reporting Services 2008 was better than with Reporting Services 2005 on the 4x4 and 4x2 hardware platforms (see Figure 3). We discovered no significant difference in the average response time on the 2x2 hardware platform. Furthermore, we discovered that the 4x4 hardware platform substantially outperformed either of the other two hardware platforms with either Reporting Services 2008 or Reporting Services 2005. Finally, we also noticed that the improvement of the average response time of Reporting Services 2008 on the 4x2 hardware platform over Reporting Services 2005 became more significant as the number of users submitting requests increased.

Figure 3: Mix of Renderers—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 3 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers.

Total Requests

When we analyzed the total requests counter values from our ramp up test with a mix of report renderers, we discovered that not only did Reporting Services 2008 handle a greater number of users submitting reports without generating excessive timeout or service unavailable errors and render those reports with a better average response time, Reporting Services 2008 also sustained a higher throughput than Reporting Services 2005 on the 4x4 and 4x2 hardware platforms (see Figure 4). This increased throughput was most substantial on the 4x4 hardware platform, on which the throughput for the duration of the test was approximately 40% better with Reporting Services 2008 than with Reporting Services 2005. The new memory management capabilities of Reporting Services 2008 enabled the superior processing capability of the 4x4 hardware platform to perform substantially better than Reporting Services 2005 on the same platform.

Figure 4: Mix of Renderers—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 5 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Ramp Up PDF Report Renderer Test

With the PDF report renderer, Reporting Services 2008 outperformed Reporting Services 2005 on the 4x4 and 4x2 hardware platforms, with the 4x4 hardware platform substantially outperforming the 4x2 and 2x2 hardware platforms regardless of the Reporting Services. Figures 5 and 6 graphically display the average response time and total requests counters for each hardware platform for each version of Reporting Services. With the PDF report renderer, until the HTTP 503 error threshold discussed previously was hit with Reporting Services 2005, the processor was the performance bottleneck.

Average Response Time

When we analyzed the average response time counter values from our ramp up test with the PDF report renderer, we discovered that the average response time with Reporting Services 2008 was better than with Reporting Services 2005 on the 4x4 and 4x2 hardware platforms (see Figure 5). Furthermore, we discovered that the 4x4 hardware platform substantially outperformed either of the other two hardware platforms with either Reporting Services 2008 or Reporting Services 2005.

Figure 5: PDF Renderer—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 5 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Total Requests

When we analyzed the total requests counter values from our ramp up test with the PDF report renderer, we discovered that Reporting Services 2008 responded to a significantly greater number of requests compared to Reporting Services 2005 over the same amount of time only on the 4x4 hardware platform (see Figure 6).

Figure 6: PDF Renderer—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 6 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Ramp Up XLS Report Renderer Test

With the XLS report renderer, Reporting Services 2008 outperformed Reporting Services 2005 primarily on the 4x4 hardware platform, with the 4x4 hardware platform substantially outperforming the 4x2 and 2x2 hardware platforms regardless of the Reporting Services version. Figures 7 and 8 graphically display the average response time and total requests counters for each hardware platform for each version of Reporting Services. With the XLS report renderer, until the HTTP 503 error threshold discussed previously was hit with Reporting Services 2005, the processor was the performance bottleneck.

Average Response Time

When we analyzed the average response time counter values from our ramp up test with the XLS renderer, we discovered that the average response time with Reporting Services 2008 was better than with Reporting Services 2005 on the 4x4 and 2x2 hardware platforms (see Figure 7). Furthermore, we discovered that the 4x4 hardware platform substantially outperformed either of the other two hardware platforms with either Reporting Services 2008 or Reporting Services 2005. Finally, we also noticed that (contrary to our tests with the mix of renderers and with the PDF report renderer) the average response time of Reporting Services 2008 and Reporting Services 2005 on the 2x2 hardware platform was somewhat better than on the 4x2 hardware platform, with Reporting Services 2005 outperforming Reporting Services 2008 on this platform. This performance difference was not expected and we did not have time to investigate precisely why this performance difference was observed.

Figure 7: XLS Renderer—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 7 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers. On the 4x4 hardware platform, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors. Notice that the HTTP 503 error threshold with Reporting Services 2005 was only hit on the 4x4 hardware platform; on the other hardware platforms using the XLS report renderer with Reporting Services 2005, the processor was the performance bottleneck.

Total Requests

When we analyzed the total requests counter values from our ramp up test with the XLS renderer, we discovered that Reporting Services 2008 responded to a significantly greater number of requests compared to Reporting Services 2005 over the same amount of time only on the 4x4 hardware platform (see Figure 8). Finally, we noticed that the total requests of Reporting Services 2008 and Reporting Services 2005 on the 2x2 hardware platform were slightly higher than on the 4x2 hardware platform.

Figure 8: XLS Renderer—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 8 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On the 4x4 hardware platform, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors. Notice that the HTTP 503 error threshold with Reporting Services 2005 was hit only on the 4x4 hardware platform; on the other hardware platforms using the XLS report renderer with Reporting Services 2005, the processor was the performance bottleneck.

Ramp Up HTML Renderer Test

With the HTML report renderer, Reporting Services 2008 did not significantly outperform Reporting Services 2005 on any of the hardware platforms. Furthermore, the 4x4 hardware platform did not substantially outperform either the 4x2 or the 2x2 hardware platforms regardless of the Reporting Services version. Figures 9 and 10 graphically display the average response time and total requests counters for each hardware platform for each version of Reporting Services.

Note: It should be noted again here that our analysis of the performance counters on the Report Server catalog server as well as on the reporting data source server indicated that these tiers were a performance bottleneck with Reporting Services 2008 and were not a performance bottleneck with Reporting Services 2005. Based on our workload and our hardware/software configuration for these tests, Reporting Services 2008 could have performed even better than it did with more tuning and/or with more hardware capability on these tiers than Reporting Services 2005. We do not know, with more capacity on the back-end servers, whether the HTML renderer on Reporting Services 2008 would have outperformed the HTML rendered on Reporting Services 2005. Further testing in this area is needed.

Average Response Time

When we analyzed the average response time counter values from our ramp up test with the HTML renderer, we discovered that Reporting Services 2008 on the 4x2 and the 2x2 hardware platforms had a better average response time than Reporting Services 2005 on the same hardware platform. However, Reporting Services 2008 on the 4x4 hardware platform suffered from degradation in response times as the number of users and the workload increased (see Figure 9). We are not sure why this degradation occurred and the root cause requires more analysis and testing.

Figure 9: HTML Renderer—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 9 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Total Requests

When we analyzed the total requests counter values from our ramp up test with the HTML renderer, we discovered that Reporting Services 2008 did not render a significantly higher number of reports in the same amount of time compared to Reporting Services 2005 (see Figure 10) until after the HTTP 503 error threshold was reached.

Figure 10: HTML Renderer—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 10 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Push Test

We also performed some limited testing to test how far we could push the 4x4 hardware platform without significant errors. To accomplish this, we modified our VSTS test to eliminate the 20-second think time. We tested with the mix of report renders and then pushed the 4x4 hardware platform for 25 minutes, ramping up to 10,000 users over the first 10 minutes and then continuing at that number of users for the duration of the push test.

Errors/Sec

When we analyzed the errors/sec counter values from our push test with a mix of report renderers, we discovered that excessive HTTP 503 Service Is Unavailable errors occurred during the Reporting Services 2005 tests on all hardware platforms at 9 minutes into our test at approximately 9,000 users (see Figure 11). During the Reporting Services 2008 push test on the 4x4 hardware platform, we observed that no such hard limit was hit.

Figure 11: Push Test with Mix of Report Renderers—Errors/Sec

Http Errors vs. Request Time-Out Errors

To better understand the hardware limits we hit with Reporting Services 2008 compared to Reporting Services 2005, we compared the HTTP 503 errors we saw with Reporting Services 2005 with the Reporting Services time-out errors that we saw with Reporting Services 2008. We discovered that while the failure rate for reports with Reporting Services 2005 was directly correlated with HTTP 503 errors, the failure rate for reports with Reporting Services 2008 was directly correlated with time-out errors. These time-out errors, combined with our analysis of the disk queuing on the data source, indicate that the data source was unable to keep up with the request for data from the front-end servers.

Figure 12: Push Test – Detailed Error Analysis

Note: The chart lines for the Reporting Services 2005 tests in Figure 12 only include the errors for the user load before the number of errors/sec climbed to the point where they completely skewed the overall numbers.

Average Response Time

When we analyzed the average response time and total requests counter values from our push test with a mix of report renderers (see Figure 13 and Figure 14), we discovered that while the average response time with Reporting Services 2005 was better than with Reporting Services 2008 until Reporting Services 2005 was completely overwhelmed and unable to keep responding (at about 9,000 users), Reporting Services 2008 just kept on rendering reports for a much greater throughput. Based on the disk queuing counters that we observed on the Reporting Services data source, we believe that significantly greater response times could have been achieved with Reporting Services 2008 with more hardware capacity.

Figure 13: Push Test with Mix of Report Renderers—Average Response Time

Figure 14: Push Test with Mix of Report Renderers—Total Requests

Miscellaneous

During our testing, we also observed the following:

1. The Reporting Services 2005 best practice of using the file system for extreme loads was observed to have a negative impact on results and should be avoided in Reporting Services 2008. For more information on this setting in Reporting Services 2005, see Planning for Scalability and Performance with Reporting Services.

2. During our initial optimization of the default Reporting Services 2008 and Reporting Services 2005 configurations, the only change that we made was to preallocate space to the ReportServerTempDB database. This change had a significant beneficial impact on performance. We did not attempt to optimize the performance of this database by placing this database on a very fast drive or by spreading this database across multiple drives. From our analysis of our tests, we believe that the disk configuration of the ReportServer and ReportServerTempDB databases is even more important in Reporting Services 2008 than in Reporting Services 2005. Based on our tests, we believe that every Reporting Services 2008 installation or upgrade should pay close attention to the disk configuration of these databases. For additional information about optimizing disk configuration for Reporting Services 2005, see Planning for Scalability and Performance with Reporting Services. While the advice in this white paper is targeted to Reporting Services 2005, most or all of the advice is applicable to Reporting Services 2008. More testing is probably advised in this area.

3. At the end of our tests, we made some preliminary tests to determine if Windows Server 2008 would provide some benefit over Windows Server 2003. Among the improvements in Windows Server 2008 are improvements in memory management and in the TCP/IP stack. While there was some improvement of Reporting Services 2008 on Windows 2008 versus Reporting Services 2008 on Windows 2003, the data that we collected was inconclusive. We expect that the rendering of many larger reports may well benefit from the memory performance improvements in Windows Server 2008. In addition, subsequent to the build that we used for our tests, there is an improvement in Reporting Services 2008 related to the rendering of images (PDF, EMF/Print, TIFF) when Reporting Services 2008 is running on Windows Server 2008. Based on other testing, this has been shown to result in about a 5.7% performance benefit for image rendering. More testing is required to determine the benefit of Windows Server 2008 in conjunction with Reporting Services 2008.

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].

Event	SubEvent	Events	Duration
Event	SubEvent	Events	Usage-Based Optimization	No Aggregations	Handmade Aggregations
Query Subcube	Cache data	1329	2,960	464	512
Query Subcube	Non-cache data	204	1,514	1,223	1,480