Performance inconsistency when retrieving data sap bpc 7.5 MS

[Former Member]

Hello All,

Since few day's we are having performance inconsistency when retrieving data --Normally the data retrieval time takes less than 1 min for few reports and now it is taking more than 12 min .

And for few reports the data retrieval time is 2 min normally but now it is more than 8 min.

Some times the performance is good and retrieval is happening as expected.

We tried with full optimization , but still the above inconsistent behavior is observed .

Kindly help what needs to checked and fixed to avoid this.

Regards

Venkkat

[Former Member · Accepted solution]

Hi Venkkat

You need to determine first if the performance is load related or configuration related or maybe both.

To do the you will need to test the same reports on a "hot" (system under normal or peak use with other users actively using the system) vs "cold" (no other users or activities running on the system besides the testing user).

If the cold system performs well but not the hot one, you need to investigate where is the bottleneck and most of the time is localized around the SQL SSAS or SQL DB engine. Most of the time you will find a combination of high CPU utilization, insufficient memory and excessive disk access is the culprit.

One thing to know is that when you run an EVDRE report there is one or two types of queries issued:

• MDX query - if the report doesn't contain base level members
• SQL query - if the report contains ONLY base level members
• MDX + SQL query if the report contains both base level members and aggregated members

So you can start by adjusting the report to determine which query causes a slow response: MDX or SQL. A quick way to remove the MDX for an existing report is by setting the SQLONLY parameter to Y in the EVDRE.

If the report is still slow with SQLONLY = Y then you can focus on the database engine and investigate performance around that area.

Note that the configuration of the TEMPDB is often poorly understood and overlooked. As surprising as it may sound, when running an EVDRE report against base level members, the BPC query engine generates a sequence of selects and inserts and thus the performance of TEMPD is crucial - especially on a hot system.

Therefore even if full optimize has been run, the system performance will not improve significantly if the TEMPDB is not lightning fast.

Have a look at this post:

http://blogs.msdn.com/b/psssql/archive/2014/04/09/sql-server-2014-tempdb-hidden-performance-gem.aspx

Hope this helps.

Stefan

[Former Member · Accepted solution]

Hello Stefan,

Thanks for your answer. And we did the below steps

- After changing the max limit memory set on to data base server and changing the page file configuration settings as required on to the respective application , olap and data base servers.

- And performed the full optimization after.

Thansk

Venkkat