min server memory option

Question

min server memory option

Darek Tomyn

SSCrazy

Points: 2551
More actions
March 3, 2004 at 9:44 am

#86240

How do I know when these settings actually take? That is, sometimes when I run:
exec master..sp_configure N'min server memory (MB)', 2048
GO
exec master..sp_configure N'max server memory (MB)', 2048
GO
reconfigure with override
go
there is the error message:
"Warning: unable to allocate 'min server memory' of WhateverISetItToMB."
while other times there is no warning. The times that there is no warning I am sitting here now wondering if it actually took. I am thinking that just a sp_configure will work to show me this, but no longer know if this is correct. From looking at performance monitoring memory counters and task manager there is no indication if it is ok or not. That is, from my understanding of min memory is that it actually does not hit min memory until SQL Server needs it (am I correct?). Up until now I thought that it would show up as used memory to Windows, but I am beginning to think I am wrong.

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

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

Jbabington SSC Eights! Points: 854 More actions · Answer 1

Darek,

Tried several times to get the error you are getting and had no luck duplicating this. These Memory Settings are immediate.

On the flip side is there any reason you are setting the min and max memory to the same allocation?

I may stand corrected but 99% of the time it is best to allocate a min and max amount not a "FIXED" amount of memory... SQL 7.0 & 2K are dynamically handling the memory as needed... using a fixed memory setting is not really a good idea as this prevents other applications that may run on the db server (that should reside someplace else) to allocated memory such as Analysis Services or the like.

Also follow best practice of Minimum Memory of a Base allocation of say

· 32MB without Replication,

· 64 MB of Memory (or greater) allocation with Replicated components installed

· 128 MB of Memory (or greater) allocation with Replicated components installed & Analysis Services.

Also follow best practice of Maximum Memory allocation of say

· 75% Total System Memory

· Or Total Connection Memory and Additional Process Memory subtracted from the total system memory… You wish to avoid context switching excessive paging

This is the base memory pool which SQL Server allocates at startup and will not be addressed by other applications. If you monitor the memory usage from startup the server may not use all the Min allocated memory but it is "reserved".

I am certain you will have others respond far better than I have in regard to memory allocation issues but I do hope this helps get you pointed in the right direction.

Jim

Jbabington@hotmail.com

Jbabington
Jbabington@hotmail.com

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 2

Thanks for the reponse. The background beyond the "fixed server memory" decision is as follows:

- running Windows 2000 with 4 GB RAM

- running SQL Server 2000 Standard Edition which can only use 2 GB of RAM

- there are batch jobs that are started externally by SQL Server via SQL Agent

So, even though there has never been a problem before, it was though that there is plenty of memory so why not allocate the maximum for Standard Edition (2 GB).

The last thing I will mention about this is that it is not just when setting "fixed memory" that I get this error. Even if I set a min server memory less than the max I will *sometimes* get this error. So, I guess what I was hoping was that someone could actually respond to say that 'yes, you will know that min server memory has been successfully set by viewing XXXXX'.

Jbabington SSC Eights! Points: 854 More actions · Answer 3

No problem on the response and thanks for the clarification on the memory allocation of 2GB.

I still would not allocate the full amount "Fixed" I would let the server dynamically handle the memory <preference issue for me I guess>. I am still perplexed with the error you describe and why it is infrequent...

If you run sp_Configure (no additional options)

the config_value is the currently set options

the run_value are the currently running values displayed for the server including Min and Max memory.

Jim Babington

jbabington@hotmail.com

Jbabington
Jbabington@hotmail.com

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 4

try running your sql using sql-agent.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 5

Why are you suggesting this? That is, are you thinking that it sometimes cannot allocate the memory because not all of the services are up and running (like SQL Agent)? Please explain.

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 6

It's because within sqlserver-memory it cannot allocate memory for your query. When I run it via sqlagent on servers that have this memory-pressure, the chance of getting it executed is higher (imho).

It is a temporary issue your sqlserver has.

In my experience its best to add ram, or you can alter the min server memory option to start with.