Estimate performance and capacity requirements for Microsoft Business Connectivity Services in SharePoint Server 2010

 

Applies to: SharePoint Server 2010

**Summary:**This article provides performance and planning guidance and discusses the impact of using Business Connectivity Services in Microsoft SharePoint Server 2010.

For more information about Business Connectivity Services, see Business Connectivity Services overview (SharePoint Server 2010).

In this topic:

• Glossary

• Test farm characteristics

• Test results

• Recommendations

Glossary

The following list defines the Business Connectivity Services terms used in this document.

Term	Definition
Association	An association links related external content types. For example, customers can be associated with their sales orders. Associations are used together with Web Parts.
External Item	An instance of an external content type.
External List	A list of items of an external content type.
External System	A supported source of data that can be modeled by Business Connectivity Services, such as a database, Web service, or custom .NET Framework assembly.
Profile Page	A profile page displays the data for an item of an external content type.
Secure Store Service	A shared service that securely stores credential sets for external data sources and associates those credential sets to identities of people or to group identities.
Web Part	A reusable component of a SharePoint site that presents information pulled from multiple data sources.

Test farm characteristics

This section defines the test scenarios and describes the test process that was used for each scenario. Detailed information such as test results and specific parameters are given in the test results sections later in this article.

Test name	Test description
External list	Render a typical external list. Change the features of the external list (number of items, item size, and so on) to see how this affects throughput and latency.
Profile page	Render a typical profile page. Change the features of the profile page (number of items per association, item size, and so on) to see how this affects throughput and latency.

Dataset

External list and profile page capacity and performance are highly dependent on the volume of data that is processed. For external lists, how much data is processed is determined by three variables: the number of items in the external list, the number of columns per item, and the size of each item. The following table describes the representative external lists were used in testing.

External List	Small	Medium	Large
Number of items	500	2000	4000
Number of columns per item	25	25	25
Item size	2 KB	4 KB	8 KB

For profile pages, how much data that is processed depends on the number and complexity of associations that are used. An association links related external content types in a system. A profile page can contain multiple associations, and each association can have many items. The following table describes the representative profile pages used in testing.

Profile Page	Small	Medium	Large
Number of associations	2	2	10
Number of items per association	100	500	2500
Item size	4 KB	4 KB	4 KB

Workload

Tests measured throughput and latency effect on profile pages and external lists. The tests were designed to help develop estimates of how throughput and latency respond to changes to the following variables:

• Number of front-end Web servers.

• Number of items in the external list.

• Size of the external item.

• Number of items per association.

• Authentication method (PassThrough mode or Secure Store Service authentication).

• External data source (Windows Communication Foundation (WCF) Web service or SQL Server database).

• Load on the front-end Web server measured as CPU usage.

The specific capacity and performance figures that this article lists 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 complete an initial system design, test the configuration to determine whether the system will support the factors in the environment.

Green and red zone definitions

For each configuration we ran two tests to determine a green zone, or recommended throughput that can be sustained, and a red zone, or max throughput that can be tolerated for a short time but should be avoided.

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

• For the green zone, all the front-end Web servers in the farm have a CPU utilization of 40-50 percent consistently. This is achieved by increasing the user load that is used and establishing think times in the tests, which means each test may have a different user load.

• For the red zone, all of the front-end Web servers in the farm have a CPU utilization of 90-99 percent consistently. This is achieved by increasing the user load that is used in the tests, which means each test may have a different user load.

Hardware, settings and topology

This section describes the hardware, settings, and topologies that were used in the test.

Lab hardware

To provide a high level of test-result detail, several farm configurations were used for testing. Farm configurations ranged from one to four Web servers and a single database server computer that is running Microsoft SQL Server 2008 database software.

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

	Front-End Web server	Application server	Database server	External system
Processors	2 processors @2.33 GHz (4 cores)	2 processors @2.33 GHz (4 cores)	4 processors @3.2 GHz (4 cores)	4 processors @3.2 GHz (4 cores)
RAM	8 GB	8 GB	32 GB	32 GB
Operating System	Windows Server 2008 R1 (x64)	Windows Server 2008 R1 (x64)	Windows Server 2008 R1 (x64)	Windows Server 2008 R1 (x64)
Number of network adapters	2	2	2	2
Network adapter speed	1 GB	1 GB	1 GB	1 GB
Authentication	NTLM	NTLM	NTLM	NTLM
Software version	SharePoint Server 2010 pre-release version	SharePoint Server 2010 pre-release version	SQL Server 2008	SQL Server 2008

Tests used two external systems: a WCF Web service and a database. The external systems were hosted on a separate physical computer (details described in the table that lists the specific hardware). The following list describes the two external systems:

• WCF Web service   A WCF Web service that returns cached, in-memory data. Data is stored efficiently in a hash table and returned immediately upon being called by Business Connectivity Services. The data consists of 8000 rows of 25 fields each of various .NET types.

• Database   A table that has 25 columns, various data types, and 8000 rows. The table is in a separate database that is hosted on SQL Server 2008.

Topology

CPU and memory on the front-end Web server is an important limiting factor for throughput. CPU on the Application server should also be considered for Business Connectivity Services authentication modes that require calls to the Secure Store Service.

The topology was varied by adding additional front-end Web servers.

Business Connectivity Services Capacity Planning Topology

Test results

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

The test graphs use the following legend to describe the dataset:

External system, Test type,[Authentication],[Number of associations], Item size, Number of items, CPU load

Item	Description
External system	The external data source: WCF (WCF Web service) or DB (database).
Test type	The test type: EL (external list) or PP (profile page).
Authentication	The authentication method: SSS (a Secure Store Service authentication mode (for example, WindowsCredentials) is used). If SSS is not specified, PassThrough mode was used during testing.
Number of associations	The number of associations in the profile page (for example, 2A). This item applies only to profile page tests.
Item size	The size of the item (in KB).
Number of items	Number of items in the external list or number of items per association.
CPU load	The CPU load: RZ (red zone) front-end Web server CPU utilization is more than 90 percent or GZ (green zone) front-end Web server CPU utilization is between 40-50 percent.

All of the red zone tests reported on in this article were conducted without think time, a natural delay between consecutive operations. In a real-world environment, each operation is followed by a delay as the user performs the next step in the task. By contrast, in the red zone tests, each operation was immediately followed by the next operation, which resulted in a continual load on the farm. This load introduced database contention and other factors that can adversely affect performance.

Effect of the number of front-end Web servers on latency

The following graphs show the test result of the number of front-end Web servers on latency. The test results are distributed over several graphs to make it easier to compare related variables.

Chart 1:

The following graph shows the results of the external list tests. It can be used to compare how the external system (database or WCF Web service) and CPU load (Red Zone or Green Zone) affect performance.

Chart 1: Effect of the number of front-end Web servers on latency

The following list describes the dataset that was used.

• WCF,EL,4k,500,RZ: An external list that has 500 items, 4 K of data per item, the external system is a WCF Web service, and the front-end Web server CPU usage is more than 90 percent.

• WCF,EL,4k, 500,GZ: An external list that has 500 items, 4 K of data per item, the external system is a WCF Web service, and the front-end Web server CPU usage is between 40-50 percent.

• DB, EL, 4k,500, GZ: An external list that has 500 items, 4 K of data per item, the external system is a database, and the front-end Web server CPU usage is between 40-50 percent.

Chart 2:

The following graph shows the results of the profile page tests. It can be used to compare how the external system (database or WCF Web service) and CPU load (red zone or green zone) affect performance.

Chart 2: Effect of the number of front-end Web servers on latency

As is shown in Chart 1 and Chart 2, the average page time stays almost the same for both green zone and red zone scenarios despite adding more front-end Web servers and users. (During testing we increased the user load in order to keep all the front-end Web servers in the necessary CPU activity range). However, this is still a performance gain because increasing the number of front-end Web servers in the farm enables SharePoint Server to serve more users at the same rate. There is a noticeable, approximately five-fold, improvement in green zone cases compared to red zone cases. So, we recommend that you keep front-end Web server CPU utilization in the 40-50 percent range.

The following list describes the dataset that was used:

• WCF,PP,2A, 100,RZ: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• DB, PP,2A,100,RZ: A profile page that has 2 associations, 100 items per association, the external system is a database, and the front-end Web server CPU utilization is more than 90 percent.

• WCF, PP,2A,100,GZ: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

• DB, PP, 2A,100,GZ: A profile page that has 2 associations, 100 items per association, the external system is a database, and the front-end Web server CPU utilization is between 40-50 percent.

Chart 3:

The following graph shows the results of the red zone tests. Similar data is paired together so that the only variable is the authentication that is used. This data can be used to determine whether using Secure Store Service effects performance.

Chart 3: Effect of the number of front-end Web servers on latency

In red zone scenarios, Secure Store Service does not seem to bring noticeable overhead as measured by average page time. This can be attributed to the fact that Secure Store Service overhead is overshadowed by a bigger factor, which is the high user load. In most cases, the Secure Store Service and non-Secure Store Service results are similar.

You should also remember that Secure Store Service will have minimal effect on the Application Servers.

The following list describes the dataset that was used:

• WCF,EL,4k,500,RZ: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,SSS,4k,500,RZ: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,PP,2A,100,RZ: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,PP,SSS,2A,100,RZ: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB, EL, SSS,4k,500,RZ: An external list that has 500 items, 4 KB of data per item, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB, PP,2A,100,RZ: A profile page by using 2 associations, 100 items per association, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

Chart 4:

The following graph shows the results of the green zone tests. Similar data is paired so that the only variable is the authentication that is used. The similar dataset can be used to determine whether using Secure Store Service effects performance.

Chart 4: Effect of the number of front-end Web servers on latency

The overhead associated with Secure Store Service becomes more apparent for profile pages during typical load scenarios. There is a small, about 20 milliseconds, additional average page time for profile pages.

The following list describes the dataset that was used:

• WCF, EL,4k,500,GZ: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF, EL,SSS,4k,500,GZ: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU usage is between 40-50 percent.

• WCF, PP,2A,100,GZ: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF, PP,SSS,2A,100,GZ: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB, EL,4k,500,GZ: An external list that has 500 items, 4 KB of data per item, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB, EL,SSS,4k,500,GZ: An external list that has 500 items, 4 KB of data per item, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB, PP,2A,100,GZ: A profile page that has 2 associations, 100 items per association, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB, PP,SSS,2A,100,GZ: A profile page that has 2 associations, 100 items per association, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of the number of front-end Web servers on throughput

Chart 1:

The following graph shows the results of the external list tests as measured in request per second (RPS). It can be used to compare how the CPU load (red zone or green zone) affect performance.

Chart 1: Effect of the number of front-end Web servers on throughput

The following list describes the dataset that was used:

• WCF, EL,4k,500,RZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• WCF, EL,4k,500,GZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

Chart 2:

The following graph shows the results of the profile page tests as measured in request per second (RPS). It can be used to compare how the external system (database or WCF Web service) and CPU load (red zone or green zone) affect performance.

Chart 2: Effect of the number of front-end Web servers on throughput

As is shown in Chart 1 and Chart 2, RPS scales linearly until the third front-end Web server is added. After which, the trend becomes logarithmic, or sub-linear. Although there is added benefit in adding more front-end Web servers to the farm, it provides less value for the cost. In particular, adding a fourth front-end Web server yields a fairly small benefit.

The following list describes the dataset that was used:

• WCF,PP,2A,100,RZ,RPS: A profile page that has two associations, 100 items per association, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• DB,PP,2A,100,RZ,RPS: A profile page that has two associations, 100 items per association, the external system is a database, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,PP,2A,100,GZ,RPS: A profile page that has two associations, 100 items per association, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,PP,2A,100,GZ,RPS: A profile page that has two associations, 100 items per association, the external system is a database, and the front-end Web server CPU utilization is between 40-50 percent.

Chart 3:

The following graph shows the results of the red zone tests as measured in requests per second (RPS). Similar data is paired so that the only variable is the authentication that is used. These results can be used to determine whether using Secure Store Service affects performance.

Chart 3: Effect of the number of front-end Web servers on throughput

The following list describes the dataset that was used:

• WCF,EL,4k,500,RZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,SSS,4k,500,RZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,PP,2A,100,RZ,RPS: A profile page that has 2 association, 100 items per association, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,PP,SSS,2A,100,RZ,RPS: A profile page that has 2 association, 100 items per association, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,EL,4k,500,RZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,EL,SSS,4k,500,RZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,PP,2A,100,RZ,RPS: A profile page that has 2 associations, 100 items per association, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,PP,SSS,2A,100,RZ,RPS: A profile page that has 2 associations, 100 items per association, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

Chart 4:

The following graph shows the results of the green zone tests as measured in requests per second (RPS). Similar data is paired together so that the only variable is the authentication that is used. It can be used to determine whether using Secure Store Service affects performance.

Chart 4: Effect of the number of front-end Web servers on throughput

As is shown in Chart 3 and Chart 4, Secure Store Service overhead does result in lower RPS values in some cases. However, the linear-logarithmic trend is similar and in most cases the RPS difference between Secure Store Service and non-Secure Store Service overhead is subtle.

The following list describes the dataset that was used:

• WCF,EL,4k,500,GZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,EL,SSS,4k,500,GZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,PP,2A,100,GZ,RPS: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,PP,SSS,2A,100,GZ,RPS: A profile page that has 2 associations, 100 items per association, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,EL,4k,500,GZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB, EL,SSS,4k,500,GZ,RPS: An external list that has 500 items, 4 KB of data per item, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,PP,2A,100,GZ,RPS: A profile page that has 2 associations, 100 items per association, the external system is a database, PassThrough authentication mode is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,PP,SSS,2A,GZ,RPS: A profile page that has 2 associations, 100 items per association, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of item size on latency

The following graph shows the test result of external item size on latency. The tests increased the size of the items in the external list and measured the effect on latency.

Effect of item size on latency

The following list describes the dataset that was used:

• WCF,EL,500,RZ: An external list that has 500 items, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,SSS,500,RZ: An external list that has 500 items, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,500,GZ: An external list that has 500 items, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,EL,SSS,500,GZ: An external list that has 500 items, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of item size on throughput

The following graph shows the test result of external item size on throughput as measured in requests per second (RPS). The tests increased the size of the items in the external list and measured the effect on throughput.

Effect of item size on throughput

RPS performance always drops below linear as the item size increases. High load conditions provide more RPS. However, as shown in earlier test results, high load conditions also cause longer page response times.

The following list describes the dataset that was used:

• WCF,EL,500,RZ: An external list that has 500 items, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,SSS,500,RZ: An external list that has 500 items, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,500,GZ: An external list that has 500 items, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,EL,SSS,500,GZ: An external list that has 500 items, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of number of items on latency

The following graph shows the test result of the number of items on latency. The tests increased the number of items in the external list and measured how it affected how long it took to render the page.

Effect of number of items on latency

The following list describes the dataset that was used:

• WCF,EL,4k,RZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,SSS,4k,RZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,EL,4k,RZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, and the front-end Web server CPU utilization is more than 90 percent.

• DB,EL,SSS,4k,RZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,4k,GZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,EL,SSS,4k,GZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,EL,4k,GZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,EL,SSS,4k,RZ: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of number of items on throughput

The following graph shows the test result of the number of items on throughput as measured in requests per second (RPS). The tests increased the number of items in the external list and measured the effect on throughput.

Effect of number of items on throughput

As this graph shows, RPS drops almost linearly as the number of items increases. Compared to earlier tests that studied the effect of item size, increasing the number of items seems to have a bigger effect on performance than increasing the item size.

The following list describes the dataset:

• WCF,EL,4k,RZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL, SSS,4k,RZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is being used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,EL,4k,RZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, and the front-end Web server CPU utilization is more than 90 percent.

• DB,EL,SSS,4k,RZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is being used, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,EL,4k,GZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF Web service, and the front-end Web server CPU utilization is between 40-50 percent.

• WCF,EL, SSS,4k,GZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is being used, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,EL,4k,GZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,EL,SSS,4k,GZ,RPS: An external list that has a variable number of items, each item has 4 KB of data, the external system is a database, a Secure Store Service authentication mode (for example, WindowsCredentials) is being used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of number of items per association on latency

The following graph shows the test result of the number of items in an association on latency. The tests increased the number of items in an association and measured how it affected how long it took to render the page.

Effect of number of items per association on latency

The following list describes the dataset:

• WCF,PP,2A,RZ: A profile page that has 2 associations, the external system is a WCF Web service, and the front-end Web server CPU utilization is more than 90 percent.

• WCF,PP, SSS,2A,RZ: A profile page that has 2 associations, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is being used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,PP,2A,GZ: A profile page that has 2 associations, the external system is a database, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,PP,SSS,2A,GZ: A profile page that has 2 associations, the external system is a database, Secure a Store Service authentication mode (for example, WindowsCredentials) is being used, and the front-end Web server CPU utilization is between 40-50 percent.

Effect of number of items per association on throughput

The following graph shows the test result of the number of items in an association on throughput, as measure in requests per second (RPS). The tests increased the number of items in an association and measured the effect on throughput.

Effect of number of items per association on throughput

The following list describes the dataset:

• WCF,PP,2A,RZ: A profile page that has 2 associations, the external system is a WCF Web service, and the front-end Web server CPU utilization is larger r than 90 percent.

• WCF,PP,SSS,2A,RZ: A profile page that has 2 associations, the external system is a WCF Web service, a Secure Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is more than 90 percent.

• DB,PP,2A,GZ: A profile page that has 2 associations, the external system is a database, and the front-end Web server CPU utilization is between 40-50 percent.

• DB,PP,SSS,2A,GZ: A profile page that has 2 associations, the external system is a database, Secure a Store Service authentication mode (for example, WindowsCredentials) is used, and the front-end Web server CPU utilization is between 40-50 percent.

Recommendations

This section provides general performance and capacity recommendations. Use these recommendations to determine the capacity and performance characteristics of the starting topology that you created and to decide whether you have to scale out or scale up the starting topology.

Hardware recommendations

For specific information about minimum and recommended system requirements, see Hardware and software requirements (SharePoint Server 2010) ().

Business Connectivity Services performance-related recommendations

This section provides performance recommendations for using external lists and profile pages, and general performance recommendations for Business Connectivity Services.

External list recommendations

The following table describes how data moves from the external system into an external list.

Load	Process	Render
Microsoft Business Connectivity Services queries the external system and loads the returned data into SharePoint Server.	Applies any additional processing (sort, filter, group) on the loaded data.	The external list renders the items on the page.

Microsoft Business Connectivity Services does not have an in-memory cache for external items. The data has to be loaded, processed, and rendered every time that an external list is refreshed. Therefore, many of these recommendations try to limit how much data that has to be processed.

The following list describes the external list recommendations:

• Keep the number of items that have to be processed as low as possible by limiting the number of rows returned from the external system. This is the key factor in external list performance. We recommend you keep the number of rows returned to 100-500. The number of rows returned from the external system should not exceed 2000. You can use filters to limit the number of items that are returned by the external system. For more information about filters, see How to: Create an External Content Type Based on a SQL Server Table (https://go.microsoft.com/fwlink/p/?LinkId=192184).

• Rendering a list is CPU-intensive on the front-end Web server and the Application server. The number of items rendered differs from the total number of items that are loaded and processed. The number of items that are rendered depends on the configuration of the external list view. Consider the overall user experience, how many items can be comfortably viewed on a screen, and keep the number of items rendered per page to a reasonable number. We recommend you keep the number of items to around 30 per page (this is the default.)

• Keep the number of columns in an external list to a reasonable number. A large number of columns can affect performance and can also make for a poor user experience (too many columns to comfortably view on a screen).

• Do not include large-sized columns (especially strings) in list views. Columns larger than 1 KB should not be included in a list view. The external content type can still include the large-sized column. However, it should be shown only in the single-item view.

• When designing an external list, configure the default view to be the view that most users will want to see. Changing the sort or filter on a view requires the data to be loaded, processed, and rendered.

Profile page recommendations

• The number of associations is a key factor in profile page performance. We recommend that you keep the number of associations to 2 at the most for the best performance.

• Expect lower performance numbers, for both throughput and latency, with a larger number of items per associations.

General Business Connectivity Services recommendations

• There was a noticeable, approximately five-fold, improvement in performance in the effect of the number of front-end Web servers on latency and compared green zone cases to red zone cases. We recommend that you keep the front-end Web server CPU utilization in the 40-50 percent range.

• The number of items seems to have more an effect on performance than the item size. If you control your external data source, keep the item size high and number of items low for better results. For example, consider aggregating the large data into a single item instead of spreading the data into many items.

• Diagnostic logging levels for Microsoft Business Connectivity Services can also be an important factor in latency and throughput as perceived users. Keep the logging levels at the minimum levels that are allowed for by your business needs for regular usage. Turn the logging levels to a higher verbosity temporarily only when closer monitoring is needed.

• The performance of the external system plays a large role in Business Connectivity Services performance. Consider the latency and throughput of the external system when you plan for your capacity and performance.

Scaled-up and scaled-out topologies

You can estimate the performance of your starting-point topology by comparing your topology to the starting-point topologies that are provided in Plan for availability (SharePoint Server 2010) (https://go.microsoft.com/fwlink/p/?LinkId=189518). Doing so can help you quickly determine whether you must scale up or scale out your starting-point topology to meet your performance and capacity goals.

To increase the capacity and performance of one of the starting-point topologies, you can either scale up by increasing the capacity of your existing server computers, 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.

• To provide for more user load, add Web server computers.

• To provide for more data load, add capacity to the database server role by increasing the capacity of a single (clustered or mirrored) server, by upgrading to a 64-bit server, or by adding clustered or mirrored servers.

• Maintain a ratio of no more than eight Web server computers to one (clustered or mirrored) database server computer. Although testing yielded a specific optimal ratio of Web servers to database servers for each test scenario, deployment of more robust hardware, especially for the database server, may yield better results in the environment.

Estimating throughput targets

Many factors can affect throughput. These factors include the following:

• Number of users

• Type, complexity, and frequency of user operations

• Number of postbacks in an operation

• Performance of data connections.

Each of these factors can have a major effect on farm throughput. Carefully consider each of these factors when you plan your deployment.

SharePoint Server 2010can be deployed and configured in many ways. As a result, there is no simple way to estimate how many users can be supported by a given number of servers. Make sure that you conduct testing in your own environment before you deploy SharePoint Server 2010 in a production environment.

Optimizations

Common bottlenecks and their causes

During performance testing, several different common bottlenecks were revealed. A bottleneck is a condition in which the capacity of a particular constituent 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
Database contention (locks)	Database locks prevent multiple users from making conflicting modifications to a set of data. When a set of data is locked by a user or process, no other user or process can change that same set of data until the first user or process finishes modifying the data and relinquishes the lock.	To help reduce the incidence of database locks, you can do the following: Distribute submitted forms to more document libraries. Scale up the database server. Tune the database server hard disk for read/write. Methods exist to circumvent the database locking system in SQL Server 2005, such as the NOLOCK parameter. However, we do not recommend or support use of this method due to the possibility of data corruption.
Database server disk I/O	When the number of I/O requests to a hard disk exceeds the disk’s I/O capacity, the requests will be queued. As a result, the time to complete each request increases.	Distributing data files across multiple physical drives makes parallel I/O possible. The blog SharePoint Disk Allocation and Disk I/O (https://go.microsoft.com/fwlink/p/?LinkId=129557) contains much useful information about resolving disk I/O issues.
Web server CPU utilization	When a Web server is overloaded with user requests, average CPU utilization 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 higher-speed processors. See Plan for availability (SharePoint Server 2010) (https://go.microsoft.com/fwlink/p/?LinkId=189518) for more information.

Performance monitoring

To help you determine when you have to scale up or scale out your system, use performance counters to monitor the health of your 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.

Web servers

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

Performance counter	Apply to object	Notes
Processor time	Total	Shows the percentage of elapsed time that this thread used the processor to execute instructions.
Memory utilization	Application pool	Shows the average utilization of system memory for the application pool. You must determine the correct application pool to monitor. The basic guideline is to determine peak memory utilization for a given Web application, and assign that number plus 10 to the associated application pool.

Database servers

The following table shows performance counters and processes to monitor for database servers in your farm.

Performance counter	Apply to object	Notes
Average disk queue length	Hard disk that contains SharedServices.mdf	Average values larger than 1.5 per spindle indicate that the write times for that hard disk are insufficient.
Processor time	SQL Server process	Average values larger than 80 percent indicate that processor capacity on the database server is insufficient.
Processor time	Total	Shows the percentage of elapsed time that this thread used the processor to execute instructions.
Memory usage	Total	Shows the average utilization of system memory.

See Also

Other Resources

Resource Center: Business Connectivity Services in SharePoint Server 2010