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.
The operating system configurations along with the other 3rd party prerequisite configurations are vital to the overall environmental performance of BPC.
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.
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)
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
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.
- SQL Server 2008 White Paper: Analysis Services Performance Guide
- Tips for configuring Microsoft SQL Server in a virtual machine (1002951)
- 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
- 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
Use the Enable 3GB support COM+ property under the Advanced tab when possible.
Set COM+ Pool size to 4 instead of 5 for BPC 7.0 MS
BPC 7.0 has it's own performance tuning guide geared toward Windows 2003. That is why it is not mentioned in this WIKI and any customers still using 7.0 should not use this WIKI and instead use the performance tuning guide for that version.
Where is the 7.0 tuning guide?
he probably refers to http://scn.sap.com/docs/DOC-4164 this is also valid for 7.0 version.
see please also http://scn.sap.com/docs/DOC-4143
Just want to update 2 things for BPC 10 MS.
So I usually, recommend 40 as maximum and 25 as low and SQL should have 50% of memory. 10% just for OS.
If customer is using Consolidation stored procedure heavily and SQL script logic than MDX query, SQL DB should have more.
If customer has complex MDX formula including measure and dimension formula, we should give more memory to MSAS.