Many factors can affect the performance and availability of your Microsoft Office SharePoint Server 2007 deployment, including network bandwidth and resource consumption. Excel Services in Microsoft Office SharePoint Server 2007 in Office SharePoint Server 2007 can affect system performance, depending on the volume of client connections and the number of simultaneous Excel Calculation Services session requests. Calculation size and complexity can also affect Excel Calculation Services resource consumption.

The scope and complexity of Excel calculations can affect the performance and availability of Excel Services in an Office SharePoint Server 2007 deployment. As calculation volume, complexity, and frequency increase, more system resources are consumed. Define baseline hardware requirements for system memory, CPU speed, and storage for each application server that runs Excel Calculation Services.

Office SharePoint Server 2007 is logically divided into three tiers: the front-end Web server tier, the application server tier, and the database tier. During installation, Excel Services installs two components (Excel Web Access and Excel Web Services) on the front-end Web server tier, and one component (Excel Calculation Services) on the application server tier, as shown in the following diagram.

A network load balancer enables Office SharePoint Server 2007 to scale out front-end Web servers. The network load balancer is available to all Office SharePoint Server 2007 applications. You can scale out application servers by adding computers within the shared services framework in Office SharePoint Server 2007.

There are three schemes for load balancing Excel Calculation Services requests: Workbook URL, Round-Robin, and Local. You can configure separate load balancing schemes for each Shared Services Provider (SSP).

For information about how to install the Office SharePoint Server 2007 Excel Services components, see the following articles:

There are two data access models you can use for any of the Excel Services server farm topologies: trusted subsystem and constrained Kerberos delegation.

Trusted subsystem This is the default setting for a Windows server farm, because it does not have the extra configuration requirements of the delegation model. In the trusted subsystem model, front-end Web servers and application servers running Excel Calculation Services trust the accounts of the associated Office SharePoint Server 2007 applications by using the SSP. In a trusted subsystem environment, when opening files from Microsoft Office SharePoint Server 2007, permission checks on the files can be performed against end-user identities even if Kerberos is not configured. If Excel Calculation Services application servers are opening workbooks from UNC shares or HTTP Web sites, the user account cannot be impersonated, and the process account must be used.

Constrained Kerberos delegation This is the most secure configuration for communication between front-end Web servers and Excel Calculation Services application servers. Constrained Kerberos delegation is also the most secure configuration for accessing back-end data sources from application servers. Constrained Kerberos delegation is the preferred configuration for deploying Excel Services. For external data connections, Integrated Windows authentication will only work if the delegation model is implemented.

The following topology diagram shows the logical architecture for a stand-alone Excel Services deployment on a single server.

In this example, all Office SharePoint Server 2007 components are installed on a single computer. This includes the Excel Services front-end Web server components and Excel Calculation Services, the application server component of Excel Services. This also includes the configuration and content databases, which are installed by using Microsoft SQL Server Express Edition. This topology is useful for testing the core functionality of Excel Services, but it is not recommended as a scalable solution.

This configuration supports multiple Windows SharePoint Services 3.0 Web applications by using Integrated Windows authentication. In this configuration, external data access can be provided by using data connection libraries, the Microsoft Single Sign-On service (SSO), or data connections embedded in workbooks. Load balancing is provided by the Excel Services load–balancing component. This topology uses the trusted subsystem data access method. The file access method is an Excel Calculation Services process account configured as an SSO administrator.

The following topology diagram shows the logical architecture for a small farm Excel Services deployment that uses the trusted subsystem data access model.

In this example, the Excel Services front-end Web server components and the application server component are installed on one server, and the SQL Server 2005 configuration and content databases are installed on a separate server. This configuration also shows the Single Sign-On service running on the Office SharePoint Server 2007 server. The configuration also shows the Excel Calculation Services process account configured as an SSO administrator.

The following topology diagram shows the logical architecture for a small farm Excel Services deployment that uses the constrained Kerberos delegation data access model.

In this example, the Excel Services front-end Web server components and the application server component are installed on one server, and the SQL Server 2005 configuration and content databases are installed on a separate server. This configuration also shows the Single Sign-On service running on the Office SharePoint Server 2007 server. The configuration also shows the Excel Calculation Services process account configured as an SSO administrator.

The following topology diagram shows the logical architecture for a medium farm Excel Services deployment that uses the trusted subsystem data access model.

In this example, the Excel Services front-end Web server components, the application server component, and the SQL Server 2005 configuration and content databases are installed on three separate servers. This configuration also shows the Single Sign-On service running on the Office SharePoint Server 2007 front-end Web server and on the Excel Services application server. The configuration also shows the Excel Calculation Services process account configured as an SSO administrator.

In this example, the Excel Services front-end Web server components, the application server component, and the SQL Server 2005 configuration and content databases are installed on three separate servers. This configuration also shows the Single Sign-On service running on the Office SharePoint Server 2007 front-end Web server and on the Excel Services application server. The configuration also shows the Excel Calculation Services process account configured as an SSO administrator.

The following topology diagram shows the logical architecture for a large farm Excel Services deployment that uses the trusted subsystem data access model.

In this example, multiple Excel Services front-end Web server components, multiple application server components, and multiple SQL Server 2005 configuration and content databases are each installed on separate servers. This configuration also shows the Single Sign-On service running on the Office SharePoint Server 2007 front-end Web server and on the Excel Services application server. The configuration also shows the Excel Calculation Services process account configured as an SSO administrator.

Important:

For large farm deployments, it is recommended to run Office SharePoint Server 2007 in a homogeneous environment. All of the servers in a large farm should run either a 32-bit version of Windows Server 2003 or a 64-bit version of Windows Server 2003. It is not recommended to run Office SharePoint Server 2007 in a mixed environment.

The following topology diagram shows the logical architecture for an Excel Services deployment that isolates front-end Web server components and SQL Server 2005 configuration and content databases in a perimeter network that is separated from the corporate intranet. Excel Services application servers are deployed within the corporate intranet and all Excel Services components are isolated from remote client computers and from external data sources.

In this example, Excel Services application servers are deployed within the corporate network and are protected from direct external access. This configuration enables you to tightly control who can access your application servers and data. By isolating front-end Web servers in a perimeter network, you limit the attack surface of your environment to public-facing Web servers.

Note:

If you have a Windows firewall enabled on the application servers, you must open the specific port on which the shared services Web site that hosts Excel Calculation Services is running.

The following topology diagram shows the logical architecture for a deployment that includes Microsoft Windows Compute Cluster Server 2003 and Excel Services.

In this example, the Excel Services front-end Web server components and the application server component are installed on individual compute cluster nodes that comprise a compute cluster. These nodes share a SQL Server 2005 configuration and content database that runs on a server that is external to the compute cluster. A head node runs the compute cluster scheduler that schedules and load balances jobs among the nodes in the cluster.

The number of simultaneous client connections can impact the performance and availability of front-end Web servers. For a deployment designed to support a large number of client connections, you need to plan for the deployment of enough front-end Web servers to support an adequate level of system performance and availability. Determine the number of front-end Web servers that are required for your deployment scenario. Define baseline hardware requirements for system memory, CPU speed, and storage for each front-end Web server.

To determine the appropriate scale of a deployment to support Excel Calculation Services in Office SharePoint Server 2007, you need to know how many concurrent Excel Calculation Services session requests the scenario needs to support. For a deployment designed to support a large volume of Excel Calculation Services sessions, you need to plan for enough Excel Calculation Services servers to support an adequate level of performance and availability. Add Excel Calculation Services servers to improve performance and availability. Excel Calculation Services Proxy is a front-end Web server component used to load-balance session requests among the available application servers that run Excel Calculation Services.

You can configure a per-user session limit for application servers that run Excel Calculation Services. This value is the maximum number of concurrent sessions allowed for each user. Windows SharePoint Services 3.0 Web sites can be configured to allow anonymous user access. In this case, the Excel Calculation Services session limit for a single user will still apply. All anonymous users share the same session limit value that is configured for a single user.

The following configuration changes are recommended for an Excel Services environment that supports a large volume of Excel Calculation Services session requests and workbook calculations.

IIS time-out If calculations in Excel Services are expected to take longer than 110 seconds, increase the IIS time-out value. A ThreadAbort exception will occur when calculations exceed the IIS time-out setting. The default IIS time-out setting is 110 seconds.

ASP.NET MaxConnection When the number of client connection requests requires front-end Web servers to open multiple concurrent connections to application servers running Excel Calculation Services, you need to increase the maximum number of ASP.NET connections. In ASP.NET, configure the MaxConnection setting to a maximum connection limit of 10, as shown in the following example:

Incorrectly editing the registry might severely damage your system. Before making changes to the registry, you should back up any valued data on the computer.

HeapDeCommitFreeBlockThreshold During a period when Excel Calculation Services application servers are experiencing a high volume of requests, the application servers might encounter performance issues caused by memory allocation failures. Because of memory fragmentation, this can occur even if application servers have adequate amounts of RAM installed to handle the volume of requests. To alleviate this problem, use the Registry Editor to change the HeapDeCommitFreeBlockThreshold setting to REG_DWORD 0x00040000. To change the setting, use the following registry path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

MaxUserPort You might need to increase the range of available ports by changing the value of the MaxUserPort registry setting. The MaxUserPort setting specifies the highest port number that TCP can assign when an application requests an available user port from the system. To change this registry setting, run the Registry Editor and use the following registry path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

TcpNumConnections You might need to increase the maximum number of connections that TCP can have open simultaneously by changing the value of the TcpNumConnections registry setting. If the TcpNumConnections registry key does not exist, run the Registry Editor to create and configure the key. To change this registry setting, run the Registry Editor and use the following registry path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

TcpTimedWaitDelay During a period when Excel Calculation Services application servers are experiencing a high volume of requests, there is a possibility that some requests will be denied. If this occurs, you might be encountering a problem with the default setting of the TcpTimedWaitDelay registry key. By default, the TcpTimedWaitDelay setting is set to 240 seconds, or four minutes. This is the amount of time that must elapse before a closed TCP session can be released and the resources can be reassigned. To alleviate this problem, use the Registry Editor to change the TcpTimedWaitDelay setting from 240 seconds to 30 seconds. If the TcpTimedWaitDelay registry key does not exist, run the Registry Editor to create and configure the key by using the following registry path:

The following performance test results provide guidance based on comparisons of throughput, response times, and resource utilization of Excel Services by using different topologies and data sets. These recommendations are also based on comparisons of performance factors by using different external data authentication schemes for online analytical processing (OLAP) and relational external data connections. Resource requirements and response times in Excel Services depend on workbook size, workbook complexity, the number of concurrent users, and external data latency. Tests are run by using a defined data set, and results vary depending on data set design.

The baseline configuration for each tested data set includes Office SharePoint Server 2007 dashboards, with each dashboard rendering two Excel Web Access Web Parts and two key performance indicator (KPI) Web Parts. The result is four Excel Calculation Services sessions for each page hit.

Twenty percent of the sessions are assumed to have some interactivity, such as paging or data refresh. The tests are based on the assumption that 5,000 users are actively accessing dashboards and portal KPIs linked to cells in Microsoft Office Excel 2007 workbooks.

This section provides information about the attributes of the data set and scenarios used for performance testing. Exceptions to these attributes are noted in individual configurations, as applicable. Excel Services performance depends on the attributes of the data set, including memory usage, CPU usage, throughput, and response time. It is important to consider the characteristics of your data set before using the performance test results provided here. The results you get will vary based on the data used in your environment, and other variables.

The performance tests were run using the following test scenarios. The scenarios are based on an assumption of a total of 5000 users actively accessing dashboard pages and Excel workbooks, with Office Excel Web Access. The scenarios are also based on the assumption that a variety of dashboards are accessed with varying degrees of frequency. The tests include:

A small number of dashboards that are accessed frequently by a large number of users.

A large number of dashboards that are accessed infrequently by a small number of users.

This range of dashboard usage corresponds to an enterprise environment in which a small number of dashboards are used across the entire organization, and a large number of dashboards are used by groups within the organization.

The workbooks in this scenario are not connected to an external data source. Resource consumption is determined by the size, complexity, the amount of formatting, and the number of calculations used in each workbook. Workbook size is determined by the number of rows and columns each workbook contains. All of these factors affect resource consumption and throughput in Excel Services. The workbooks include a range of features, such as formatting, conditional formatting, and charting. The dataset used includes a range of workbooks with the following attributes:

The workbooks in this scenario contain data connections to Analysis Services cubes. These workbooks include a range of features, such as formatting and conditional formatting. In addition to the variables that affect resource consumption and throughput in every scenario, the amount of Analysis Services data that is consumed by the workbooks in this scenario, and whether or not the workbooks are refreshed on open, can affect Excel Services performance. This scenario is based on the assumption that 15% of the workbooks have data connections that refresh on open. Caching has a major impact on the performance of external data connections. Typically, Excel Services uses cached data if there is a connection to an external data source, using the same credentials multiple times. Caching performance is also affected by the Excel Services External Data Cache Lifetime setting.

Percentage

Rows

Row size

Other

10%

<1,000

512 – 2,048

DCL SSO (or Kerberos)

20%

<1,000

512 – 2,048

DCL SSO (or Kerberos), refresh on open

40%

<1,000

100 – 512

One to four PivotTable reports and one to four PivotChart reports embedded in workbooks

20%

<1,000

<100

Four to six PivotTable reports and four to six PivotChart reports embedded in workbooks

10%

1,000 – 5,000

<100

Two PivotTable reports and two PivotChart reports embedded in workbooks

The workbooks in this scenario contain data connections to SQL Server relational databases. These workbooks include a range of features, such as formatting and conditional formatting. In addition to the variables that affect resource consumption and throughput in every scenario, whether or not the workbooks are refreshed on open can affect Excel Services performance in this scenario. This scenario is based on the assumption that 15% of the workbooks have data connections that refresh on open. Caching has a major impact on the performance of external data connections. Typically, Excel Services uses cached data if there is a connection to an external data source, using the same credentials multiple times. Caching performance is also affected by the Excel Services External Data Cache Lifetime setting.

Percentage

Rows

Row size

Other

2%

<1,000

<512

DCL SSO (or Kerberos), periodic refresh

20%

<500

<512

DCL SSO (or Kerberos), refresh on open

40%

1,001 – 5,000

1024 – 2,048

One or two PivotTable reports and one or two PivotChart reports embedded in workbooks

20%

5,000 – 10,000

1024 – 2,048

DCL SSO; one or two PivotTable reports and one or two PivotChart reports embedded in workbooks

10%

10,000 – 20,000

512 – 1,024

DCL SSO; two to four PivotTable reports and two to four PivotChart reports embedded in workbooks

Performance tests were run using the following distribution of workbooks. Exceptions to this distribution are noted for some of the configurations. In each configuration, workbooks were distributed based on the percentages shown in the data set attribute tables. For Example, 30% of the workbooks contained no external data. Of these, 20% had between 5000 and 7000 rows, as shown in the Workbooks without external data connections table. This resulted in 6% of the total workbook distribution (20% of 30%), consisting of workbooks with no external data and containing between 5000 and 7000 rows. Some of the configurations isolate workbooks of a specific type. For example, workbooks containing OLAP data connections configured to refresh on open.

Performance tests were run using the following request mix. There are exceptions to this request mix. For example, in tests using workbooks with no external data, there are no interactive operations with external data queries. Exceptions are noted for some of the configurations.

In addition to adding resources, limiting the number of open Excel Calculation Services sessions can help mitigate performance and availability issues. One way to limit the number of open Excel Calculation Services sessions is to decrease session time-out settings. Indiscriminately limiting session duration can cause active sessions to close, which can result in data loss.

The preferred solution is to use Office Excel Web Access Short Session Mode to configure shorter initial time-out settings for sessions that have no client-side interactivity when the sessions are opened. You can configure a second time-out setting for sessions that are active as soon as they are opened. In this example, sessions that remain inactive for the duration of the initial time-out setting are closed by the Excel Calculation Services server. Sessions that are active before the initial time-out setting expires will remain open until the second time-out setting expires.