BPC 10.1 Best Practice Performance Tuning

Purpose

This wiki content provides best practice performance tuning configurations for SAP Business Planning and Consolidations 10.0 and 10.1  If performance problems still persist after implementing these configurations please engage SAP services team to do a more detailed analysis of the environment. See the BPC 10 Sizing guide for a detailed understanding on how to size your system.

Overview

The operating system configurations along with the other 3^rd party prerequisite configurations are vital to the overall environmental performance of BPC.

Database Server

SQL Server 2008 R2, 2012, 2014

• BPC does NOT support SQL standard edition only Enterprise edition. See SAP KBA  1644014 - Does BPC support SQL Standard Edition
• Set the SQL Server Maximum memory limit to 50% of the total memory on the database server
• Split the Data and Log Files onto separate physical drives for the user databases (ex: ApShell)
• Split the TempDB data and log files onto separate physical drives (see the following note for further information 1387343 - BPC: SQL Database Tasks )
• Parallelization setting Max degree of Parallelism set to 0. 
• The TEMPDB on the database should have 1 data file per CPU. Please follow note 1387343 for further information.

Microsoft SQL Server Analysis Services (SSAS)

•  Disable the Flight Recorder unless necessary for troubleshooting
•  Change the External Connection Timeout (ExternalConnectionTimeout) to 0
•  Change the External Command Timeout (ExternalCommandTimeout) to 0
•  Leave the Low Memory Limit as 65
•  Leave the Total Memory Limit as 80
• Change the Server Timeout setting from 3600 to 7200
• Check ThreadPool\Query\MaxThreads (Formula: 10 or 2 x # of processor cores)
• Check ThreadPool\Process\MaxThreads (Formula: 64 or 10 x # of processor cores)
• Preallocate parameter change set to 30 see below note (NOTE: The preallocate parameter can be found in the msmdsrv.ini file located in the Config folder for Analysis Services and best practice is to make a backup of the ini file before modifying it. Path: X:\Program Files\Microsoft SQL Server\MSAS10.InstanceName\OLAP\Config)

NOTE FOR SINGLE SERVER CONFIGURATION:

The SSAS service memory setting for a single server installation should be set to a maximum of 40 and low as 25, leaving 50% of memory to SQL and 10% to the operating system.

Application Server

IIS 7.0 or 7.5

Change the Response Buffering Limit and Maximum Requesting Entity Body Limit to 10485760 (Found under Default Web Site > ASP)

(BPC 10 does not require ASP so you can disregard this setting)

COM+ Components

For load balancing purposes, set the application pooling pool size (within the Pooling & Recycling tab) to 5 for the following COM+ components in BPC 7.5 only (Note if you upgrade the BPC SP these might change back to default and you will need to reset them).

• Everest Update
• K2Processing
• OsoftDatabaseUSER
• OsoftDataService
• OsoftSystemConfig

In BPC 10, set the application pooling pool size (within the Pooling & Recycling tab) to 5 for the BPCDM component.

Additional Performance Bottlenecks

• Anti-virus scanning: Make sure that BPC folders on the app server are excluded from scanning and that the data, log and OLAP directories on the DB are excluded.
• NIC card configuration: Network interface card configuration is one of the single greatest factors affecting application performance. NICs should be configured for at least 1000 Mbps (1 GB).
• Remove all unnecessary properties within each of your application's dimensions that are designated (checked off) as "InApp". Please see Note 1851220 for further information on usage of the InApp dimension property setting.

Related Documents

• SQL Server 2008 White Paper: Analysis Services Performance Guide
• Tips for configuring Microsoft SQL Server in a virtual machine (1002951)
• http://support.citrix.com/article/CTX132799
• BPC 10 Sizing Guide
• BPC 10.1 Sizing Guide
• EPM Add-in Performance Best Practices
• Poor virtual machine application performance
• Microsoft Analysis Service performance guide for SQL 2012 and SQL 2014

Related Notes

• 1601153 - OLE DB error: OLE DB or ODBC error: Timeout expired
• 1402580 - BPC: The size of BPC SQL Server database is growing very quickly
• 1387343 - BPC: SQL Database Tasks
• 1635749 - Error Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool
•  2115501 - Helpful tips when using Citrix Xenapp 6.5
• 1950966 - How to capture a SQL or MDX trace using SQL Server Profiler
• 1678822 - BPC 7.0 / 7.5 / 10.0 / 10.1 for Microsoft: Third Party Software Support Matrix