Technical Notes

SQL Server Reporting Services Disaster Recovery Case Study

denny.lee — Fri, 23 Oct 2009 16:01:00 GMT

Authors: Ayad Shammout (CareGroup Healthcare), Denny Lee

Technical Reviewers: Lukasz Pawlowski, Sanjay Mishra, Prem Mehra, Michael Thomassy, Lindsey Allen, Burzin Patel, Andy Wu, Thomas Kejser, Carl Rabeler, Quoc Bui

Applies to: SQL Server 2005, SQL Server 2008

Introduction

There are many ways to perform disaster recovery with Microsoft SQL Server Reporting Services (SSRS). Based upon customer experience and internal testing, this technical note provides guidance around best practices to design and manage robust end-to-end disaster recovery (DR). This DR method will involve both automatic and manual failover in the form of content switches, SQL Server failover clustering, and database mirroring. This case study focuses on the lessons learned from CareGroup Healthcare System.

Environment Setup

Figure 1 shows a basic disaster recovery scenario. The left side (in green) represents the primary data center, while the right side (in orange) represents the disaster recovery (DR) site. In general the two sites should be identical or at least very similar to each other so that the DR site will be able to handle the load of the primary data center; all of this should be transparent to the users.

Figure 1: Primary data center and the disaster recovery site

Primary Data Center

The primary data center contains the Reporting Services Web service servers (there is only one for the purpose of this diagram); the report catalogs are placed on a separate server (RSDB). For more information about how to design a scale out Reporting Services environment, see the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series. As a general rule, you should set up the report catalogs (RSDB) in a failover cluster, because the report catalogs are central to the Reporting Services environment. For more information about configuring and optimizing report catalogs, see Report Server Catalog Best Practices.

Disaster Recovery Site

The disaster recovery site should closely duplicate the primary data center Reporting Services environment. It should be located in another geographic location such that if the primary data center experiences a resource disruption (for example, in connectivity or power), all traffic can be redirected to the alternate site with minimal disruption to service. In noncritical scenarios, you can utilize fewer resources (for example, less powerful servers, fewer servers) but for mission-critical systems, we highly recommend a server-for-server duplication of resources.

Network Setup

To ensure connectivity from the clients to the primary data center and the disaster recovery site, a common technique is to use a content switch to load-balance traffic within the individual sites as well as between the global sites. In the case of CareGroup Healthcare, a Cisco GSS is used as the content switch. As well, there is direct fiber network connectivity between the primary data center and the disaster recovery site to ensure minimal latencies for any communication between the two centers. If the primary site goes down for any reason, the content switch transparently redirects all client traffic to the disaster recovery set of Reporting Services servers. If the content switch is unavailable, the IP address can be changed at the DNS level. This latter change is a manual switch with a slightly longer network outage, which is due to the DNS cache clearing the old IP address and pointing to the new one.

Database Setup

For the CareGroup Healthcare environment, the primary RSDB instance within the primary data center is named Boston\sql4 and is a SQL Server cluster using SQL Server 2008 Enterprise, with the content switch pointing to the alias sql4. An active/passive failover cluster is used; this allows other active database instances to be located on the server in the passive node. This technique works well if the passive node is not overutilized such that it can handle the Reporting Services workload if the Reporting Services active node goes offline. It may make sense to go to an active/active cluster if there is enough traffic, concurrent users, and/or resources, though the discussion of the tradeoffs between idle machines and high availability scenarios is outside the scope of this technical note. Each of these clustering techniques has unique advantages; ultimately, it is important to ensure that the report catalogs are consistently running with minimal downtime. For more information about how to best configure a SQL Server clustering environment, see SQL Server 2008 Failover Clustering.

To ensure that the RSDB within the disaster recovery site is up-to-date, a common technique is to use asynchronous database mirroring. For the CareGroup Healthcare environment, the mirrored RSDB instance is named Montréal\sql4 and is a SQL Server cluster using SQL Server 2008 Enterprise. Note that while the domain names are different (Montréal vs. Boston in this case), the SQL Server clusters for the primary data center and the disaster recovery site have the same name (more on this later). As noted in Report Server Catalog Best Practices, the key database to keep in sync is the report server (RSDB) database, because it stores all of the report metadata, including report definitions, report history and snapshots, and scheduling information. All Reporting Services operations must connect to this database to access its metadata to perform their functions. The reason asynchronous database mirroring is commonly chosen is that asynchronous mirroring has minimal or no impact on response time performance. In addition, asynchronous mirroring works well because RSDB metadata is not frequently updated. For more information about database mirroring, see SQL Server Replication: Providing High Availability using Database Mirroring and Asynchronous Database Mirroring with Log Compression in SQL Server 2008.

The following table describes an example endpoint setup for these database mirrors.

Mirror Setup

Boston\sql4 (Primary)

Montréal\sql4 (DR site)

Name

Principal

Partner

Listener Port

50212

Encryption

Yes

Role

Partner

Initializing Database Mirror

A relatively easy way to initialize a database mirroring setup is to:

1) Make full and transaction log backups of the Reporting Services databases on the principal server.

2) Copy the backups over to the disaster recovery site, restoring each Reporting Services database in no-recovery mode.

3) Set up the failover partner on the mirror (that is, the DR site) before you set up the failover partner on the principal server.

Failover Scenarios

There are three main disaster recovery scenarios that can occur within this Reporting Services disaster recovery environment:

· Reporting Services servers within the primary data center go offline.

· The active server for the RSDB in the primary data center failover cluster goes offline.

· The entire cluster for the RSDB in the primary data center goes offline.

Please note that this note does not explicitly cover the scenarios concerning the Reporting Source databases. While most of the information here is applicable to these databases (such as data marts, operational data stores, transactional systems, and data warehouses), they have their own disaster recovery requirements and procedures that should be separate from the reporting infrastructure.

Reporting Services Servers within the Primary Data Center Go Offline

As noted in Figure 2, both Reporting Services servers point to the same cluster of databases. In this case, they both point to the active/passive database cluster Boston\sql4.

Figure 2: Reporting Services servers within the Primary Data Center go offline

In this scenario where the Reporting Services servers within the primary data center go offline, the hardware content switch will detect a network connectivity issue and automatically fail over to the disaster recovery site. By default, the Reporting Services farm connects to the report catalog in the primary data center because it has the most up-to-date information (provided that the network is fast and responsive). Therefore in most cases, the client connectivity to the Reporting Services environment in the DR site is minimally impacted (clients might need to be closed and restarted to establish a new network connection, or they could receive a broken link/page in the browser and need to refresh or restart their report execution).

Active RSDB Server in the Primary Data Center Failover Cluster Goes Offline

If the active server in the primary data center failover cluster goes offline, the active/passive setup of the SQL Server failover cluster will kick in. The active server will automatically fail over to the passive instance of the node with little to no service interruption. The report server may report that it cannot connect to the report server database, but after the passive node comes online, it will automatically re-establish the connection. All report server connectivity will continue as previously performed except that now the passive instance has become the active instance.

Figure 3: Active cluster for RSDB in the primary data center goes offline

Entire RSDB Cluster in the Primary Data Center Goes Offline

If the primary data center RSDB SQL Server cluster goes offline, the disaster recovery solution is to manually fail over to the RSDB on the disaster recovery site to continue serving up reports. But note that this is a manual failover process, which requires manual configuration of the Reporting Services servers in the Reporting Services farm to point to the RSDB instance within the disaster recovery site. It is important to be aware that, with asynchronous mirroring, manual failover by definition forces the service to allow data loss. With Reporting Services, however, the data loss should be minimal because report metadata is not frequently updated and snapshots can be re-created. By configuring the primary data center and disaster recovery site RSDBs with identical instance names (recall that both instances are named sql4 but that they are in different domains), the manual failover is simplified because the Reporting Services servers will connect to an identically named instance of the RSDB (different domain, but same name).

Figure 4: Entire cluster for RSDB in the primary data center goes offline

Primary Data Center Outage Scenarios

If the entire primary data center goes offline for either a planned or unplanned outage, the content switch will automatically fail over to the Reporting Services farm in the disaster recovery site, but you will need to perform a manual failover to ensure that the Reporting Services servers can connect to the copy of the RSDB in the disaster recovery site.

Planned Outages

For planned outages:

1) The content switch will perform the task of suspending the primary IP addresses and activating the DR site IP addresses so that all connections will be redirected to the disaster recovery site.

2) You will need to execute a script on the primary database to manually switch to the mirrored database. After the partner database becomes the primary, the same script can be re-executed to switch back to the original primary database. Here is an example:

-- Script to manually fail over to the mirror database

-- Example: switch from Boston\sql4 to Montreal\sql4

USE [master]

GO

-- Database ReportingServer

ALTER DATABASE ReportingServer SET PARTNER FAILOVER

GO

Unplanned Outages

For unplanned outages:

1) The content switch will perform the task of suspending the primary IP addresses and activating the DR site IP addresses so that all connections will be redirected to the disaster recovery site.

2) You will need to run a manual failover script to force the service to switch with possible data loss. The script needs to run on the mirrored server in the case of loss of the principal server. Here is an example:

-- Script to force database fail over of Boston\sql4 to Montreal\sql4

-- in case of SQL Server instance failure

USE [master]

GO

-- Database ReportingServer

ALTER DATABASE ReportingServer

SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

GO

After this script is run, the mirror server is the principal server and the mirror itself is suspended.

For more information about Reporting Services configuration for the database connection, see Configuring a Report Server Database Connection.

Break/Rebuild the Mirror

After the system goes down, it is undesirable to have the active system push data to the passive system (it cannot anyways). So it is important to break the mirror with the primary data center server and then re-establish it after the primary data center (in this example, Boston) server is back online. Note that the disaster recovery (in this example, Montréal) server is the one that is now active, so you will need to manually transfer the data from the DR site server back to the primary data center server (in this example, Montréal to Boston). To fully break the mirror, execute the following on the primary server:

ALTER DATABASE ReportingServer SET PARTNER OFF

RESTORE DATABASE ReportingServer WITH RECOVERY

To rebuild the mirror, follow the original steps in the Initializing Database Mirror section above.

Detail Logistics

It is important to understand that the Reporting Services report jobs, schedules, and subscriptions will not be mirrored between the RSDB from the primary data center and the disaster recovery site. This occurs because SQL Server Agent jobs are stored in the msdb database, which is not mirrored. A common method to resolve this issue is to script all of these Reporting Services jobs in the primary site and then use these scripts to re-create them on the disaster recovery site (with the jobs disabled); activate these jobs only if the system fails over. In the case of Reporting Services database migration, it is not necessary to perform this operation, as noted in How to: Migrate a Reporting Services Installation.

Note that logins created on the primary site may not be replicated to the disaster recovery site. To work around this problem, we recommend storing all of these logins into their own database and mirroring this database to the DR site as well. Then create a script to generate the logins within the DR site to re-establish the logins. For more information, see How to transfer logins and passwords between instances of SQL Server.

Conclusion

With these guidelines, you can prepare your Reporting Services environment for disaster recovery scenarios. The key here is that you will need to use a combination of hardware components, network connectivity, SQL Server database mirroring, and architecture to create a disaster recovery infrastructure to support an enterprise SQL Server Reporting Services environment. As noted, this case study has used specific techniques such as active/passive clustering and asynchronous database mirroring to support their disaster recovery requirements. Your situation may be different, but this technical note should provide you with the basic framework for Reporting Services disaster recovery. The hope is that you will never need to use it, but at least now you are ready in case you do.

Deploying a Business Intelligence Solution Using SharePoint, Reporting Services, and PerformancePoint Monitoring Server with Kerberos

CarlRabeler — Tue, 13 Oct 2009 21:23:00 GMT

Author: Carl Rabeler

Reviewers: Prem Mehra, Troy Starr, Kevin Donovan, Burzin Patel, Doron Bar-Caspi, Prash Shirolkar, John Wyss

This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

Note: The business intelligence solution discussed in this technical note was configured and tested in the SQL CAT customer lab and is currently being deployed (substantially as discussed in this article) at a customer site.

Design Requirements

The design requirements for this business intelligence solution are:

· Deploy the solution into a Windows Server 2008 R2 domain.Deploy all server applications in this solution onto servers running Windows Server 2008 R2.

· Deploy a server farm running Office SharePoint Server 2007 (SharePoint), which includes the SharePoint Web front-end (WFE) and the application server, on one server and the SQL Server (SQL Server) database on another server.

· Configuring the SharePoint services for later scale-out to multiple SharePoint WFE front-end servers and multiple SharePoint application servers

· Deploy SQL Server 2008 Reporting Services (Reporting Services) on a server, integrate Reporting Services with SharePoint, and deploy its databases to a shared metadata database server

· Deploy Microsoft Office PerformancePoint Server 2007 Monitoring Server (PerformancePoint) on the SharePoint application server, deploying its Monitoring System database to the shared metadata database server, and configuring PerformancePoint for later scale-out to a dedicated application server.

· Utilize SQL Server and SQL Server Analysis Services (Analysis Services) data sources (the data source servers) for the Reporting Services reports and PerformancePoint dashboards.

· Design reports using Report Builder 2.0 and PPS Dashboard Designer.

· Execute Reporting Services reports and view PPS dashboards utilizing Windows® Internet Explorer® 7 or Internet Explorer 8.

· Configure the MOSS application server to authenticate business users retrieving business intelligence data into their Reporting Services reports and PPS dashboards from the the data source servers using the business user’s authentication credentials.

Solution Architecture

The following two diagrams represent the solution architectures for the initial deployment and for eventual scale-out. The discussion in the remainder of this document describes the challenges we faced and our resolutions in implementing this solution architecture.

Initial Deployment Architecture

In this initial deployment architecture, the SharePoint WFE and application server along with the PerformancePoint Server are located on a single application server (called the MOSS App Svr in the following diagram), with a single dedicated server on which Reporting Services is installed in SharePoint mode (called the SSRS Svr in the following diagram), and with a single server on which SQL Server is installed that contains all of the metadata for SharePoint, PerformancePoint and Reporting Services (called the Metadata Svr in the following diagram).

Full Scale-Out Deployment Architecture

In this full scale-out deployment architecture, the components in the previous diagram are scaled out to the following components:

· Multiple SharePoint WFE servers (called WFE (1) through WFE (n) in the following diagram).

· Multiple SharePoint application servers (called MOSS App Svr (1) through MOSS App Srv (n) in the following diagram).

· A dedicated SharePoint metadata server (called the MOSS DB Svr in the following diagram).

· A dedicated PerformancePoint Server application server (called the PPS App Svr in the following diagram).

· A dedicated PerformancePoint Server metadata server (called the PPS Mntr Sys DB Svr in the following diagram).

· Multiple Reporting Services application servers (called SSRS Svr (1) through SSRS Svr (n) in the following diagram).

· A dedicated Reporting Services metadata server (called the SSRS DB Svr in the following diagram).

Challenges Encountered and Resolutions

In designing and deploying this solution, we encountered a number of installation challenges. Some were unique to Kerberos, and others were related to software installation. These challenges and our resolutions are discussed next.

Note: This technical note assumes a basic understanding of Kerberos; a full discussion of Kerberos architecture and step-by-step configuration procedures is beyond the scope of this article. For more information about Kerberos architecture, see Microsoft TechNet. It contains has many articles, including the following one: Kerberos. For step-by-step guidance, see the articles that are mentioned in context in this technical note. We include our comments on lessons learned from our use of these articles. There is no single article for this combination of components.

Designing and Configuring for Kerberos Authentication

To enable the middle-tier applications (MOSS, PPS, and SSRS) on the Application server to seamlessly authenticate users in accessing report and dashboard data on the data source servers, Kerberos is required. If it is properly configured, the client computer obtains the Kerberos ticket for the user from the Kerberos Key Distribution Center (KDC) based on properly registered Service Principal Names (SPNs), and then it presents this ticket to the SharePoint server farm when it establishes a connection.

Note: The KDC is a network service that supplies session tickets and temporary session keys to users and computers within an Active Directory® domain. The KDC runs on each domain controller as part of Active Directory Domain Services.

The application servers in the SharePoint server farm, if they are properly configured, impersonate this user when then retrieve the requested data from the appropriate SQL data server (SQL Server or Analysis Services). This is the classic double-hop scenario, which requires Kerberos to securely impersonate a user credentials when connecting to a service on another computer. In order for the Internet Information Services (IIS) service or the Reporting Services service to impersonate the user during connection to the appropriate SQL Data server, the service account under which the IIS service or the Reporting Services service is running must have permission to impersonate, and the computers involved must be authorized for delegation.

Note: For more information about troubleshooting with respect to Kerberos and delegation, see Kerberos authentication and troubleshooting delegation issues.

On the application server in our solution, there are multiple IIS Web applications within MOSS (Central Administration, a portal site, a My Site, and an SSP Administration site) as well as the PPS web application and the SharePoint WFE. These Web applications utilize different identities via dedicated application pools, and these Web applications listen on both default and nondefault ports (that is, ports other than TCP port 80 and SSL port 443). In addition, the WFE forwards the SSRS requests to the SSRS server. In order for impersonation of the user’s Kerberos credentials to work properly, the identity for each Web application must have an SPN registered. Furthermore, the appropriate SPN must be utilized when the client connects to a particular IIS Web application. In addition, the SSRS service on the SSRS server along with the Analysis Services and SQL Server services on the SQL Data servers must each have properly registered SPNs. To implement Kerberos authentication for this environment, we utilized the approach described in the following Microsoft TechNet article: Configure Kerberos authentication (Office SharePoint Server), supplemented by the Excel® Services documentation in the following Knowledge Base article: How to configure SharePoint Server 2007 and Excel Services for Kerberos authentication and PPS-specific information in the following Microsoft TechNet article: Configure Monitoring Server for Kerberos delegation.

Note: One of things we really like about the Configure Kerberos authentication (Office SharePoint Server) TechNet article is its step-by-step approach to verifying that Kerberos is configured and working properly. However, you should be aware that the Event ID in the Event log for a successful login using Kerberos has changed with Windows Server 2008 and Windows Vista®. It is now Event ID 4624 (see Knowledge Base article 947226); it was Event ID 540 in Windows Server 2003 (as documented in the article).

Kerberos Challenge 1:

As pointed out in the Configure Kerberos authentication (Office SharePoint Server) article, Internet Explorer does not, by default, include port numbers in its Kerberos ticket requests. If port numbers are not included, impersonation is attempted using the Kerberos ticket associated with the application pool identity for the Web application listening on port 80 (or port 443 if SSL is involved). Because in our solution architecture we have Web applications listening on both nondefault and default port numbers, this default behavior does not result in the appropriate Kerberos ticket being retrieved for Web applications listening on nondefault ports – resulting in authentication errors.

To enable Internet Explorer to include port numbers in every request, you can change a registry setting on every client. For more information about this behavior and registry key, see Knowledge Base article 908209. While this Knowledge Base article refers only to Internet Explorer 6.0, the behavior and the registry key changes also apply to Internet Explorer 7 and 8. For the customer environment for which we were designing this solution, modifying the registry on each client (even by means of a global policy) was not acceptable. Without the port numbers in the Kerberos ticket request, only one SPN can be registered for each host name.

Our challenge was to design a solution that enabled an SPN for each Web application in IIS such that a unique Kerberos ticket is retrieved for each Web application on the application server if the application is listening on a nondefault port.

Kerberos Resolution 1:

To enable a unique SPN and Kerberos ticket for each IIS Web application listening on a nondefault port, we did the following:

Defined unique host names in DNS for each IIS Web application on the Application server.
Configured each Web application with its own application pool identity (service account).
Registered four SPN variations for each unique host name using the service account identity for that Web application.

To illustrate, we began by creating host names in DNS for the SharePoint portal site (hostname), for Central Administration (hostnameca), and PerformancePoint (hostnamepps). Then we registered SPNs for the short host name as well as the fully qualified domain name (FQDN) (hostname.mydomain.com), registering these names both with and without the port number.

Note: We registered SPNs both with and without port numbers to ensure that our Kerberos configuration worked successfully whether Internet Explorer submitted the port numbers or not.

hostname

setspn -A HTTP/hostname mydomain\mossportalpool
setspn -A HTTP/hostname:80 mydomain\mossportalpool
setspn -A HTTP/hostname.mydomain.com mydomain\mossportalpool
setspn -A HTTP/hostname.mydomain.com:80 mydomain\mossportalpool

hostnameca

setspn -A HTTP/hostnameca mydomain\mossfarmadmin
setspn -A HTTP/hostnameca:11111 mydomain\mossfarmadmin
setspn -A HTTP/hostnameca.mydomain.com mydomain\mossfarmadmin
setspn -A HTTP/hostnameca.mydomain.com:11111 mydomain\mossfarmadmin

hostnamepps

setspn -A HTTP/hostnamepps mydomain\ppsapppool
setspn -A HTTP/hostnamepps:40000 mydomain\ppsapppool
setspn -A HTTP/hostnamepps.mydomain.com mydomain\ppsapppool
setspn -A HTTP/hostnamepps.mydomain.com:40000 mydomain\ppsapppool

This approach resolved the Internet Explorer issue with connecting to a Web application running on a nondefault port. With this approach, when Internet Explorer connects to a virtual hostname for the appropriate Web application (rather than the physical machine name), DNS refers the user to the application server, and it uses the SPN registered for the virtual hostname. As a result, the appropriate Kerberos ticket is retrieved (rather than the Kerberos ticket for the application listening on port 80). Using our example, when Internet Explorer connects to hostnameca or hostnameca:11111, the SPN for the mossfarmadmin is used, and if Internet Explorer connects to hostnamepps or hostnamepps:40000, the SPN for ppsapppool is used.

Note: We also registered SPNs for the service account for the SQL Server relational engine, Analysis Services, and Reporting Services services. Only two SPNs were registered for these services because connectivity to these services is not made by Internet Explorer; instead, the middle-tier services on the application servers connect directly to these services.

Kerberos Challenge 2:

Our solution to Kerberos Challenge 1 required that we configure IIS to utilize different application pool identities for each Web application: for example, hostname, hostnameca, and hostnamepps each used a different application pool identity. In IIS 7.5 with Windows Server 2008 R2 (as well as with IIS 7.0 in Windows Server 2008), IIS utilizes kernel mode authentication by default. Kernel mode authentication runs under the machine account of the IIS server. For more information, see Security Authentication <authentication> and Advanced Settings Dialog Box – Windows Authentication Feature. This change to kernel mode authentication is generally a good thing, because performance is improved and the configuration for Kerberos is simplified in many scenarios; authentication is done under the LocalSystem account, regardless of the application pool identity. However, in a Web farm environment (such as in our scenario), we needed to use the application pool for authentication to ensure the correct Kerberos ticket would be utilized for the appropriate Web service when it was scaled out to multiple application and front-end servers.

Kerberos Resolution 2:

There are two ways to resolve this problem:

· Set the useAppPoolCredentials attribute in the system.webServer/security/authentication/Windows-Authentication configuration section of the ApplicationHost.config file to “True”.

· Disable Kernel Mode authentication.

We chose the second option. As mentioned previously in this technical note, we used this TechNet article: Configure Monitoring Server for Kerberos delegation to help us configure PPS. This article specifically recommends that we configure kernel mode authentication to use application pool credentials with IIS 7.0 and Windows Server 2008. As a result, this was our initial approach to resolving this issue. This solution worked well with Internet Explorer 7. However, with Internet Explorer 8, we encountered an additional challenge: The first time a user connects to a Web application in MOSS that is configured with Kerberos and that uses an application pool identity, the user is repeatedly prompted for authentication when navigating for the first time through nonroot portions of the SharePoint site. We discovered that if we disabled kernel mode authentication, we did not see this behavior.

After discussing the situation with the developers on the SharePoint and Internet Explorer teams, we discovered that we had run into a recently discovered incompatibility with kernel mode authentication when using an application pool identity. The incompatibility was traced back to performance enhancements in Internet Explorer 8 that impact SharePoint 2007. We ran both functional and performance tests in our environment using Internet Explorer 7 and Internet Explorer 8, with kernel mode authentication disabled for all of the Web applications that used dedicated application pool identities, and we determined that this configuration functioned properly and performed well. As a result, our recommended solution is to disable kernel mode authentication with SharePoint 2007 to seamlessly support a mix of Internet browsers.

Kerberos Challenge 3:

The resolutions to our first two Kerberos challenges performed as expected when the SharePoint farm consisted of a single SharePoint server (with either a local or a remote SQL Server database). However, we encountered an issue with this approach when we scaled out to multiple front-end Web servers and multiple application servers. When we scaled out, we changed the name of our Web applications for Network Load Balancing (NLB) to a virtual name, which NLB then directed to a specific physical server. For example, we changed the name for our SharePoint portal site from hostname to kerbportal.mydomain.net and changed our SPNs accordingly. We followed the steps in Update a Web application URL and IIS bindings (Office SharePoint Server) to update the Web application URL and IIS bindings. When we did so, we were unable to view our deployed Reporting Services reports and PerformancePoint dashboards. We received errors stating that the published PPS dashboards and SSRS reports were not able to find the underlying dashboard and report locations. For example, reports that were deployed to http://hostname/ReportServer were still trying to connect to that location, but the newly configured location for the report server was http://kerbportal.mydomain.com/ReportServer. However, the deployed report was not configured to use this new URL path.

Note: For more information about this problem, see section 8.4 of Reporting Services SharePoint Integration Troubleshooting.

Kerberos Resolution 3:

The solution was quite straightforward after we identified the problem. Namely, we needed to configure virtual host names in DNS that would eventually be utilized in NLB, but until it was time to scale out, we pointed these virtual host names in DNS to the single SharePoint server.

So, for example, for the SharePoint portal site, create a DNS entry for a virtual host called (for example) kerbportal that points to the SharePoint server. Later, you can update this entry in DNS to point to the NLB server. Then, make sure to specify kerbportal.mydomain.com in the Host Header field when you create the portal Web site in the Central Administration application in SharePoint. Similarly, configure virtual names for your My Site and SSP Administration sites. Following this approach, adjust the SPNs that you register to utilize the virtual host names that you will use when you later scale out. If you use this approach, you will not have to reconfigure your SPNs when you later scale out, and you will not have to redeploy your Reporting Services reports or your PPS dashboards.

For more information, see Plan for host-named site collections (Office SharePoint Server).

Installing Software Components

During the installation of the various software components, we also encountered a number of installation challenges. While none of these challenges were showstoppers, we present these resolutions to ensure that you can easily resolve these issues if you encounter them. The challenges we faced and how we resolved them are discussed in the following sections.

Installing the SQL Server Reporting Services Add-in on the SharePoint Server on Windows Server 2008 R2

When we installed the SQL Server Reporting Services Add-in (rsSharepoint.msi) on the SharePoint server, we followed the steps listed in SQL Server Books Online: How to: Install or Uninstall the Reporting Services Add-in. When we attempted to install the Reporting Services Add-in (rsSharepoint.msi) on the SharePoint server on Windows Server 2008 R2, the Reporting Services Add-in did not complete successfully, and the installer rolled back the attempted installation. When we reviewed the Application log in Event Viewer, we discovered numerous “Insufficient SQL database permission” errors. We reran the Reporting Services Add-in from a command prompt with elevated permissions, but we received the same errors. To resolve this issue, we followed the “How to Install Reporting Services Add-in in Files-Only Mode” section in How to: Install or Uninstall the Reporting Services Add-in. These steps enabled us to successfully install the Reporting Services Add-in. We verified the successful installation of the Reporting Services Add-in by reviewing the RS_SP_<N>.log file in C:\Users\<login>\AppData\Local\Temp.

Configuring SQL Server Reporting Services in SharePoint Mode for Windows Authentication with User Access Control Enabled

When we configured Reporting Services integration in SharePoint, we followed the steps listed in SQL Server Books Online: How to: Configure Report Server Integration in SharePoint Central Administration. When we configured report server integration settings in Central Administration, we were unable to select Windows Authentication to specify that connections be made using Windows integrated authentication. While this initially seemed to indicate a Kerberos problem, we checked and double-checked our Kerberos configuration and found no errors. After some research on the Internet using Bing, we found the following article Troubleshooting Server and Database Connection Problems that suggested that User Account Control (UAC) might be the problem – although the problem described in this article was not exactly the same as the one we were experiencing. We closed the Central Administration site in Internet Explorer and reconnected with elevated permissions, and then we were successfully able to select Windows Authentication for our SharePoint integration with Reporting Services.

Installing Prerequisites for PerformancePoint Monitoring Server on the SharePoint Server with SQL Server 2008

When we installed the prerequisites for PerformancePoint Monitoring Server on the SharePoint Server with SQL Server 2008, we followed the steps listed in the following TechNet article: Install PerformancePoint Monitoring Server with SQL Server 2008. However, we were unable to install the SQLServer2005_XMO component (XMO 9.0), which is a prerequisite for a fully-functional PPS installation. After some research on the Internet using Bing, we discovered Knowledge Base article 970496, which explained that there was a known incompatibility between the SQL Server Reporting Services Add-in and XMO 9.0. This bug prevents the installation of PerformancePoint Planning Server (not Monitoring Server) and the importing of key performance indicators (KPIs) from SQL Server when using Monitoring Server. However, further research revealed that this bug was fixed in both the Cumulative Update Package 5 for SQL Server 2005 Service Pack 3 and the Cumulative Update Package 15 for SQL Server 2005 Service Pack 2. Details of the fix can be found in Knowledge Base article 972694. By using the newest version of XMO 9.0, we were able to install the SQL Server 2005 Management Objects Collection and subsequently import KPIs from SQL Server.

Use the Application Pool for Root PPS Site and Subsites

When we installed PerformancePoint Monitoring Server, we followed the steps listed in the following TechNet article: Configure Monitoring Server for Kerberos delegation. As discussed previously, we configured a unique application pool and identity for the PPSMonitoringPreview and PPMonitoringWebService subsites, as well as a unique virtual host name and port number for the PPS Web application. The virtual host name enables us to use the appropriate Kerberos ticket for the PPS service account. However, when we used Internet Explorer to connect to the PPSMonitoring site (HTTP://ppsmonitoring.mydomain.com:40000), we were unable to connect to either the WebService or the Preview subsites. Because we were reasonably confident of our Kerberos configuration at this point, we immediately looked at the identity for the application pool for the root site as well as these two subsites. We discovered that while we had configured the application pool identity for each of the subsites to the PPS service account, the root site was not similarly configured – instead, it was configured to use the NetworkService account. As soon as we changed the identity of the application pool for the root site to the PPS service account, PPS worked as expected. Upon rereading the TechNet article, we noted that this step was not clearly stated.

SharePoint with Report Builder 1.0, Not Report Builder 2.0

During our testing of this solution, we created some reports using Report Builder 2.0 to display the user identity when connecting to our SQL Data servers in order to verify that Kerberos was configured and working properly. When we attempted to open one of these reports using the Report Builder ClickOnce application, we received the following error: “System.IO.StreamReader: The Report Element was not found.”

After a quick bit of research on the Internet using Bing, we learned that by default, the ClickOnce application opens Report Builder 1.0 rather than Report Builder 2.0. The solution is to install the add-in, Microsoft® SQL Server® 2008 Report Builder 2.0 ClickOnce for SharePoint in order to make Report Builder 2.0 ClickOnce available from a SharePoint site.

Note: This add-in is separate from the ClickOnce version of Report Builder 2.0 in SQL Server 2008 PCU1.

You then need to configure the default ClickOnce application in Report Manager and in SharePoint Central Administration to open Report Builder 2.0 rather than Report Builder 1.0. The following ReadMe and SQL Server Books Online articles explain the problem and solution:

· Microsoft SQL Server 2008 Report Builder 2.0 ClickOnce for SharePoint Readme

· How to: Set Report Builder 2.0 as the Default ClickOnce Report Builder Version

Summary

With proper planning, you can successfully you can successfully deploy a server farm using Microsoft Office SharePoint Server 2007, Microsoft SQL Server 2008 Reporting Services, and Microsoft Office PerformancePoint Server 2007 Monitoring Server, and you can configure Kerberos authentication for user authentication throughout the server farm. The use of virtual host names for each Web application with a unique application pool identity enables you to specify unique SPNs for each of these Web applications such that Internet Explorer clients can seamlessly connect to the resources within the server farm using their own authentication credentials.

Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads

tkejser — Tue, 22 Sep 2009 09:50:00 GMT

Authors: Thomas Kejser, Lindsey Allen, Arvind Rao and Michael Thomassy

Contributors and reviewers: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng and Howard Yin

Introduction

Recently, we performed a lab test that had a large OLTP workload in the Microsoft Enterprise Engineering Center. The purpose of this lab was to take an intensive Microsoft SQL Server workload and see what happened when we scaled it up from 64 processors to 128 processors. (Note: This configuration is supported as part of the Microsoft SQL Server 2008 R2 release.). The workload had highly concurrent insert operations going to a few large tables.

As we began to scale this workload up to 128 cores, the wait stats captured were dominated by PAGELATCH_UP and PAGELATCH_EX. The average wait times were tens of milliseconds, and there were a lot of waits. These waits were not expected, or they were expected to be a few milliseconds only.

In this TechNote we will describe how we first diagnosed the problem and how we then used table partitioning to work around it.

Diagnosing the Problem

When you see large waits for PAGELATCH in sys.dm_os_wait_stats, you will want to do the following. Start your investigation with sys.dm_os_waiting_tasks and locate a task waiting for PAGELATCH, like this:

SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'

Example Output:

The resource_description column lists the exact page being waited for in the format: <database_id>:<file_id>:<page_id>.

Using the resource_description column, you can now write this rather complex query that looks up all these waiting pages:

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms
, s.name AS schema_name
, o.name AS object_name
, i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd
JOIN (
SELECT *
, CHARINDEX(':', resource_description) AS file_index
, CHARINDEX(':', resource_description
  , CHARINDEX(':', resource_description)) AS page_index
, resource_description AS rd
FROM sys.dm_os_waiting_tasks wt
WHERE wait_type LIKE 'PAGELATCH%'
) AS wt
    ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
    AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)
    AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id

JOIN sys.schemas s ON o.schema_id = s.schema_id

The query shows that the page we are waiting for is in a clustered index, enforcing the primary key, of a table with this structure:

CREATE TABLE HeavyInsert (
ID INT PRIMARY KEY CLUSTERED
, col1 VARCHAR(50)
) ON [PRIMARY]

What is going on here, why are we waiting to access a data page in the index?

Background Information

To diagnose what was happening in our large OLTP workload, it’s important to understand how SQL Server handles the insertion of a new row into an index. When a new row is inserted into an index, SQL Server will use the following algorithm to execute the modification:

Record a log entry that row has been modified.
Traverse the B-tree to locate the correct page to hold the new record.
Latch the page with PAGELATCH_EX, preventing others from modifying it.
Add the row to the page and, if needed, mark the page as dirty.
Unlatch the page.

Eventually, the page will also have to be flushed to disk by a checkpoint or lazy write operation.

However, what happens if all the inserted rows go to the same page? In that case, you can see a queue building up on that page. Even though a latch is a very lightweight semaphore, it can still be a contention point if the workload is highly concurrent. In this customer case, the first, and only, column in the index was a continuously increasing key. Because of this, every new insert went to the same page at the end of the B-tree, until that page was full. Workloads that use IDENTITY or other sequentially increasing value columns as primary keys may run into this same issue at high concurrency too.

Solution

Whenever many threads need synchronized access to a single resource, contention can occur. The solution is typically to create more of the contended resource. In this case, the contended resource is the last page in the B-tree.

One way to avoid contention on a single page is to choose a leading column in the index that is not continually increasing. However, this would have required an application change in the customer’s system. We had to look for a solution that could be implemented within in the database.

Remember that the contention point is a single page in a B-tree. If only there was a way to get more B-trees in the table. Fortunately, there IS a way to get this: Partition the table. The table can be partitioned in such a way that the new rows get spread over multiple partitions.

First, create the partition function and scheme:

CREATE PARTITION FUNCTION pf_hash (TINYNT) AS RANGE LEFT FOR VALUES (0,1,2)

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

This example uses four partitions. The number of partitions you need depends on the amount of INSERT activity happening on the table. There is a drawback to hash-partitioning the table like this: Whenever you select rows from the table, you have to touch all partitions. This means that you need to access more than one B-tree – you will not get partition elimination. There is a CPU cost and latency cost to this, so keep the number of partitions as small as possible (while still avoiding PAGELATCH). In our particular customer case, we had plenty of spare CPU cycles, so we could afford to sacrifice some time on SELECT statements, as long as it helped us increase the INSERT rate.

Second, you need a column to partition on, one that spreads the inserts over the four partitions. There was no column available in the table for this in the Microsoft Enterprise Engineering Center scenario. However, it is easy to create one. Taking advantage of the fact that the ID column is constantly increasing in increments of one, here is a simple hash function of the row:

CREATE TABLE HeavyInsert_Hash(
ID INT NOT NULL
, col1 VARCHAR(50)
, HashID AS CAST(ABS(ID % 4) AS TINYINT) PERSISTED NOT NULL)

With the HashID column, you can cycle the inserts between the four partitions. Create the clustering index in this way:

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

By using this new, partitioned table instead of the original table, we managed to get rid of the PAGELATCH contention and increase the insertion rate, because we spread out the high concurrency across many pages and across several partitions, each having its own B-tree structure. We managed to increase the INSERT rate by 15 percent for this customer, with the PAGELATCH waits going away on the hot index in one table. But even then, we had CPU cycles to spare, so we could have optimized further by applying a similar trick to other table with high insert rates.

Strictly speaking, this optimization trick is a logical change in the primary key of the table. However, because the new key is just extended with the hash value of the original key, duplicates in the ID column are avoided.

The single column unique indexes on a table are typically the worst offender if you are experiencing PAGELATCH contention. But even if you eliminate this, there may be other, nonclustered indexes on the table that suffer from the same problem. Typically, the problem occurs with single column unique keys, where every insert ends up on the same page. If you have other indexes in the table that suffer from PAGELATCH contention, you can apply this partition trick to them too, using the same hash key as the primary key.

Not all applications can be modified, something that is a challenge for ISVs. However, if you DO have the option of modifying the queries in the system, you can add an additional filter to queries seeking on the primary key.

Example: To get partition elimination, change this:

SELECT * FROM HeavyInsert_Hash
WHERE ID = 42

To this:

SELECT * FROM HeavyInsert_Hash
WHERE ID = 42 AND HashID = 42 % 4

With partition elimination, the hash partitioning trick is almost a free treat. You will still add one byte to each row of the clustered index.

DBCC Checks and Terabyte-Scale Databases

stuarto — Fri, 14 Aug 2009 00:13:00 GMT

Author: Stuart Ozer Contributors: Suresh Kandoth, Ryan Stonecipher

Technical Reviewers: Peter Carlin, Burzin Patel, Bob Ward

1. Overview:

Historically, the SQL Server product team and the support organization have recommended that all maintenance plans for Microsoft® SQL Server® include running DBCC CHECKDB on a regular basis to detect and correct potential corruption in the database. In the case of mission-critical applications such as trading systems, medical records, and banking operations, many customers have interpreted this recommendation as requiring even a daily DBCC check to ensure that no errors are present before performing a backup operation.

However, since the SQL Server 6.5 days, in which a daily DBCC CHECKDB may have been a best practice, there have been several trends - both challenges and opportunities -- that make it worth reconsidering the way that DBCC should fit into SQL Server database maintenance plans, especially for large databases.

Challenges:

Many mission-critical databases have grown to massive size. It is not unusual to find databases in the 5-20 terabyte range on SQL Server, in which a DBCC operation requires many hours to complete. This is usually incompatible with a daily maintenance window, if feasible at all on a 24x7 system.
Every new version of SQL Server has expanded the range of logical checks performed by DBCC so that it has become more time-consuming to execute. For example, SQL Server 2005 introduced data purity checks to validate that data present in columns adheres to valid ranges for the data type. SQL Server 2005 also introduced checks that verify the integrity of indexed views.

Opportunities:

Enterprise-class storage subsystems and the widespread use of RAID, especially in mission-critical settings, have made the storage tier much less prone to physical corruption.
SQL Server has introduced new mechanisms to detect the most frequent forms of physical corruption independent of DBCC. For example, torn page detection was introduced in SQL Server 7.0, checksum verification of physical pages was introduced in SQL Server 2005, and checksum protection of tempdb pages was introduced in SQL Server 2008.
We continue to invest in technologies to automatically detect and correct sources of corruption - such as the introduction in SQL Server 2005 of an in-memory page scan to find checksum violations due to ECC failures that might occur during manipulation of read-only pages.

As more customers are deploying databases at the scale of terabytes or tens of terabytes, frequent DBCC checks are becoming less practical. But the many advances in both SQL Server and enterprise-class hardware offerings are enabling customers to back-away from the "Daily DBCC" best practices of the SQL Server 6.5 era, while still maintaining high confidence in the integrity of their data.

2. Suggestions for customers

DBCC CHECKDB remains an important tool for detecting and correcting logical consistency problems and physical corruption in the database. However, for large-scale databases utilizing a high quality SAN or storage subsystem, the specific recommendations this technical note presents can reduce the frequency of DBCC and certainly relax the prior standard of running such checks on daily basis.

SQL Server 2005 introduced an important new mechanism to proactively detect physical corruption in database pages: a database option that adds a checksum to each page as it is written to the I/O system and validates the checksum as it is read. This database option is called PAGE_VERIFY = CHECKSUM, and utilizing it by default is a powerful alternative to scheduling frequent runs of DBCC CHECKDB. For more information, see ALTER DATABASE SET Options (Transact-SQL) in SQL Server Books Online: http://msdn.microsoft.com/en-us/library/bb522682.aspx.

A. Recommendations for new databases (created in SQL Server 2005 or SQL Server 2008):

We encourage customers who have created their databases in SQL Server 2005 or SQL Server 2008 to rely on a strategy of using this PAGE_VERIFY = CHECKSUM feature and reduce the scheduled use of DBCC to a minimum. Below are some guidelines on how to accomplish this.

1) Use PAGE_VERIFY = CHECKSUM on all new databases created in SQL Server 2005 or later (this is the default setting for new databases).

2) Perform daily incremental or differential backup.

By performing an incremental/differential BACKUP WITH CHECKSUM operation each day (or even a full backup if feasible), you are guaranteed to read each page that has been added or modified since the prior backup. Because the reads performed during backup will validate the page checksums, any corruption that has occurred due to I/O errors will be detected, and can be corrected by using the current backup set to restore corrupt pages. (If the database is using the bulk-logged or full recovery model, page-level restore can be used to minimize time for recovery.)

The steps above will ensure that most physical page corruption due to I/O errors will be detected on a daily basis and can be promptly corrected. Because I/O errors have historically been the largest source of database corruption, PAGE_VERIFY = CHECKSUM can significantly reduce the frequency of DBCC operations needed to maintain a high level of confidence in the integrity of the physical database.

There are two other sources of corruption that can still arise even if PAGE_VERIFY = CHECKSUM is used, although they are rare - and they are problems that only DBCC CHECKDB can discover proactively:

a) ‘Scribbler' induced errors - where in-memory pages are corrupted either by third-party code running inside the SQL Server process or by drivers or other software with sufficient privileges executing in Windows® kernel mode.

SQL Server allows a variety of customer or third-party code to access the SQL Server address space, including extended stored procedures (XPs), unsafe SQL CLR assemblies, EKM providers, and OLE DB drivers for linked servers. Scribbler errors can arise as a result of bugs in these extensions, or from software such as malware protection tools or I/O drivers running in the kernel.

b) Potential SQL Server bugs that create logical errors.

We are not aware of any bugs in the current version of SQL Server that lead to logical errors in database objects, but the logical checks in DBCC can locate problems due to legacy errors or unknown errors in current versions of the product. Logical errors can usually be repaired by rebuilding indexes or re-establishing foreign key constraints - they are not typically fixed by restoring from a backup.

In addition, CHECKSUM cannot detect I/O problems if a page header itself is corrupt, but DBCC CHECKDB can.

These and other additional sources of errors, external to SQL Server, are reasons why customers should not eliminate DBCC CHECKDB entirely from maintenance plans. But the fact that the majority of errors located by DBCC CHECKDB result from I/O channel problems that are effectively caught using CHECKSUM allows customers to reduce the frequency of complete DBCC CHECKDB executions.

B. Recommendations for migrated databases:

If you are using a database that was created prior to SQL Server 2005, or if you are using a database that was created with a more recent version but without using PAGE_VERIFY = CHECKSUM, you need to be aware that checksums are added only as pages are added or modified. An initial run of DBCC CHECKDB using the PHYSICAL_ONLY option, after enabling the PAGE_VERIFY = CHECKSUM option, will check the integrity of all legacy pages but will not add a checksum.

Is there any risk that legacy pages from a migrated database can become corrupt and will not be detected by the checksum mechanism? It turns out that there are rare circumstances in which data on storage media can become corrupted in the absence of I/O ("bit rot") and not be detected by parity or RAID redundancy mechanisms, especially on low-end storage systems. If this occurs on a page without a SQL Server checksum, it can lead to database corruption that can only be detected by regular PHYSICAL_ONLY checks of DBCC CHECKDB.

So, in order to eliminate frequent DBCC CHECKDB runs for a database that was not originally created with PAGE_VEIRFY = CHECKSUM, we recommend either:

Rebuilding all clustered indexes and recreating any heaps. This will ensure that all data and index pages are written once with CHECKSUM enabled.
Or, less practically, creating a new database using PAGE_VEIRFY = CHECKSUM and migrating all objects to the new database.

The alternative, in a legacy database for which not all pages have a CHECKSUM, is to run DBCC CHECKDB with the PHYSICAL_ONLY option regularly prior to taking any full backup. If a problem is detected on a non-checksum page, the prior backup set can be used to restore a correct image of that page.

3. Periodic DBCC Strategies

Even if daily DBCC checks need no longer be part of an enterprise-class maintenance strategy, periodic DBCC checks remain an important tool. Frequency of DBCC checks should take into account whether scribbler errors are possible due to third party extensions running in-process, the kinds of additional privileged software running on the server, and the reliability and sophistication of the storage tier.

There are a variety of strategies to make DBCC compatible with shorter maintenance cycles and high availability production environments. Among those that are popular for large-scale, mission-critical deployments are:

Utilizing a SAN-based snapshot, mounted to another instance of SQL Server that runs DBCC independently of the production system
Performing DBCC on a per-filegroup basis, on a rotating schedule
Using Resource Governor to adjust the degree of parallelism (MAXDOP) of the session running DBCC operations, either to run highly parallelized during a short maintenance window, or single-threaded in the background during non-critical production hours
Using DBCC to perform the faster physical page validation checks (PHYSICAL_ONLY) more frequently than the logical checks. Such tests will bypass time-consuming validation of foreign key references and nonclustered indexes, but will ensure that all allocated pages are readable and will detect the majority of problems that can arise from problems in the I/O channel.

Some of these, and other strategies for executing DBCC operations, can be found in the recent blogs by Paul Randal and Bob Dorr, at:

http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx

http://blogs.msdn.com/psssql/archive/2009/02/20/sql-server-is-checkdb-a-necessity.aspx

4. Conclusion

While the reliability of SQL Server has improved dramatically over the past decade, database integrity still depends on the reliability of the storage tier and can be influenced by high-privileged, third-party code running in Windows kernel mode or permitted inside the SQL Server address space. SQL Server has invested in significant technologies to automatically detect and correct problems with data observed on-disk and in-memory, and this has reduced the need for frequent complete database integrity checks using DBCC CHECKDB. Periodic scheduled DBCC operations remain a best practice, but not at the near-daily frequency recommended in the past for mission-critical deployments. The recommendations here for using CHECKSUM features, along with periodic DBCC CHECKDB strategies, can help achieve a balance between a reasonable maintenance cycle and high confidence in database integrity.

Please refer to the following posts on more information about the CHECKSUM facilities:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/29/Enabling-CHECKSUM-in-SQL2005.aspx

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/checksum-and-tempdb.aspx

http://technet.microsoft.com/en-us/library/cc917726.aspx

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

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