Use fixed memory size

Question

Use fixed memory size

sheinaz tajdin

SSC Enthusiast

Points: 106
More actions
October 26, 2006 at 2:56 pm

#173268

Hi all,
I will label myself a total newbie and i desperately need direction.
Here it goes...I installed SQL Server 2005. I am trying to follow direction and set the memory to use fixed memory size. I can not find this option anywhere. I have the memory tab on which i have Server memory options. here i have "Use AWE to allocate memory" , mimunim server memory, maximum server memory and other memory options section. Normally as per the what i understood, i should also have the option to use fixed size on this tab...but i dont!!!
I thought this might have been because maybe, i dont have advanced options, so i ran a script from the internet to set my show options to '1'. This also did not change anything.
So can anyone please help me do what i need to so that i can have the "Use fixed memory size" option!
Thanks in advance.
Sheinaz

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

To use fixed memory in SQL Server 2005, you need to set the minimum and maximum memory setting to be equal to each other. It is recommended to allow SQL Server to handle memory. Why do you want to use a fixed size? If you are trying to limit how much SQL Server consumes, adjust the Max Server Memory property.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sheinaz tajdin SSC Enthusiast Points: 106 More actions · Answer 2

Hi john,

thanks for your quick response. yes we see that sql server is using half the cpu and we can not afford for that to happen right now so we are trying to limit it. we have already tried adjusting the max server memory property and it has no affect. we actually have two sql servers on different networks and we were comparing them and the other one HAS the option of a radio button with "Use fixed memory size" in the memory tab. But our current SQL server does not. This is why we are so confused as we are almost certain that this will fix our problem of how much sql server consumes.

I will try the max=min concept and see how it works, but if you also knwo how i can get the radio button to show up for fixed size, i would appreciate it.

thanks

sheinaz

John Rowan SSC Guru Points: 56440 More actions · Answer 3

I'm confused now, your latest post says that your SQL Server is using half of the CPU. Is this correct? If this is the case, adjusting your Memory settings isn't going to do much for you. Can you clarify this for me? Also, if indeed CPU was a typo and you are referring to SQL Server using 1/2 of your Memory, can you explain how much memory you have and why it is not OK for SQL Server to use it?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sheinaz tajdin SSC Enthusiast Points: 106 More actions · Answer 4

sorry yes, cpu was a typo, sql server is using half of memory. i have 1G and SQL can not use all of that because this is not a machine only dedicated to sQL because we dont have a choice to make it such. anyways, i have set min=max and mem usage is already better. but i will need to make a clean install of sql server because i found out last night that the purchased SQL server 2005 was installed over the trial version of SQL server 2005.... so some of the trial residue has been left over and this is why i am missing options to lock memory size.etc..

EdVassie SSC Guru Points: 60531 More actions · Answer 5

The 'fixed memory size' or 'lock working set' button is in SQL2000 only, not in SQL2005. If you are managing a SQL2000 you can choose this option, but it is not available in SQL2005.

The feedback I have had from PSS is that you should always have a minimum memory allocation smaller than the maximum allocation. Even on a large box with AWE in use, it is still a good idea to set min memory (say) 200 MB lower than max memory. This difference in memory use will allow SQL to manage its memory usage better.

If SQL is using less than its max memory and it needs to allocate more it will, even if the memory request takes SQL over the max limit. If you have specified a fixed amount of memory for SQL to use, the mechanism for releasing memory is disabled, so SQL could continue to use more than your specified maximum amount until it is next restarted. If min memory is always less than max, then SQL will release the over-allocation as soon as it can.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

sheinaz tajdin SSC Enthusiast Points: 106 More actions · Answer 6

Hi EdVassie,

Actually, I would have to disagree with the idea the SQL Server 2005 does not have the lock memory/fix memory option. Because i have installed SQL Server 2005 on a fresh machine and that one has the option of fix/lock memory in the memory tab. This other machine on which SQL server 2005 is installed, it was installed over a trial version of server 2005 which seems to still contain residue of trial 2005 which is why we have some options missing. I guess a clean install is needed.

as for the memory concepts, i agree with you and it makes sense, i will play with it a bit more, but for now until i do a fresh/clean install, the min=max memory seems to limit mem usage...

thanks alot!

sheinaz