cancel
Showing results for 
Search instead for 
Did you mean: 

Memory Management for SQL Server DB

Former Member
0 Kudos

Hi!

We would like to increase the RAM-size of our Windows 2008 server from 6 GB to 10 GB.

Our phys_memsize parameter is currently set to value: 3500

Question:

Do we need to increase the size of SQL Server buffers, phys_memsize and other settings?

Via tcode ST04 I can see within Memory section:

Phys. Memory (MB): 6000

Current Memory: 2500

SQL memory Setting: FIXED

Any helpful information will be very appeciated

kind regards

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member189546
Active Contributor
0 Kudos

Hello,

Please check your parameters against this note.

879941 Configuration Parameters for SQL Server 2005

regards,

John Feely

Former Member
0 Kudos

Hi!

Can I change the value of minimum server memory and maximum server memory within SQL Server Management Studio and then restart the DB inc. SAP or should I do this with the following script?

exec sp_configure 'min server memory (MB)', 1500

exec sp_configure 'max server memory (MB)', 1500

reconfigure with override

Former Member
0 Kudos

Yes you can change the DB parameter from Management studo:

http://merill.net/2010/05/limit-sql-server-memory-usage-on-your-workstation-laptop-or-vm/

Former Member
0 Kudos

Hi!

Thank you!

Do I need to restart the DB and/or OS in order to use new increased parameter or is this change dynamic?

Former Member
0 Kudos

No, you don't have to restart the DB server. Its an online operation.

After changing you can check by clicking running values under memory section of DB server properties.

Thanks

Mushtaq

Former Member
0 Kudos

Hi Holger,

If that is a dedicated sql server machine, you can give upto 6 GB and leave 4 GB for the OS.

you can check data cache and proc cache ratio's and page life expectancy currently. it will give you an rough idea about how is the memory pressure on the system.

Mush

Thanks

markus_doehr2
Active Contributor
0 Kudos

> We would like to increase the RAM-size of our Windows 2008 server from 6 GB to 10 GB.

> Our phys_memsize parameter is currently set to value: 3500

>

> Question:

> Do we need to increase the size of SQL Server buffers, phys_memsize and other settings?

This depends in which area you want to use the memory.

Markus

Former Member
0 Kudos

>This depends in which area you want to use the memory.

I just want to adapt all the necessary buffers in SAP as well in SQL Server due to increase of RAM from 6 to 10 GB.

E.g. the parameter phys_memsize I will change from 3500 to 4500.

The question still: how to identify how much buffer needs MS SQL when it starts and works

and whether and if yes how to adapt the buffers of SQL Server?

Thank you!

markus_doehr2
Active Contributor
0 Kudos

> E.g. the parameter phys_memsize I will change from 3500 to 4500.

Ok - that will be then 1 GB out of 4 GB that you use. Stay 3 left.

> The question still: how to identify how much buffer needs MS SQL when it starts and works

> and whether and if yes how to adapt the buffers of SQL Server?

You don't need to adapt them, the system will work as before. But the more memory you give the database the fast your system will be. So I'd increase the fixed memory parameter accordingly - means, you can add 3 Gb.

Markus

Former Member
0 Kudos

Hi!

I would like to share my new increased RAM size of 10 GB as follows:

4.500 to SAP (setting parameter PHYS_MEMSIZE=4500)

4.500 to DB

1.000 I would let for OS

Question

How can I increase the value of SQL Server memory?

Is this setting dynamic?

When I look into the memory settings in SQL management Studio I see that

minimum server memory is equal to maximum server memory and is set currentyl to value 2343.

So I would like to set the value "4500" by setting this value under "minimum server memory" and under "mamixum server memory"