Memory usage - max setting

[Frank Esser]

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

[Erland Sommarskog]

Re: Memory usage - max setting

[posted and mailed, please reply in news]

Frank Esser (essfra@gmx.de) writes:[color=blue]
> 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?[/color]

Since SP3 is the latest service pack, you may want to start there and
see if it helps.

As far as I know, this should not occur, so you may have a memory leak.
Hm, are you using any extented stored procedures or calling any OLE
objects in process? I don't if these could take a tool.

I'll ping the people in our private MVP forum, to see if anyone has an
idea, but in the end the only way to resolve may be open a case with
Microsoft.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Craig Kelly]

Re: Memory usage - max setting

"Erland Sommarskog" <sommar@algonet .se> wrote:[color=blue]
> [posted and mailed, please reply in news]
>
> Frank Esser (essfra@gmx.de) writes:[color=green]
> > 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.[/color][/color]

<snip>

BOL refers to these settings controlling the buffer pool and doesn't mention
the full VM size reported by task manager. I'm not a SQL Server internals
expert, but if SQL Server is bumping against your max memory setting, I
would expect your VM size in Task Manager to report a higher number (thread
stacks, other memory allocators, etc, etc).

Craig

[Jasper]

Re: Memory usage - max setting

essfra@gmx.de (Frank Esser) wrote in message news:<18d2e86d. 0402100734.2786 1260@posting.go ogle.com>...[color=blue]
> 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[/color]

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\TargetP ages 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/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp

[Frank Esser]

Re: Memory usage - max setting

Thanks a lot for your answers but indeed I found an answer to my
problem:
SQL server does not stop at the max memory but uses a maximum of 1.5
times of the set max value. When I set the max server memory to 16MB
it will actually use exactly 24MB as maximum, shown in Windows Task
Manager.

It is explained in the online books:

Quote:
************
Note If you have installed and are running the Full-Text Search
support (Microsoft Search service, also known as MSSearch), then you
must set the max server memory option manually to leave enough memory
for the MSSearch service to run. The max server memory setting must be
adjusted in conjunction with the Windows NT 4.0 virtual memory size
such that the virtual memory remaining for Full-Text Search is 1.5
times the physical memory (excluding the virtual memory requirements
of the other services on the computer). Configure the SQL Server max
server memory option so that there is sufficient virtual memory left
to satisfy this Full-Text Search memory requirement. Total virtual
memory - (SQL Server maximum virtual memory + virtual memory
requirements of other services) >= 1.5 times the physical memory.
************










"Craig Kelly" <cnkelly@worldn et.att.net> wrote in message news:<8ZAWb.878 3$hR.245921@bgt nsc05-news.ops.worldn et.att.net>...[color=blue]
> "Erland Sommarskog" <sommar@algonet .se> wrote:[color=green]
> > [posted and mailed, please reply in news]
> >
> > Frank Esser (essfra@gmx.de) writes:[color=darkred]
> > > 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.[/color][/color]
>
> <snip>
>
> BOL refers to these settings controlling the buffer pool and doesn't mention
> the full VM size reported by task manager. I'm not a SQL Server internals
> expert, but if SQL Server is bumping against your max memory setting, I
> would expect your VM size in Task Manager to report a higher number (thread
> stacks, other memory allocators, etc, etc).
>
> Craig[/color]