Sql Memory Leaks: dual

Monday, March 19, 2012

Memory Usage, SBS 2000 & SQL 2000

Hello,
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
ChrisI expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
--
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> Hello,
>>
>> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
>> processor box with 2GB of RAM) continues to grow over a few day period.
>> I
>> have double-checked to make sure that SP3a is installed and am trying to
>> double-check to make sure that MDAC 2.71 is installed (correctly).
>> First,
>> what's the best way to assure that each of these are installed without
>> errors or issues? Second, if those are then, what else could be causing
> the
>> memory to continue to grow over time?
>>
>> I know that I can limit the SQL configuration to 1GB (for example) but
>> that's not really the key problem here. As I write this the memory has
>> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
>> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
>> Adding RAM is obviously an option but that looks like it's only going to
> be
>> a band-aid to the problem because it will just continue to eat the
>> additional memory unless I lock the SQL RAM usage however I feel the key
>> issue to determine why the RAM is continuing to grow day-by-day.
>>
>> Thanks in advance!
>>
>> Chris
>>
>

Friday, March 9, 2012

Memory settings/considerations when running multiple instances of SQL Server

I've got a server, Dual PIII 733's with 2gb of RAM that I setup with 3 named
instances of SQL Server. I'm not sure what I should do with the memory
settings, specifically the min and max and whether that has any bearing or
not.
I noticed when pulling up task manager that the memory for 1 instance will
grow to be 800mb or so while the other 2 will hover around 46-60 mb. I
tried setting them each to use exactly 500mb but that didn't appear to have
any impact. I know that the memory will grow as the databases get used but
the other 2 instances still stayed low even when I was restoring a 40gb
database. I also noticed that stopping the services from the first instance
would kill the sqlserver.exe process that had the high allocation of memory,
but the others still stayed in the 46-60 mb range.
How can I manage the memory used by each instance of SQL Server or what
should I set so that I don't have to worry about it? Any help with this is
greatly appreciated.
Thx,
KenThis is a multi-part message in MIME format.
--000107050009040201040201
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
My advice would be just to leave it how it is. By default, SQL 2000 out
of the box uses a dynamic memory management strategy (as it seems you
already know). With only 2G of physical RAM in the box you don't need
to play with /3GB switches or AWE memory or anything fancy like that.
SQL Server will cache data into its address space as it is accessed
(restoring a DB is not the same as accessing data in that DB, which is
done via standard T-SQL statements like SELECT, INSERT, UPDATE &
DELETE). It will try to maintain a minimum free physical RAM threshold
(by default 10MB but configurable) so when another app (like one of your
other sqlservr.exe instances for example) make a request for memory and
there is less than that threshold available, it will release some of its
memory allocation in order for the OS to satisfy the new memory request
of the other app AND maintain that free memory threshold.
If the SQL instances using less RAM need the memory then they will ask
for it and get it at the other instance's expense. Basically, the most
needy SQL instances will have the most memory (as it should be).
If you really want to set hard limits for your SQL instances you can set
high & low limits for the dynamic memory management to work with via
sp_configure ("max server memory" and "min server memory") or you can
set a fixed amount of memory for SQL Server to work with (so that it
does *not* swap pages out of RAM) via sp_configure ("set working set size").
But I'd just leave it exactly how it is if I were you.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Ken Patton wrote:
>I've got a server, Dual PIII 733's with 2gb of RAM that I setup with 3 named
>instances of SQL Server. I'm not sure what I should do with the memory
>settings, specifically the min and max and whether that has any bearing or
>not.
>I noticed when pulling up task manager that the memory for 1 instance will
>grow to be 800mb or so while the other 2 will hover around 46-60 mb. I
>tried setting them each to use exactly 500mb but that didn't appear to have
>any impact. I know that the memory will grow as the databases get used but
>the other 2 instances still stayed low even when I was restoring a 40gb
>database. I also noticed that stopping the services from the first instance
>would kill the sqlserver.exe process that had the high allocation of memory,
>but the others still stayed in the 46-60 mb range.
>How can I manage the memory used by each instance of SQL Server or what
>should I set so that I don't have to worry about it? Any help with this is
>greatly appreciated.
>Thx,
>Ken
>
>
--000107050009040201040201
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>My advice would be just to leave it how it is. By default, SQL
2000 out of the box uses a dynamic memory management strategy (as it
seems you already know). With only 2G of physical RAM in the box you
don't need to play with /3GB switches or AWE memory or anything fancy
like that. 
 
SQL Server will cache data into its address space as it is accessed
(restoring a DB is not the same as accessing data in that DB, which is
done via standard T-SQL statements like SELECT, INSERT, UPDATE &
DELETE). It will try to maintain a minimum free physical RAM threshold
(by default 10MB but configurable) so when another app (like one of
your other sqlservr.exe instances for example) make a request for
memory and there is less than that threshold available, it will release
some of its memory allocation in order for the OS to satisfy the new
memory request of the other app AND maintain that free memory threshold. 
 
If the SQL instances using less RAM need the memory then they will ask
for it and get it at the other instance's expense. Basically, the most
needy SQL instances will have the most memory (as it should be). 
 
If you really want to set hard limits for your SQL instances you can
set high & low limits for the dynamic memory management to work
with via sp_configure ("max server memory" and "min server memory") or
you can set a fixed amount of memory for SQL Server to work with (so
that it does not swap pages out of RAM) via sp_configure ("set
working set size"). 
 
But I'd just leave it exactly how it is if I were you. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Ken Patton wrote:
<blockquote cite="midd6j517$7rh$1@.news01.intel.com" type="cite">
<pre wrap="">I've got a server, Dual PIII 733's with 2gb of RAM that I setup with 3 named
instances of SQL Server. I'm not sure what I should do with the memory
settings, specifically the min and max and whether that has any bearing or
not.
I noticed when pulling up task manager that the memory for 1 instance will
grow to be 800mb or so while the other 2 will hover around 46-60 mb. I
tried setting them each to use exactly 500mb but that didn't appear to have
any impact. I know that the memory will grow as the databases get used but
the other 2 instances still stayed low even when I was restoring a 40gb
database. I also noticed that stopping the services from the first instance
would kill the sqlserver.exe process that had the high allocation of memory,
but the others still stayed in the 46-60 mb range.
How can I manage the memory used by each instance of SQL Server or what
should I set so that I don't have to worry about it? Any help with this is
greatly appreciated.
Thx,
Ken
</pre>
</blockquote>
</body>
</html>
--000107050009040201040201--

Monday, March 19, 2012

Memory Usage, SBS 2000 & SQL 2000

Friday, March 9, 2012

Memory settings/considerations when running multiple instances of SQL Server

Sql Memory Leaks

Blog Archive

About Me