Sql Memory Leaks: instances

Showing posts with label instances. Show all posts

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, February 20, 2012

Memory Leaks

SQL server is experiencing Memory leaks. So much so that 2gb of ram
dissapears and the server stops.
THe server has 3 instances of SQL and most operations use linked servers
using oledb provider for oracle driver.
I have read some artavles that memory leaks can happen when the data type
long is returned using ref cursor.
The main queries used the syntax select * from openquery(linked
server,'oracle query') and are either select or update. Some cases sp's are
created with an input parameter and this input parameter is used to update a
single row and this sp is called severl times. Some articles i have read also
claim that memory leaks also happen when such a statement is repeatedly
executed.
Can anyone elaborate on this or give any suggestions as to the cause.
SQL 2000 SP3 on windows server 2000
MDAC is 2.7 SP1"mat" <mat@.discussions.microsoft.com> wrote in message
news:6601DAC1-672B-4B9B-B32D-F2EC4BEA4A18@.microsoft.com...
> SQL server is experiencing Memory leaks. So much so that 2gb of ram
> dissapears and the server stops.
> THe server has 3 instances of SQL and most operations use linked servers
> using oledb provider for oracle driver.
> I have read some artavles that memory leaks can happen when the data type
> long is returned using ref cursor.
> The main queries used the syntax select * from openquery(linked
> server,'oracle query') and are either select or update. Some cases sp's
> are
> created with an input parameter and this input parameter is used to update
> a
> single row and this sp is called severl times. Some articles i have read
> also
> claim that memory leaks also happen when such a statement is repeatedly
> executed.
> Can anyone elaborate on this or give any suggestions as to the cause.
> SQL 2000 SP3 on windows server 2000
> MDAC is 2.7 SP1
Are you using the Microsoft OLE-DB provider for Oracle, or Oracle's OLE-DB
provider. I would suggest that you use Oracles OLE-DB provider. It seems
to work better for me. Notes: This was SQL 2k, Oracle 8i and SQL2k
,Oracle 9i. I have not used with Oracle 10g.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Cheers..
i'm using Microsofts Provider.
I'll try it, but do you know what caused these problems
?
"Rick Sawtell" wrote:
> "mat" <mat@.discussions.microsoft.com> wrote in message
> news:6601DAC1-672B-4B9B-B32D-F2EC4BEA4A18@.microsoft.com...
> > SQL server is experiencing Memory leaks. So much so that 2gb of ram
> > dissapears and the server stops.
> >
> > THe server has 3 instances of SQL and most operations use linked servers
> > using oledb provider for oracle driver.
> > I have read some artavles that memory leaks can happen when the data type
> > long is returned using ref cursor.
> > The main queries used the syntax select * from openquery(linked
> > server,'oracle query') and are either select or update. Some cases sp's
> > are
> > created with an input parameter and this input parameter is used to update
> > a
> > single row and this sp is called severl times. Some articles i have read
> > also
> > claim that memory leaks also happen when such a statement is repeatedly
> > executed.
> >
> > Can anyone elaborate on this or give any suggestions as to the cause.
> >
> > SQL 2000 SP3 on windows server 2000
> > MDAC is 2.7 SP1
>
> Are you using the Microsoft OLE-DB provider for Oracle, or Oracle's OLE-DB
> provider. I would suggest that you use Oracles OLE-DB provider. It seems
> to work better for me. Notes: This was SQL 2k, Oracle 8i and SQL2k
> ,Oracle 9i. I have not used with Oracle 10g.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"mat" <mat@.discussions.microsoft.com> wrote in message
news:B3D168E8-0B3E-427C-9E8F-5580B56A39AE@.microsoft.com...
> Cheers..
> i'm using Microsofts Provider.
> I'll try it, but do you know what caused these problems
> ?
> "Rick Sawtell" wrote:
>> "mat" <mat@.discussions.microsoft.com> wrote in message
>> news:6601DAC1-672B-4B9B-B32D-F2EC4BEA4A18@.microsoft.com...
>> > SQL server is experiencing Memory leaks. So much so that 2gb of ram
>> > dissapears and the server stops.
>> >
>> > THe server has 3 instances of SQL and most operations use linked
>> > servers
>> > using oledb provider for oracle driver.
>> > I have read some artavles that memory leaks can happen when the data
>> > type
>> > long is returned using ref cursor.
>> > The main queries used the syntax select * from openquery(linked
>> > server,'oracle query') and are either select or update. Some cases sp's
>> > are
>> > created with an input parameter and this input parameter is used to
>> > update
>> > a
>> > single row and this sp is called severl times. Some articles i have
>> > read
>> > also
>> > claim that memory leaks also happen when such a statement is repeatedly
>> > executed.
>> >
>> > Can anyone elaborate on this or give any suggestions as to the cause.
>> >
>> > SQL 2000 SP3 on windows server 2000
>> > MDAC is 2.7 SP1
>>
>> Are you using the Microsoft OLE-DB provider for Oracle, or Oracle's
>> OLE-DB
>> provider. I would suggest that you use Oracles OLE-DB provider. It
>> seems
>> to work better for me. Notes: This was SQL 2k, Oracle 8i and SQL2k
>> ,Oracle 9i. I have not used with Oracle 10g.
>>
>> HTH
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
I don't know what causes the problems, but we have had intermittent problems
using the Microsoft drivers for Oracle in the past. Even the new ADO.NET
provider from MS had some issues here and there. Once we switched our .NET
to Oracle's .NET provider, the problems went away.
You may want to check out the Oracle metalink support site.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks
Are there any conflicts between the Microsoft and aoracle providers.
I could not create a linked server using the oracle provider using
enterprise manger. I was gettint the errormessage
7302 coull not create an instance of ole db provider....
I Managed to create it on the server itself but when i try and view the
tables or execute a query from a remote machine i get the above error
"Rick Sawtell" wrote:
> "mat" <mat@.discussions.microsoft.com> wrote in message
> news:B3D168E8-0B3E-427C-9E8F-5580B56A39AE@.microsoft.com...
> > Cheers..
> >
> > i'm using Microsofts Provider.
> >
> > I'll try it, but do you know what caused these problems
> > ?
> >
> > "Rick Sawtell" wrote:
> >
> >>
> >> "mat" <mat@.discussions.microsoft.com> wrote in message
> >> news:6601DAC1-672B-4B9B-B32D-F2EC4BEA4A18@.microsoft.com...
> >> > SQL server is experiencing Memory leaks. So much so that 2gb of ram
> >> > dissapears and the server stops.
> >> >
> >> > THe server has 3 instances of SQL and most operations use linked
> >> > servers
> >> > using oledb provider for oracle driver.
> >> > I have read some artavles that memory leaks can happen when the data
> >> > type
> >> > long is returned using ref cursor.
> >> > The main queries used the syntax select * from openquery(linked
> >> > server,'oracle query') and are either select or update. Some cases sp's
> >> > are
> >> > created with an input parameter and this input parameter is used to
> >> > update
> >> > a
> >> > single row and this sp is called severl times. Some articles i have
> >> > read
> >> > also
> >> > claim that memory leaks also happen when such a statement is repeatedly
> >> > executed.
> >> >
> >> > Can anyone elaborate on this or give any suggestions as to the cause.
> >> >
> >> > SQL 2000 SP3 on windows server 2000
> >> > MDAC is 2.7 SP1
> >>
> >>
> >> Are you using the Microsoft OLE-DB provider for Oracle, or Oracle's
> >> OLE-DB
> >> provider. I would suggest that you use Oracles OLE-DB provider. It
> >> seems
> >> to work better for me. Notes: This was SQL 2k, Oracle 8i and SQL2k
> >> ,Oracle 9i. I have not used with Oracle 10g.
> >>
> >>
> >> HTH
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >>
> >>
> I don't know what causes the problems, but we have had intermittent problems
> using the Microsoft drivers for Oracle in the past. Even the new ADO.NET
> provider from MS had some issues here and there. Once we switched our .NET
> to Oracle's .NET provider, the problems went away.
> You may want to check out the Oracle metalink support site.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Friday, March 9, 2012

Memory settings/considerations when running multiple instances of SQL Server

Monday, February 20, 2012

Memory Leaks

Memory Leaks

Memory Leaks

Sql Memory Leaks

Blog Archive

About Me