Memory allocation for SAP BI 7.0 instances

[Former Member]

I have a BW 3.5 production system on Windows SQL platform. We will be migrate to a new hardware 64-bit physcal server, and upgrade to BI 7.0 ABAP/ SQL Server 2005. The new server has 32GB memory and we plan to install DB, CI and DI on the same box. My question to you is how do I allocate this 32 GB memory? My plan is:

SQL Server Database: 12GB

Central Instance: 10GB

Dialog Instance: 10GB

Is this configuration ok or something I should do differently? How big page file I should give?

Thanks for your input.

Yujun Ran

[markus_doehr2]

> SQL Server Database: 12GB

> Central Instance: 10GB

> Dialog Instance: 10GB

>

> Is this configuration ok or something I should do differently?

I would not install a separate DI but use the CI also as DI. I would divide the memory 50/50. Note: the more memory the database has, the fewer reads on the harddisks need to be done --> faster.

> How big page file I should give?

two times RAM - so 64 GB.

Markus

[Former Member]

Mark,

Thanks for your answer. Currently, we have a two servers (32-bit, 4GB memory each), DB/CI and DI, each have 30 work processes. Going to new server, if we only have DB/CI, this CI will have total 60 work processes if we want to keep the same.

In this new server, the ideal we plan to have a DI is that sometimes we found out even we have enough work process available but still no one is trying to use them, but seem waiting first many work processes instead. Dell recommend us to install a separate DI on the same box so somehow can have total same number of work processes (so we still have 30 work processes on CI and DI) but to ensure they all get utilized when system is busy. Does this configuration make sense or as you said here just one CI with 60 work processes?

Thanks,

Yujun

[markus_doehr2]

60 workprocesses is VERY much for a BI system.

How many users are concurrently logged in and using the system?

Markus

[Former Member]

Hi Markus,

We have about 40-50 concurrent users logged on at most. For CI, our total work processes are allocated as below: DIA: 14 UPD: 4 ENQ:1BGD:7 SPO:1 UP2: 2

The problem for us is that when one user launch a long query, it could take 3-5 dialog work processes (not sure why, our BW analysts said that is normal) for a while (could be 30-60 minutes). If 2-3 users are doing the same thing, all dialog work process will be taken and nobody can move their mouse! That's why we set these many work processes.

The only work process seem not be used much is UPD, maybe we can cut them to 1 or 2?

Thanks,

Yujun

[markus_doehr2]

> The problem for us is that when one user launch a long query, it could take 3-5 dialog work processes (not sure why, our BW analysts said that is normal) for a while (could be 30-60 minutes).

30 - 60 minutes occupying a dialog workprocess? I doubt that this is "normal"...

> If 2-3 users are doing the same thing, all dialog work process will be taken and nobody can move their mouse! That's why we set these many work processes.

I understand... but again, keeping a dialog WP busy for 30 minutes is certainly not right... we have on our BI system a rdisp/max_wprun_time of 600 seconds - which is sufficient for all our queries...

> The only work process seem not be used much is UPD, maybe we can cut them to 1 or 2?

UPD is the asynchronous update process - since BI is not transactional based, 2 will be enough.

Markus

[Former Member]

Yes, we have been struggling on training users properly and tuning these queries. Thanks very much for very helpful answers. I gave points on both answers you provided.

Thanks again,

Yujun

[markus_doehr2]

You're welcome.

Did you try to find out why those queries are taking so long?

Markus

[Former Member]

To be honest, most cases are probably users run the queries without providing proper parameters so it becomes blank queries somehow, not to mention that the queries themselves need be fine tuned. We have to deal these separately.

However, like I mentioned earlier, the strange thing is when a user launch a query, instead of using 1-2 DIA WP, it uses 4 or 5! Our BW analyst said "it's because some query is based on a multiprovider which has 4-5 cubes in it. When user execute a query, it has a thread for each cube". Do you know better way to deal this issue?

Yujun

[markus_doehr2]

> To be honest, most cases are probably users run the queries without providing proper parameters so it becomes blank queries somehow, not to mention that the queries themselves need be fine tuned. We have to deal these separately.

I know that problem - and that's why we limited the runtime so the query will stop and do so until the user provides correct selection criterias We tried to educate users to do so from the beginning but we found out, that only this "brute force" method works.

It's not only the runtime that comes into place but also the memory consumption if users do not select appropriately.

> However, like I mentioned earlier, the strange thing is when a user launch a query, instead of using 1-2 DIA WP, it uses 4 or 5! Our BW analyst said "it's because some query is based on a multiprovider which has 4-5 cubes in it. When user execute a query, it has a thread for each cube". Do you know better way to deal this issue?

That is normal - and again, if users do not select correctly or the data model was not designed to what users actually select those multiprovider selections will put a high load on the database and using a lot of memory.

Markus

[Former Member]

Well said! Thanks for all insightful information.

Yujun

[Former Member]

Hi Mark,

Sorry I am picking up this old thread again. Regarding 12GB Ram we want to give to database, where do I define this one? In SQL 2005 management studio, there is place to define minimum server memory and maxmium server memory. Should I set it 12GB on both? or take default 3GB on minimum and only set 12GB on maximum?

Another question is that for 32GB total memory, we give 12GB for DB, 12GB for CI and 10 GB for DI as we discussed. But should we leave something for OS and system? Or OS has some ways to use these memories even with above configurations?

Thanks,

Yujun Ran