Re: Memory Usage/Hog

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: daniel.ferguson (daniel.ferguson.nospam_at_kodak.com)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 14:06:14 -0600

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...
>>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"!
>>
>
>

Relevant Pages

  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem > 1 GB memory
    ... > Is there any way to 1) not to load the whole table on memory in order ... SQL Server maintains a cache of data that is accessed frequently. ... If there is no index on customer ...
    (comp.databases.ms-sqlserver)
  • Re: How to clear memory usage after executing queries ?
    ... Uh, Tom, if the pages that have been the cache the longest are constantly ... SQL Server will push pages out of memory only if it has to read new ... > pages into memory. ...
    (microsoft.public.sqlserver.programming)
  • RE: Consolidating instances of SQL server
    ... SQL Server is designed to use memory. ... The maximum amount of memory SQL Server can use varies depending on your ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)