Data Federation Service makes server crash (CPU on 100%)

[alfons_gonzalez]

Hi,

We have a multisource universe (2 different SQL server instances). we have noticed that in certain cases queries executed against this universe puts the server where the APS Datafederation service is running on a CPU 100% consumption. Have someone noticed this behaviour? How can we avoid that?

In the universe we have set up a limit execution time of 5 minutes but once the server got the 100% CPU nothing works as expected. System is not responsive and we haven't access to the machine console so the only solution (dramatic for a Production environment) is to power off/on the virtual machine.

I have read about people complaining about performance of data federation but in our case problem is not the time it takes but the impact on the server (it makes it unusable).

Any feedback/idea is welcome

Thanks,

Alfons

[former_member193452]

Hi Alfons,

This is quite unusual behavior. The sql server should push the query up to the Server and extracting a smaller set to send to the memory. When memory becomes exhausted then it moves to CPU usage.

There was an patch update to improve memory management in both 4.1 (SP10) and 4.2 (sp4 patch1) versions.

The first step required by development would be 1. check the landscape and that the memory is sized properly. The DF service requires a minumum of 4 gb just to load the application. The DF service is in the APS. It should be isolated on its own and give sufficient memory in the JVM.

Heres a bit of information on calculating that. How to use the DFAT to calculate Memory Needs

Next, In the DFAT tool the statistics for connections should be calculated. This will determine how the sql is optimized.

How to Compute Statistics

Also check your pam and make sure you are using the correct drivers for the version of BI and Sql Server.

Jacqueline

[alfons_gonzalez]

Hi Jacqueline,

Thanks for your answer. After assesing several options we finally decided to "downgrade" the multisoure universe to single-source. After having doing that stability issues on the server (CPU usage) have disappeared.

Alfons