Book excerpt -- Chapter 3: Architecture

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

This article is an excerpt from Beginning Excel Services by Liviu Asnash, Eran Megiddo, and Craig Thomas, and property of John Wiley & Sons, Incorporated (ISBN 978-0-470-10489-7) copyright March 2007, all rights reserved. No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

This chapter goes behind the scenes and explains how the scenarios discussed in Chapter 1 work. After reading this chapter, you should have a better understanding of what Excel Services does, and how it does it.

The first part of this chapter begins with a discussion of the Microsoft Office SharePoint Server architecture. It then examines the main components of Excel Services — the Excel Calculation Server (ECS), the Excel Web Access (EWA) Web Part, and the Excel Web Services API — and the flow of information between them.

The second part of this chapter examines the basic operational concepts of Excel Services: sessions, workbook operations, querying from external databases, caching, scaling the system, thread management, and charting.

The last section of this chapter revisits many of these topics and a few more from the point of view of performance. It recaps the architecture of the server and describes how you can make the most out of the hardware. Here you will explore the use of load balancing, networking, memory resources, CPU resources, I/O, and charting, as well as the interaction between the client machine and the browser.

Getting to Know Excel Services

To get started, take a look at a simple scenario and the main server components that are involved. Say you are a user who authors an Excel workbook and saves it to a SharePoint document library. You create a new SharePoint web page and add an EWA Web Part to it. You modify the EWA properties to point it to the workbook. Then you browse to the web page to see the workbook displayed as HTML in the browser inside the EWA. Figure 3-1 shows an example of what this web page might look like.

Figure 3-1

When the web page is opened in the browser, SharePoint builds the HTML for the page and asks each Web Part to generate its HTML snippet. The EWA and Excel Services open the file from the document library, calculate it, and generate its HTML representation. The main Excel Services components are the EWA Web Part and the ECS. Figure 3-2 shows these components and their relationship to the browser and the SharePoint document library.

The ECS is the engine behind Excel Services. It contains the logic to open workbooks, calculate them, query workbook data, and perform operations. The EWA is a layer on top of the ECS that transforms the ECS information to the HTML rendering.

In this example, the EWA sends a request to the ECS to open a workbook and display a range of data from it. The ECS downloads the workbook from the document library, opens it, calculates it, and returns the information from the requested range. The EWA receives the range information, transforms it to its HTML representation, and returns it to SharePoint, which sends it back to the browser as part of the whole web page. The EWA also generates a number of JavaScripts that are sent to the browser as part of the page. These scripts are used primarily for interactivity.

Figure 3-2

When the user switches to the second sheet in the browser, the EWA asks the ECS for the range on that sheet, and the ECS returns it from the already opened workbook. When the workbook was originally opened, a session was created and is maintained on the ECS. Any further operation that the user does (for example, refreshing data from a pivot table in the workbook) is performed in a similar fashion. The EWA sends the request to the ECS. The ECS performs the operation (in this case, it queries an external database for the pivot data), recalculates the workbook as needed, and returns the range information. The EWA then generates the HTML.

The following section takes a closer look at each of the main components of Excel Services to help you better understand this example scenario.

Understanding the Architecture

There are four key components of Excel Services:

• Microsoft Office SharePoint Server

• Excel Calculation Server (ECS)

• Excel Web Access (EWA) Web Part

• Excel Web Services API

Office SharePoint Server Architecture

This section explains at a high level the Microsoft Office SharePoint Server architecture. The main components in the SharePoint Server architecture are the web front end (WFE), the application server, and the database server (see Figure 3-3).

The WFEs are responsible for rendering the pages for the user requests. They might query data directly from the database server, or communicate with the appropriate application server to perform the operation. All the WFEs are mirrors of each other, and are usually load-balanced using network load balancing (NLB). (Load balancing is discussed in more detail later in this chapter.)

The application servers perform the more resource-intensive operations. This applicative logic is separated from the WFE to allow the WFE to be lightweight and scale independently. (Scaling is discussed in more detail later in this chapter.) Each application server has one or more roles, which define the features that are enabled on that machine. For example, one application server might be assigned the role of ECS, and a second application server might be assigned the roles of search querying and indexing. When a WFE needs to use a specific application from an application server, it uses a machine that contains the relevant role.

Figure 3-3

The database servers store the user, application, and configuration data. The user data contains information such as documents that are saved in document libraries. The application data is specific to each feature running in the SharePoint farm, which is described shortly. For example, it may contain some of the search indexing information. The configuration data contains the administrative settings at the farm and application level.

These components can be deployed on the same machine, or scaled out to multiple machines. Chapter 6 discusses the various topologies that are supported.

The SharePoint Hierarchy

A SharePoint farm is a collection of one or more machines that have the roles of WFE, application server, and database server, and are managed by the same central administrator.

A SharePoint farm is composed of one or more web applications. Each web application, also known as Windows SharePoint Services (WSS) Virtual Server (vServer), is a separate Internet Information Server (IIS) application pool and has a different IIS port. Most farms will have one web application, but in large enterprises, the central Information Technology (IT) manages several web applications in the same farm. Each web application has a separate content database for storing its user data.

A web application has one or more WSS site collections. The site collection is the top-level container for sites and subsites.

Each of the WFE machines supports all the web applications and all their site collections and sites. Figure 3-4 shows the SharePoint hierarchy.

Figure 3-4

In the figure, the following are web applications:

• http://finance

• http://operations

• http://hr

The following are web site collections:

• http://hr/sites/reports

• http://hr/sites/teams

And the following are sites and subsites:

• http://hr/sites/teams/benefits

• http://hr/sites/teams/recruiting

• http://hr/sites/teams/recruiting/college

Shared Services Provider

A Shared Services Provider (SSP) is a group of services on the application servers (such as ECS and Search) that support the WFE. By default, there is one SSP in each SharePoint farm, and it supports all its web applications and site collections.

The administrator can create additional SSPs and map each web application to an SSP. Each SSP runs its services on the application servers in a separate process. This means each group of web applications on the WFE can have a separate ECS process, and each process can be managed separately.

Figure 3-5 shows an example of three web applications, each mapped to one of two SSPs that exist in the farm.

All SSPs run on all the application server machines. Each application server that contains the ECS role has a separate ECS process for each SSP. Figure 3-6 shows an example of two application server machines, with different roles enabled on them. Each of these machines has two SSPs running on them, each with the roles that are enabled on that machine.

The Excel Calculation Server (ECS)

The ECS is a web service and runs as part of the IIS on each application server machine. As mentioned, there is a separate process (application pool) for each SSP.

The WFE components (that is, the EWA and the API) communicate with the ECS via Web services. This Web service is not supported and not documented by Microsoft. The recommended way to interact with Excel Services is via the WFE API (described shortly). Following is the URL for the ECS Web service:

http://ECSMachineURL/TheSSPName/ExcelCalculationServer/ExcelService.asmx

Figure 3-5

Figure 3-6

The Excel Web Access (EWA) Web Part

The EWA is a SharePoint Web Part. It can be used inside a SharePoint Web page by itself, or together with other Web Parts to form a dashboard.

The Excel Viewer is a predefined Web page that has one instance of the EWA on it. It can be used to view one workbook specified as a parameter on the URL.

The flow described in the basic example at the beginning of this chapter is an oversimplification. In practice, when the web page is rendered on the WFE, the EWA has a small HTML file that is generated quickly, without a call to the ECS. This small HTML file contains an iframe element and a progress message. The iframe message is sourced back to the WFE, and it will do the more expensive operation of actually opening the workbook through the ECS. The progress message is displayed until the iframe comes back with the HTML for displaying the spreadsheet.

When a user performs an operation in the workbook, only the iframe is refreshed, without requiring a postback of the entire page (which might have additional Web Parts and controls).

The Excel Web Services API

Excel Services exposes an API to allow programmatic access to its functionality. The API is exposed as a web service on the WFE, and also enables applications running on the WFE to link with it locally, as shown in Figure 3-7.

Whether you access Excel Services through the EWA or the API, the basic concepts of sessions, requests, state management, publishing workbooks, loading workbooks on the server, accessing external data, performing operations, and memory management are the same. With very few exceptions, the same administrator settings apply for both the EWA and the API.

Figure 3-7

Each SharePoint web application and site has an instance of the web service. You must access the API from the correct site to ensure that the site context is used for authentication and authorization. For example, the URL of the API for the web application at http://vServer is the following:

http://vServer/_vti_bin/ExcelService.asmx

And the URL of the API for the site at http://vServer/site is the following:

http://vServer/site/_vti_bin/ExcelService.asmx

Chapter 14 provides more details about using the API.

Understanding Operational Concepts

This section drills down into various aspects of these components and their interactions. It starts with a discussion about sessions, which are the isolation unit for the user and provide the context in which all the operations run. Sessions start when users open a workbook, and end when they close the workbook or when the session times out. The state of a session is private to the user of the session, and it is not seen by other sessions.

The discussion then examines the concepts related to performing requests (or operations) on the server. Examples of such requests are opening a workbook, paging down, filtering a table, and drilling in a pivot table.

Next, you will look into the process of publishing workbooks to the server and loading them. Publishing is the process of putting an Excel workbook in a location from which the server can load it (such as a SharePoint document library). On the server, the file gets downloaded to the ECS machine and then loaded into memory.

The ECS caches workbooks and other objects in memory to improve performance. You will explore the various settings that affect the caching and how those affect the behavior of the server.

You will also look at the basics of querying external data. The full details on this complex matter are in Chapter 5.

Next, you will learn about ways to scale out the server and to load-balance.

The discussion concludes with a look at how ECS threads are used to service requests.

Sessions

Any interaction that a user has with a workbook is done in the context of a session. A new session is automatically started every time a user opens a workbook. It may continue with additional user interactions, such as refreshing data or filtering a table, and it ends when the workbook is closed or after a timeout.

Opening a workbook is always done in the context of a new session. A user may open multiple sessions with the same workbook or with different workbooks. Each EWA instance on a Web Part Page will result in a separate session being opened. For example, if a user opens a Web Part Page with three EWA instances, two of them opening workbook A and the third opening workbook B, then three sessions will be opened. When the same user or a different user opens the same Web Part Page in a new browser instance, three more sessions will be opened.

Sessions provide isolation for the user. Changes performed in one session are not seen by any other session. For example, if two sessions are opened with the same workbook, and the user changes some workbook cell values in the first session, the user of the second session (be it the same user or a different one) will not see those changes in the second session. The best way to think of a session on the server is to imagine users opening workbooks in read-only mode in Excel. Each of those instances of Excel is a session, and changes made in those sessions are kept within them until they are saved.

The session is closed when the workbook is closed, either explicitly through the API or when a new workbook is opened in the EWA (depending on the EWA settings). In addition, sessions are automatically closed when a configurable timeout has expired after a period of user inactivity. After a session times out, the workbook continues to be displayed in the EWA, and the user is notified if he or she makes changes to the timed-out session that will be lost. A new session is then opened.

Session Settings

Each session consumes server resources, mainly memory and (when a request is being performed) additional resources such as a CPU and I/O. Administrators should configure the server for the right balance between preserving these resources and providing adequate service to their users.

Administrators can configure the maximum number of sessions a user is allowed to open at any time. By default, this is set to 25 sessions.

In addition, administrators can configure the session timeouts. There are two settings for timeouts. The short session timeout is used when the session has a workbook that was just opened (with no other operation performed on the workbook), and has a default value of 75 seconds. The session timeout is used after additional interactivity has been performed on the workbook, and has a default value of 5 minutes. In many cases, dashboards just display (open) workbooks without any additional interactivity, so setting a shorter timeout helps preserve server resources. You can change these timeout settings at the trusted location level. For more information about settings and trusted locations granularity, see Chapter 7.

Exposing the Session ID

The web service API exposes the session ID. It returns the session ID when a workbook is opened, and the session ID is passed as a parameter to the other API methods.

The EWA also exposes a JavaScript method that can be called to get the session ID from a running instance of the Web Part. You can extend the EWA to other controls on a page that interacts with its session through the API.

Session State

After each operation is finished, the session is left in a certain state. Operations on the session can change its state or query its state. Closing the workbook is a special operation that terminates the session.

Figure 3-8 shows the initial state of the session after the workbook is opened (State 1), followed by the states after two additional operations of setting values into cells are performed (State 2 and State 3).

Figure 3-8

The session state is maintained in memory on the ECS machine. In a scaled-out configuration (when there are multiple ECS machines), all requests for a session will always be routed to the same ECS machine — the one that contains the session state.

When working with the EWA, the browser keeps the session ID and sends it to the server in every request, to identify the session that will process the request.

Workbook Operations

Operations are requests that the user performs on workbooks. Examples of operations are opening and closing workbooks, getting values from cells and setting values into cells, calculating the workbook, refreshing data, filtering and drilling a pivot table, filtering and sorting a table, and finding a value in the workbook.

The typical flow of a request is as follows:

1. The user sends the request from the client machine to the WFE, which forwards the request to the ECS.

2. The ECS performs the request, updates the state of the workbook in the session, and returns the result back to the WFE. In the case of the EWA, the result typically contains the new range to be displayed.

The ECS does not allow a user to perform more than one request per session at any given time, except for the following operations that can be performed in parallel in a session:

• Getting values from the workbook

• Getting charts from the workbook

• Closing the workbook

A user can cancel a request by sending a special cancellation request to the WFE and, from there, to the ECS. The cancellation request attempts to stop the operation that is being performed in the session to free system resources, and to enable the user to perform a new operation in the same session.

Request Settings

The ECS maintains a request timeout that can be configured by the administrator. If a request does not finish until the request timeout has expired, the ECS cancels the request. This prevents single requests from consuming large amounts of resources on the server.

Administrators can configure the request timeout. The default value of this setting is 5 minutes. You can change this setting at the trusted location level. For more information about settings and trusted locations granularity, see Chapter 7.

Publishing and Loading Workbooks

This section examines how workbooks are published, stored, and loaded on the server.

Publishing

In the publishing process, the author creates the workbook and saves it to the server. The authoring is usually done in Excel or in another application that can generate a workbook. Excel Services supports both the Office Open XML (*.xlsx) file format and the Excel Binary (*.xlsb) file format.

You can publish workbooks to a SharePoint document library or to a location outside of SharePoint. The latter can be either a Universal Naming Convention (UNC) path (such as a file share) or an HTTP address (such as a web folder). You can use a regular save operation to publish to the server at either of these locations. The Publish to Excel Services menu option allows a workbook author to specify the ranges to publish and workbook parameters, but it performs a regular save operation with the specified publishing information written into the file. When you publish ranges, the whole workbook is actually saved, and the published ranges are just notations in the file. The server does not do any processing on the file — it is kept in the storage location in the same form that it was published in.

Loading Workbooks on the Server

The ECS component loads the workbook file to Excel Services. It downloads the file from its storage location to the ECS machine, and then loads the file in memory. The security aspects (authentication and authorization) are discussed in Chapter 8.

The mechanism used to download the file depends on the storage location type. Files from SharePoint document libraries are downloaded using the SharePoint object model. Files from UNC and HTTP locations are downloaded using the appropriate protocol.

A downloaded workbook file is opened in read-only mode. A workbook author can make changes to the file while it is open on the server, but these changes will not affect existing sessions on the ECS. However, new sessions will get the updated file. The ECS allows for multiple versions of the same workbook to be loaded simultaneously in different sessions.

Figure 3-9 shows the process of publishing workbooks and loading them on the server.

Figure 3-9

Workbook Disk Cache

In order to minimize the number of file downloads from the storage location, the ECS maintains a file cache on its local disk. Every time a user opens a file, the ECS verifies that he or she has permissions to open it (as detailed in Chapter 8) and that the file timestamp has not changed in its storage location compared to the cache.

The administrator can control several settings related to the disk cache. The maximum size of the disk cache has a default value of 40GB. The caching is turned on by default, and can be turned off if there is not enough local disk space. When caching is turned off, the files are still downloaded to the local cache location, but they are deleted after the session is closed instead of being kept in the cache.

By default, the location of the local cache is under the temporary directory of the ECS machine. Administrators should limit the permissions to the cache directory to the ECS process account so that users cannot go around the security of the storage locations by reading the files from the ECS cache. In addition, you should consider using Windows Encrypted File System (EFS) to provide encryption to this folder.

The maximum workbook size has a default of 10MB. Administrators can change this workbook to control if large files are allowed on the server. This setting can be configured for each trusted location. For more information about settings and trusted locations granularity, see Chapter 7.

Querying Data from External Databases

You can refresh all the data connections in a workbook or drill down in an Online Analytical Processing (OLAP) pivot table query data from an external database.

The ECS is the component that performs the data retrieval. It first determines the end-user credentials through single sign-on (SSO), the user and password embedded in the workbook, Kerberos-constrained delegation, or the ECS process account. The ECS then connects to the database by using those credentials and performs the query. The ECS updates the sheet with the results of the query, and calculates any dependent formulas before returning the result to the WFE.

Querying data from external databases is a very complex scenario in terms of the server architecture. Chapter 5 covers external queries in depth, including the following topics:

• The types of connections that are supported

• The protocols and databases that can be used

• The types of credentials and credential delegations that the ECS provides

• Connection pooling and data caching

• Asynchronous and parallel queries

• Refresh on open and periodic refresh

• Administrator settings for controlling and optimizing data queries

Caching and Memory Utilization

The ECS caches information in memory to improve performance. This section discusses those caches, and the settings available to control their impact on the overall memory footprint of the ECS.

The following table outlines an example of how a workbook is cached.

User Operation	Server Operation
User opens workbook A.	Session 1 loads and calculates the workbook.
User opens workbook A in a second session.	Session 2 reuses workbook calculated by session 1.
User closes session 1 and session 2.	The workbook is kept in the cache.
User opens workbook A in a third session.	Session 3 reuses calculated workbook fromcache.
User closes session 3.	The workbook is returned to the cache.
No activity.	The server frees resources, and the workbook is released.
User opens workbook A in a fourth session.	Session 4 loads and calculates the workbook.

Workbooks are kept in the cache so they do not have to be reloaded and recalculated for every new session.

Sharing Workbooks Between Sessions

There are some cases in which it is not possible to share a workbook. For example, to avoid disclosing personal information, if the workbook refreshes a data query that uses the end-user’s credentials on open, the query results are not shared.

The following list shows some of the cases in which it is not possible to share a workbook state between sessions and require recalculation for each session:

• The user has performed some interactivity on the workbook (for example, the user has set some parameters values or has drilled down in a pivot table). In that case, the workbook state is private to the session and will not be shared.

• The workbook contains the results of a data query that was performed with different credentials. (For more details on external queries and their caching implications, see Chapter 5.)

• The workbook contains calls to user-defined functions (UDFs) that are marked as returning personal information.

• The workbook contains some volatile functions (for example =Now() or =Rand()) and the Volatile Function Cache Lifetime administrator setting does not allow for sharing the workbook state. This setting allows administrators to specify the maximum amount of time in which workbooks that contain volatile functions can be reused from the cache, in order to trade the accuracy of these volatile functions with performance and better caching. The default value is 5 minutes. This setting can be configured for each trusted location. (For more information about settings and trusted locations granularity, see Chapter 7.)

Managing Unused Objects

A workbook can be used by one or more sessions, or not used by any session. If the workbook is unused, then it may be kept in the cache as long as resources are available.

The ECS will free some of the unused objects if one of the following conditions is met:

• A memory allocation has failed.

• One of the administrator settings for the management of unused objects is exceeded.

The Memory Cache Threshold administrator setting determines the size of the ECS process that triggers freeing unused objects. When the size of the process goes above this limit, unused objects are freed to reduce the size of the process.

The Maximum Unused Object Age administrator setting determines how long objects can be kept unused before being freed. Unused objects older than this age are freed even if the process size is below the limit, in order to allow other processes on the machine to use the memory.

If the machine has only the ECS process on it, you can increase these settings to let the ECS take full advantage of the resources and maximize its performance. When other processes share the machine, you can reduce them to achieve the right balance.

The ECS decides which objects to free based on an algorithm that takes into account when the object was last used, how much it was used, how complex the object is, and how much memory it uses. It will first free objects that were not used recently, were not used much, are less complex, and use a lot of memory. These factors are weighted together to decide the order of freeing the objects when the process needs more memory.

Maximum Private Bytes

The administrator can set a maximum size for the ECS process. When this limit is reached, new requests will fail for this specific ECS machine, and might be redirected to another ECS machine if it is available. For example, this limit can be reached if there are no unused objects in memory. (Existing sessions use a lot of memory.)

This limit is called Maximum Private Bytes, and it can be set by the administrator. The limit you should set depends on how much memory is available on the machine, and if the machine is running other processes that require a lot of memory.

Figure 3-10 shows how the system behaves and frees memory, depending on the size of the process and the administrator settings.

Cached Objects

In the previous examples, the workbook instance was discussed as an object that can be shared between sessions, and that can be used or unused. The ECS manages the following objects:

• The calculated workbook instance — This was discussed earlier.

• The results of a data query — Data queries are a potentially expensive operation. The query results can be shared among those that use the same identity when connecting to the data source.

• The loaded workbook — This includes the cached workbook available on the local disk.

• A saved workbook — An example is a workbook that is generated when you start Excel, open a snapshot with the EWA, or call GetWorkbook with the API. If this cached object is available, performing the operation a second time brings the saved workbook from the cache instead of saving it again.

Figure 3-10

• A generated chart image — The process of generating the image is costly, so caching is used whenever possible.

• A range of data for the EWA — The EWA asks the ECS for the range it needs to display, and the ECS returns it from the cache if it is available. This range contains all the information needed for the EWA to render it.

Scaling to Multiple Machines

Excel Services supports multiple topologies. The most compact one is running all the components of the WFE and the ECS on the same machine. It can be used in relatively small, departmental, and evaluation deployments.

In larger installations, the WFE and the ECS can be deployed to separate machines. In addition to rendering the EWA Web Part, the WFE supports all SharePoint functionality.

Scaling Up and Scaling Out

To determine the best way to scale your system, you need to analyze your specific requirements. Scaling up means adding more resources to your machine. Scaling out means adding more machines to the farm.

You can scale up by using a multi-processor machine, adding more memory (preferably on the 64-bit architecture), and/or having faster network cards on your machine. As with any other performance analysis, you should start by understanding your bottleneck. For example, if your workbooks are calculation-intensive, adding more CPUs will help. If you load a lot of large workbooks, adding more memory will help. (Optimization of the CPU and memory is discussed in more detail later in this chapter.)

Scaling out is another alternative you should consider. Of course, you will need to factor in the price of the hardware and the maintenance and administration of the farm when you’re determining the best way to scale out.

You can scale out the WFE and the ECS independently. For example, you can scale out the WFE if there is a lot of SharePoint front-end activity (such as displaying the SharePoint home page, browsing through lists and document libraries, and so on), as shown in Figure 3-11. Or you can scale out the ECS if there are a lot of Excel calculations being performed.

A combination of scaling up and scaling out is usually best. WFE machines are normally cheaper, so it would be most cost-effective to scale them out and scale up application server machines. (Chapter 6 discusses capacity planning for Excel Services.)

Figure 3-11

Load Balancing

When a client machine opens a connection to the WFE, the normal SharePoint load balancing (usually NLB) is used to choose the WFE machine that will service that request.

When the WFE opens a new Excel Services session on the ECS, it chooses the ECS machine according to the load-balancing scheme. This scheme is an administrator setting, and has the following values:

• Local — The WFE will attempt to use the ECS located on the same machine as the WFE. Choose this method if each machine in the farm contains both the WFE and the ECS.

• Round Robin — The WFE will choose the next ECS machine for every new session. You can use this method to better distribute the load between the ECS machines in some scenarios.

• Workbook URL (default) — The WFE will use a hash based on the workbook URL to determine the ECS machine. The same workbook will be serviced by the same ECS machine. This method reduces the memory consumption on the ECS machine, because each workbook will be opened only on one ECS machine. It does have the disadvantage of not using the CPU on the other ECS machines if the sessions all go to the same (or few) workbooks.

After the session opens and the ECS is chosen, all subsequent requests in the same session will be serviced by the same ECS machine (that is, the machine that contains the state of the session in its memory).

ECS Thread Management

Incoming requests to the ECS are queued by the IIS, and the next available request starts its execution when a thread is available. In the basic case, a request is taken out from the queue and assigned a thread that will execute it. After the request is done, the thread is returned to the thread pool to be used by a new request. The number of requests that get executed in parallel is the same as the number of available threads.

When a request performs an I/O or networking operation (such as fetching a file), its thread is not utilized. In some cases, the I/O or networking operation is performed asynchronously to avoid underutilization of the CPU on the ECS machine, and during this time, the thread is returned to the pool for use by another request. Not all I/O operations are asynchronous, so if your CPU is underutilized on the ECS while requests are queuing up, you should increase the number of available threads.

Queries to external data are performed in parallel on separate requests. For example, if a workbook has several pivot tables and the user refreshes them, the pivot tables will be executed in parallel to optimize for the total time. You can control the maximum number of queries that a request may run in parallel through the Maximum Concurrent Queries Per Session administrator setting.

Excel Services does not support multi-threaded recalculation (MTR). MTR is a new feature that was added to the Excel 2007 client, in which complex calculations are automatically broken down into multiple smaller parts that are run in parallel on multiple threads. In Excel Services, calculations are run serially in each request.

Charting

Charts are calculated and rendered in Excel as follows:

1. The EWA asks the ECS for a range that contains a chart.

2. The ECS calculates that range, including calculating the data on which the chart is based. This data could come from a sheet range or from a pivot table (in the case of pivot charts).

3. The ECS returns the range data along with the location and ID of the chart to the EWA.

4. The EWA generates the HTML for the range, which includes an img tag for each chart. These img tags are linked back to the WFE with the ID of the charts.

5. When rendering the page, the browser makes another call to the WFE for the chart image.

6. The WFE calls the ECS to generate the chart image.

7. The ECS generates the chart image, stores it on the local disk cache for future use, and returns it to the WFE. The ECS uses the Graphics Device Interface (GDI) to render the chart image. This operation allows only one chart to be generated at any time in an ECS process.

8. The WFE return the image to the browser, which displays it.

Optimizing for Performance

This section analyzes the various architectural aspects of Excel Services from the performance point of view. You expect good performance and full utilization of your hardware. There usually is a trade-off between the response time and throughput, and the other aspects of the server such as security, data staleness, and functionality. Excel Services has a large number of ways to help the administrator and the author reach the balance that is best for them.

There are two major measurements for the performance of the server: response time and throughput. Response time expresses how the end user measures the performance of the server from the moment he or she performs an operation until the results come back and are displayed in the client. Throughput measures the capability of the system to perform a number of parallel requests from multiple users. It is measured in terms of requests per second that are actually performed.

The performance of the server depends on the optimal utilization of its resources, including load balancing, network, I/O, memory, and CPU. The following sections explain the various factors that impact the usage of each of these resources, and provide tips on achieving the right balance between performance and functionality.

Load Balancing

Excel Services supports the following three ways of load balancing the ECS machines when a new session is started:

• Local load balancing — The WFE opens the session on the ECS that exists on the same machine. Use this load balancing when each machine contains both the WFE and the ECS, and the expected request mix is mainly opening diverse workbooks. For this scenario, caching on the ECS is not relevant, so working with a local ECS ensures the most optimal load balancing by the NLB on the WFE, and no network communications is needed between the WFE and the ECS. On the other hand, if sessions are expected to have multiple requests, you should not use this configuration, because the requests after the first one might be served on a WFE that is located on a different machine than the ECS.

• Round-robin — The WFE picks a random ECS machine to open the session. This load balancing is best if there are a few workbooks (so they can all fit in the memory of each ECS) and they are not used uniformly. This method distributes the requests evenly, achieving good utilization of the ECS CPUs, but not the best utilization of memory. If you have a lot of workbooks, you should not use this method, because each workbook will likely be opened in the memory of each ECS machine, will not have enough memory to be cached, and will therefore have an inferior performance.

• Workbook URL — This is the default load balancing. The WFEs will always open any workbook on the same ECS machine. Use this method when there are a lot of workbooks and requests on them. It will achieve a good utilization of memory, but might not achieve the best utilization of CPU. For example, if one workbook is accessed much more than the others, the ECS associated with that workbook will have a much higher CPU usage than the other ECS machines.

As you can see, each method had unique performance characteristics, and no method is optimal for all cases. Choose the one that best fits your scenario.

Network

Excel Services is a system distributed over multiple machines connected through the network in one of the following ways:

• Connection between the client machines and the WFE

• Connection between the WFE, the application server, and the database server

• Connection with other servers (such as external databases, SSO servers, or Active Directory servers)

Connection Between Client Machines and the WFEs

There are many factors that affect the performance of the connection between the client machines and the server. These include the network topology and speed, intranet versus extranet, HTTP versus HTTPS, firewalls, and load balancing. Some of these might be outside your control, but you should be aware of them because they impact the response time that your users will experience.

Loading one page of a workbook might require transmitting several hundred kilobytes over the network. As the designer of the workbook, you can impact this size as follows:

• You can reduce the number of rows and columns displayed. For example, a range of 75 rows by 20 columns will contain 1500 cells.

• Some features require larger amounts of data. Charts are sent as images to the browser, and the image size depends on the complexity and size of the chart. Some formatting features (such as conditional formatting) will send additional data for each relevant cell.

• A dashboard that has multiple EWA Web Parts on it will be as large as the sum of the Web Parts. To reduce the load time of the Web page, you could have a top-level dashboard that contains only the most important information, and use linked dashboards for additional details.

Secure connections (HTTPS) are slower because they must encrypt the communications. After you have complete your security analysis (for example, determining how sensitive the information passed over the network is, who your users are, and how secure the network is), you should balance it against the performance impact of using the HTTPS protocol.

When you’re load balancing multiple WFEs, you can choose between hardware and software load balancers. This might impact the performance as well.

A firewall is normally a requirement when you’re using the server in extranet scenarios, but it will slow down the communications. For extranet scenarios, the speed outside your internal network is much slower. Excel Services does not have a mode in which it sends smaller amounts of information for low network speeds.

Connection Between the Office SharePoint Server Machines

In most topologies, the WFE, application server and database server machines are connected with a high-speed local network. You can ensure that the appropriate network speeds and network cards are used to connect the machines in the farm.

In some topologies, a firewall should be deployed between the WFE machines on one side, and the application server and database server machines on the other side to further protect the access to the workbook content. In addition, the administrator can require secure connections between the WFE and the ECS. As with the connections to the WFE, your specific needs should determine the right balance between security and performance.

Downloading files over the network from their storage location might be a significant performance hit. Excel Services uses a local cache to minimize additional downloads after the initial one. The ECS accesses the file storage location to check that the file has not changed, and that the user has permissions to access the file for every new session, even when the file is in the cache. If the file storage is in an external location (such as a UNC share or web folder), make sure that the storage is fast enough and does not become a bottleneck. For some very specific solutions that require a limited set of workbooks that do not change often, consider deploying the files locally to each ECS machine. The administrator can set the maximum workbook size setting to prevent extremely large files from affecting the overall performance of the server.

Connection with External Machines

Additional machines outside the farm are involved in some of the requests, especially the ones related to querying for external data. For example, to refresh a pivot table, the following servers might be accessed: the database that contains the data, the SSO server for getting credentials, the SharePoint database server for loading the connection information from a data connection library, and the active directory for certain credential types. Optimizing external data queries is a complex topic, and it is explained in detail in Chapter 5.

Memory

The WFE is a stateless machine, meaning that only the requests currently being executed require memory. The amount of memory that each request consumes depends on the size of the range being rendered. In most scenarios, memory is not a bottleneck on the WFE.

On the other hand, the ECS is stateful and keeps in memory the state of all the opened sessions. You can use the following formula to approximate the amount of data stored in ECS memory:

Total memory = (number of concurrent sessions * session size) + unused items cache

The amount of memory required depends on how much is used by the sessions, plus the size of the unused items cache kept in memory. Allowing for a larger ECS memory size will improve the ECS performance. The ECS memory size is limited by the available memory on the machine and by other processes that might need to use that memory. Each of the components that make up the ECS memory is discussed separately in the following sections.

Number of Concurrent Sessions

The number of concurrent sessions depends on the session length and the number of sessions opened per second.

Here are some tips on reducing the session length:

• Set the session timeout and short session timeout to values that are appropriate for your organization. The shorter you set these values, the fewer sessions will remain open, therefore reducing the memory footprint. For example, if you expect your users to only look at a dashboard without performing any interactivity, you could set the timeout values to 0. The downside of setting them to small values is that users might lose their work after a certain period of inactivity. Remember that after a session times out, the EWA continues to display the workbook, and users will get an error only if they try to perform an operation at a later time. You can configure the session timeout settings differently for each trusted location (see Chapter 7).

• When you’re designing a web page that uses the EWA, consider configuring the EWA with the Close Session Before Opening A New One option enabled. When this option activated, users will not be able to navigate to the previous workbook by using the browser’s Back button.

• When you use the API, explicitly call CloseWorkbook as soon as you don’t need the session anymore. CloseWorkbook is an API method that closes the session.

Here are some ideas to reduce the number of sessions opened:

• As an administrator, you can control the maximum number of sessions that a user is allowed to open.

• As a web page designer, you can control the number of EWAs on a page. Remember that each EWA instance uses a separate session, even if they all display the same workbook.

• In the API, cache the session ID and use the same session for multiple method calls.

Session Size

As you learned earlier in this chapter, a session might share its state with other sessions, as long as they contain the same workbook information. When the session has a shared state, its size is very small. The size of a session that is not shared depends on the size of the workbook. To reduce the size of the session, use smaller workbooks. In addition, you can do the following to share as much state as possible between sessions:

• Use the same credentials for all users when accessing external data. This is appropriate for most scenarios, except when the query returns personal information that depends on the user opening the workbook.

• Use the External Data Cache Lifetime settings to determine how current you need your data to be. For example, if you know that your back-end data source is updated every 24 hours, there is no point in refreshing your query every 5 minutes. (For more details on optimizing external data, see Chapter 5.)

• Minimize the use of workbooks with volatile formulas and UDFs. Volatile functions return a different value on every calculation. You can use the Volatile Function Cache Lifetime administrator setting to determine how current your volatile formulas will be. The less current they are, the better the performance will be (at the expense of having them recalculate every time).

• Using UDFs that return personal information prohibits workbooks from being shared. Call these UDFs only where this is absolutely necessary.

• Performing operations (such as setting the value of parameters, drilling, filtering, and sorting) results in the workbook not being shared between sessions. As the designer of a web page, you can configure the EWA to not allow these operations.

Cache Size of Unused Items

You learned about the unused items cached earlier in this chapter. You can configure the size of the cache and how long items are kept there with the Maximum Private Bytes, Memory Cache Threshold, and Maximum Unused Object Age settings.

Increasing the number of items that can be cached so that future requests will reuse these cached items instead of recalculating them enhances the performance of the server. The downside of having more items cached is that it requires more memory to be available to the ECS process.

Available Memory

As with almost any process, the performance of the ECS is improved significantly if there is enough physical memory. The recommended configuration is a 64-bit machine, which allows for scaling up in terms of memory.

The ECS is designed to use memory for improved performance, and you should take that into consideration when tuning up your system. Depending on your specific needs, try to create a balance between reducing the memory consumption (following the tips presented earlier) and increasing the available memory.

As part of this equation, you should also take into consideration what other processes are running on the ECS machine and their memory requirements. When there are several SSPs running, keep in mind that each SSP has its own ECS process with its memory considerations and separate settings.

CPU

Operations such as loading files and querying external data consume I/O and network resources, and storing the session state requires memory. Operations such as calculating the workbook use the CPU on the ECS. The EWA and API on the WFE are not very CPU-intensive.

For many workbooks, the calculation is relatively fast (less than 1 second). Other calculations might require seconds, minutes, and — in some extreme scenarios — even hours. The following sections discuss ways of achieving your performance goals with both low-end and high-end workbooks.

Low-End Workbooks

The low-end workbooks are the majority. They might have tens or hundreds of formulas, and they calculate very fast when run by themselves on a machine. But you should still optimize these workbooks, because the server runs many of them in parallel. Here are some tips:

• Try not to use volatile formulas and UDFs in your workbooks (these are functions that return a different value on every calculation). The Volatile Function Cache Lifetime administrator setting determines how current your volatile formula will be. If you allow formulas to be less current, your performance will improve because the ECS will use cached results of previous calculations of these formulas.

• Remember that UDFs may consume CPU. Optimize your UDFs and the way they are called from the workbook (for example, you might use some caching in the UDF). Test the UDFs and install only those that are efficient.

• You can set the Workbook Calculation Mode setting to Manual to prevent some trusted locations from performing automatic calculations. This gives you control over which workbook authors can use the CPU, and enables you to focus your efforts on optimizing the workbooks that are allowed to perform calculations.

• Make sure your workbook authors optimize the workbooks they create. Start with the workbooks that are used the most, because improving them will have the largest impact on your server’s performance.

• If the CPU is underutilized (the CPU is not close to 100 percent utilization, and yet the ECS has reached its maximum throughput), try increasing the number of available threads.

High-End Workbooks

You can use Excel Services to offload heavy calculations to the server. Relatively few workbooks with heavy calculations are calculated once or iteratively, usually via the API. A classic example is a Monte Carlo simulation (which tests the effects of various inputs over a model). Here are a few recommendations for best performance:

• Run high-end workbooks on dedicated hardware. You can do this by using a separate farm or by running it during the night when the normal end-user activity is at a minimum. Having dedicated hardware allows you to configure your server settings to optimize for this scenario. In addition, it provides predictable performance, because no other noise is running on the server.

• If your workbooks perform little to no external data queries, consider reducing the number of threads on the ECS to between 1 and 4 per CPU. This configuration will result in less context switching and better use of the CPU and memory.

• You might need to increase the Maximum Request Duration setting and the various IIS timeouts to ensure that requests are not aborted in the middle of their execution.

• Use a high performance computing (HPC) solution, such as Microsoft Windows Compute Cluster Server (CCS) to manage and balance your application.

• Excel Services does not support multi-threaded recalculation (MTR), which is a new Excel 2007 feature. MTR allows a complex workbook calculation to be split into multiple threads for parallel execution. On Excel Services, the calculation is executed serially on one thread. In some cases, you can achieve similar results by splitting your workbook into several smaller workbooks, and using your client application to call the individual workbooks and combine the results. You can also offload some of the calculation to UDFs that can be designed to run on multiple threads or even multiple machines.

I/O

The ECS uses the local disk to load workbooks from the disk cache. Loading workbooks is usually considered an expensive operation. One way to minimize the amount of workbooks loaded is to have enough memory so that the workbook is kept in the memory cache after its first load.

In addition, you can use the Maximum Workbook Size administrator setting to set a limit on the file size. This setting can have a different value for each trusted location, thus allowing the administrator to limit the amount of I/O some workbook authors are allowed to consume on the server. You can also ask the authors of the workbooks to design for smaller file sizes.

The ECS uses the local disk to cache chart images, calculated ranges, and saved workbooks. For example, when a chart image is generated on the ECS, it is saved in the cache. The next request for the same chart will return it from the disk cache rather than recalculating it. The assumption is that this operation is more effective than regenerating the chart.

The local disk is also used for logging, and the administrator can configure how verbose the logging is (see Chapter 7 for details). Logging can be very useful to investigate issues, but at high verbosity levels, it will cause a lot of I/O and might have a significant performance impact. Be sure to reduce the verbosity level according to your needs.

Given all the ways the disk is used, you should ensure that your disk is efficient. For optimal performance, you should use a local disk (as opposed to a remote share) and ensure that the disk is not fragmented.

Charting

Charting is expensive in terms of performance. It affects the network, CPU, and I/O in the following ways:

• Chart images could be large. They are sent over the network from the ECS to the WFE, and from there to the browser.

• Chart image generation can be CPU-intensive. More importantly, it uses GDI, which holds a global process-level lock and allows only one chart to be generated at any time by an ECS process.

• To reduce the number of charts that are generated, charts are cached on the local disk for future use. This increases the I/O consumption.

To reduce the resources consumed by charting, eliminate any unnecessary use of charts. In addition, you can use the Maximum Chart Size administrator setting at the trusted location level to limit how large the charts can be. The default value is 1MB.

Client Machine and Browser Speed

In addition to the client connection speed, the perceived responsiveness depends in part on the client machine and browser speed.

Workbooks can generate complex HTML and some JavaScript, which require client CPU cycles to render. Take into account that if a web page has several workbooks, they will all get rendered on the client machine, thus significantly reducing the perceived performance.

The client browser will cache some of the icons and JavaScripts, making the first browsing of a web page with EWA on it slower than the subsequent pages.

Although the client hardware is usually outside the server administrator’s control, you should take it into account when estimating and analyzing the response time that your users are experiencing.

Summary

In this chapter, you learned about the architecture of Excel Services, its main components, and operational concepts, as well as how to configure it for best performance. Specifically, you learned the following:

• Excel Services is part of Office SharePoint Server, which has one or more web front ends (WFEs), application servers, and database servers. Shared Services Providers (SSPs) allow the hosting and managing of parallel logical application servers in a farm.

• The main components of Excel Services are the Excel Calculation Server (ECS), Excel Web Access (EWA) Web Part, and Excel Web Services API. The ECS is deployed on the application server and is the engine for loading and calculating workbooks. The EWA and the API are deployed on the WFE and provide the end-user interface and the programmatic interface, respectively.

• User activity is performed in the context of a session, which provides isolation. The session state is kept in the ECS memory and is not disclosed to other sessions.

• The ECS has caches of recently used objects in memory and on disk for improved performance. You can control the amount of memory used by the ECS and these caches.

• To achieve good performance and make the best use of your hardware, you should be aware of the various settings that Excel Services has. These settings allow you to tune the performance according to your needs. In addition, you can improve performance by controlling the way workbooks are authored and the web pages containing the EWA.

Chapter 4 discusses the Excel features that are not supported on Excel Services. Some workbooks are not supported on the server, and others are supported in a different way on the server and the client. You will learn about these features, and how to adapt your workbooks for the best experience on the server.