Estimate performance and capacity requirements for Excel Services in SharePoint Server 2010

 

Si applica a: SharePoint Server 2010

Ultima modifica dell'argomento: 2015-03-09

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.

Nota

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:

• Test farm characteristics

• Test Results

• Recommendations

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, Servizi di calcolo Excel, 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 Servizi di calcolo Excel 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 Servizi di calcolo Excel 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 Servizi di calcolo Excel, 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
Servizi di calcolo Excel	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 Servizi di calcolo Excel 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 Servizi di calcolo Excel and Web front-end servers for the scale-up tests, and then varied the number of processors and available memory in the Servizi di calcolo Excel computers.

Servizi di calcolo Excel 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 Servizi di calcolo Excel 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 Servizi di calcolo Excel computers to the farm. These throughput numbers are specifically for the Servizi di calcolo Excel 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

Recommended Results

The following chart shows our results for recommended sustainable throughput.

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

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 Servizi di calcolo Excel computers, the CPU load is reaching the maximum seen for a single Web front-end computer. This implies that adding another Servizi di calcolo Excel 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.

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 Servizi di calcolo Excel computer. Also notice that exactly as with the recommended results, adding a third Web front-end computer does not add to throughput as Servizi di calcolo Excel is the limiting factor after the second Web front-end computer is added.

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 Servizi di calcolo Excel 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

Servizi di calcolo Excel Tier	1x1	1x2	1x3	2x1	2x2	2x3	3x1	3x2	3x3
% CPU (average over all Servizi di calcolo Excel 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 Servizi di calcolo Excel 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

Servizi di calcolo Excel Tier	1x1	1x2	1x3	2x1	2x2	2x3	3x1	3x2	3x3
% CPU (average over all Servizi di calcolo Excel 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 Servizi di calcolo Excel 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 Servizi di calcolo Excel tier. For these tests, a 1x1 topology was used.

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

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 Servizi di calcolo Excel 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 Servizi di calcolo Excel tier. Note that one of the first bottlenecks an Servizi di calcolo Excel 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 Servizi di calcolo Excel computers may be necessary. Add additional Servizi di calcolo Excel 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 > applicazione Excel Services > Global Settings:

• Maximum Private Bytes  — By default, Servizi di calcolo Excel 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 Servizi di calcolo Excel, 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 — Servizi di calcolo Excel will cache unused objects (for example, read-only workbooks for which all sessions have timed out) in memory. By default, Servizi di calcolo Excel 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 Servizi di calcolo Excel. 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, Servizi di calcolo Excel will keep objects in the memory cache as long as possible. To reduce the Servizi di calcolo Excel 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 > applicazione Excel Services > 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, Servizi di calcolo Excel 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 Servizi di calcolo Excel tier computers. However, there may be users in your organization that need these settings to be increased for Servizi di calcolo Excel 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, Servizi di calcolo Excel 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 — Servizi di calcolo Excel 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
Servizi di calcolo Excel Memory	Excel Services holds each workbook in memory throughout the user's session. A large number of workbooks, or large workbooks, can cause Servizi di calcolo Excel to consume all available memory causing the actually consumed "Private Bytes" to exceed "Maximum Private Bytes."	Scale Up with more memory in the Servizi di calcolo Excel tier computers, or Scale Out with the addition of more Servizi di calcolo Excel computers. The choice will partly depend on if CPU is also reaching a maximum.
Servizi di calcolo Excel 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 Servizi di calcolo Excel computers, or add Servizi di calcolo Excel 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.

Servizi di calcolo Excel

The following table shows performance counters and processes to monitor for application servers, or in this case Servizi di calcolo Excel, 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 Servizi di calcolo Excel 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 Servizi di calcolo Excel, memory consumption for the Servizi di calcolo Excel 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 Servizi di calcolo Excel tier during the user's session. Follow general SharePoint Server guidelines for monitoring and troubleshooting of the SQL Server tier.