Hello:
I have a server with almost 4 Gigs of RAM. Though SQL
Server's memory config option allows me to allocate all of
the available memory, I understand that Std. Ed. can only
address upto a max. of 2 GB.
So, I set this option to 2 GB and lo and behold, once the
application floodgates were opened, SQL's target and total
server memory consumption rose to and continues to hover
around the 1.7 GB mark.
At this time, I try to run a 'select' against a view and
after a minute of execution, I get error 701 (There is
insufficient system memory to run this query).
My understanding is that, while SQL will take as much
memory as it can and will probably hold on to it as well,
it will release chunks of memory back to the OS for other
processes. In this though, the process call was from
within SQL Server during a low peak application usage time.
To make my long story short, I'm not sure if SQL Server is
holding on to something, which is avoiding this query to
be executed successfully... OR, if the application has
some lingering processes against SQL, which coul be
causing it grief.
I hope this makes sense. And thank you in advance for all
your responses.to be more precise, SE allows 2GB of address space, which
can be mapped to physical memory, but not always.
by default, s2k allocates 256MB of address space to memory
structures other than the buffer cache. In many cases,
only a small amount of the 256M of address space for other
structures actually gets mapped to physical memory.
hence the common observation of 1.74GB of memory actually
being used by sql svr.
what is your query doing?
does it access a very large number of rows?
does it involve a large amount of intermediate data?
it could be your query requires a very large amount of the
memory structures other than the buffer cache.
does your query run immediately after SQL starts up,
before other users drive up memory usage (to the buffer
cache) ie, does this error message only happen after a
very large amount of memory is used by SQL.
if so, then your app would probably run best on a full 64-
bit OS/APP, the AWE is of no use
>--Original Message--
>Hello:
>I have a server with almost 4 Gigs of RAM. Though SQL
>Server's memory config option allows me to allocate all
of
>the available memory, I understand that Std. Ed. can only
>address upto a max. of 2 GB.
>So, I set this option to 2 GB and lo and behold, once the
>application floodgates were opened, SQL's target and
total
>server memory consumption rose to and continues to hover
>around the 1.7 GB mark.
>At this time, I try to run a 'select' against a view and
>after a minute of execution, I get error 701 (There is
>insufficient system memory to run this query).
>My understanding is that, while SQL will take as much
>memory as it can and will probably hold on to it as well,
>it will release chunks of memory back to the OS for other
>processes. In this though, the process call was from
>within SQL Server during a low peak application usage
time.
>To make my long story short, I'm not sure if SQL Server
is
>holding on to something, which is avoiding this query to
>be executed successfully... OR, if the application has
>some lingering processes against SQL, which coul be
>causing it grief.
>I hope this makes sense. And thank you in advance for all
>your responses.
>.
>
Showing posts with label gigs. Show all posts
Showing posts with label gigs. Show all posts
Monday, March 19, 2012
memory usage issues
the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
out of 2.5 gigs. is this normal? is there a way to prevent this? should it
be controlled somehow?From BOL:
--snip
To acquire as much memory as possible without generating excess paging I/O,
each instance of SQL Server sets a target of acquiring memory until free
physical memory on the computer is in the range of 4 MB to 10 MB. This range
was chosen because testing has shown that Windows NT and Windows 2000 have
minimal memory swapping until the memory allocations equal the available
physical memory minus 4 MB. An instance of SQL Server that is processing a
heavy workload keeps the free physical memory at the lower end (4 MB) of the
range; an instance that is processing a light workload keeps the free memory
at the higher end of the range (10 MB).
--snip
This behavior is expected and is designed for maximum performance on a
dedicated SQL server.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
> out of 2.5 gigs. is this normal? is there a way to prevent this? should it
> be controlled somehow?
>|||thank you both.
"harvinder" <hs@.metratech.com> wrote in message
news:001b01c360e4$282db540$a301280a@.phx.gbl...
> sql server will like to take most of the memory if
> required and no other process need memory... generally it
> is good idea to leave it this way(dynamic)...but if u
> want to have control over memory usage u can change the
> parameter max memory from EM or QA.
> Thanks
> --Harvinder
> >--Original Message--
> >the memory usage on our SQL 2000 server keeps creeping up
> to near 1.5 gig
> >out of 2.5 gigs. is this normal? is there a way to
> prevent this? should it
> >be controlled somehow?
> >
> >
> >.
> >|||Additionally SQL will not release memory ( even if no one is logged in)
unless there is < 5 ( between 5 and 10 ) MB of free memory... Why work to
reduce memory utilization if there is no need?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and its
community of SQL Server professionals.
www.sqlpass.org
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
> out of 2.5 gigs. is this normal? is there a way to prevent this? should it
> be controlled somehow?
>|||And just to hammer it home :-)
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
out of 2.5 gigs. is this normal? is there a way to prevent this? should it
be controlled somehow?
out of 2.5 gigs. is this normal? is there a way to prevent this? should it
be controlled somehow?From BOL:
--snip
To acquire as much memory as possible without generating excess paging I/O,
each instance of SQL Server sets a target of acquiring memory until free
physical memory on the computer is in the range of 4 MB to 10 MB. This range
was chosen because testing has shown that Windows NT and Windows 2000 have
minimal memory swapping until the memory allocations equal the available
physical memory minus 4 MB. An instance of SQL Server that is processing a
heavy workload keeps the free physical memory at the lower end (4 MB) of the
range; an instance that is processing a light workload keeps the free memory
at the higher end of the range (10 MB).
--snip
This behavior is expected and is designed for maximum performance on a
dedicated SQL server.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
> out of 2.5 gigs. is this normal? is there a way to prevent this? should it
> be controlled somehow?
>|||thank you both.
"harvinder" <hs@.metratech.com> wrote in message
news:001b01c360e4$282db540$a301280a@.phx.gbl...
> sql server will like to take most of the memory if
> required and no other process need memory... generally it
> is good idea to leave it this way(dynamic)...but if u
> want to have control over memory usage u can change the
> parameter max memory from EM or QA.
> Thanks
> --Harvinder
> >--Original Message--
> >the memory usage on our SQL 2000 server keeps creeping up
> to near 1.5 gig
> >out of 2.5 gigs. is this normal? is there a way to
> prevent this? should it
> >be controlled somehow?
> >
> >
> >.
> >|||Additionally SQL will not release memory ( even if no one is logged in)
unless there is < 5 ( between 5 and 10 ) MB of free memory... Why work to
reduce memory utilization if there is no need?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and its
community of SQL Server professionals.
www.sqlpass.org
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
> out of 2.5 gigs. is this normal? is there a way to prevent this? should it
> be controlled somehow?
>|||And just to hammer it home :-)
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
out of 2.5 gigs. is this normal? is there a way to prevent this? should it
be controlled somehow?
Friday, March 9, 2012
Memory Upgrade and SQL 2000
We have a SQL server that has a failover cluster on our network. We are looking to updrade the memory by adding a couple gigs of ram.
We don't need to upgrade the failover cluster to have the exact amount of memory as the primary do we?
DotNetJunkieHow big of the RAM you have currently? Do you want to or need to change the memory configuration after the upgrade? For example, turning the AWE on.|||joe,
Yes we will need to turn on AWE since we are currently only at 1.5 gigs.
It we break the 2gig barrier, would we then need to have > 2 gig on the failover server?
DotNetJunkie|||Yes, it would have to be the same for the failover. The configuration has to be the same. I don't think you would have problems installing it if it's not. But when the primary fails, the failover would have to take over whatever the primary is doing. That's when you have problems if you don't have the same settings in memory.
We don't need to upgrade the failover cluster to have the exact amount of memory as the primary do we?
DotNetJunkieHow big of the RAM you have currently? Do you want to or need to change the memory configuration after the upgrade? For example, turning the AWE on.|||joe,
Yes we will need to turn on AWE since we are currently only at 1.5 gigs.
It we break the 2gig barrier, would we then need to have > 2 gig on the failover server?
DotNetJunkie|||Yes, it would have to be the same for the failover. The configuration has to be the same. I don't think you would have problems installing it if it's not. But when the primary fails, the failover would have to take over whatever the primary is doing. That's when you have problems if you don't have the same settings in memory.
Friday, February 24, 2012
Memory Paging Issue
We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I know
that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
shown below:
Averaged of 24 hour period:
Page Reads/sec: 39.287
Pages Input/sec: 438.466
I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
2000 Enterprise Edition. I have now been asked to give management some idea
of when this configuration will be to limited and prove to them that this
will eliminate the paging issues.
Anybody have any ideas on how I can intelligently give Management this
information? Does Microsoft have any Capacity Planning White Papers?First, what happens to the application when it starts
paging? What behaviors do you notice? Have you done any
other monitoring?
Since SQL 2000 is a different beast than SQL 7, start your
numbers based on what you see in SQL 7 - with N number of
users doing X, Y, and Z type of work, we max out at 4
proc/2 GB for SQL. But that really is a guesstimate.
What you need to honestly do is understand why your app is
paging - is the code written poorly? Sure, throwing new
OS, SQL, and HW at it may solve your problem, but it may
not. You should definitively prove that. As much as I
love SQL 2K, moving for the sake of moving is never a good
idea.
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||sql server by itself should not cause paging,
are there other applications that could be generating the
paging activity?
are there extended stored procs?
if there a large difference between the memory usage and
virtual memory in task manager?
have you checked the perfmon counters for Process - sql
servr Page File Bytes and Page Faults?
I like many of the features in SQL Server 2000, but agree
with Allan
in any case the SQL Serve version should not be related to
the paging,
your SQL instance may certainly benefit from more memory,
but should show in the physical disk counters, not the
memory paging counters
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||I do know that the code is very poorly written and there are over 200
databases on the server. I have recommended that they need to move some of
the databases to a different server. All 200+ are heavily used. I recently
counted over 2 million queries/Stored Procs that were run in a 1 hour
period. The only thing running on the server is SQL. I have monitored CPU
and it is actually in good shape, the only problem is paging and during the
periods of paging, extreme slowness. So far I have been unable to corelate
the paging with any particular query/stored proc.
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:010f01c3be76$2731a850$a301280a@.phx.gbl...
> First, what happens to the application when it starts
> paging? What behaviors do you notice? Have you done any
> other monitoring?
> Since SQL 2000 is a different beast than SQL 7, start your
> numbers based on what you see in SQL 7 - with N number of
> users doing X, Y, and Z type of work, we max out at 4
> proc/2 GB for SQL. But that really is a guesstimate.
> What you need to honestly do is understand why your app is
> paging - is the code written poorly? Sure, throwing new
> OS, SQL, and HW at it may solve your problem, but it may
> not. You should definitively prove that. As much as I
> love SQL 2K, moving for the sake of moving is never a good
> idea.
>
> >--Original Message--
> >We currently are using SQL 7.0 Standard Edition w/3 gigs
> of ram (yes, I know
> >that SQL is only using 2 gigs) and 4 cpu's. I am having
> paging issues as
> >shown below:
> >
> >Averaged of 24 hour period:
> > Page Reads/sec: 39.287
> > Pages Input/sec: 438.466
> >
> >I have recommended we go to a server with 8 gigs of ram,
> 8 cpu's and SQL
> >2000 Enterprise Edition. I have now been asked to give
> management some idea
> >of when this configuration will be to limited and prove
> to them that this
> >will eliminate the paging issues.
> >
> >Anybody have any ideas on how I can intelligently give
> Management this
> >information? Does Microsoft have any Capacity Planning
> White Papers?
> >
> >
> >.
> >|||SQL should not page in that configuration if it's the only app on there.
Are you seeing high disk queuing on the page file disk and have correleted
spikes in those counters with performance problems as well? If not, I
wouldn't be overly concerned about those numbers.
A couple things that causes fairly steady paging I have seen is file server
or disk-tape backup activities, but those generally won't cause severe
performance problems.
"Mike Johnson" <mj@.noemail.com> wrote in message
news:%23V8An$mvDHA.2464@.TK2MSFTNGP12.phx.gbl...
> We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I
know
> that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
> shown below:
> Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
> I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
> 2000 Enterprise Edition. I have now been asked to give management some
idea
> of when this configuration will be to limited and prove to them that this
> will eliminate the paging issues.
> Anybody have any ideas on how I can intelligently give Management this
> information? Does Microsoft have any Capacity Planning White Papers?
>
that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
shown below:
Averaged of 24 hour period:
Page Reads/sec: 39.287
Pages Input/sec: 438.466
I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
2000 Enterprise Edition. I have now been asked to give management some idea
of when this configuration will be to limited and prove to them that this
will eliminate the paging issues.
Anybody have any ideas on how I can intelligently give Management this
information? Does Microsoft have any Capacity Planning White Papers?First, what happens to the application when it starts
paging? What behaviors do you notice? Have you done any
other monitoring?
Since SQL 2000 is a different beast than SQL 7, start your
numbers based on what you see in SQL 7 - with N number of
users doing X, Y, and Z type of work, we max out at 4
proc/2 GB for SQL. But that really is a guesstimate.
What you need to honestly do is understand why your app is
paging - is the code written poorly? Sure, throwing new
OS, SQL, and HW at it may solve your problem, but it may
not. You should definitively prove that. As much as I
love SQL 2K, moving for the sake of moving is never a good
idea.
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||sql server by itself should not cause paging,
are there other applications that could be generating the
paging activity?
are there extended stored procs?
if there a large difference between the memory usage and
virtual memory in task manager?
have you checked the perfmon counters for Process - sql
servr Page File Bytes and Page Faults?
I like many of the features in SQL Server 2000, but agree
with Allan
in any case the SQL Serve version should not be related to
the paging,
your SQL instance may certainly benefit from more memory,
but should show in the physical disk counters, not the
memory paging counters
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||I do know that the code is very poorly written and there are over 200
databases on the server. I have recommended that they need to move some of
the databases to a different server. All 200+ are heavily used. I recently
counted over 2 million queries/Stored Procs that were run in a 1 hour
period. The only thing running on the server is SQL. I have monitored CPU
and it is actually in good shape, the only problem is paging and during the
periods of paging, extreme slowness. So far I have been unable to corelate
the paging with any particular query/stored proc.
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:010f01c3be76$2731a850$a301280a@.phx.gbl...
> First, what happens to the application when it starts
> paging? What behaviors do you notice? Have you done any
> other monitoring?
> Since SQL 2000 is a different beast than SQL 7, start your
> numbers based on what you see in SQL 7 - with N number of
> users doing X, Y, and Z type of work, we max out at 4
> proc/2 GB for SQL. But that really is a guesstimate.
> What you need to honestly do is understand why your app is
> paging - is the code written poorly? Sure, throwing new
> OS, SQL, and HW at it may solve your problem, but it may
> not. You should definitively prove that. As much as I
> love SQL 2K, moving for the sake of moving is never a good
> idea.
>
> >--Original Message--
> >We currently are using SQL 7.0 Standard Edition w/3 gigs
> of ram (yes, I know
> >that SQL is only using 2 gigs) and 4 cpu's. I am having
> paging issues as
> >shown below:
> >
> >Averaged of 24 hour period:
> > Page Reads/sec: 39.287
> > Pages Input/sec: 438.466
> >
> >I have recommended we go to a server with 8 gigs of ram,
> 8 cpu's and SQL
> >2000 Enterprise Edition. I have now been asked to give
> management some idea
> >of when this configuration will be to limited and prove
> to them that this
> >will eliminate the paging issues.
> >
> >Anybody have any ideas on how I can intelligently give
> Management this
> >information? Does Microsoft have any Capacity Planning
> White Papers?
> >
> >
> >.
> >|||SQL should not page in that configuration if it's the only app on there.
Are you seeing high disk queuing on the page file disk and have correleted
spikes in those counters with performance problems as well? If not, I
wouldn't be overly concerned about those numbers.
A couple things that causes fairly steady paging I have seen is file server
or disk-tape backup activities, but those generally won't cause severe
performance problems.
"Mike Johnson" <mj@.noemail.com> wrote in message
news:%23V8An$mvDHA.2464@.TK2MSFTNGP12.phx.gbl...
> We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I
know
> that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
> shown below:
> Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
> I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
> 2000 Enterprise Edition. I have now been asked to give management some
idea
> of when this configuration will be to limited and prove to them that this
> will eliminate the paging issues.
> Anybody have any ideas on how I can intelligently give Management this
> information? Does Microsoft have any Capacity Planning White Papers?
>
Monday, February 20, 2012
Memory management
I have 12 gigs of memory on each server in a cluster. Each server carries
about the same load. In
this case I should allot 5.5 gigs to each server and leave 1 gig for the
OS? Is 1 gig enough for the OS?
I should use the /3GB switch and set the max server memory to 5.5 for
each server. Is that correct?
thanks
You need to leave enough memory in case all instances land on the same node,
enough for the OS, and enough for other processes and services running on
that node.
An easy way to tell how much you should leave for other processes is to see
how much memory is consumed when SQL Server is not running on that node.
To use up to 3 GB User Mode space, you need to enable the /3GB switch. If
you enable the AWE configuration setting, you can use up to 4 GB. To use
more than 4 GB, you will also need to enable the /PAE switch, unless you are
running Win2K3, which enables that by default.
Keep in mind that the User mode/Kernel mode balance of the first 4 GB is
VIRTUAL, not physical memory, and is for each process; so, by using the /3GB
switch, you've reduced the Kernel mode space from 2 GB to 1 GB. However,
this is VIRTUAL, it can page. So, it says nothing about how much space the
OS will consume.
Typically, the OS will not use much more than 384 to 512 GB of RAM; it is
typically the other User Mode processes that will request memory. If it is
not available in physical RAM, then it will page, including any Kernel mode
allocations. You should make all attempts to keep paging to a minimum. So,
you might have to tweak the settings a little bit.
Also, be aware that if you are addressing more than 3 GB of memory by
enabling the AWE configuration, the memory is physically acquired at startup
and will not be preempted by other processes: the AWE region will be static.
The Buffer Pool will still be dynamic however.
Sincerely,
Anthony Thomas
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:8D9275F7-A6A7-4BC9-8DCE-2F2AE8E7FA26@.microsoft.com...
> I have 12 gigs of memory on each server in a cluster. Each server carries
> about the same load. In
> this case I should allot 5.5 gigs to each server and leave 1 gig for the
> OS? Is 1 gig enough for the OS?
> I should use the /3GB switch and set the max server memory to 5.5 for
> each server. Is that correct?
> thanks
>
|||Thank you for your response. I have only one instance of SQL running on each
box with no other applications. I have 12 gig of memory on each box. I am
going to have each server use half of its available memory in case of a
failover. My question is 12 gigs of memory total per server, is 1 gig enough
for the OS?
"Anthony Thomas" wrote:
> You need to leave enough memory in case all instances land on the same node,
> enough for the OS, and enough for other processes and services running on
> that node.
> An easy way to tell how much you should leave for other processes is to see
> how much memory is consumed when SQL Server is not running on that node.
> To use up to 3 GB User Mode space, you need to enable the /3GB switch. If
> you enable the AWE configuration setting, you can use up to 4 GB. To use
> more than 4 GB, you will also need to enable the /PAE switch, unless you are
> running Win2K3, which enables that by default.
> Keep in mind that the User mode/Kernel mode balance of the first 4 GB is
> VIRTUAL, not physical memory, and is for each process; so, by using the /3GB
> switch, you've reduced the Kernel mode space from 2 GB to 1 GB. However,
> this is VIRTUAL, it can page. So, it says nothing about how much space the
> OS will consume.
> Typically, the OS will not use much more than 384 to 512 GB of RAM; it is
> typically the other User Mode processes that will request memory. If it is
> not available in physical RAM, then it will page, including any Kernel mode
> allocations. You should make all attempts to keep paging to a minimum. So,
> you might have to tweak the settings a little bit.
> Also, be aware that if you are addressing more than 3 GB of memory by
> enabling the AWE configuration, the memory is physically acquired at startup
> and will not be preempted by other processes: the AWE region will be static.
> The Buffer Pool will still be dynamic however.
> Sincerely,
>
> Anthony Thomas
>
> --
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:8D9275F7-A6A7-4BC9-8DCE-2F2AE8E7FA26@.microsoft.com...
>
>
|||There is only one virtual server even though you have two host nodes.
Memory is reserved for instances, not nodes. With 12GB and no non-SQL
applications, I would set SQL to 10GB memory and leave it there.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:F1276980-01D7-4F2D-8C02-A7EDA0A0CB05@.microsoft.com...[vbcol=seagreen]
> Thank you for your response. I have only one instance of SQL running on
> each
> box with no other applications. I have 12 gig of memory on each box. I
> am
> going to have each server use half of its available memory in case of a
> failover. My question is 12 gigs of memory total per server, is 1 gig
> enough
> for the OS?
> "Anthony Thomas" wrote:
|||I may have misstated - I have one instance running on each server (basically
a standard setup) in the cluster. If a failover occurs and I have set each
to 5.5 gigs, I would have enough memory on one server to cover the 2 servers
combined. But my question is, 5.5 + 5.5 = 11, is the remaining 1 gig enough
for the OS?
"Geoff N. Hiten" wrote:
> There is only one virtual server even though you have two host nodes.
> Memory is reserved for instances, not nodes. With 12GB and no non-SQL
> applications, I would set SQL to 10GB memory and leave it there.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:F1276980-01D7-4F2D-8C02-A7EDA0A0CB05@.microsoft.com...
>
>
|||Maybe. I would watch the performance counters and see. If the system
starts paging, you know you have trimmed it too close. Also, you don't have
to run identical memory settings between instances. If one instance has a
lower average page life expectancy, you can increase performance by giving
more memory to one instance. You would end up running a 7-5 or an 8-4 GB
memory allocation split.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:45D22865-05C7-4ABA-96E9-834D7E9A3D2E@.microsoft.com...[vbcol=seagreen]
>I may have misstated - I have one instance running on each server
>(basically
> a standard setup) in the cluster. If a failover occurs and I have set
> each
> to 5.5 gigs, I would have enough memory on one server to cover the 2
> servers
> combined. But my question is, 5.5 + 5.5 = 11, is the remaining 1 gig
> enough
> for the OS?
> "Geoff N. Hiten" wrote:
|||One more question, if I set the 3GB switch will that automatically limit the
OS to using 1 gig of memory? If that is the case and I do not configure the
sum of the max memory on both servers in the cluster to equal 11 gigs (12 gig
on each server minus 1 gig OS) I would be wasting whatever is less than 11
gigs. Is that correct?
"Geoff N. Hiten" wrote:
> Maybe. I would watch the performance counters and see. If the system
> starts paging, you know you have trimmed it too close. Also, you don't have
> to run identical memory settings between instances. If one instance has a
> lower average page life expectancy, you can increase performance by giving
> more memory to one instance. You would end up running a 7-5 or an 8-4 GB
> memory allocation split.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:45D22865-05C7-4ABA-96E9-834D7E9A3D2E@.microsoft.com...
>
>
|||The switch covers Virtual memory for each SQL instance, not necessarily
physical memory from the system.
12GB is the absolute upper limit for /3GB. I would be very careful using it
in your situation.
Again, I would start conservative (2GB for the OS and the rest split between
the SQL instances) and watch what happens. You should be recording
performance monitor counters anyway for long term system analysis. These
records will tell you whether or not you have overcommitted memory.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:A93EB02F-913E-4B62-9738-6D2989B4FA5C@.microsoft.com...[vbcol=seagreen]
> One more question, if I set the 3GB switch will that automatically limit
> the
> OS to using 1 gig of memory? If that is the case and I do not configure
> the
> sum of the max memory on both servers in the cluster to equal 11 gigs (12
> gig
> on each server minus 1 gig OS) I would be wasting whatever is less than 11
> gigs. Is that correct?
> "Geoff N. Hiten" wrote:
|||I think you need to go back and read my initial response again. All memory
is virtual except that allocated exclusively for AWE space. That space MUST
be backed by physical RAM, but all else, 1 GB Kernel space, 2 GB Kernel
space, 2 GB User mode space, or 3 GB User mode space is ALL virtual and is
allocated to EACH process. How much is in physical RAM is dependent on what
is active and what is available, all else is paged.
Since SQL Server wants to keep the Buffer Cache in memory, although it may
scale it back, it will NEVER page it. SQL Server will, however, page
sections from the other four memory managers.
Again, run the OS and other processes without SQL Server running to get an
estimate of how much PHYSICAL RAM those pieces would consume if not
constrained by SQL Server.
Sincerely,
Anthony Thomas
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:A93EB02F-913E-4B62-9738-6D2989B4FA5C@.microsoft.com...
> One more question, if I set the 3GB switch will that automatically limit
the
> OS to using 1 gig of memory? If that is the case and I do not configure
the
> sum of the max memory on both servers in the cluster to equal 11 gigs (12
gig[vbcol=seagreen]
> on each server minus 1 gig OS) I would be wasting whatever is less than 11
> gigs. Is that correct?
> "Geoff N. Hiten" wrote:
have[vbcol=seagreen]
a[vbcol=seagreen]
giving[vbcol=seagreen]
GB[vbcol=seagreen]
non-SQL[vbcol=seagreen]
running[vbcol=seagreen]
box.[vbcol=seagreen]
of a[vbcol=seagreen]
gig[vbcol=seagreen]
same[vbcol=seagreen]
running[vbcol=seagreen]
is[vbcol=seagreen]
switch.[vbcol=seagreen]
To[vbcol=seagreen]
unless[vbcol=seagreen]
GB[vbcol=seagreen]
using[vbcol=seagreen]
RAM; it[vbcol=seagreen]
If[vbcol=seagreen]
Kernel[vbcol=seagreen]
by[vbcol=seagreen]
at[vbcol=seagreen]
be[vbcol=seagreen]
server[vbcol=seagreen]
for[vbcol=seagreen]
5.5[vbcol=seagreen]
about the same load. In
this case I should allot 5.5 gigs to each server and leave 1 gig for the
OS? Is 1 gig enough for the OS?
I should use the /3GB switch and set the max server memory to 5.5 for
each server. Is that correct?
thanks
You need to leave enough memory in case all instances land on the same node,
enough for the OS, and enough for other processes and services running on
that node.
An easy way to tell how much you should leave for other processes is to see
how much memory is consumed when SQL Server is not running on that node.
To use up to 3 GB User Mode space, you need to enable the /3GB switch. If
you enable the AWE configuration setting, you can use up to 4 GB. To use
more than 4 GB, you will also need to enable the /PAE switch, unless you are
running Win2K3, which enables that by default.
Keep in mind that the User mode/Kernel mode balance of the first 4 GB is
VIRTUAL, not physical memory, and is for each process; so, by using the /3GB
switch, you've reduced the Kernel mode space from 2 GB to 1 GB. However,
this is VIRTUAL, it can page. So, it says nothing about how much space the
OS will consume.
Typically, the OS will not use much more than 384 to 512 GB of RAM; it is
typically the other User Mode processes that will request memory. If it is
not available in physical RAM, then it will page, including any Kernel mode
allocations. You should make all attempts to keep paging to a minimum. So,
you might have to tweak the settings a little bit.
Also, be aware that if you are addressing more than 3 GB of memory by
enabling the AWE configuration, the memory is physically acquired at startup
and will not be preempted by other processes: the AWE region will be static.
The Buffer Pool will still be dynamic however.
Sincerely,
Anthony Thomas
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:8D9275F7-A6A7-4BC9-8DCE-2F2AE8E7FA26@.microsoft.com...
> I have 12 gigs of memory on each server in a cluster. Each server carries
> about the same load. In
> this case I should allot 5.5 gigs to each server and leave 1 gig for the
> OS? Is 1 gig enough for the OS?
> I should use the /3GB switch and set the max server memory to 5.5 for
> each server. Is that correct?
> thanks
>
|||Thank you for your response. I have only one instance of SQL running on each
box with no other applications. I have 12 gig of memory on each box. I am
going to have each server use half of its available memory in case of a
failover. My question is 12 gigs of memory total per server, is 1 gig enough
for the OS?
"Anthony Thomas" wrote:
> You need to leave enough memory in case all instances land on the same node,
> enough for the OS, and enough for other processes and services running on
> that node.
> An easy way to tell how much you should leave for other processes is to see
> how much memory is consumed when SQL Server is not running on that node.
> To use up to 3 GB User Mode space, you need to enable the /3GB switch. If
> you enable the AWE configuration setting, you can use up to 4 GB. To use
> more than 4 GB, you will also need to enable the /PAE switch, unless you are
> running Win2K3, which enables that by default.
> Keep in mind that the User mode/Kernel mode balance of the first 4 GB is
> VIRTUAL, not physical memory, and is for each process; so, by using the /3GB
> switch, you've reduced the Kernel mode space from 2 GB to 1 GB. However,
> this is VIRTUAL, it can page. So, it says nothing about how much space the
> OS will consume.
> Typically, the OS will not use much more than 384 to 512 GB of RAM; it is
> typically the other User Mode processes that will request memory. If it is
> not available in physical RAM, then it will page, including any Kernel mode
> allocations. You should make all attempts to keep paging to a minimum. So,
> you might have to tweak the settings a little bit.
> Also, be aware that if you are addressing more than 3 GB of memory by
> enabling the AWE configuration, the memory is physically acquired at startup
> and will not be preempted by other processes: the AWE region will be static.
> The Buffer Pool will still be dynamic however.
> Sincerely,
>
> Anthony Thomas
>
> --
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:8D9275F7-A6A7-4BC9-8DCE-2F2AE8E7FA26@.microsoft.com...
>
>
|||There is only one virtual server even though you have two host nodes.
Memory is reserved for instances, not nodes. With 12GB and no non-SQL
applications, I would set SQL to 10GB memory and leave it there.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:F1276980-01D7-4F2D-8C02-A7EDA0A0CB05@.microsoft.com...[vbcol=seagreen]
> Thank you for your response. I have only one instance of SQL running on
> each
> box with no other applications. I have 12 gig of memory on each box. I
> am
> going to have each server use half of its available memory in case of a
> failover. My question is 12 gigs of memory total per server, is 1 gig
> enough
> for the OS?
> "Anthony Thomas" wrote:
|||I may have misstated - I have one instance running on each server (basically
a standard setup) in the cluster. If a failover occurs and I have set each
to 5.5 gigs, I would have enough memory on one server to cover the 2 servers
combined. But my question is, 5.5 + 5.5 = 11, is the remaining 1 gig enough
for the OS?
"Geoff N. Hiten" wrote:
> There is only one virtual server even though you have two host nodes.
> Memory is reserved for instances, not nodes. With 12GB and no non-SQL
> applications, I would set SQL to 10GB memory and leave it there.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:F1276980-01D7-4F2D-8C02-A7EDA0A0CB05@.microsoft.com...
>
>
|||Maybe. I would watch the performance counters and see. If the system
starts paging, you know you have trimmed it too close. Also, you don't have
to run identical memory settings between instances. If one instance has a
lower average page life expectancy, you can increase performance by giving
more memory to one instance. You would end up running a 7-5 or an 8-4 GB
memory allocation split.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:45D22865-05C7-4ABA-96E9-834D7E9A3D2E@.microsoft.com...[vbcol=seagreen]
>I may have misstated - I have one instance running on each server
>(basically
> a standard setup) in the cluster. If a failover occurs and I have set
> each
> to 5.5 gigs, I would have enough memory on one server to cover the 2
> servers
> combined. But my question is, 5.5 + 5.5 = 11, is the remaining 1 gig
> enough
> for the OS?
> "Geoff N. Hiten" wrote:
|||One more question, if I set the 3GB switch will that automatically limit the
OS to using 1 gig of memory? If that is the case and I do not configure the
sum of the max memory on both servers in the cluster to equal 11 gigs (12 gig
on each server minus 1 gig OS) I would be wasting whatever is less than 11
gigs. Is that correct?
"Geoff N. Hiten" wrote:
> Maybe. I would watch the performance counters and see. If the system
> starts paging, you know you have trimmed it too close. Also, you don't have
> to run identical memory settings between instances. If one instance has a
> lower average page life expectancy, you can increase performance by giving
> more memory to one instance. You would end up running a 7-5 or an 8-4 GB
> memory allocation split.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:45D22865-05C7-4ABA-96E9-834D7E9A3D2E@.microsoft.com...
>
>
|||The switch covers Virtual memory for each SQL instance, not necessarily
physical memory from the system.
12GB is the absolute upper limit for /3GB. I would be very careful using it
in your situation.
Again, I would start conservative (2GB for the OS and the rest split between
the SQL instances) and watch what happens. You should be recording
performance monitor counters anyway for long term system analysis. These
records will tell you whether or not you have overcommitted memory.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:A93EB02F-913E-4B62-9738-6D2989B4FA5C@.microsoft.com...[vbcol=seagreen]
> One more question, if I set the 3GB switch will that automatically limit
> the
> OS to using 1 gig of memory? If that is the case and I do not configure
> the
> sum of the max memory on both servers in the cluster to equal 11 gigs (12
> gig
> on each server minus 1 gig OS) I would be wasting whatever is less than 11
> gigs. Is that correct?
> "Geoff N. Hiten" wrote:
|||I think you need to go back and read my initial response again. All memory
is virtual except that allocated exclusively for AWE space. That space MUST
be backed by physical RAM, but all else, 1 GB Kernel space, 2 GB Kernel
space, 2 GB User mode space, or 3 GB User mode space is ALL virtual and is
allocated to EACH process. How much is in physical RAM is dependent on what
is active and what is available, all else is paged.
Since SQL Server wants to keep the Buffer Cache in memory, although it may
scale it back, it will NEVER page it. SQL Server will, however, page
sections from the other four memory managers.
Again, run the OS and other processes without SQL Server running to get an
estimate of how much PHYSICAL RAM those pieces would consume if not
constrained by SQL Server.
Sincerely,
Anthony Thomas
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:A93EB02F-913E-4B62-9738-6D2989B4FA5C@.microsoft.com...
> One more question, if I set the 3GB switch will that automatically limit
the
> OS to using 1 gig of memory? If that is the case and I do not configure
the
> sum of the max memory on both servers in the cluster to equal 11 gigs (12
gig[vbcol=seagreen]
> on each server minus 1 gig OS) I would be wasting whatever is less than 11
> gigs. Is that correct?
> "Geoff N. Hiten" wrote:
have[vbcol=seagreen]
a[vbcol=seagreen]
giving[vbcol=seagreen]
GB[vbcol=seagreen]
non-SQL[vbcol=seagreen]
running[vbcol=seagreen]
box.[vbcol=seagreen]
of a[vbcol=seagreen]
gig[vbcol=seagreen]
same[vbcol=seagreen]
running[vbcol=seagreen]
is[vbcol=seagreen]
switch.[vbcol=seagreen]
To[vbcol=seagreen]
unless[vbcol=seagreen]
GB[vbcol=seagreen]
using[vbcol=seagreen]
RAM; it[vbcol=seagreen]
If[vbcol=seagreen]
Kernel[vbcol=seagreen]
by[vbcol=seagreen]
at[vbcol=seagreen]
be[vbcol=seagreen]
server[vbcol=seagreen]
for[vbcol=seagreen]
5.5[vbcol=seagreen]
Subscribe to:
Posts (Atom)