When the performance of BPC data manager package is not consisent. (EPM BPC Microsoft platform)

[james_lim]

When we say the performance issue of BPC, we need to make clear which performance issue.
Data retrieving performance issue in the report?
The performance of Logic calculation? Data posting performance in the input template?
Or performance of data manager package?

The reason why we need to make clear is the bottom reason of performance issue is quite different. (I will post about each root cause of performance issue later.)


When I visited a customer site 3 weeks ago, I heard the complaint about the inconsistent performance of their custom data manager package.
So we monitored SQL process and found there was some CXPACKET wait type.

CXPACKET wait type comes from Microsoft SQL engine for waiting worker threads on the CPU.
SQL server usually split a SQL query into multiple internal queries by query engine and multiple CPU threads are performing those partial queries and sync its result afterwards. Most of case, it works perfectly without any problem but sometimes, especially when insert a lot of data, CXPACKET wait type happens. It means query engine is waiting one of other partial query.

The problem is, when it happens, its performance becomes REALLY SLOW. (I saw a worst case that took several hours to finish. Usually, it can be finished within 30 minutes.)

That's why customer complains the performance of their custom data manager package is not consistent.

Some Microsoft experts says if user maintains indexes and statistics very well, this CXPACKET WAIT should not happen but as I addressed, when user tries to insert a lot of records in the table, sometimes it happens.

To resolve this issue, there is one workaround which is disable parallelism option (MAXDOP: Max degree of parallelism option.)

Of course, when we disable MAXDOP, it means we gave up parallel execution feature of SQL server. Therefore, its performance will be decreased but it will guarantee consistent execution time. (Actually, performance difference is not that big.)

In addition, we will not set this option forever because this is an option of SQL server, it will affect all other query performance, Therefore, if a custom data manager package has this issue, we can disable this option as a first step of package and enable it when package finishes.

Here is a screen shot of the package that has two additional SQL execute Tasks.

The first SQL execute task should have below statement
sp_configure 'show advanced options', 1; --Should show advanced option to set parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;   --Turn off parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO

The last SQL execute task should have below statement.
sp_configure 'max degree of parallelism', 0;   --Turn on parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'show advanced options', 0;  --will not show advanced options.
GO
RECONFIGURE WITH OVERRIDE;
GO

In conclusion, when user executes this package, parallelism option will be turned off during package is running and roll back parallelism option when it finishes.

Of course, this option should be added when user finds CXPACKET wait type in the process monitor while their package is running and user should not run same package at the same time because parallelism option might be turned on/off unitentionally. (User can avoid this through set the flag while package is running.)

The two customers solved inconsistent performance issue of their cusom package using this solution. Of course, user should check the environment and other condition like running lite-optimization at the same time before apply this solution. 

Performance is important but sometimes consistent performance is more important when it is scheduled job.