es****@gmx.de (Frank Esser) wrote in message news:<18**************************@posting.google. com>...
I am using SQL 8 Personal edition with sp2 applied. I set the max
server memory to 32MB and leave the min server memory at 0. When my
application starts hitting the database hard the memory usage reported
through task manager peaks between 41-42MB. I've stopped and restarted
the MSSQLserver service and checked that the running values are what I
set them to be. Does anybody have any ideas as to why the sqlservr.exe
would be utilizing more memory than the configured value?
This message was posted some years ago but nobody answered. Now I have
the same problem! Does anybody have an explanation?
Thanks a lot
The buffer pool is sized on the lower of the amount of physical
memory, or user mode address space (2GB by default)-MemToLeave which
is (again by default) around 384MB. If max server memory is set then
it becomes the limit if it is lower still. So if a server has 1GB and
no limit the BPool will be 1 GB (or more accuratley 1GB of reserved
space - it's not committed until used). On a server with 1GB for
example and max server memory set to 100MB then the BPool is sized at
100MB as this is lower than the physical memory.This in no way
constrains the SQL process from consuming more than 100MB, it merely
limits the address space reserved for the BPool pages (and by
definition the amount of memory that can be committed for thos BPool
pages). To confirm the sizing of the BPool you can use Buffer
Manager\TargetPages and convert from 8k pages into MB.When the server
starts it works out the limit of the BPool, reserves the MemToLeave
area,reserves the BPool and then releases the MemToLeave area. Having
said that, MemToLeave can obviously be greater than 384 MB as on a
server with 1GB, there will be ~1GB of free space in the user mode
address space. So (I am getting to a point!) the max server memory
limits the maximum size of the buffer pool but it should be in no way
taken as a limit to how much memory SQL can commit from its process
address space (2GB by default). Clear as mud :-)
For more on SQL's memory architecture check out
http://msdn.microsoft.com/data/defau...v_01262004.asp