SQL Server 2005 Scalability and Performance Plan (1)

This white paper provides relevant content about scalability in different reporting service implementation architectures. Also provides guidelines, recommendations, and hints based on using Microsoft SQL Server Reporting services to complete your own performance tests.

Brief introduction

Microsoft®sql server™reporting Services is a reporting platform that includes scalable and manageable central management report servers and extensible web-based, desktop-based report delivery. Report service is an important part of Microsoft's integrated business intelligence platform. For many organizations, delivering information through a report is a very important daily business process. As a result, report performance must be consistent and predictable. As reporting data increases, organizations must be able to increase reporting capacity in a predictable, low-cost manner.

About this document

This document is intended primarily to help customers and partners to plan, optimize, and extend their reporting service execution in the context of increased data volumes. The following are the main elements of this article:

Performance and scalability of different hardware configurations, such as scaling out and scaling in

Performance impact of report caching and file system storage

Best Practices for optimizing report services

Recommendations for performance testing

Although the article is written for Microsoft SQL Server, Reporting Services, most of the information applies to earlier versions as well.

Prerequisite

The white document does not intend to describe the reporting service in detail. For some details, refer to the http://www.microsoft.com/sql/reporting/website. In addition to Reporting Services, the document assumes that the reader is familiar with the following:

Microsoft SQL Server

Internet Information Services (IIS)

Microsoft. NET Framework

This information can be consulted on the MSDN site for http://msdn.microsoft.com.

Overview

Reporting Services is a web-based report of a comprehensive service platform for creating, managing, and delivering traditional paper reports with interactivity. When the report is executed, the Reporting services platform performs the following steps:

Re-get Report data

Process data based on report directives and report definitions

To display a report in a specific format

Reporting Services also performs other tasks to support report processing, such as managing and processing subscriptions, managing and processing snapshots and cache requests, and service report management requests.

There are three main parts of the work in Reporting services:

Users can access published reports online and interactively

Subscriptions and transactional-driven reports are delivered to the user in the form of email or file sharing

Create and automate reports in real time through online users

The focus of this article is on the first part, where users can access published reports online and interactively. This feature is of great interest to most users. Subscription delivery has the advantage of booking, users can control anytime and anywhere. Interactive reports are more difficult to plan because most depend on the size and complexity of the report, the number of concurrent users, and the format that the report displays. Users in the application of interactive reports, the system response speed also has a high expectation.

With the advent of SQL Server Reporting Services, end users can interactively create and execute reports through the new Report Builder tool. The extra load created by a live report is difficult to quantify because it depends on what the user wants to do and how to do it effectively, and the quantification of the real-time report is described in the next version of the file. This article mainly includes general performance instructions, create the load of interactive reports and test on different configurations. At the same time, the final performance data also depends on your own working environment and you need to perform your own performance testing. The pictures and results in this article are intended to illustrate the extended features that may appear on different configurations.

Scalability vs Reliability

The scalability of the system is difficult to define because there are different metrics for different people. Easy to cause confusion is scalability and reliability, which often appear in the same article. Reliability is an important consideration for any system configuration, and its existence is more or less affected by scalability. In this article, scalability is defined as a system capability to support the increased workload of the system without altering the basic design and architecture of the system. Ideally, if you add system resources, you should increase the same percentage of system capacity to handle more work.

This may be a bit too intuitive, and it's very difficult to achieve a "linear" extensibility. In practice, the required capacity of the system does not meet the linear growth well. This is because the excess of management, collaboration, and communication costs can also occur between different system components. System reliability is based on subtle differences of opinion. The reliability of the system refers to the "ability to handle the workload increase smoothly without any failure". In addition, when the system increases the workload, the reliable system should be able to stop working uninterrupted or not at the same time. Degradation of performance should also be a smooth process. Although any system can become ineffective when the pressure is too high, it is possible for a reliable system to recover from these accidents. The ability to succeed in Reporting Services is focused on finding workloads and the amount of work that the system can smooth out, creating reliable systems to meet extended requirements.

Scaling Up vs outward scaling

The extensibility design of Reporting Services enables users to deploy parts on a single server or multiple servers as they wish. When users start using Reporting Services, they often ask, "buy a large server (scale-up) or a small server (scale-out)." This article will describe these extensibility features to help you solve this problem.

One way to extend up (scale-up) is to "leverage large, homogeneous, multiprocessor servers to provide additional capabilities". The advantage of this approach is that it can provide a simple configuration and management process relative to scale-out. Scale-up is also an extension of the SQL serve relational engine and Analysis Services.

Outward extension (Scale-out) is a configuration of Enterprise Edition Reporting Services, which is preferred by most users. Importantly, Scale-out can accomplish the following tasks:

Enables users to add or remove capabilities as needed

Provides an acceptable, manageable, scalable way to increase and remove capabilities

Allow heavy workloads to balance on many daily-use servers

Provides an allowable degree of internal error

If you want to deploy reporting Services with the Scale-out configuration, note the collaboration between multiple report servers, so that each directory that accesses a single report server is installed in a local or remote SQL Server relational database. For details, see http://www.microsoft.com/sql/reporting/and Http://technet.microsoft.com/sql.

Report Service Components

To better understand scalability, let's first look at the architecture of the report services, as shown in Figure 1 below, as well as a variety of components.

Figure 1: Report Service architecture

The report service can be decomposed into three layers logically, as shown in table 1 below:

Table 1

　　　　

component	features
	a Web service, do the following things: ·　　　　Handling soap and URL requests ·　　　　Working with reports, including executing queries, checking expressions, producing output formats ·　　　　Provides snapshot and report cache management · This article does not detail the support and strengthening of security policies and authoritative reporting servers that provide Windows services and can be responsible for subscriptions and batch operations. The following two SQL style=
Customer application	Customer application through SOAP We b service and URL requests to access the server. Report management tools and Report Viewer applications are client applications that are included in the report service. Microsoft®visual studio®2005 provides a report viewer for controlling reports embedded in the client. Report creation Tool (Builder) is the authoritative tool for real-time report creation. Many third party software vendors also provide their own client applications.

Extension specification

This section describes the basic configuration options for report services, and how they affect performance and extensibility. This program is to help users learn an effective report service configuration and load requirements, and answer the following questions:

Do you need to consider deploying the directory to a remote server?

Extending the report server up and adding additional report servers that's better?

What is the best configuration for your processor report server?

Although the tests performed by Microsoft on different configurations have resulted in a specific report workload, the actual performance requirements will depend on many factors in your work environment. Includes the following factors:

Number of concurrent users

The size and complexity of report generation

Generate on-demand and subscription reports

Production of live and cached reports

The test results in the following sections are used to determine the relative performance and scalability characteristics of various configurations. Note that some of the original rules, such as the number of pages viewed per second, will vary in different environments. The primary focus is on the relative improvements that can be made to the distribution of resources in the environment or to the addition of resources. The latter section provides guidance for creating your own performance baselines.

Local vs Remote Configuration

Microsoft has tested two local configurations, running a report server and its directory on a single server.

Figure 2: Local Directory implementation

In a local configuration, the SQL Server relational database competes with the reporting service for a valid machine resource. If you have enough resources, there is nothing wrong with it.

You may consider setting the maximum memory and the maximum number of processors for use by the SQL Server database engine, in order to reduce the competition with Reporting Services, see Appendix A For more information. The customer also chooses the configuration shown in Figure 2 because it requires only SQL Server permissions.

Instead, the remote directory implementation, shown in Figure 3, extends the report service component through two physical servers (the report service engine and the remote directory host).

Figure 3: Remote Directory implementation

Remote configuration eliminates the competition between machine resources. However, you must provide sufficient network bandwidth between the report server and the directory server

Extend up and out

After you split the directory into other systems, you can choose to extend the report server up by increasing the processor, or by increasing the machine to extend it outward. Figure 4 depicts an outward-extending configuration that uses a multiple report server to access a single directory.

Figure 4: Multi-Report server access to an outward-extending configuration of a single directory

An outward-extending configuration typically manages the directory with a remote relational database server that is detached from any report server node. It is not possible to store directories on any of the report server nodes, and this configuration is not recommended because the database server consumes the resources of the report server.

A processor that extends outward with a configuration that accesses a remote directory using the Processor report server. A 8-processor with an externally-expanded configuration using 4 processor-only reporting servers. As a result, an outward-extending configuration adds more than just the processor, but also memory and network connectivity.

Local and remote directory performance comparisons

The first idea in an extensibility plan is to consider whether the report Server directory is local or remote.

In local mode, the same physical machine manages the report server and report server directories. The directory is independent of the source database of the report data, and the report data typically resides on different servers.

Single-machine configuration is the simplest implementation, but also the most economical way, but there are a few shortcomings. Most importantly, moving directories to a remote server is the first step in using an outward-extending configuration. The discussion will take place in the following chapters.

To answer the question of how to add the processor to the local and split the directory way better, the following system configuration will be used to test:

Processor Report Server, local directory (2-PROC)

2 Processor Report Server, remote directory (2-proc sqlremote)

Processor Report Server, local directory (2-PROC)

Processor report Server, remote directory (4-PROC)

These test results show some of the more significant facts.

With the processor system, local and remote execution results are broadly similar for a little load.

The processor local system exhibits better performance per second than the processor local system, but it is not twice times the performance of the processor remote system.

Table 2 shows the comparison of four configurations at peak capacity, which is the maximum number of sessions with performance starting down to a limit of 30 seconds.

Table 2

　　

	AVG req/sec	Peak Sessions attained
2 Proc (local)	Baseline	Baseline
2 Proc (Remote)	-10%	Baseline
4 Proc (local)	53%	17%
4 Proc (Remote)	100%	117%

Local to 2-processor remote implementations from the processor, in fact, have little effect on session peaks. There is a slight decrease in throughput, as data is transferred between networks.

In a high workload, doubling the processors for local directory implementations (2-processor to 4-processor) does not substantially double the available resources. It only provides a slight increase in the peak session and a 53% improvement in requests per second.

However, on a remote directory configuration, making the number of processors from 2 to 4 can lead to a linear increase. When the 4 processor is used, the peak number of requests doubles more than the peak session.

Key points

If you run a processor-wide local system, splitting the directory to other servers will result in subtle changes to the overall system performance.

The split directory does not provide management and monitoring benefits because the system cannot allocate resources between the report server and the database processing.

If you run a processor-aware local system, the split directory to other servers will show significant performance improvements.

For extensions, the first step in the remote directory implementation is to extend the configuration outward

Extend up

This section focuses on increasing the available capabilities and performance by increasing the processor (scaling up) in a remote directory configuration. In this case, the Scale-out configuration extends from the processor to the 4-processor. In subsequent tests, when the response time exceeds the predetermined limit time of 30s, the limit is reached, and 30s is generally considered a response time that is difficult for users to tolerate.

Table 3

　　

　　

	Average Requests/second	Maximum # Sessions	Page views per Minute
2 Proc (remote)	10.71 (Baseline)	(Baseline)	604 (Baseline)
4 Proc (remote)	23.91 (123%)	1300 (117%)	1327 (120%)

Average number of requests per second

When used by a large number of users, a processor system can handle significantly more requests than a remote system with a processor. Doubling the number of available processors in the remote directory is slightly more than doubling the average request for each server.

Peak of Session

A processor remote configuration can support more remote implementations than doubling the peak of a processor session.

Page view per minute

The number of page views per minute reflects the ability to generate the page. When the processor in the remote directory implementation is increased from 2 to 4, you can get 120% of the Page view performance improvement at high load.

Key points

After you split the report directory into a stand-alone system, increasing the number of processors from 2 to 4 is essentially doubling the performance of the reporting service without reducing the response speed.

The test is done on the server, with no 2 or 4 processors. The optimization of a large number of processor systems will be checked in future tests.

Extend outward

This section focuses on the performance and capabilities of an outward-extending configuration. In Microsoft's test, the report server was actually a replica of the processor's remote directory implementation. As a result, the machines that extend outward are twice times and 4 times times the size of all system resources (memory, storage, and network cards) and processors.

When you compare the system capabilities of 2-processor remote execution with the outward scaling of the processor and the 8-processor, a near linear extension is provided by extending the configuration outward. The table below summarizes the 2-processor, percent processor, and 8-processor improvements.

Table 4

　　

	Peak page views/hr	Maximum Simultaneous user session
2 Proc (remote)	10.71 (Baseline)	(Baseline)
2 X 2 Proc (remote)	23.87 (210%)	1300 (216%)
4 X 2 Proc (remote)	45.18 (378%)	2500 (416%)

Comparing the average request peaks per second and the supported session peaks, the 2 X processor outward extended configuration provides a better result than the linear value of the remote execution of the processor.

Extending outward from the 2 x-processor to 4 X-processor outward expansion does not provide a true linear extension. Moreover, it does not provide significant capability improvements, with 89% per second request improvements and 92% of the supported session peak improvements.

　　

Key points

Outward scaling provides better, less efficient, and better capabilities without the need for a large amount of hardware investment

If you expect to continue to increase the demand for reporting Services, scaling out is a flexible way to increase your extra capacity

Compare up and outward extend

A direct scaling up and outward-extending instance compares the processor remote directory with the outward-extending processor. Assume that 4 processors reside on the same report server, while extending out to 2 processors.

The test results show that there is only a slight difference between the two implementations. Extends outward between two nodes have 8GB of memory, the processor remote has 4GB of memory. Considering the equivalent number of sessions, the response time of the processor system is fairly uniform. The outward scaling of the processor has a slight advantage in response time. Look forward to deploying the report services in the ASP. Network applications cater to the advantages of inexpensive hardware assembly in an outward-extending configuration.

Key points

If you have a 2-processor remote directory implementation, you need to double your ability to scale up to 4-processor report server or extend out to 2 report servers.

Although transferring to an outward-extending configuration requires you to transfer to the Enterprise version of the reporting service, it has many advantages over the original capabilities. Mainly include the following:

A small number of processor servers are much cheaper than large SMP servers

Additional machines can take advantage of reserved memory address space without being transferred to 64-bit hardware

Save downtime by adding capabilities to new servers instead of upgrading existing servers

Multiple reporting servers provide better availability, and if one server fails, other servers can continue to accept requests

You can easily scale out to 6, 8, or 10 processors. SMP servers typically have low performance returns after a 8 processor

With 64-bit processor

The SQL Server 2005 Reporting Service supports 64-bit processors, including Intel ITANIUM2 processors, AMD and Intel's x64 architecture processors. On x64 systems, report services can run on the original 64-bit mode and 32-bit WOW (Windows on Windows) subsystem. Overall, a 64-bit system running on the same processor does not improve the production of reports. However, the main advantage is that users can view and export larger reports. When you are in a high workload, you may get better throughput on a 64-bit machine because the memory competition will drop and garbage collection will be less. Microsoft is not able to fully test these platforms, but will use test results to update these documents in a planned manner.

Report Caching and Storage

In a reporting service implementation, an important factor in performance and capability is "to generate a report from the data of the original system, or to take advantage of the cache or snapshot data". This section describes some of the options, and potential performance impact options.

Cache instance

The report server has a two-tier cache:

1. When a report server produces a report, it returns the report definition from the report Server directory and obtains the data required by the report from the data source. It then creates a temporary report format (stored in the session cache) and writes to the REPORTSERVERTEMPDB database. It uses these results to cache instances to create and remit to the final report.

For each full "live" report, it repeats these steps. It can guide requests for subsequent reports if the report has been processed and is in the cache. This reduces the time to retrieve the data and create the report.

2. The report server also tries to cache the output format, or in-memory report snapshots, or temporary directories in the file system. If the result of the request is found in the output cache, it will be circuitous between the process and the description step, thus generating better performance. If you need to know more about how to decide which cache to use, see the performance statistics class in Appendix B.

The cache will eventually expire, causing the new data to be retrieved from the report server again. You can control the cache duration, predetermined (for a particular report or shared), or mandatory deadlines based on predetermined intervals.

Cached reports have an impact on storage, although SQL Server 2005 Reporting Services can store data succinctly and provide data compression. When deciding how many caches or snapshot mirrors should be kept, consider the following:

When the cache instance is created, the query parameters are applied. If you need a report with different query parameters, the report service will generate an extra cache instance.

There are no report parameters for queries, which can be used to create different versions of the report from the cached instance.

Report snapshots

Snapshots involve the same time interval report format, stored in a more persistent state. For example, snapshots can be stored in the report Server directory, not in reportservertempdb. The directory can also maintain a multiple-time interval version, as a report history that allows users to choose between them.

The SQL Server 2005 Reporting Service automatically compresses snapshots in the directory. It can also store snapshot mirrors to the local file system. The following sections highlight the performance impact of taking advantage of cache instances, compressed snapshots, and file system storage.

Cache instance

One way to improve the ability and scale of reporting service deployments is to avoid executing reports that depend on live data. You can do this by using cached data.

Figure 5 shows a report that retrieves, processes, and describes a 150K row of data that is executed with live and cached data.

Figure 5:150-k Time calculation for a single report

When aggregated, these data indicate that the live data requires more than 261% of the time for Reporting Services compared to the adoption of caching. Reports that need to return large amounts of live data require more resources than reports that use cached data.

It is unreasonable to require users to avoid running live reports when the system is using spikes. Typically, users do not realize that running these reports can affect system performance and other system users. Some "citizen" practice allows you to consider the number of users transferred to your system to improve the capabilities and response, including the following:

Whenever you do, avoid retrieving reports and executing large amounts of live data. Replace with cached data.

If this situation cannot be avoided, at least try to limit the number of runs of such reports, especially during rush hours.

When reports can be generated from cached data, these reports are scheduled during off-peak hours to update the cache to avoid affecting other users.

Compress snapshot Storage

The SQL Server 2005 Reporting Service enables managers to decide for themselves where the snapshot data is stored and how it is stored.

Using snapshots can significantly improve report performance, but it consumes database storage space. To help balance storage and performance issues, report Services provides compression options. The default setting is to compress snapshots and report definitions. You can also turn off compression.

For example, in a 20,000-row report, the SQL snapshot is compressed to reduce the size of 20% before compression. The reduced portion not only translates to important storage in the report server directory, but also greatly reduces the amount of traffic between the report server and the directory.