(0) exportieren Drucken
Alle erweitern

Estimate performance and capacity requirements for Excel Services in SharePoint Server 2010 (in englischer Sprache)

SharePoint 2010

Veröffentlicht: 04. November 2010

This article describes the effects of using Excel Services in Microsoft SharePoint Server 2010 on topologies running Microsoft SharePoint Server 2010. You can use this information to better scale your deployments based on your latency and throughput requirements.

Gg398058.note(de-de,office.14).gifHinweis:

It is important to be aware that the specific capacity and performance figures presented in this article will differ from the figures in real-world environments. The figures presented are intended to provide a starting point for the design of an appropriately scaled environment. After you have completed your initial system design, test the configuration to determine whether the system will support the needs of your environment.

In this article:

For general information about how to plan and run your capacity planning for SharePoint Server 2010, see Capacity management and sizing for SharePoint Server 2010.

Test farm characteristics

This section describes the dataset, workloads, hardware settings, topology, and test definitions that were used during the performance and capacity testing of Excel Services.

Dataset

Excel Services capacity and performance is highly dependent on the makeup of the workbooks that are hosted on the service. The size of the workbook and the complexity of calculations have the most impact. Our testing used representative sizes and complexities, but every workbook is different, and your capacity and performance depends on the actual workbooks you use, and their specific size and complexity.

We simulated Excel workbooks on a farm dedicated to Excel to evaluate our capacity profile. Note that no other SharePoint Server tests were running during our capacity profile tests. Within this farm, we used three buckets of workbooks – Small, Large, and Very Large – based on workbook size and complexity:

Workbook Characteristics Small Large Very Large

Sheets

1-3

1-5

1-20

Columns

10-20

10-500

10-1,000

Rows

10-40

10-10,000

100-30,000

Calculated Cells

0-20%

0-70%

0-70%

Number of Formats

1-10

1-15

1-20

Tables

0-1

0-2

0-5

Charts

0-1

0-4

0-4

Workbook Uses External Data

0%

20%

50%

Workbook Uses a Pivot Table

0%

3%

3%

Workbook Uses Conditional Formats

0%

10%

20%

This test farm included 2,000 SharePoint Server sites. Each site contained one small, one large, and one very large workbook. The distribution of the workbooks on the SharePoint Server pages was 10% small workbooks and 90% large and very large workbooks. Additionally, the test farm dataset included SharePoint Server pages that contained 1-5 Excel Web Parts.

Workload

To simulate application usage, workloads were created to perform one or more of the following operations:

Action Mix Small Workbook Large Workbook

View

50%

70%

Edit

35%

15%

Collaborative Viewing

10%

10%

Collaborative Editing

5%

5%

In addition, 17% of all the workbooks included external data. For large and very large workbooks that included external data, refreshes were performed 80% of the time; small workbooks do not include external data.

Each workload includes think time between user actions of 10 seconds. Think time refers to user action delays that simulate how long a user might take to perform the actions. This differs from other SharePoint Server 2010 capacity planning documents. Excel Services is stateful —the workbook is maintained in memory between user interactions — making it important to simulate a full user session and not merely individual requests. On average, there are 0.2 requests per second for a single user workload.

We randomly selected one of the 2,000 sites to run the test for each workload. We used the percentages in the following table to select application and application size, within that site.

Workbook Selection Use Percentage

Small Workbook

30%

Large Workbook

55%

Dashboard

10%

Very Large Workbook

5%

Green and Red Zone definitions

For each configuration two zones were determined before throughput tests were performed. One zone was the green zone or recommended zone in which throughput can be sustained. The other zone was the red zone or maximum zone in which throughput can be tolerated for a short time but should be avoided.

To determine our red and green zone user loads, we first conducted a step test and then stopped when the following conditions were met:

  • Green zone   We stopped at the point when any of the computers in our farm (Web front-end, Dienste für Excel-Berechnungen, or Microsoft SQL Server) exceeded 50% CPU usage or the response time for the overall system exceeded 1 second.

  • Red Zone   We stopped at the point where the successful RPS for the Dienste für Excel-Berechnungen computers in the farm was at a maximum. Past this point, the overall throughput for the farm started to decrease and/or we would start to see failures from one of the tiers. Often the maximum private bytes in Dienste für Excel-Berechnungen would be exceeded when throughput was in the red zone.

After conducting the step tests, we retreated from these maximum values to run a longer constant load test of 1 hour. We stopped the green zone test when 75% of the load was used. We peaked in the red zone step test when we used 65% of the load. If the green zone test was limited by memory, and the CPU usage percentage never exceeded 50%, we instead used 75% of the load number calculated for the red zone.

The average response time was less than .25 seconds for both green and red zones, and for both scale-out and scale-up tests.

Hardware Settings and Topology

This section describes the kinds of computer hardware we used in our lab and the farm configuration topologies that we used in our tests.

Lab Hardware

Several farm configurations were used for our testing to provide a high level of test-result detail. The farm configurations ranged from one to three Web front-end servers, one to three application servers for Excel Services and Dienste für Excel-Berechnungen, and a single database server computer that is running Microsoft SQL Server 2008. Additionally, our tests used four client computers. All servers were 64-bit, and the client computers were 32-bit.

The following table lists the specific hardware that we used for testing.

Machine Role CPU Memory Network

Web front-end server

2 proc/4 core 2.33 GHz Intel Xeon

8 GB

1 gig

Dienste für Excel-Berechnungen

2 proc/4 core 2.33 GHz Intel Xeon

8 GB

1 gig

SQL Server

4 proc/4 core 2.6 GHz Intel Xeon

16 GB

1 gig

Topology

Our testing experience indicates that memory on the Dienste für Excel-Berechnungen tier and CPU on the Web front-end server tier are the most important limiting factors for throughput. Be aware that your experience may vary. As a result, we varied the number of computer servers in both tiers for the scale-out tests.

We deployed a topology of 1:1 for the Dienste für Excel-Berechnungen and Web front-end servers for the scale-up tests, and then varied the number of processors and available memory in the Dienste für Excel-Berechnungen computers.

Dienste für Excel-Berechnungen is not especially demanding on the SQL Server instance running SharePoint Server 2010, as the workbook is read a binary large object (BLOB) from SharePoint Server 2010 and put in memory on the Dienste für Excel-Berechnungen tier (and additionally disk cached). At no time did SQL Server become a bottleneck. For all tests, bottleneck is defined as a state in which the capacity of a particular component of a farm is reached.

Test Results

The following tables show the test results of Excel Services in Microsoft SharePoint Server 2010. For each group of tests, only certain specific variables are changed to show the progressive effect on farm performance.

Note that all the tests reported on in this article were conducted with think or wait time (think time equals 10 seconds between user actions). This differs from the capacity planning results for other parts of SharePoint Server 2010.

For information about Excel Services bottlenecks, see the Common bottlenecks and their causes section in this article.

Overall Scale

The table here summarizes the effect of adding additional Web Front-End and dedicated Dienste für Excel-Berechnungen computers to the farm. These throughput numbers are specifically for the Dienste für Excel-Berechnungen computers, and do not reflect the effect on the overall farm.

Topology Baseline Maximum (RPS) Baseline Recommended (RPS)

1x1

38

31

1x2

35

26

1x3

28

21

2x1

57

35

2x2

62

46

2x3

52

39

3x1

51

32

3x2

81

69

3x3

83

64

Diagramm mit maximalen und empfohlenen Anforderungen pro Sekunde

Recommended Results

The following chart shows our results for recommended sustainable throughput.

Diagramm mit dem Durchsatz beim Hinzufügen von ECS-Servern

The previous chart shows that there is overhead associated with adding Web front-end computers to the farm. However, this is offset as Dienste für Excel-Berechnungen computers are added. A single Web front-end became the bottleneck after adding two additional Dienste für Excel-Berechnungen computers. This Web front-end bottleneck reversed any benefit that was gained from the additional capacity of adding a second and third Dienste für Excel-Berechnungen computer. Also notice that three Web front-end computers did not add any more throughput, as Dienste für Excel-Berechnungen became the limiting factor.

Diagramm mit WFE-Prozentwerten für CPU-Verwendung

Notice in the previous chart that as Web front-end computers are added, the CPU load on each computer is reduced significantly. Note too, that with two Web front-end computers and three Dienste für Excel-Berechnungen computers, the CPU load is reaching the maximum seen for a single Web front-end computer. This implies that adding another Dienste für Excel-Berechnungen computer would make the Web front-end tier the limiting factor. Remember that these results are for the “recommended” load. This is why the CPU load is maxing out at around 35% instead of at an increased level.

Maximum Results

The following chart shows our results for maximum peak throughput.

Diagramm mit dem maximalen Durchsatz für das Hinzufügen von ECS-PCs

Similar to our recommended results, we see that a single Web front-end computer is the limiting factor as we add a second and third Dienste für Excel-Berechnungen computer. Also notice that exactly as with the recommended results, adding a third Web front-end computer does not add to throughput as Dienste für Excel-Berechnungen is the limiting factor after the second Web front-end computer is added.

Diagramm mit WFE-Prozentwerten und maximaler CPU-Verwendung

The results in the previous chart show that multiple Web front-end computers do not become as heavily loaded as a single Web front-end computer configuration. This indicates that the Dienste für Excel-Berechnungen computers are the bottleneck after the second Web front-end computer is added.

Detailed Results

This section shows details for the recommended and maximum results obtained in our tests.

Recommended Results

The following tables show the recommended results of our tests.

Overall 1x1 1x2 1x3 2x1 2x2 2x3 3x1 3x2 3x3

Client Successful RPS

30.56

34.55

31.67

26.03

45.94

68.37

20.71

38.82

63.70

Client Response Time (sec.)

0.22

0.18

0.19

0.16

0.19

0.20

0.15

0.15

0.17

TPS

1.58

1.77

1.61

1.40

2.38

3.54

1.08

2.03

3.25

Web Front-end Tier 1x1 1x2 1x3 2x1 2x2 2x3 3x1 3x2 3x3

% CPU (average over all Web Front-end computers

33.73

37.64

33.84

14.61

23.95

36.90

7.54

13.12

21.75

Dienste für Excel-Berechnungen Tier 1x1 1x2 1x3 2x1 2x2 2x3 3x1 3x2 3x3

% CPU (average over all Dienste für Excel-Berechnungen computers)

30.56

34.55

31.67

26.03

45.94

68.37

20.71

38.82

63.70

Peak Private Bytes (maximum over all Dienste für Excel-Berechnungen computers)

5.94E+09

5.82E+09

5.79E+09

5.87E+09

6.09E+09

5.92E+09

5.79E+09

5.91E+09

5.85E+09

Maximum Results

The following tables show the maximum results of our tests.

Overall 1x1 1x2 1x3 2x1 2x2 2x3 3x1 3x2 3x3

Client Successful RPS

37.85

56.70

51.17

35.19

62.04

81.31

27.79

51.62

82.58

Client Response Time (sec.)

0.19

0.28

0.23

0.16

0.20

0.25

0.16

0.16

0.22

TPS

1.92

2.96

2.59

1.81

3.21

4.60

1.41

2.72

4.30

Web Front-end Tier 1x1 1x2 1x3 2x1 2x2 2x3 3x1 3x2 3x3

% CPU (average over all Web Front-end computers

41.08

67.78

58.59

19.44

34.11

45.97

10.19

17.79

28.69

Dienste für Excel-Berechnungen Tier 1x1 1x2 1x3 2x1 2x2 2x3 3x1 3x2 3x3

% CPU (average over all Dienste für Excel-Berechnungen computers)

24.99

18…44

10.96

23.57

20.56

17.77

18.97

17.04

18.10

Peak Private Bytes (maximum over all Dienste für Excel-Berechnungen computers)

5.91E+09

5.85E+09

5.91E+09

5.88E+09

5.99E+09

6.502E+09

5.94E+09

5.94E+09

6.04E+09

Scale Up Test results

We also measured the effect of adding CPUs and memory to the Dienste für Excel-Berechnungen tier. For these tests, a 1x1 topology was used.

Diagramm mit der Auswirkung des Hinzufügens von CPUs zu ECS

Our results in the previous chart show that adding additional CPUs was helpful but did not significantly affect the overall throughput.

Diagramm mit der Auswirkung des Hinzufügens von RAM zu ECS

The red zone line in the previous chart shows however, that adding memory does have a significant effect on throughput, especially at peak times. In this test, the same hardware was used throughout. However, the Maximum Private Bytes for the Excel Services process was limited. Since workbooks are kept in memory, the size of the workbooks has a significant effect on how many workbooks, and also how many users, any Dienste für Excel-Berechnungen computer can support.

Recommendations

This section provides general performance and capacity recommendations for hardware, Excel Services settings, common bottlenecks and troubleshooting.

Note that Excel Services capacity and performance is highly dependent on the makeup of the workbooks that are hosted on the service. The size of the workbook and the complexity of calculations have the most effect. Our testing used representative sizes and complexities, but every workbook is different, and your capacity and performance depends on the specific size and complexity of the workbooks you use.

Hardware Recommendations

Excel Services uses standard hardware for both Web front-end servers and application servers, there are no special requirements. General SharePoint Server 2010 guidelines on CPU number, speed, and memory are applicable for computers in the Dienste für Excel-Berechnungen tier. Note that one of the first bottlenecks an Dienste für Excel-Berechnungen computer is likely to encounter is memory and this may require you to add resources. Before you do, we recommend that you test with a representative set of workbooks from your organization, as the size and complexity of workbooks have a large effect on how much more capacity the addition of memory is likely to have.

To increase the capacity and performance of one of the starting-point topologies, you can do one of two things. You can either scale up by increasing the capacity of your existing servers or scale out by adding additional servers to the topology. This section describes the general performance characteristics of several scaled-out topologies.

The sample topologies represent the following common ways to scale out a topology for an Excel Services scenario:

  • To provide for more user load, check the CPU and memory for the existing Excel Services application servers. Add additional memory if the CPU is not a concern, or add CPUs if memory is not a concern. If both memory and CPU are reaching their upper limits, additional Dienste für Excel-Berechnungen computers may be necessary. Add additional Dienste für Excel-Berechnungen or application servers until the point that the Web front-end servers become the bottleneck, and then add Web front-end servers as needed.

  • In our tests, SQL Server was not a bottleneck. Excel Services does not make large demands on the database tier, as workbooks are read and written as whole documents, and also workbooks are held in memory throughout the user’s session.

Performance-Related Excel Services Settings

One of the ways to control the performance characteristics of Excel Services is to control how memory is used. Each of the global settings in the following list are set through SharePoint Server 2010 Central Administration > Application Management: Manage Service Applications > Excel Services-Anwendung > Global Settings:

  • Maximum Private Bytes  — By default, Dienste für Excel-Berechnungen will use up to 50% of the memory on the computer. If the computer is shared with other services, it may make sense to lower this number. If the computer is not being shared and is dedicated to Dienste für Excel-Berechnungen, and is indicating that memory may be a limiting factor, increasing this number may make sense. In any event, experimenting by adjusting this number can guide the administrator to making the necessary changes in order to better scale up.

  • Memory Cache Threshold — Dienste für Excel-Berechnungen will cache unused objects (for example, read-only workbooks for which all sessions have timed out) in memory. By default, Dienste für Excel-Berechnungen will use 90% of the Maximum Private Bytes for this purpose. Lowering this number can improve overall performance if the server is hosting other services in addition to Dienste für Excel-Berechnungen. Increasing this number increases the chances that the workbook being requested will already be in memory and will not have to be reloaded from the SharePoint Server content database.

  • Maximum Unused Object Age — By default, Dienste für Excel-Berechnungen will keep objects in the memory cache as long as possible. To reduce the Dienste für Excel-Berechnungen memory usage, in particular with other services that are running on the same computer, it may make more sense to impose a limit on how long objects are cached in memory.

There are also settings available to control the maximum size of a workbook and the lifetime of a session, which in turn control how long a workbook is held in memory. These settings are associated with each trusted location and are not global. These settings can be set through SharePoint Server 2010 Central Administration > Application Management: Manage Service Applications > Excel Services-Anwendung > Trusted Locations, and then edit the settings for each trusted location in the Workbook Properties section on the Edit Trusted File Location page.

  • Maximum Workbook Size

  • Maximum Chart or Image Size

By default, Dienste für Excel-Berechnungen is limited to 10 MB or smaller workbooks and 1 MB or smaller charts/images. Obviously using larger workbooks and larger charts/images puts more strain on the available memory of the Dienste für Excel-Berechnungen tier computers. However, there may be users in your organization that need these settings to be increased for Dienste für Excel-Berechnungen to work with their particular workbooks.

  • Session Timeout — By decreasing the session time out, memory is made available for either the unused object cache or other services faster.

  • Volatile Function Cache Lifetime — Volatile functions are functions that can change their value with each successive recalculation of the workbook, for example date/time functions, random number generators, and so on. Because of the load this could generate on the server, Dienste für Excel-Berechnungen does not recalculate these values for each recalculation, instead caching the last values for a short time period. Increasing this lifetime can reduce the load on the server. However, this depends on having workbooks that use volatile functions.

  • Allow External Data — Dienste für Excel-Berechnungen can draw on external data sources. However, the time that is required to draw upon the external source can be significant, with potentially a large amount of data returned. If external data is allowed, there are several additional settings that can help throttle the effect of this feature.

Common bottlenecks and their causes

During performance testing, several different common bottlenecks were revealed. Bottlenecks are defined as a state in which the capacity of a particular component of a farm is reached. This causes a plateau or decrease in farm throughput.

The following table lists some common bottlenecks and describes their causes and possible resolutions.

Troubleshooting performance and scalability

Bottleneck Cause Resolution

Dienste für Excel-Berechnungen Memory

Excel Services holds each workbook in memory throughout the user's session. A large number of workbooks, or large workbooks, can cause Dienste für Excel-Berechnungen to consume all available memory causing the actually consumed "Private Bytes" to exceed "Maximum Private Bytes."

Scale Up with more memory in the Dienste für Excel-Berechnungen tier computers, or Scale Out with the addition of more Dienste für Excel-Berechnungen computers. The choice will partly depend on if CPU is also reaching a maximum.

Dienste für Excel-Berechnungen CPU

Excel Services can depend on a large amount of processing in the application tier, depending on the number and complexity of workbooks.

Increase the number of CPUs and/or cores in the existing Dienste für Excel-Berechnungen computers, or add Dienste für Excel-Berechnungen computers.

Web server CPU usage

When a Web server is overloaded with user requests, average CPU usage will approach 100 percent. This prevents the Web server from responding to requests quickly and can cause timeouts and error messages on client computers.

This issue can be resolved in one of two ways. You can add Web servers to the farm to distribute user load, or you can scale up the Web server or servers by adding faster processors.

Performance monitoring

To help you determine when you have to scale up or scale out the system, use performance counters to monitor the health of the system. Use the information in the following tables to determine which performance counters to monitor, and to which process the performance counters should be applied.

Front-end Web server

The following table shows performance counters and processes to monitor for front-end Web servers in your farm.

Performance Counter Apply to object Notes

% Processor Time

Processor (w3wp)

Shows the percentage of elapsed time that this thread used the processor to execute instructions.

% Processor Time

Processor (_Total)

Shows the percentage of elapsed time that all threads on the server computer that used the processor to execute instructions.

Private Bytes

Process (w3wp)

This value should not approach the Max Private Bytes set for w3wp processes. If it does, additional investigation is needed into what component is using the memory.

Dienste für Excel-Berechnungen

The following table shows performance counters and processes to monitor for application servers, or in this case Dienste für Excel-Berechnungen, within your farm.

Performance Counter Apply to object Notes

% Processor Time

Processor (_Total)

Shows the percentage of elapsed time that all threads on the server that used the processor to execute instructions.

% Processor Time

Processor (w3wp)

The Dienste für Excel-Berechnungen runs within its own w3wp process, and it will be obvious which w3wp process this is as it will be getting the bulk of the CPU time.

Average Disk Queue Length

PhysicalDisk(_Total)

Watch for too much disk writing because of logging.

Private Bytes

Process(w3wp)

Excel Services caches workbooks in memory, until the user's session expires (the time out for which is configurable). If a large amount of data is being processed through the Dienste für Excel-Berechnungen, memory consumption for the Dienste für Excel-Berechnungen w3wp will increase.

SQL Server

As we have previously described, Excel Services is light on the SQL Server tier, as workbooks are read once into memory on the Dienste für Excel-Berechnungen tier during the user's session. Follow general SharePoint Server guidelines for monitoring and troubleshooting of the SQL Server tier.

Änderungsverlauf

Datum Beschreibung

04. November 2010

Erstveröffentlichung

Fanden Sie dies hilfreich?
(1500 verbleibende Zeichen)
Vielen Dank für Ihr Feedback.
Anzeigen:
© 2014 Microsoft