SQL Server Performance Forum – Threads Archive

SQL 2000 Standard Memory flatline

Hi all, We have an SQL 2000 Standard SP4 system with 3gb of RAM running on Windows 2003 Server R2 Standard (SBS2003 transitioned) The SQL memory usage of this server flatlines at around 1665mb and will not go any higher. I understand SQL 2000 Standard on Windows 2003 Server Standard has a 2gb memory limit, however why wont the memory push up to this limit? The OS shows there is around 700mb of the 3076 unused (SQL uses 1660, windows uses around 700 making the maximum used around 2300 of the 3076) I have set the /3gb switch on windows boot, I have changed the SQL memory configurations many different times. If I try and set ‘min server memory’ to anything over around 1500mb I get the error on SQL startup; Warning: unable to allocate ‘min server memory’ of 1720mb In the above example surely if the Server has a 2gb limit it should let me set say 1720/2048? or 1920/2048 or even 2048/2048, however anything over around 1500/2048 generates the above error. Our server continues to flatline at 1660mb, any help to squeeze out an extra 200-400mb of RAM would be appreciated or an explination as to why it can not be done. Thanks for your help Jim

That doesn’t seem out of line to me – SQL Server’s memory allocation is broken into several separate sections, and it can be slightly tricky measuring all of them to see a complete picture of what is happening. Generally on a dedicated SQL box it does just what it’s supposed to do, if you don’t mess with it, which is to automatically allocate as much memory as possible. For standard edition the buffer pool does max out at less than 2Gb (might be the 1660 you describe) because of the other components of SQL Server and the OS. Breaking that limit requires SQL Server 2005 (or 2000 Enterprise) and at least 4GB RAM, and some version of the OS that can address more than 4GB, which would be either the 64-bit version or 32-bit Enterprise + AWE.

Suggest a reference on SQL Server internals by the likes of Kalen Delaney or Ken Henderson; or this chapter, especially the end: http://searchsqlserver.techtarget.c…de_to_SQL_Server_Architecture_Chap 11_AWL.pdf

Thanks Merrill, So would I be correct to assume then that the MemoryToLeave section of the SQL memory architecture (around 384mb) is NOT represented by either Task Manager or any other SQL diagnostic apps as alloctaed/used SQL memory? Thus my 1660mb is actually 1660+384 (2044) max available memory. or is my server still not functioning at full potential as Task Manager etc DO calculate this MemToLeave area and thus my server is really only 1660mb (including 384 MemToLeave) and thus still running under its max potential?

I think you are right; my understanding of mem to leave (though incomplete) is that it’s MEMory to LEAVE for the operating system, to run things such as COM objects or extended stored procedures. SQL Server does something like allocate a single block of RAM for those functions, and then purposefully gives that back over to the OS. I might not be interpreting this right, though.

]]>