Memory allocation in MS-SQL 2000 instances

Hi,
From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
instances. It is a nice machine with 4 Gb of memory but the memory allocation
is very weird:
Instance A: 1400Mb
Instance B: 1000Mb
Instance C: 80Mb (!)
Instance C is performing badly under a bit of pressure which seems not strange
considering these allocations.
With that in mind, is there a way to check and re-allocate memory? I'd like to
see if the instances really need these amounts of memory and if not, to move
some over to other instances.
Thanks!
Dries Besselsdries wrote:
> Hi,
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory allocation
> is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not strange
> considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd like to
> see if the instances really need these amounts of memory and if not, to move
> some over to other instances.
> Thanks!
> Dries Bessels
Could it be that there simply isn't any more memory for the C instance?
You doesn't specify which SQL and OS versions you are running, but there
could be settings that leave e.g. 1 GB for the OS and then you haven't
got any more left for SQL. SQL server will grap memory but doesn't
release it again. This means that if the first two instances already had
grapped the memory then there is nothing left for instance C.
If you have the change, you could try to re-boot the server and then
check if the memory consumption becomes more equal between the three
instances.
Regards
Steen|||dries (dries@.bessels.stop-spam.org) writes:
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory
> allocation is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not
> strange considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd
> like to see if the instances really need these amounts of memory and if
> not, to move some over to other instances.
You cannot reallocate memory between instances per se, but you configure
per server the max amount of memory an instance can use.
SQL Server's general strategy on memory is to grab as much it needs
and that is available. The use for this memory is cache. The more data
that is in cache, the faster data can be accessed. When there is competition
for memory, SQL Server will yield. In this it appears that the three
instances are competing.
3 instance on 4GB may be a tad low. You are running with the /3GB switch
in window?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Memory allocation in MS-SQL 2000 instances

Hi,
From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
instances. It is a nice machine with 4 Gb of memory but the memory allocatio
n
is very weird:
Instance A: 1400Mb
Instance B: 1000Mb
Instance C: 80Mb (!)
Instance C is performing badly under a bit of pressure which seems not stran
ge
considering these allocations.
With that in mind, is there a way to check and re-allocate memory? I'd like
to
see if the instances really need these amounts of memory and if not, to move
some over to other instances.
Thanks!
Dries Besselsdries wrote:
> Hi,
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory allocat
ion
> is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not str
ange
> considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd lik
e to
> see if the instances really need these amounts of memory and if not, to mo
ve
> some over to other instances.
> Thanks!
> Dries Bessels
Could it be that there simply isn't any more memory for the C instance?
You doesn't specify which SQL and OS versions you are running, but there
could be settings that leave e.g. 1 GB for the OS and then you haven't
got any more left for SQL. SQL server will grap memory but doesn't
release it again. This means that if the first two instances already had
grapped the memory then there is nothing left for instance C.
If you have the change, you could try to re-boot the server and then
check if the memory consumption becomes more equal between the three
instances.
Regards
Steen|||dries (dries@.bessels.stop-spam.org) writes:
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory
> allocation is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not
> strange considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd
> like to see if the instances really need these amounts of memory and if
> not, to move some over to other instances.
You cannot reallocate memory between instances per se, but you configure
per server the max amount of memory an instance can use.
SQL Server's general strategy on memory is to grab as much it needs
and that is available. The use for this memory is cache. The more data
that is in cache, the faster data can be accessed. When there is competition
for memory, SQL Server will yield. In this it appears that the three
instances are competing.
3 instance on 4GB may be a tad low. You are running with the /3GB switch
in window?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Memory allocation in MS-SQL 2000 instances

Hi,
From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
instances. It is a nice machine with 4 Gb of memory but the memory allocation
is very weird:
Instance A: 1400Mb
Instance B: 1000Mb
Instance C: 80Mb (!)
Instance C is performing badly under a bit of pressure which seems not strange
considering these allocations.
With that in mind, is there a way to check and re-allocate memory? I'd like to
see if the instances really need these amounts of memory and if not, to move
some over to other instances.
Thanks!
Dries Bessels
dries wrote:
> Hi,
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory allocation
> is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not strange
> considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd like to
> see if the instances really need these amounts of memory and if not, to move
> some over to other instances.
> Thanks!
> Dries Bessels
Could it be that there simply isn't any more memory for the C instance?
You doesn't specify which SQL and OS versions you are running, but there
could be settings that leave e.g. 1 GB for the OS and then you haven't
got any more left for SQL. SQL server will grap memory but doesn't
release it again. This means that if the first two instances already had
grapped the memory then there is nothing left for instance C.
If you have the change, you could try to re-boot the server and then
check if the memory consumption becomes more equal between the three
instances.
Regards
Steen
|||dries (dries@.bessels.stop-spam.org) writes:
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory
> allocation is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not
> strange considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd
> like to see if the instances really need these amounts of memory and if
> not, to move some over to other instances.
You cannot reallocate memory between instances per se, but you configure
per server the max amount of memory an instance can use.
SQL Server's general strategy on memory is to grab as much it needs
and that is available. The use for this memory is cache. The more data
that is in cache, the faster data can be accessed. When there is competition
for memory, SQL Server will yield. In this it appears that the three
instances are competing.
3 instance on 4GB may be a tad low. You are running with the /3GB switch
in window?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Memory allocation in MS-SQL 2000 instances

Hi,
From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
instances. It is a nice machine with 4 Gb of memory but the memory allocation
is very weird:
Instance A: 1400Mb
Instance B: 1000Mb
Instance C: 80Mb (!)

Instance C is performing badly under a bit of pressure which seems not strange
considering these allocations.

With that in mind, is there a way to check and re-allocate memory? I'd like to
see if the instances really need these amounts of memory and if not, to move
some over to other instances.

Thanks!
Dries Besselsdries (dries@.bessels.stop-spam.org) writes:
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory
> allocation is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
> Instance C is performing badly under a bit of pressure which seems not
> strange considering these allocations.
> With that in mind, is there a way to check and re-allocate memory? I'd
> like to see if the instances really need these amounts of memory and if
> not, to move some over to other instances.

You cannot reallocate memory between instances per se, but you configure
per server the max amount of memory an instance can use.

SQL Server's general strategy on memory is to grab as much it needs
and that is available. The use for this memory is cache. The more data
that is in cache, the faster data can be accessed. When there is competition
for memory, SQL Server will yield. In this it appears that the three
instances are competing.

3 instance on 4GB may be a tad low. You are running with the /3GB switch
in window?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Memory Allocation for Jobs Running under SQL Agent

I use the job processing functionality to do a variety of things by running
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I run
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
Mike Mattix
CP Kelco, Inc
Okmulgee, OK
Hi
No. But how much RAM do you have and how much is allocated the SQL Server?
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/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK
|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> 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/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>
>

Memory Allocation for Jobs Running under SQL Agent

I use the job processing functionality to do a variety of things by running
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in
a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I ru
n
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
Mike Mattix
CP Kelco, Inc
Okmulgee, OKHi
No. But how much RAM do you have and how much is allocated the SQL Server?
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/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> 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/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>
>

Memory Allocation for Jobs Running under SQL Agent

I use the job processing functionality to do a variety of things by running
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I run
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
--
Mike Mattix
CP Kelco, Inc
Okmulgee, OKHi
No. But how much RAM do you have and how much is allocated the SQL Server?
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/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> 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/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
> >I use the job processing functionality to do a variety of things by running
> > DTS packages. One of my DTS packages has been failing as run a .NET
> > application to convert/modify the data extracted from an Oracle Database
> > in a
> > previous step. I get an exception "There is insufficient system memory to
> > run this query." when the application runs under the Agent but not when I
> > run
> > the same package interactively. Is there some restriction on memory use
> > for
> > jobs running under the SQL Agent? If this is so is there someway to
> > modify
> > it?
> >
> > thanks for any assistance
> >
> > --
> > Mike Mattix
> > CP Kelco, Inc
> > Okmulgee, OK
>
>