Memory Distribution for SAP, Windows 2003 and SQl 2005

[Former Member]

Hi All,

We are running on 64bit Server Windows 2003 Enterprise Edition, SQL 2005, ECC 6.0 ABAP only.

Physical memory size = 32GB.

Current PHY_MEM setting = 19GB

SQL is allocalted fixed 13 GB.

Swap or Virtucal memory - 60GB.

I am not sure, if Zero memory management allows Windows Operating system to gain mamory automatically ? or should I correct some settings in SAP Memory or SQL memory to allow OS to gain some physical memory ?

Pls advice.

Krishna

[markus_doehr2 · Accepted solution]

Physical memory size = 32GB.

Current PHY_MEM setting = 19GB

SQL is allocalted fixed 13 GB.

Swap or Virtucal memory - 60GB.

This looks good! As you can you have configured the system to use all available physical memory (19 + 13 = 32).

I am not sure, if Zero memory management allows Windows Operating system to gain mamory automatically ? or should I correct some settings in SAP Memory or SQL memory to allow OS to gain some physical memory ?

If you increase the size of PHYS_MEMSIZE without decreasing the database memory your system will start to swap --> slowdown.

Check ST02 - Extended memory - Max. use - are the 19 GB used? If not, I´d decrease the PHYS_MEMSIZE and increase the database memory.

Markus

[Former Member · Accepted solution]

Hi Markus,

We have applied new settings few days back. Max Extended memory used sofar is 1.6GB.

Your advice do make sense, so I will leave few days and reduce PHYS_MEMSIZE and see how goes.

The comment on your swapping is 100% accurate. We have experienced it.

How will increasing DB memory size contributes positively ? How do I measure if DB happy or struggling ?

And regarding OS, the question is still open and not sure if there are any issue. But, no signs of problems yet.

Thanks,

Krishna

[markus_doehr2 · Accepted solution]

We have applied new settings few days back. Max Extended memory used sofar is 1.6GB.

Your advice do make sense, so I will leave few days and reduce PHYS_MEMSIZE and see how goes.

So that means, that you only use 1.6 GB effectively (plus the buffers of course) from the 19 GB you have assigned.

How will increasing DB memory size contributes positively ? How do I measure if DB happy or struggling ?

I would watch ST02 for a few more days, maybe a month to see the real maximum memory usage also during period closing times. If you don´t see more than 1.6 GB without a restart then I would decrease PHYS_MEMSIZE to (e. g.) 10 GB and add the 9 GB for the database.

I would also change the memory allocation model according to

Note 1002587 - Flat Memory Model on Windows

This will decrease context switches and thus increasing speed for dialog processing.

Check the task manager and make sure the total memory usage doesn´t go beyond your physical available memory. Especially have a look on that if you follow the above note.

Also check that you have implemented the hotfix

http://support.microsoft.com/kb/931308

Markus

[Former Member · Accepted solution]

Hi Markus,

I am really collecting some interesting information during this discussion, so I am dragging on although my question in principle is answered.

a. Regarding the PHYS_MEM, I am more or less clear.

b. With Respect to SQL Memory allocation, as mentioned earlier, we have allocated some fixed 12.9GB. and ST04 report shows, Data cache used up to 7.6GB, Proc Cache used 2.6 GB and log size allocated it self 5.8 GB. We are getting occational alerts that DB buffer hit ratio has fallen below 80%, dows this mean, data cache is over writing itself when it used up its maximum ?

c. Regarding the flat memory model configuration, the following discussion says there is no real advantage and the note says it uses more memory and less CPU power. We have 16 CPUs, so we we really find any advantage ? I know you are just suggesting and not reccomonding, I am keen on your comments.

Regards,

Krishna

[markus_doehr2 · Accepted solution]

b. With Respect to SQL Memory allocation, as mentioned earlier, we have allocated some fixed 12.9GB. and ST04 report shows, Data cache used up to 7.6GB, Proc Cache used 2.6 GB and log size allocated it self 5.8 GB. We are getting occational alerts that DB buffer hit ratio has fallen below 80%, dows this mean, data cache is over writing itself when it used up its maximum ?

Yes - exactly.

DB buffer hit ratio 100 % means: All the requested data was in the cache. If more data is read, the pages, that have not been accessed for a long time, are displaced by others. The best performance is reached, if your data cache hit ration doesn´t go down to more than 95 %. If it does, then your cache/buffer is too small or someone executed a HUGE report displacing all the "valid" data and hence slowing down the system.

c. Regarding the flat memory model configuration, the following discussion says there is no real advantage and the note says it uses more memory and less CPU power. We have 16 CPUs, so we we really find any advantage ? I know you are just suggesting and not reccomonding, I am keen on your comments.

That is an interesting statement.

The absolute CPU power is not significant in that area, it´s the number of "CPU cycles" that are used during context switches are, which are done by one CPU at a time no matter how many of them you have. If you switch to flat memory model, you will see a decrease of kernel time in the task manager graph (the "red" part if you switch it on).

However, since your system doesn´t seem to be a very very high loaded system and is not virtualized you can use the traditional/default memory model.

Markus

[Former Member · Accepted solution]

Hi Markus,

Thanks for all information.

Krishna

[Former Member]

hi Krishna,

Allocating too much of memory for database it not a good sign for performance. try to allocate around 5-7 GB at the max....

Also you need to find some good notes for the same.

Note 618868 - FAQ Oracle performance

Note 830576 - Parameter recommendations for Oracle 10g

Note 983566 - RZ10 Buffer size for ABAP programs in basic maintenance

SAP Note 121808 Swaps in program buffer although space is free

Regards,

[markus_doehr2]

Allocating too much of memory for database it not a good sign for performance. try to allocate around 5-7 GB at the max....

I don´t know where you got that information but it´s wrong.

The more memory a database has, the less it needs to read from disk (disk is 10 - 100 times slower than memory) and conclusively the system is MUCH faster with a huge amount of cache for the database.

Markus