May 29, 2008 at 5:51 pm
I think you are viewing this 28MB figure against sqlservr.exe under task manager. Is it?
Manu
May 30, 2008 at 12:13 am
Matt Miller (5/29/2008)
I thought the min memory setting had nothing to do with startup value for RAM. SQL Server starts up grabbing whatever memory it needs to start (which could be either bigger or smaller than min memory). It only acts as a low-water mark once memory usage pushes past min memory setting; so - the memory doesn't have to reach or go over the min memory setting, but it won't go back below the min memory setting once it exceeds it until the server is restarted.
Not 100% sure. I seem to recall that on SQL 2000 the behaviour was as I described. It may well have changed in 2005 though.
Memory is one of the areas I need to do a lot more investigation into. It's one erason I'm answering as many memory-related questions as possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2008 at 7:41 am
GilaMonster (5/30/2008)
Matt Miller (5/29/2008)
I thought the min memory setting had nothing to do with startup value for RAM. SQL Server starts up grabbing whatever memory it needs to start (which could be either bigger or smaller than min memory). It only acts as a low-water mark once memory usage pushes past min memory setting; so - the memory doesn't have to reach or go over the min memory setting, but it won't go back below the min memory setting once it exceeds it until the server is restarted.Not 100% sure. I seem to recall that on SQL 2000 the behaviour was as I described. It may well have changed in 2005 though.
Memory is one of the areas I need to do a lot more investigation into. It's one erason I'm answering as many memory-related questions as possible.
Hmm... You answer made me go dig some more. I too don't spend nearly enough time on it, so I'm somewhat shaky. So I went and found these two things from BOL:
Server Memory Options (SQL Server 2000)
So there seems to be for 2000 two separate scenarios on Windows Server 2000:
- when AWE is enabled (32-bit only) and being used (it gets ignored if physical mem<3GB apparently), then SQL Server grabs the MAX memory and holds onto it. Apparently no swapping happens at that point.
- when AWE is NOT enabled:
Use min server memory to guarantee a minimum amount of memory to an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced.
Note SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never necessitates the allocation of the amount of memory specified in min server memory, then SQL Server will run with less memory.
Use max server memory to prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. SQL Server does not immediately allocate the memory specified in max server memory on startup. Memory usage is increased as needed by SQL Server until reaching the value specified in max server memory. SQL Server cannot exceed this memory usage unless the value of max server memory is raised.
Important: Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the full amount of memory specified in max server memory on server startup. For more information about AWE memory, see Managing AWE Memory.
SQL Server 2005's version seems to imply that it can use dynamic memory management with AWE when installed on a Win2003 server. I haven't found anything on SQL 2000 on Win 2003 yet....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 7:59 am
Thanks Matt.
When AWE is enabled on Windows 2003 + SQL Server EE 2000 SP4, SQL server does utilize the maximum memory allocated at start up.
Below is the explanation that I got from Microsoft for memory utilization of SQL 2005 on Windows 2003 :
By design , SQL Server 2000 will immediately start to use the total AWE memory available. When AWE enabled is set to 1, instances of SQL Server 2000 do not dynamically manage the size of the address space. SQL Server will reserve and lock almost all available memory (or the value of max server memory if the option has been set) when the server is started. You can use the SQL Server Performance Monitor Total Server Memory (KB) counter to determine how much memory is allocated by the instance of SQL Server running in AWE mode to the buffer pool region. Target Server Memory counter always indicates how much to grow & since SQL has already acquired all the memory, its logical to see the values under aforementioned counters as equal. Task Manager does not provide accurate memory usage information for AWE. Refer to SQL 2000 BOL topic “awe enabled Option” & “Managing AWE Memory”.
Now, in SQL Server 2005, during startup, SQL Server reserves only a small portion of AWE mapped memory. As additional AWE mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer resources are required, SQL Server can return AWE map. You can use the SQL Server Total Server Memory (KB) counter in Performance Monitor to determine how much memory is allocated by the instance of SQL Server running in AWE mode to the buffer pool region. Hence you’d see a difference between the two of aforementioned counters.
Since SQL Server 2005 by design doesn’t commit all the AWE memory. It can grow on a gradual basis not out rightly at startup. There are remote chances of SQL Server doing so, if there is a startup procedure that is so memory intensive that fills up the whole buffer pool region & then SQL Server will have to go & commit additional memory from AWE region. For e.g. if there is 16 GB of RAM on a server with SQL Server 2005 AWE enabled & max/ min server memory set to default. Then on a 32Bit system the buffer pool will grow up to the 16 GB. Hence there has to a startup procedure which should bring in 16 GB of data in memory so that SQL Server allocates additional pages from AWE [although there should be some memory for O/S usage as well, but I am just taking an ideal scenario]. I don’t think that’s likely to happen.
Prakash B
May 30, 2008 at 8:51 am
Hmmm... it appears I'm way behind on information on memory capacity and the ability to get past the 4 GB addressing limit of 32 bits. Can anyone either provide the paragraph or two explaination of AWE and how W2K3 Server and/or server motherboards are dealing with RAM > 4 GB? What I've heard on this thread suggests that AWE is similar in concept to the 32-bit subsystem available for good ole Win 3.1 ever so many years ago
All I need is the basic concepts and perhaps a link to somewhere that can provide more detailed background. Thanks!
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2008 at 8:51 am
It can't be 28MB of memory. I doubt SQL Server runs in 28MB.
You must have something else going on with the box. I'd re-open the case and work through it with PSS. I can't believe that they told you that 28MB is normal. Either you're leaving something out or there are serious configuration issues on the OS.
Is anything else running on the server? Other major server software?
Is this 32bit?
May 30, 2008 at 9:09 am
Interestingly enough, if I watch the SQLServ process in task manager (on my Dev Ed. install) - it starts with about 25MB on the very first reading. Of course - within 10 seconds it's up to about 100MB, and climbs quickly from there once I start throwing it things to do.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply