Memory Usage/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.

> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?

> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.

> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficu
lt to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?[/vb
col]
The key word here is "nearly". There are several ways a query plan can be re
-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (
worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest yo
u investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Andrew,
> Thanks for the response. Your information agrees with what I have run acr
oss searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curio
us about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secre
t handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familia
r with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sen
t to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
would be difficult to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached pl
ans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any incre
ase today. So far,
> performance of the overall application hasn't shown any degradation. This
is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our
target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return[vbcol=seagreen]
>