Confused about memory limits

Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
shows 3,669,532KB of RAM. SQL Server 2000 SP3.
I'm a bit confused as to if SQL server can use more memory or not.
In task manager, I see that sql server is using 1.7 gigs of memory (sounds
like 2gb to me).. which I'm reading is the maximum. Even though the server
properties have the minimum set at 0 and the maximum set at 38xxMB.
All the discussion says that setting "awe enabled" will allow SQL Server to
take advantage of memory on servers with more then 4gb of memory.
Well.. I don't have more then 4gb. I have 4. Can I make it use more memory?
Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
causes of some recent performance issues we've been having.
Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
in my boot.ini at all, I don't think I need that?
Thanks,
GregI would add /3GB but that is only if I had a memory bottleneck.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"greg" <greg@.discussions.microsoft.com> wrote in message
news:706F9484-62C7-4BDC-8AD5-F3079AA77828@.microsoft.com...
> Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
> shows 3,669,532KB of RAM. SQL Server 2000 SP3.
> I'm a bit confused as to if SQL server can use more memory or not.
> In task manager, I see that sql server is using 1.7 gigs of memory (sounds
> like 2gb to me).. which I'm reading is the maximum. Even though the server
> properties have the minimum set at 0 and the maximum set at 38xxMB.
> All the discussion says that setting "awe enabled" will allow SQL Server
> to
> take advantage of memory on servers with more then 4gb of memory.
> Well.. I don't have more then 4gb. I have 4. Can I make it use more
> memory?
> Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
> causes of some recent performance issues we've been having.
> Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
> in my boot.ini at all, I don't think I need that?
>
> Thanks,
> Greg|||If your SQL Server is Standard edition, you are capped at 2gb
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:706F9484-62C7-4BDC-8AD5-F3079AA77828@.microsoft.com...
> Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
> shows 3,669,532KB of RAM. SQL Server 2000 SP3.
> I'm a bit confused as to if SQL server can use more memory or not.
> In task manager, I see that sql server is using 1.7 gigs of memory (sounds
> like 2gb to me).. which I'm reading is the maximum. Even though the server
> properties have the minimum set at 0 and the maximum set at 38xxMB.
> All the discussion says that setting "awe enabled" will allow SQL Server
> to
> take advantage of memory on servers with more then 4gb of memory.
> Well.. I don't have more then 4gb. I have 4. Can I make it use more
> memory?
> Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
> causes of some recent performance issues we've been having.
> Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
> in my boot.ini at all, I don't think I need that?
>
> Thanks,
> Greg|||What edition of SQL Server 2000 are you using. If it is Standard Edition, you
are limited to 2GB Max.
"greg" wrote:
> Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
> shows 3,669,532KB of RAM. SQL Server 2000 SP3.
> I'm a bit confused as to if SQL server can use more memory or not.
> In task manager, I see that sql server is using 1.7 gigs of memory (sounds
> like 2gb to me).. which I'm reading is the maximum. Even though the server
> properties have the minimum set at 0 and the maximum set at 38xxMB.
> All the discussion says that setting "awe enabled" will allow SQL Server to
> take advantage of memory on servers with more then 4gb of memory.
> Well.. I don't have more then 4gb. I have 4. Can I make it use more memory?
> Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
> causes of some recent performance issues we've been having.
> Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
> in my boot.ini at all, I don't think I need that?
>
> Thanks,
> Greg|||SQL Server 2000, Enterprise Edition.
Just found this option that says I should *not* be using 'awe enabled'...
damn this is confusing!
http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx
Help?|||I don't believe this article does suggest that you disable AWE - what is
your reasoning behind this conclusion?
You'll need AWE enabled if you're wanting SQL Server to see more than a few
GB of memory.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:F3795964-7DA6-46DE-8718-9D5FE1BC99B6@.microsoft.com...
> SQL Server 2000, Enterprise Edition.
> Just found this option that says I should *not* be using 'awe enabled'...
> damn this is confusing!
> http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx
>
> Help?
>|||Added /3gb to boot.ini and rebooted this weekend, now using 2.7 gigs of
memory! Sweet.
CPU usage is up now so I guess that's good. Less time thrashing/waiting on
the disk, more time doing actual work.

Confirming Processor Usage

After installing the SQL Server 2000 Standard Edition, how
can one tell that all processors and memory are being used
by SQL Server?
I realize that applications should receive a faster
response time, but there must be a way to check on the
server box itself (or within SQL Server) that it is
utilizing all available processors (or whatever the
maximum number of processors that SQL Server 2000 Standard
Edition is capable of using).
Can someone please let me know as I'm hard pressed right
now to find anything out there that indicates a way of
checking this item.
Thanks.
HouseYou can use the performance monitor tool that comes with Windows. I think
it's actually called System monitor now. There are lots of counters related
to the processors, disks, sql server etc. These may get you going:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Jason House" <jason.house@.ns.sympatico.ca> wrote in message
news:052301c34be0$0143bd80$a101280a@.phx.gbl...
> After installing the SQL Server 2000 Standard Edition, how
> can one tell that all processors and memory are being used
> by SQL Server?
> I realize that applications should receive a faster
> response time, but there must be a way to check on the
> server box itself (or within SQL Server) that it is
> utilizing all available processors (or whatever the
> maximum number of processors that SQL Server 2000 Standard
> Edition is capable of using).
> Can someone please let me know as I'm hard pressed right
> now to find anything out there that indicates a way of
> checking this item.
> Thanks.
> House

Configuring SQL to use AWE

I have configured a SQL200 instance to use 5gb of my server's memory.
At startup the log indicates that AWE is being used, and I see
that the memory is being grabbed when the instance starts.
But in the log indicates that the Working Set Size is set to 1 GB.
Does this mean anything when using AWE..
Am I getting full use of AWE on my instance. How can I tell....
Thanks
Paul
Check the Buffer Manager counter in Perfmon. There are a set of
events/classes related to AWE which you can check.
Anith

Configuring SQL to use AWE

I have configured a SQL200 instance to use 5gb of my server's memory.
At startup the log indicates that AWE is being used, and I see
that the memory is being grabbed when the instance starts.
But in the log indicates that the Working Set Size is set to 1 GB.
Does this mean anything when using AWE..
Am I getting full use of AWE on my instance. How can I tell....
Thanks
PaulCheck the Buffer Manager counter in Perfmon. There are a set of
events/classes related to AWE which you can check.
--
Anith

Configuring SQL to use AWE

I have configured a SQL200 instance to use 5gb of my server's memory.
At startup the log indicates that AWE is being used, and I see
that the memory is being grabbed when the instance starts.
But in the log indicates that the Working Set Size is set to 1 GB.
Does this mean anything when using AWE..
Am I getting full use of AWE on my instance. How can I tell....
Thanks
PaulCheck the Buffer Manager counter in Perfmon. There are a set of
events/classes related to AWE which you can check.
Anith

configuring sql server (best practice)

Hello,
I was wondering how i could optimize sql server for best practices.
I have looked at the parameters, but what can i do with the memory resources
for example.
Zeke
It depends on what version of SQL Server and what specific
issues you are seeing or are trying to address. For the most
part, you'd want to leave the server configuration settings
alone on SQL Server 7 and above. There are some situations
where modifying the default settings can help but these are
generally implemented to address specific issues and should
be thoroughly tested before implementing on a production
server. You can find some information on the server
configuration settings in the following articles:
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
Tips for Performance Tuning
SQL Server's Configuration Settings
http://www.sql-server-performance.co...n_settings.asp
-Sue
On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hello,
>I was wondering how i could optimize sql server for best practices.
>I have looked at the parameters, but what can i do with the memory resources
>for example.
>Zeke
>
|||Hi Sue,
I'm working with sql server 2000 and it runs with other large dbms's like
oracle. What i would like to achieve is finetune the server so that both
databases runs fully optimized.
Greetings,
Zeke
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com...
> It depends on what version of SQL Server and what specific
> issues you are seeing or are trying to address. For the most
> part, you'd want to leave the server configuration settings
> alone on SQL Server 7 and above. There are some situations
> where modifying the default settings can help but these are
> generally implemented to address specific issues and should
> be thoroughly tested before implementing on a production
> server. You can find some information on the server
> configuration settings in the following articles:
> HOW TO: Determine Proper SQL Server Configuration Settings
> http://support.microsoft.com/?id=319942
> Tips for Performance Tuning
> SQL Server's Configuration Settings
>
http://www.sql-server-performance.co...n_settings.asp[vbcol=seagreen]
> -Sue
> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
> <ezekiel@.lycos.nl> wrote:
resources
>
|||You wouldn't necessarily use the server configurations to
fine tune things. The default settings work fine in most
cases, changing the settings just to try tweaking things
generally causes more problems. Server settings generally
isn't the first place to go in trying to tuning things up.
You'd want to look at the applications using the databases,
run profiler, pay to indexing/index usage, do overall
monitoring to check for potential server bottlenecks using
PerfMon, etc.
You may want to go through some of the articles at:
www.sql-server-performance.com
-Sue
On Tue, 1 Jun 2004 14:27:12 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hi Sue,
>I'm working with sql server 2000 and it runs with other large dbms's like
>oracle. What i would like to achieve is finetune the server so that both
>databases runs fully optimized.
>Greetings,
>Zeke
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com.. .
>http://www.sql-server-performance.co...n_settings.asp
>resources
>
|||Ezekil,
Have you looked into Best Practices Analyzer? It has just been released:
http://www.microsoft.com/downloads/d...displaylang=en
Tinyurl:
http://tinyurl.com/35cb4
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Ezekil wrote:

> Hello,
> I was wondering how i could optimize sql server for best practices.
> I have looked at the parameters, but what can i do with the memory resources
> for example.
> Zeke
>

configuring sql server (best practice)

Hello,
I was wondering how i could optimize sql server for best practices.
I have looked at the parameters, but what can i do with the memory resources
for example.
ZekeIt depends on what version of SQL Server and what specific
issues you are seeing or are trying to address. For the most
part, you'd want to leave the server configuration settings
alone on SQL Server 7 and above. There are some situations
where modifying the default settings can help but these are
generally implemented to address specific issues and should
be thoroughly tested before implementing on a production
server. You can find some information on the server
configuration settings in the following articles:
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
Tips for Performance Tuning
SQL Server's Configuration Settings
http://www.sql-server-performance.c...on_settings.asp
-Sue
On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hello,
>I was wondering how i could optimize sql server for best practices.
>I have looked at the parameters, but what can i do with the memory resource
s
>for example.
>Zeke
>|||Hi Sue,
I'm working with sql server 2000 and it runs with other large dbms's like
oracle. What i would like to achieve is finetune the server so that both
databases runs fully optimized.
Greetings,
Zeke
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.
4ax.com...
> It depends on what version of SQL Server and what specific
> issues you are seeing or are trying to address. For the most
> part, you'd want to leave the server configuration settings
> alone on SQL Server 7 and above. There are some situations
> where modifying the default settings can help but these are
> generally implemented to address specific issues and should
> be thoroughly tested before implementing on a production
> server. You can find some information on the server
> configuration settings in the following articles:
> HOW TO: Determine Proper SQL Server Configuration Settings
> http://support.microsoft.com/?id=319942
> Tips for Performance Tuning
> SQL Server's Configuration Settings
>
http://www.sql-server-performance.c...on_settings.asp
> -Sue
> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
> <ezekiel@.lycos.nl> wrote:
>
resources[vbcol=seagreen]
>|||You wouldn't necessarily use the server configurations to
fine tune things. The default settings work fine in most
cases, changing the settings just to try tweaking things
generally causes more problems. Server settings generally
isn't the first place to go in trying to tuning things up.
You'd want to look at the applications using the databases,
run profiler, pay to indexing/index usage, do overall
monitoring to check for potential server bottlenecks using
PerfMon, etc.
You may want to go through some of the articles at:
www.sql-server-performance.com
-Sue
On Tue, 1 Jun 2004 14:27:12 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hi Sue,
>I'm working with sql server 2000 and it runs with other large dbms's like
>oracle. What i would like to achieve is finetune the server so that both
>databases runs fully optimized.
>Greetings,
>Zeke
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.
4ax.com...
>http://www.sql-server-performance.c...on_settings.asp
>resources
>|||Ezekil,
Have you looked into Best Practices Analyzer? It has just been released:
http://www.microsoft.com/downloads/...&displaylang=en
Tinyurl:
http://tinyurl.com/35cb4
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Ezekil wrote:

> Hello,
> I was wondering how i could optimize sql server for best practices.
> I have looked at the parameters, but what can i do with the memory resourc
es
> for example.
> Zeke
>

configuring sql server (best practice)

Hello,
I was wondering how i could optimize sql server for best practices.
I have looked at the parameters, but what can i do with the memory resources
for example.
ZekeIt depends on what version of SQL Server and what specific
issues you are seeing or are trying to address. For the most
part, you'd want to leave the server configuration settings
alone on SQL Server 7 and above. There are some situations
where modifying the default settings can help but these are
generally implemented to address specific issues and should
be thoroughly tested before implementing on a production
server. You can find some information on the server
configuration settings in the following articles:
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
Tips for Performance Tuning
SQL Server's Configuration Settings
http://www.sql-server-performance.com/sql_server_configuration_settings.asp
-Sue
On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekiël"
<ezekiel@.lycos.nl> wrote:
>Hello,
>I was wondering how i could optimize sql server for best practices.
>I have looked at the parameters, but what can i do with the memory resources
>for example.
>Zeke
>|||Hi Sue,
I'm working with sql server 2000 and it runs with other large dbms's like
oracle. What i would like to achieve is finetune the server so that both
databases runs fully optimized.
Greetings,
Zeke
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com...
> It depends on what version of SQL Server and what specific
> issues you are seeing or are trying to address. For the most
> part, you'd want to leave the server configuration settings
> alone on SQL Server 7 and above. There are some situations
> where modifying the default settings can help but these are
> generally implemented to address specific issues and should
> be thoroughly tested before implementing on a production
> server. You can find some information on the server
> configuration settings in the following articles:
> HOW TO: Determine Proper SQL Server Configuration Settings
> http://support.microsoft.com/?id=319942
> Tips for Performance Tuning
> SQL Server's Configuration Settings
>
http://www.sql-server-performance.com/sql_server_configuration_settings.asp
> -Sue
> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekiël"
> <ezekiel@.lycos.nl> wrote:
> >Hello,
> >
> >I was wondering how i could optimize sql server for best practices.
> >
> >I have looked at the parameters, but what can i do with the memory
resources
> >for example.
> >
> >Zeke
> >
>|||You wouldn't necessarily use the server configurations to
fine tune things. The default settings work fine in most
cases, changing the settings just to try tweaking things
generally causes more problems. Server settings generally
isn't the first place to go in trying to tuning things up.
You'd want to look at the applications using the databases,
run profiler, pay to indexing/index usage, do overall
monitoring to check for potential server bottlenecks using
PerfMon, etc.
You may want to go through some of the articles at:
www.sql-server-performance.com
-Sue
On Tue, 1 Jun 2004 14:27:12 +0200, "Ezekiël"
<ezekiel@.lycos.nl> wrote:
>Hi Sue,
>I'm working with sql server 2000 and it runs with other large dbms's like
>oracle. What i would like to achieve is finetune the server so that both
>databases runs fully optimized.
>Greetings,
>Zeke
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com...
>> It depends on what version of SQL Server and what specific
>> issues you are seeing or are trying to address. For the most
>> part, you'd want to leave the server configuration settings
>> alone on SQL Server 7 and above. There are some situations
>> where modifying the default settings can help but these are
>> generally implemented to address specific issues and should
>> be thoroughly tested before implementing on a production
>> server. You can find some information on the server
>> configuration settings in the following articles:
>> HOW TO: Determine Proper SQL Server Configuration Settings
>> http://support.microsoft.com/?id=319942
>> Tips for Performance Tuning
>> SQL Server's Configuration Settings
>http://www.sql-server-performance.com/sql_server_configuration_settings.asp
>> -Sue
>> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekiël"
>> <ezekiel@.lycos.nl> wrote:
>> >Hello,
>> >
>> >I was wondering how i could optimize sql server for best practices.
>> >
>> >I have looked at the parameters, but what can i do with the memory
>resources
>> >for example.
>> >
>> >Zeke
>> >
>|||Ezekiël,
Have you looked into Best Practices Analyzer? It has just been released:
http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Tinyurl:
http://tinyurl.com/35cb4
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Ezekiël wrote:
> Hello,
> I was wondering how i could optimize sql server for best practices.
> I have looked at the parameters, but what can i do with the memory resources
> for example.
> Zeke
>

Configuring Memory for SQL 2005 Standard on Windows 2003 Enterpris

Hello,
I need some assistance configuring SQL 2005 running on an 8GB server running
Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
I'm still confused as to what I need to do. Given this configuration, do I
need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and if
I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to SQL
2005.
Thanks."Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> Hello,
> I need some assistance configuring SQL 2005 running on an 8GB server
> running
> Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
> I'm still confused as to what I need to do. Given this configuration, do I
> need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and
> if
> I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to
> SQL
> 2005.
>
When running 64bit Windows and 64bit SQL Server, nothing special needs to be
done.
On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see the
following BOL topic:
Using AWE
http://msdn2.microsoft.com/en-us/library/ms175581.aspx
However, AWE has a performance penalty, so you might be better off just
using the /3GB switch and limiting SQL Server to non-AWE memory. Basically
each server workload will, at some point, experience a diminishing
performance return for incremental memory addition. Under load, measure the
SQLServer:Buffer Manager: Page reads/sec. If adding memory stops materially
affecting this number at or below 3GB, you probably don't want to use AWE.
David|||Thanks for your response. Please let me know if I am understanding this
correctly. If I use the /3GB switch, my OS will have access to 1GB of memory
and SQL 2005 (and other apps) will have access to 3GB. However, the remaining
4GB of the server's total of 8GB will not be used? Am I understanding this
correctly?
Also, if I use the /pae parameter (and therefore should not use the /3GB
parameter), I would have access to the entire 8GB but may not have as good of
performance as with using the /3GB switch? Did I understand you correctly?
Thanks!
"David Browne" wrote:
> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> > Hello,
> >
> > I need some assistance configuring SQL 2005 running on an 8GB server
> > running
> > Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
> > I'm still confused as to what I need to do. Given this configuration, do I
> > need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and
> > if
> > I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to
> > SQL
> > 2005.
> >
> When running 64bit Windows and 64bit SQL Server, nothing special needs to be
> done.
> On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see the
> following BOL topic:
> Using AWE
> http://msdn2.microsoft.com/en-us/library/ms175581.aspx
>
> However, AWE has a performance penalty, so you might be better off just
> using the /3GB switch and limiting SQL Server to non-AWE memory. Basically
> each server workload will, at some point, experience a diminishing
> performance return for incremental memory addition. Under load, measure the
> SQLServer:Buffer Manager: Page reads/sec. If adding memory stops materially
> affecting this number at or below 3GB, you probably don't want to use AWE.
> David
>
>|||The penalty for AWE is not that heavy that you should ignore it altogether.
The /PAE is so windows can access memory above 4GB and allows apps using AWE
to also access the memory above 4GB as well. The If SQL Server is the only
app on the server then I would set the /3GB and /PAE in the boot.ini. Then
set the MAX Memory setting in SQL Server to 7GB and turn on AWE. You may
have to adjust the max setting down some if you need more memory for the os
or other apps.
--
Andrew J. Kelly SQL MVP
"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:8F92E162-EADB-4ECD-A616-A6A5E9ADF7A6@.microsoft.com...
> Thanks for your response. Please let me know if I am understanding this
> correctly. If I use the /3GB switch, my OS will have access to 1GB of
> memory
> and SQL 2005 (and other apps) will have access to 3GB. However, the
> remaining
> 4GB of the server's total of 8GB will not be used? Am I understanding this
> correctly?
> Also, if I use the /pae parameter (and therefore should not use the /3GB
> parameter), I would have access to the entire 8GB but may not have as good
> of
> performance as with using the /3GB switch? Did I understand you correctly?
> Thanks!
> "David Browne" wrote:
>> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
>> news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
>> > Hello,
>> >
>> > I need some assistance configuring SQL 2005 running on an 8GB server
>> > running
>> > Windows 2003 Enterprise. I've read some of the SQL 2005 documentation,
>> > but
>> > I'm still confused as to what I need to do. Given this configuration,
>> > do I
>> > need to do anything for SQL 2005 to be able to utilize the 8GB of RAM,
>> > and
>> > if
>> > I do, what exactly needs to be done? Sorry, I'm a newbie when it comes
>> > to
>> > SQL
>> > 2005.
>> >
>> When running 64bit Windows and 64bit SQL Server, nothing special needs to
>> be
>> done.
>> On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see
>> the
>> following BOL topic:
>> Using AWE
>> http://msdn2.microsoft.com/en-us/library/ms175581.aspx
>>
>> However, AWE has a performance penalty, so you might be better off just
>> using the /3GB switch and limiting SQL Server to non-AWE memory.
>> Basically
>> each server workload will, at some point, experience a diminishing
>> performance return for incremental memory addition. Under load, measure
>> the
>> SQLServer:Buffer Manager: Page reads/sec. If adding memory stops
>> materially
>> affecting this number at or below 3GB, you probably don't want to use
>> AWE.
>> David
>>|||Thanks Andrew. You actually answered a follow-up question I had reqarding
whether or not I should be using both the /3GB and /PAE in the boot.ini file.
Do you know what the affect would be if I only added the /PAE switch without
the /3GB switch?
"Andrew J. Kelly" wrote:
> The penalty for AWE is not that heavy that you should ignore it altogether.
> The /PAE is so windows can access memory above 4GB and allows apps using AWE
> to also access the memory above 4GB as well. The If SQL Server is the only
> app on the server then I would set the /3GB and /PAE in the boot.ini. Then
> set the MAX Memory setting in SQL Server to 7GB and turn on AWE. You may
> have to adjust the max setting down some if you need more memory for the os
> or other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> news:8F92E162-EADB-4ECD-A616-A6A5E9ADF7A6@.microsoft.com...
> > Thanks for your response. Please let me know if I am understanding this
> > correctly. If I use the /3GB switch, my OS will have access to 1GB of
> > memory
> > and SQL 2005 (and other apps) will have access to 3GB. However, the
> > remaining
> > 4GB of the server's total of 8GB will not be used? Am I understanding this
> > correctly?
> >
> > Also, if I use the /pae parameter (and therefore should not use the /3GB
> > parameter), I would have access to the entire 8GB but may not have as good
> > of
> > performance as with using the /3GB switch? Did I understand you correctly?
> >
> > Thanks!
> >
> > "David Browne" wrote:
> >
> >>
> >> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> >> news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> >> > Hello,
> >> >
> >> > I need some assistance configuring SQL 2005 running on an 8GB server
> >> > running
> >> > Windows 2003 Enterprise. I've read some of the SQL 2005 documentation,
> >> > but
> >> > I'm still confused as to what I need to do. Given this configuration,
> >> > do I
> >> > need to do anything for SQL 2005 to be able to utilize the 8GB of RAM,
> >> > and
> >> > if
> >> > I do, what exactly needs to be done? Sorry, I'm a newbie when it comes
> >> > to
> >> > SQL
> >> > 2005.
> >> >
> >>
> >> When running 64bit Windows and 64bit SQL Server, nothing special needs to
> >> be
> >> done.
> >>
> >> On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see
> >> the
> >> following BOL topic:
> >> Using AWE
> >> http://msdn2.microsoft.com/en-us/library/ms175581.aspx
> >>
> >>
> >> However, AWE has a performance penalty, so you might be better off just
> >> using the /3GB switch and limiting SQL Server to non-AWE memory.
> >> Basically
> >> each server workload will, at some point, experience a diminishing
> >> performance return for incremental memory addition. Under load, measure
> >> the
> >> SQLServer:Buffer Manager: Page reads/sec. If adding memory stops
> >> materially
> >> affecting this number at or below 3GB, you probably don't want to use
> >> AWE.
> >>
> >> David
> >>
> >>
> >>
>
>|||"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
> Thanks Andrew. You actually answered a follow-up question I had reqarding
> whether or not I should be using both the /3GB and /PAE in the boot.ini
> file.
> Do you know what the affect would be if I only added the /PAE switch
> without
> the /3GB switch?
>
SQL Server would use a 2GB user mode address space and access the rest of
the memory through AWE.
David|||As opposed to using a 3GB user mode address space if the /3GB switch was
used? I guess I'm trying to get a better idea of why I would use the /3GB
switch in conjunction with using the /PAE switch as opposed to just using the
/PAE switch by itself.
"David Browne" wrote:
> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
> > Thanks Andrew. You actually answered a follow-up question I had reqarding
> > whether or not I should be using both the /3GB and /PAE in the boot.ini
> > file.
> > Do you know what the affect would be if I only added the /PAE switch
> > without
> > the /3GB switch?
> >
> SQL Server would use a 2GB user mode address space and access the rest of
> the memory through AWE.
> David
>
>|||"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:4E5ACEB7-F6F1-47BA-A867-33664F4362A2@.microsoft.com...
> As opposed to using a 3GB user mode address space if the /3GB switch was
> used?
>I guess I'm trying to get a better idea of why I would use the /3GB
> switch in conjunction with using the /PAE switch as opposed to just using
> the
> /PAE switch by itself.
>
Using /3GB gives SQL Server a larger user mode address space (which is
good), but constricts to total kernel memory to 1GB. This limits the total
physical memory usable on the server to (I think) 16GB.
David
> "David Browne" wrote:
>> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
>> news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
>> > Thanks Andrew. You actually answered a follow-up question I had
>> > reqarding
>> > whether or not I should be using both the /3GB and /PAE in the boot.ini
>> > file.
>> > Do you know what the affect would be if I only added the /PAE switch
>> > without
>> > the /3GB switch?
>> >
>> SQL Server would use a 2GB user mode address space and access the rest of
>> the memory through AWE.
>> David
>>|||For one that is 1GB more that SQL Server can address directly (without using
AWE) and 1 GB less that needs to be managed by AWE. But it can also come in
handy if you need more directly addressable memory than just 2GB. The data
buffer is the only part of SQL Server than can use the AWE memory.
Everything else needs to use the 2 or 3GB (depending on the /3GB) so it can
get tight with only 2GB in some cases.
--
Andrew J. Kelly SQL MVP
"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:4E5ACEB7-F6F1-47BA-A867-33664F4362A2@.microsoft.com...
> As opposed to using a 3GB user mode address space if the /3GB switch was
> used? I guess I'm trying to get a better idea of why I would use the /3GB
> switch in conjunction with using the /PAE switch as opposed to just using
> the
> /PAE switch by itself.
> "David Browne" wrote:
>> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
>> news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
>> > Thanks Andrew. You actually answered a follow-up question I had
>> > reqarding
>> > whether or not I should be using both the /3GB and /PAE in the boot.ini
>> > file.
>> > Do you know what the affect would be if I only added the /PAE switch
>> > without
>> > the /3GB switch?
>> >
>> SQL Server would use a 2GB user mode address space and access the rest of
>> the memory through AWE.
>> David
>>

Configuring Memory for SQL 2005 Standard on Windows 2003 Enterpris

Hello,
I need some assistance configuring SQL 2005 running on an 8GB server running
Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
I'm still confused as to what I need to do. Given this configuration, do I
need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and i
f
I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to SQ
L
2005.
Thanks."Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> Hello,
> I need some assistance configuring SQL 2005 running on an 8GB server
> running
> Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
> I'm still confused as to what I need to do. Given this configuration, do I
> need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and
> if
> I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to
> SQL
> 2005.
>
When running 64bit Windows and 64bit SQL Server, nothing special needs to be
done.
On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see the
following BOL topic:
Using AWE
http://msdn2.microsoft.com/en-us/library/ms175581.aspx
However, AWE has a performance penalty, so you might be better off just
using the /3GB switch and limiting SQL Server to non-AWE memory. Basically
each server workload will, at some point, experience a diminishing
performance return for incremental memory addition. Under load, measure the
SQLServer:Buffer Manager: Page reads/sec. If adding memory stops materially
affecting this number at or below 3GB, you probably don't want to use AWE.
David

Configuring memory for SQL 2000

I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
Leon
Do you mean "at least 6GB" or "up to 6GB"? Either way you'll need the
/pae switch in boot.ini. This allows the OS to see more than 4GB of
physical memory. Then turn on AWE memory and set the min & max memory
settings in SQL Server as appropriate with the sp_configure proc.
For between 6GB and 7GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory', 6144
exec sp_configure 'max server memory', 7168
-- then stop & restart the server (needed for the awe enabled option)
For anything up to 6GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'max server memory', 6144
-- then stop & restart the server (needed for the awe enabled option)
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Leon Shargorodsky wrote:

>I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
>2003 Standard Edition machine with 8Gb of memory.
>How do I force SQL server to use at least 6Gb out of 8Gb?
>Thank you in advance!
>Leon
>

Configuring memory for SQL 2000

I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
LeonThis is a multi-part message in MIME format.
--010707060304040203090309
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Do you mean "at least 6GB" or "up to 6GB"? Either way you'll need the
/pae switch in boot.ini. This allows the OS to see more than 4GB of
physical memory. Then turn on AWE memory and set the min & max memory
settings in SQL Server as appropriate with the sp_configure proc.
For between 6GB and 7GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory', 6144
exec sp_configure 'max server memory', 7168
-- then stop & restart the server (needed for the awe enabled option)
For anything up to 6GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'max server memory', 6144
-- then stop & restart the server (needed for the awe enabled option)
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Leon Shargorodsky wrote:
>I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
>2003 Standard Edition machine with 8Gb of memory.
>How do I force SQL server to use at least 6Gb out of 8Gb?
>Thank you in advance!
>Leon
>
--010707060304040203090309
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Do you mean "at least 6GB" or "up to 6GB"? Either way you'll need
the /pae switch in boot.ini. This allows the OS to see more than 4GB
of physical memory. Then turn on AWE memory and set the min & max
memory settings in SQL Server as appropriate with the sp_configure proc.<br>
<br>
For between 6GB and 7GB:<br>
   exec sp_configure 'awe enabled', 1<br>
   exec sp_configure 'min server memory', 6144<br>
</tt><tt>Â Â Â exec sp_configure 'max server memory', 7168<br>
   -- then stop & restart the server (needed for the awe enabled
option)<br>
<br>
For anything up to 6GB:<br>
   exec sp_configure 'awe enabled', 1<br>
   exec sp_configure 'max server memory', 6144<br>
</tt><tt>Â Â Â -- then stop & restart the server (needed for the awe
enabled option)<br>
<br>
Hope this helps.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Leon Shargorodsky wrote:
<blockquote cite="midAD5292DB-518D-4927-A01D-ABCF84E22115@.microsoft.com"
type="cite">
<pre wrap="">I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
Leon
</pre>
</blockquote>
</body>
</html>
--010707060304040203090309--

Configuring memory for SQL 2000

I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
LeonDo you mean "at least 6GB" or "up to 6GB"? Either way you'll need the
/pae switch in boot.ini. This allows the OS to see more than 4GB of
physical memory. Then turn on AWE memory and set the min & max memory
settings in SQL Server as appropriate with the sp_configure proc.
For between 6GB and 7GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory', 6144
exec sp_configure 'max server memory', 7168
-- then stop & restart the server (needed for the awe enabled option)
For anything up to 6GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'max server memory', 6144
-- then stop & restart the server (needed for the awe enabled option)
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Leon Shargorodsky wrote:

>I'm planning to install SQL Server 2000 Enterprise Edition on Windows Serve
r
>2003 Standard Edition machine with 8Gb of memory.
>How do I force SQL server to use at least 6Gb out of 8Gb?
>Thank you in advance!
>Leon
>

Configuring AWE and SQL 2005 Memory ! Is this right ?

Hi All

I would appreciate any help from you all with regards to this:

I have a SQL 2005 standard installation with windows server 2003 enterprise Operating system

The server has 7GB of memory available to it, but as I know SQL 2003 standard edition would make use of the total server memory available to it.

Here are the changes that I have made. (I have converted it all to GB for easy reading)

I added the 3GB switch to the boot.ini file

I enabled AWE

I set mini server memory to 5GB

I set max server memory to 6GB

I turned on Performance monitor and the Target server memory counter = 6GB, the total server memory is also set to 6GB.

Now after all the changes above, the server seems to run slower and looking at task manager, the PF usage is around 6GB. If I change the max server memory, the PF usage shoots up again, it MAX server memory is set to 5GB, PF usage jumps to 5GB as well..

Can anyone please confirm that the changes I made were indeed correct and possibly educate me on the configuration and how to make the best use of server memory etc.

I look forward to hearing from you all.

Thanks

"PF Usage" is the amount of memory in use. SQL Server will use every byte of memory you make available to it, so when you set the maximum memory in SQL to 6GB, it used 6 GB. When you set max memory to 5GB, SQL Server used 5GB.

Memory access is 2 or 3 orders of magnitude faster than disk access, so caching more data in memory is usually a good thing for server performance. When you say that the server seems to run slower when you allocate more memory, are you talking about SQL Server serving queries, or the performance of client applications running on the same machine?

The more memory SQL Server uses, the less memory the OS has for other applications to use. On my workstations (which only have development databases on them), I normally limit SQL server to 128 MB of RAM. That leaves plenty of available memory for client applications like Managment Studio and Outlook.

Hope this helps,
Steve

|||

We'll be running SQL Server 2005 Standard edition with Windows Server 2003 Standard edition (32 bit). I'm told by Network Support that our OS is limited to using 4GB of RAM. If we upgrade our OS to Windows Server 2003 Enterprise edition, they say we can up the RAM to 32GB. (Max for our hardware/processor.) My question, though, is if we go to Enterprise edition on the OS, will SQL Server 2005 standard utilize as much RAM as it can? Will it go over 4GB or will we have to change the SQL Server edition to Enterprise as well, in order to take advantage of the extra RAM?

|||

More in this article.

SQL Server 2005 standard use as many RAM get.

Configuring AWE and SQL 2005 Memory ! Is this right ?

Hi All

I would appreciate any help from you all with regards to this:

I have a SQL 2005 standard installation with windows server 2003 enterprise Operating system

The server has 7GB of memory available to it, but as I know SQL 2003 standard edition would make use of the total server memory available to it.

Here are the changes that I have made. (I have converted it all to GB for easy reading)

I added the 3GB switch to the boot.ini file

I enabled AWE

I set mini server memory to 5GB

I set max server memory to 6GB

I turned on Performance monitor and the Target server memory counter = 6GB, the total server memory is also set to 6GB.

Now after all the changes above, the server seems to run slower and looking at task manager, the PF usage is around 6GB. If I change the max server memory, the PF usage shoots up again, it MAX server memory is set to 5GB, PF usage jumps to 5GB as well..

Can anyone please confirm that the changes I made were indeed correct and possibly educate me on the configuration and how to make the best use of server memory etc.

I look forward to hearing from you all.

Thanks

"PF Usage" is the amount of memory in use. SQL Server will use every byte of memory you make available to it, so when you set the maximum memory in SQL to 6GB, it used 6 GB. When you set max memory to 5GB, SQL Server used 5GB.

Memory access is 2 or 3 orders of magnitude faster than disk access, so caching more data in memory is usually a good thing for server performance. When you say that the server seems to run slower when you allocate more memory, are you talking about SQL Server serving queries, or the performance of client applications running on the same machine?

The more memory SQL Server uses, the less memory the OS has for other applications to use. On my workstations (which only have development databases on them), I normally limit SQL server to 128 MB of RAM. That leaves plenty of available memory for client applications like Managment Studio and Outlook.

Hope this helps,
Steve

|||

We'll be running SQL Server 2005 Standard edition with Windows Server 2003 Standard edition (32 bit). I'm told by Network Support that our OS is limited to using 4GB of RAM. If we upgrade our OS to Windows Server 2003 Enterprise edition, they say we can up the RAM to 32GB. (Max for our hardware/processor.) My question, though, is if we go to Enterprise edition on the OS, will SQL Server 2005 standard utilize as much RAM as it can? Will it go over 4GB or will we have to change the SQL Server edition to Enterprise as well, in order to take advantage of the extra RAM?

|||

More in this article.

SQL Server 2005 standard use as many RAM get.

Configuring and using PAE on SQS Server 2000 Enterprise Edition

We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
machine with 16 GB of memory. I am trying to use more than the default 2GB of
memory for SQL Server.
I have added the /PAE switch to the boot.ini file on the server, configured
SQL Server by issuing a
sp_configure 'show advanced options', 1
RECONFIGURE
and a
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 14336
RECONFIGURE
GO
However, it looks like SQL Server is till using less than 2GB of memory when
running. I have run the sp_configure check on it's own and noticed that the
config_value for AWE enabled is 1, but that the run_value is still 0.
Could that be the problem? What do I have to do to fix this?
I know I did restart the server after adding the settings (the setting for
'show advanced options' and 'max server memory' both show the same value for
config and run_value. It is just the AWE enable setting that has a
discrepancy.
Should I set the 'max server memory' to less than 14 GB if the server has
16GB available? How much does the OS need?
Thanks
FredHow to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
Managing AWE Memor
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
AMB
"fredscuba" wrote:
> We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> machine with 16 GB of memory. I am trying to use more than the default 2GB of
> memory for SQL Server.
> I have added the /PAE switch to the boot.ini file on the server, configured
> SQL Server by issuing a
> sp_configure 'show advanced options', 1
> RECONFIGURE
> and a
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 14336
> RECONFIGURE
> GO
> However, it looks like SQL Server is till using less than 2GB of memory when
> running. I have run the sp_configure check on it's own and noticed that the
> config_value for AWE enabled is 1, but that the run_value is still 0.
> Could that be the problem? What do I have to do to fix this?
> I know I did restart the server after adding the settings (the setting for
> 'show advanced options' and 'max server memory' both show the same value for
> config and run_value. It is just the AWE enable setting that has a
> discrepancy.
> Should I set the 'max server memory' to less than 14 GB if the server has
> 16GB available? How much does the OS need?
> Thanks
> Fred|||Alejandro,
I did go through the instructions that are shown on the pages you bookmarked
(see my original post).
My question is: why is SQL Server still using less than 2GB of memory even
after I have setup my boot.ini and enabled AWE and set the max server
memory?
Fred
"Alejandro Mesa" wrote:
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> Managing AWE Memory
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>
> AMB
>
> "fredscuba" wrote:
> > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > memory for SQL Server.
> >
> > I have added the /PAE switch to the boot.ini file on the server, configured
> > SQL Server by issuing a
> >
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> >
> > and a
> >
> > sp_configure 'awe enabled', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory', 14336
> > RECONFIGURE
> > GO
> >
> > However, it looks like SQL Server is till using less than 2GB of memory when
> > running. I have run the sp_configure check on it's own and noticed that the
> > config_value for AWE enabled is 1, but that the run_value is still 0.
> >
> > Could that be the problem? What do I have to do to fix this?
> >
> > I know I did restart the server after adding the settings (the setting for
> > 'show advanced options' and 'max server memory' both show the same value for
> > config and run_value. It is just the AWE enable setting that has a
> > discrepancy.
> >
> > Should I set the 'max server memory' to less than 14 GB if the server has
> > 16GB available? How much does the OS need?
> >
> > Thanks
> >
> > Fred|||What Edition of Windows 2003 Server?
AMB
"fredscuba" wrote:
> Alejandro,
> I did go through the instructions that are shown on the pages you bookmarked
> (see my original post).
> My question is: why is SQL Server still using less than 2GB of memory even
> after I have setup my boot.ini and enabled AWE and set the max server
> memory?
> Fred
>
> "Alejandro Mesa" wrote:
> > How to configure memory for more than 2 GB in SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >
> > Managing AWE Memory
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >
> >
> > AMB
> >
> >
> > "fredscuba" wrote:
> >
> > > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > > memory for SQL Server.
> > >
> > > I have added the /PAE switch to the boot.ini file on the server, configured
> > > SQL Server by issuing a
> > >
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > >
> > > and a
> > >
> > > sp_configure 'awe enabled', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory', 14336
> > > RECONFIGURE
> > > GO
> > >
> > > However, it looks like SQL Server is till using less than 2GB of memory when
> > > running. I have run the sp_configure check on it's own and noticed that the
> > > config_value for AWE enabled is 1, but that the run_value is still 0.
> > >
> > > Could that be the problem? What do I have to do to fix this?
> > >
> > > I know I did restart the server after adding the settings (the setting for
> > > 'show advanced options' and 'max server memory' both show the same value for
> > > config and run_value. It is just the AWE enable setting that has a
> > > discrepancy.
> > >
> > > Should I set the 'max server memory' to less than 14 GB if the server has
> > > 16GB available? How much does the OS need?
> > >
> > > Thanks
> > >
> > > Fred|||Are you sure it is Enterprise Edition and not Std? What Edition is the OS?
What does @.@.VERSION or xp_MSVer show? After you get it going I would start
out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty and
there is a little room to spare for other apps.
--
Andrew J. Kelly SQL MVP
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> Alejandro,
> I did go through the instructions that are shown on the pages you
> bookmarked
> (see my original post).
> My question is: why is SQL Server still using less than 2GB of memory even
> after I have setup my boot.ini and enabled AWE and set the max server
> memory?
> Fred
>
> "Alejandro Mesa" wrote:
>> How to configure memory for more than 2 GB in SQL Server
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> Managing AWE Memory
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>>
>> AMB
>>
>> "fredscuba" wrote:
>> > We are running SQL Server 2000 Enterprise Edition on a Windows Server
>> > 2003
>> > machine with 16 GB of memory. I am trying to use more than the default
>> > 2GB of
>> > memory for SQL Server.
>> >
>> > I have added the /PAE switch to the boot.ini file on the server,
>> > configured
>> > SQL Server by issuing a
>> >
>> > sp_configure 'show advanced options', 1
>> > RECONFIGURE
>> >
>> > and a
>> >
>> > sp_configure 'awe enabled', 1
>> > RECONFIGURE
>> > GO
>> > sp_configure 'max server memory', 14336
>> > RECONFIGURE
>> > GO
>> >
>> > However, it looks like SQL Server is till using less than 2GB of memory
>> > when
>> > running. I have run the sp_configure check on it's own and noticed that
>> > the
>> > config_value for AWE enabled is 1, but that the run_value is still 0.
>> >
>> > Could that be the problem? What do I have to do to fix this?
>> >
>> > I know I did restart the server after adding the settings (the setting
>> > for
>> > 'show advanced options' and 'max server memory' both show the same
>> > value for
>> > config and run_value. It is just the AWE enable setting that has a
>> > discrepancy.
>> >
>> > Should I set the 'max server memory' to less than 14 GB if the server
>> > has
>> > 16GB available? How much does the OS need?
>> >
>> > Thanks
>> >
>> > Fred|||Andrew & Alejandro,
I am running Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
Edition.
I think the problem is that the config_value for 'AWE enabled' is 1, but
that the run_value is still 0.
Why would that be? What do I have to do to fix this?
I am fairly sure that I restarted the server after changing the AWE setting,
but I can do it again after hours (it is a production server).
Is there any reason why the 'AWE enabled' setting would stay at a run_value
of 0, even after I try to set it with:
sp_configure 'awe enabled', 1
RECONFIGURE
Fred
"Andrew J. Kelly" wrote:
> Are you sure it is Enterprise Edition and not Std? What Edition is the OS?
> What does @.@.VERSION or xp_MSVer show? After you get it going I would start
> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty and
> there is a little room to spare for other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> > Alejandro,
> >
> > I did go through the instructions that are shown on the pages you
> > bookmarked
> > (see my original post).
> >
> > My question is: why is SQL Server still using less than 2GB of memory even
> > after I have setup my boot.ini and enabled AWE and set the max server
> > memory?
> >
> > Fred
> >
> >
> > "Alejandro Mesa" wrote:
> >
> >> How to configure memory for more than 2 GB in SQL Server
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >>
> >> Managing AWE Memory
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >>
> >>
> >> AMB
> >>
> >>
> >> "fredscuba" wrote:
> >>
> >> > We are running SQL Server 2000 Enterprise Edition on a Windows Server
> >> > 2003
> >> > machine with 16 GB of memory. I am trying to use more than the default
> >> > 2GB of
> >> > memory for SQL Server.
> >> >
> >> > I have added the /PAE switch to the boot.ini file on the server,
> >> > configured
> >> > SQL Server by issuing a
> >> >
> >> > sp_configure 'show advanced options', 1
> >> > RECONFIGURE
> >> >
> >> > and a
> >> >
> >> > sp_configure 'awe enabled', 1
> >> > RECONFIGURE
> >> > GO
> >> > sp_configure 'max server memory', 14336
> >> > RECONFIGURE
> >> > GO
> >> >
> >> > However, it looks like SQL Server is till using less than 2GB of memory
> >> > when
> >> > running. I have run the sp_configure check on it's own and noticed that
> >> > the
> >> > config_value for AWE enabled is 1, but that the run_value is still 0.
> >> >
> >> > Could that be the problem? What do I have to do to fix this?
> >> >
> >> > I know I did restart the server after adding the settings (the setting
> >> > for
> >> > 'show advanced options' and 'max server memory' both show the same
> >> > value for
> >> > config and run_value. It is just the AWE enable setting that has a
> >> > discrepancy.
> >> >
> >> > Should I set the 'max server memory' to less than 14 GB if the server
> >> > has
> >> > 16GB available? How much does the OS need?
> >> >
> >> > Thanks
> >> >
> >> > Fred
>
>|||The most obvious reason would be that you didn't actually restart SQL
Server. I would try that and see if it fixes it.
--
Andrew J. Kelly SQL MVP
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
> Andrew & Alejandro,
> I am running Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
> Edition.
> I think the problem is that the config_value for 'AWE enabled' is 1, but
> that the run_value is still 0.
> Why would that be? What do I have to do to fix this?
> I am fairly sure that I restarted the server after changing the AWE
> setting,
> but I can do it again after hours (it is a production server).
> Is there any reason why the 'AWE enabled' setting would stay at a
> run_value
> of 0, even after I try to set it with:
> sp_configure 'awe enabled', 1
> RECONFIGURE
>
> Fred
>
> "Andrew J. Kelly" wrote:
>> Are you sure it is Enterprise Edition and not Std? What Edition is the
>> OS?
>> What does @.@.VERSION or xp_MSVer show? After you get it going I would
>> start
>> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
>> and
>> there is a little room to spare for other apps.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
>> > Alejandro,
>> >
>> > I did go through the instructions that are shown on the pages you
>> > bookmarked
>> > (see my original post).
>> >
>> > My question is: why is SQL Server still using less than 2GB of memory
>> > even
>> > after I have setup my boot.ini and enabled AWE and set the max server
>> > memory?
>> >
>> > Fred
>> >
>> >
>> > "Alejandro Mesa" wrote:
>> >
>> >> How to configure memory for more than 2 GB in SQL Server
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> >>
>> >> Managing AWE Memory
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>> >>
>> >>
>> >> AMB
>> >>
>> >>
>> >> "fredscuba" wrote:
>> >>
>> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
>> >> > Server
>> >> > 2003
>> >> > machine with 16 GB of memory. I am trying to use more than the
>> >> > default
>> >> > 2GB of
>> >> > memory for SQL Server.
>> >> >
>> >> > I have added the /PAE switch to the boot.ini file on the server,
>> >> > configured
>> >> > SQL Server by issuing a
>> >> >
>> >> > sp_configure 'show advanced options', 1
>> >> > RECONFIGURE
>> >> >
>> >> > and a
>> >> >
>> >> > sp_configure 'awe enabled', 1
>> >> > RECONFIGURE
>> >> > GO
>> >> > sp_configure 'max server memory', 14336
>> >> > RECONFIGURE
>> >> > GO
>> >> >
>> >> > However, it looks like SQL Server is till using less than 2GB of
>> >> > memory
>> >> > when
>> >> > running. I have run the sp_configure check on it's own and noticed
>> >> > that
>> >> > the
>> >> > config_value for AWE enabled is 1, but that the run_value is still
>> >> > 0.
>> >> >
>> >> > Could that be the problem? What do I have to do to fix this?
>> >> >
>> >> > I know I did restart the server after adding the settings (the
>> >> > setting
>> >> > for
>> >> > 'show advanced options' and 'max server memory' both show the same
>> >> > value for
>> >> > config and run_value. It is just the AWE enable setting that has a
>> >> > discrepancy.
>> >> >
>> >> > Should I set the 'max server memory' to less than 14 GB if the
>> >> > server
>> >> > has
>> >> > 16GB available? How much does the OS need?
>> >> >
>> >> > Thanks
>> >> >
>> >> > Fred
>>|||Hi,
Apologies if this has already been checked, but have you looked in the SQL
Server log for an entry of 'Address Windowing Extensions enabled' to clarify
if AWE is enabled or not?
--
Regards
Robert
"fredscuba" wrote:
> Andrew & Alejandro,
> I am running Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
> Edition.
> I think the problem is that the config_value for 'AWE enabled' is 1, but
> that the run_value is still 0.
> Why would that be? What do I have to do to fix this?
> I am fairly sure that I restarted the server after changing the AWE setting,
> but I can do it again after hours (it is a production server).
> Is there any reason why the 'AWE enabled' setting would stay at a run_value
> of 0, even after I try to set it with:
> sp_configure 'awe enabled', 1
> RECONFIGURE
>
> Fred
>
> "Andrew J. Kelly" wrote:
> > Are you sure it is Enterprise Edition and not Std? What Edition is the OS?
> > What does @.@.VERSION or xp_MSVer show? After you get it going I would start
> > out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty and
> > there is a little room to spare for other apps.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> > news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> > > Alejandro,
> > >
> > > I did go through the instructions that are shown on the pages you
> > > bookmarked
> > > (see my original post).
> > >
> > > My question is: why is SQL Server still using less than 2GB of memory even
> > > after I have setup my boot.ini and enabled AWE and set the max server
> > > memory?
> > >
> > > Fred
> > >
> > >
> > > "Alejandro Mesa" wrote:
> > >
> > >> How to configure memory for more than 2 GB in SQL Server
> > >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> > >>
> > >> Managing AWE Memory
> > >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> > >>
> > >>
> > >> AMB
> > >>
> > >>
> > >> "fredscuba" wrote:
> > >>
> > >> > We are running SQL Server 2000 Enterprise Edition on a Windows Server
> > >> > 2003
> > >> > machine with 16 GB of memory. I am trying to use more than the default
> > >> > 2GB of
> > >> > memory for SQL Server.
> > >> >
> > >> > I have added the /PAE switch to the boot.ini file on the server,
> > >> > configured
> > >> > SQL Server by issuing a
> > >> >
> > >> > sp_configure 'show advanced options', 1
> > >> > RECONFIGURE
> > >> >
> > >> > and a
> > >> >
> > >> > sp_configure 'awe enabled', 1
> > >> > RECONFIGURE
> > >> > GO
> > >> > sp_configure 'max server memory', 14336
> > >> > RECONFIGURE
> > >> > GO
> > >> >
> > >> > However, it looks like SQL Server is till using less than 2GB of memory
> > >> > when
> > >> > running. I have run the sp_configure check on it's own and noticed that
> > >> > the
> > >> > config_value for AWE enabled is 1, but that the run_value is still 0.
> > >> >
> > >> > Could that be the problem? What do I have to do to fix this?
> > >> >
> > >> > I know I did restart the server after adding the settings (the setting
> > >> > for
> > >> > 'show advanced options' and 'max server memory' both show the same
> > >> > value for
> > >> > config and run_value. It is just the AWE enable setting that has a
> > >> > discrepancy.
> > >> >
> > >> > Should I set the 'max server memory' to less than 14 GB if the server
> > >> > has
> > >> > 16GB available? How much does the OS need?
> > >> >
> > >> > Thanks
> > >> >
> > >> > Fred
> >
> >
> >|||And check to make sure the account sql server is running under has Lock
Pages In Memory rights.
--
Andrew J. Kelly SQL MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
> The most obvious reason would be that you didn't actually restart SQL
> Server. I would try that and see if it fixes it.
> --
> Andrew J. Kelly SQL MVP
>
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
>> Andrew & Alejandro,
>> I am running Server 2003 Enterprise Edition and SQL Server 2000
>> Enterprise
>> Edition.
>> I think the problem is that the config_value for 'AWE enabled' is 1, but
>> that the run_value is still 0.
>> Why would that be? What do I have to do to fix this?
>> I am fairly sure that I restarted the server after changing the AWE
>> setting,
>> but I can do it again after hours (it is a production server).
>> Is there any reason why the 'AWE enabled' setting would stay at a
>> run_value
>> of 0, even after I try to set it with:
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>>
>> Fred
>>
>> "Andrew J. Kelly" wrote:
>> Are you sure it is Enterprise Edition and not Std? What Edition is the
>> OS?
>> What does @.@.VERSION or xp_MSVer show? After you get it going I would
>> start
>> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
>> and
>> there is a little room to spare for other apps.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
>> > Alejandro,
>> >
>> > I did go through the instructions that are shown on the pages you
>> > bookmarked
>> > (see my original post).
>> >
>> > My question is: why is SQL Server still using less than 2GB of memory
>> > even
>> > after I have setup my boot.ini and enabled AWE and set the max server
>> > memory?
>> >
>> > Fred
>> >
>> >
>> > "Alejandro Mesa" wrote:
>> >
>> >> How to configure memory for more than 2 GB in SQL Server
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> >>
>> >> Managing AWE Memory
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>> >>
>> >>
>> >> AMB
>> >>
>> >>
>> >> "fredscuba" wrote:
>> >>
>> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
>> >> > Server
>> >> > 2003
>> >> > machine with 16 GB of memory. I am trying to use more than the
>> >> > default
>> >> > 2GB of
>> >> > memory for SQL Server.
>> >> >
>> >> > I have added the /PAE switch to the boot.ini file on the server,
>> >> > configured
>> >> > SQL Server by issuing a
>> >> >
>> >> > sp_configure 'show advanced options', 1
>> >> > RECONFIGURE
>> >> >
>> >> > and a
>> >> >
>> >> > sp_configure 'awe enabled', 1
>> >> > RECONFIGURE
>> >> > GO
>> >> > sp_configure 'max server memory', 14336
>> >> > RECONFIGURE
>> >> > GO
>> >> >
>> >> > However, it looks like SQL Server is till using less than 2GB of
>> >> > memory
>> >> > when
>> >> > running. I have run the sp_configure check on it's own and noticed
>> >> > that
>> >> > the
>> >> > config_value for AWE enabled is 1, but that the run_value is still
>> >> > 0.
>> >> >
>> >> > Could that be the problem? What do I have to do to fix this?
>> >> >
>> >> > I know I did restart the server after adding the settings (the
>> >> > setting
>> >> > for
>> >> > 'show advanced options' and 'max server memory' both show the same
>> >> > value for
>> >> > config and run_value. It is just the AWE enable setting that has a
>> >> > discrepancy.
>> >> >
>> >> > Should I set the 'max server memory' to less than 14 GB if the
>> >> > server
>> >> > has
>> >> > 16GB available? How much does the OS need?
>> >> >
>> >> > Thanks
>> >> >
>> >> > Fred
>>
>|||Hi
Before you set AWE you need to set "Lock Pages In Memory" first.
How to set the "Lock Pages In Memory", Please see URL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servp_2vqh.asp
By default SQL Seerver 2000 in Windows 2003 is disable.
Goog luck
Bogi Wu
"fredscuba" wrote:
> We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> machine with 16 GB of memory. I am trying to use more than the default 2GB of
> memory for SQL Server.
> I have added the /PAE switch to the boot.ini file on the server, configured
> SQL Server by issuing a
> sp_configure 'show advanced options', 1
> RECONFIGURE
> and a
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 14336
> RECONFIGURE
> GO
> However, it looks like SQL Server is till using less than 2GB of memory when
> running. I have run the sp_configure check on it's own and noticed that the
> config_value for AWE enabled is 1, but that the run_value is still 0.
> Could that be the problem? What do I have to do to fix this?
> I know I did restart the server after adding the settings (the setting for
> 'show advanced options' and 'max server memory' both show the same value for
> config and run_value. It is just the AWE enable setting that has a
> discrepancy.
> Should I set the 'max server memory' to less than 14 GB if the server has
> 16GB available? How much does the OS need?
> Thanks
> Fred|||OK, I did reboot the server and afterwards I could verify that the run_value
for 'AWE enabled' is now 1.
The 'min server memory' is set to 1024 (1 GB) and the 'max server memory'
is set to 10240 (10GB). The server has 16GB of memory, but I will be sharing
that with the OS and with a SAS application, so I thought that 10GB for SQL
Server is a good number to start with.
*HOWEVER* when I check the memory utilization on the server through the Task
Manager, it now shows that the process sqlservr.exe is only using 141,164 K
of memory - only about 138 MB!!
Why would this be? Could this be correct? The server is being used pretty
heavily right now - it is not a transaction heavy load, more a batch type
load with millions of records being updated in a batch.
My impression was that if AWE is enabled and PAE is used, that the memory
allocation is not dynamic anymore, but static, according to what you specify
in the max server memory setting.
Why would it then show such a low memory utilization by SQL Server? (It used
to hover around 1.7 GB before I enabled PAE and AWE).
Fred
"Andrew J. Kelly" wrote:
> And check to make sure the account sql server is running under has Lock
> Pages In Memory rights.
> --
> Andrew J. Kelly SQL MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
> > The most obvious reason would be that you didn't actually restart SQL
> > Server. I would try that and see if it fixes it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> > news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
> >> Andrew & Alejandro,
> >>
> >> I am running Server 2003 Enterprise Edition and SQL Server 2000
> >> Enterprise
> >> Edition.
> >>
> >> I think the problem is that the config_value for 'AWE enabled' is 1, but
> >> that the run_value is still 0.
> >>
> >> Why would that be? What do I have to do to fix this?
> >>
> >> I am fairly sure that I restarted the server after changing the AWE
> >> setting,
> >> but I can do it again after hours (it is a production server).
> >>
> >> Is there any reason why the 'AWE enabled' setting would stay at a
> >> run_value
> >> of 0, even after I try to set it with:
> >>
> >> sp_configure 'awe enabled', 1
> >> RECONFIGURE
> >>
> >>
> >> Fred
> >>
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> Are you sure it is Enterprise Edition and not Std? What Edition is the
> >> OS?
> >> What does @.@.VERSION or xp_MSVer show? After you get it going I would
> >> start
> >> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
> >> and
> >> there is a little room to spare for other apps.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> >> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> >> > Alejandro,
> >> >
> >> > I did go through the instructions that are shown on the pages you
> >> > bookmarked
> >> > (see my original post).
> >> >
> >> > My question is: why is SQL Server still using less than 2GB of memory
> >> > even
> >> > after I have setup my boot.ini and enabled AWE and set the max server
> >> > memory?
> >> >
> >> > Fred
> >> >
> >> >
> >> > "Alejandro Mesa" wrote:
> >> >
> >> >> How to configure memory for more than 2 GB in SQL Server
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >> >>
> >> >> Managing AWE Memory
> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >> >>
> >> >>
> >> >> AMB
> >> >>
> >> >>
> >> >> "fredscuba" wrote:
> >> >>
> >> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
> >> >> > Server
> >> >> > 2003
> >> >> > machine with 16 GB of memory. I am trying to use more than the
> >> >> > default
> >> >> > 2GB of
> >> >> > memory for SQL Server.
> >> >> >
> >> >> > I have added the /PAE switch to the boot.ini file on the server,
> >> >> > configured
> >> >> > SQL Server by issuing a
> >> >> >
> >> >> > sp_configure 'show advanced options', 1
> >> >> > RECONFIGURE
> >> >> >
> >> >> > and a
> >> >> >
> >> >> > sp_configure 'awe enabled', 1
> >> >> > RECONFIGURE
> >> >> > GO
> >> >> > sp_configure 'max server memory', 14336
> >> >> > RECONFIGURE
> >> >> > GO
> >> >> >
> >> >> > However, it looks like SQL Server is till using less than 2GB of
> >> >> > memory
> >> >> > when
> >> >> > running. I have run the sp_configure check on it's own and noticed
> >> >> > that
> >> >> > the
> >> >> > config_value for AWE enabled is 1, but that the run_value is still
> >> >> > 0.
> >> >> >
> >> >> > Could that be the problem? What do I have to do to fix this?
> >> >> >
> >> >> > I know I did restart the server after adding the settings (the
> >> >> > setting
> >> >> > for
> >> >> > 'show advanced options' and 'max server memory' both show the same
> >> >> > value for
> >> >> > config and run_value. It is just the AWE enable setting that has a
> >> >> > discrepancy.
> >> >> >
> >> >> > Should I set the 'max server memory' to less than 14 GB if the
> >> >> > server
> >> >> > has
> >> >> > 16GB available? How much does the OS need?
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > Fred
> >>
> >>
> >>
> >
> >
>
>|||Hi,
I followed the link you provided, but there is a note saying that "This
functionality is available only if you are running the Microsoft® Windows®
2000 operating system".
Can I or should I do it if I am running on Windows Server 2003?
Fred
"BogiWu" wrote:
> Hi
> Before you set AWE you need to set "Lock Pages In Memory" first.
> How to set the "Lock Pages In Memory", Please see URL
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servp_2vqh.asp
> By default SQL Seerver 2000 in Windows 2003 is disable.
> Goog luck
> Bogi Wu
> "fredscuba" wrote:
> > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > memory for SQL Server.
> >
> > I have added the /PAE switch to the boot.ini file on the server, configured
> > SQL Server by issuing a
> >
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> >
> > and a
> >
> > sp_configure 'awe enabled', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory', 14336
> > RECONFIGURE
> > GO
> >
> > However, it looks like SQL Server is till using less than 2GB of memory when
> > running. I have run the sp_configure check on it's own and noticed that the
> > config_value for AWE enabled is 1, but that the run_value is still 0.
> >
> > Could that be the problem? What do I have to do to fix this?
> >
> > I know I did restart the server after adding the settings (the setting for
> > 'show advanced options' and 'max server memory' both show the same value for
> > config and run_value. It is just the AWE enable setting that has a
> > discrepancy.
> >
> > Should I set the 'max server memory' to less than 14 GB if the server has
> > 16GB available? How much does the OS need?
> >
> > Thanks
> >
> > Fred|||Never mind - the account running SQL Server already has the ability to lock
pages in memory.
My problem now is that SQL Server does not seem to be using all of the
memory I set aside for it under the max server memory setting. (see my other
post from today)
Fred
"fredscuba" wrote:
> Hi,
> I followed the link you provided, but there is a note saying that "This
> functionality is available only if you are running the Microsoft® Windows®
> 2000 operating system".
> Can I or should I do it if I am running on Windows Server 2003?
>
> Fred
> "BogiWu" wrote:
> > Hi
> > Before you set AWE you need to set "Lock Pages In Memory" first.
> > How to set the "Lock Pages In Memory", Please see URL
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servp_2vqh.asp
> > By default SQL Seerver 2000 in Windows 2003 is disable.
> >
> > Goog luck
> >
> > Bogi Wu
> > "fredscuba" wrote:
> >
> > > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > > memory for SQL Server.
> > >
> > > I have added the /PAE switch to the boot.ini file on the server, configured
> > > SQL Server by issuing a
> > >
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > >
> > > and a
> > >
> > > sp_configure 'awe enabled', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory', 14336
> > > RECONFIGURE
> > > GO
> > >
> > > However, it looks like SQL Server is till using less than 2GB of memory when
> > > running. I have run the sp_configure check on it's own and noticed that the
> > > config_value for AWE enabled is 1, but that the run_value is still 0.
> > >
> > > Could that be the problem? What do I have to do to fix this?
> > >
> > > I know I did restart the server after adding the settings (the setting for
> > > 'show advanced options' and 'max server memory' both show the same value for
> > > config and run_value. It is just the AWE enable setting that has a
> > > discrepancy.
> > >
> > > Should I set the 'max server memory' to less than 14 GB if the server has
> > > 16GB available? How much does the OS need?
> > >
> > > Thanks
> > >
> > > Fred|||Task Manager isn't PAE aware. Use Perf Mon.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:3AB99BA7-408F-4B05-B44B-DA2D78FE0053@.microsoft.com...
> OK, I did reboot the server and afterwards I could verify that the run_value
> for 'AWE enabled' is now 1.
> The 'min server memory' is set to 1024 (1 GB) and the 'max server memory'
> is set to 10240 (10GB). The server has 16GB of memory, but I will be sharing
> that with the OS and with a SAS application, so I thought that 10GB for SQL
> Server is a good number to start with.
> *HOWEVER* when I check the memory utilization on the server through the Task
> Manager, it now shows that the process sqlservr.exe is only using 141,164 K
> of memory - only about 138 MB!!
> Why would this be? Could this be correct? The server is being used pretty
> heavily right now - it is not a transaction heavy load, more a batch type
> load with millions of records being updated in a batch.
> My impression was that if AWE is enabled and PAE is used, that the memory
> allocation is not dynamic anymore, but static, according to what you specify
> in the max server memory setting.
> Why would it then show such a low memory utilization by SQL Server? (It used
> to hover around 1.7 GB before I enabled PAE and AWE).
>
> Fred
> "Andrew J. Kelly" wrote:
>> And check to make sure the account sql server is running under has Lock
>> Pages In Memory rights.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > The most obvious reason would be that you didn't actually restart SQL
>> > Server. I would try that and see if it fixes it.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> > news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
>> >> Andrew & Alejandro,
>> >>
>> >> I am running Server 2003 Enterprise Edition and SQL Server 2000
>> >> Enterprise
>> >> Edition.
>> >>
>> >> I think the problem is that the config_value for 'AWE enabled' is 1, but
>> >> that the run_value is still 0.
>> >>
>> >> Why would that be? What do I have to do to fix this?
>> >>
>> >> I am fairly sure that I restarted the server after changing the AWE
>> >> setting,
>> >> but I can do it again after hours (it is a production server).
>> >>
>> >> Is there any reason why the 'AWE enabled' setting would stay at a
>> >> run_value
>> >> of 0, even after I try to set it with:
>> >>
>> >> sp_configure 'awe enabled', 1
>> >> RECONFIGURE
>> >>
>> >>
>> >> Fred
>> >>
>> >>
>> >> "Andrew J. Kelly" wrote:
>> >>
>> >> Are you sure it is Enterprise Edition and not Std? What Edition is the
>> >> OS?
>> >> What does @.@.VERSION or xp_MSVer show? After you get it going I would
>> >> start
>> >> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
>> >> and
>> >> there is a little room to spare for other apps.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> >> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
>> >> > Alejandro,
>> >> >
>> >> > I did go through the instructions that are shown on the pages you
>> >> > bookmarked
>> >> > (see my original post).
>> >> >
>> >> > My question is: why is SQL Server still using less than 2GB of memory
>> >> > even
>> >> > after I have setup my boot.ini and enabled AWE and set the max server
>> >> > memory?
>> >> >
>> >> > Fred
>> >> >
>> >> >
>> >> > "Alejandro Mesa" wrote:
>> >> >
>> >> >> How to configure memory for more than 2 GB in SQL Server
>> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> >> >>
>> >> >> Managing AWE Memory
>> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>> >> >>
>> >> >>
>> >> >> AMB
>> >> >>
>> >> >>
>> >> >> "fredscuba" wrote:
>> >> >>
>> >> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
>> >> >> > Server
>> >> >> > 2003
>> >> >> > machine with 16 GB of memory. I am trying to use more than the
>> >> >> > default
>> >> >> > 2GB of
>> >> >> > memory for SQL Server.
>> >> >> >
>> >> >> > I have added the /PAE switch to the boot.ini file on the server,
>> >> >> > configured
>> >> >> > SQL Server by issuing a
>> >> >> >
>> >> >> > sp_configure 'show advanced options', 1
>> >> >> > RECONFIGURE
>> >> >> >
>> >> >> > and a
>> >> >> >
>> >> >> > sp_configure 'awe enabled', 1
>> >> >> > RECONFIGURE
>> >> >> > GO
>> >> >> > sp_configure 'max server memory', 14336
>> >> >> > RECONFIGURE
>> >> >> > GO
>> >> >> >
>> >> >> > However, it looks like SQL Server is till using less than 2GB of
>> >> >> > memory
>> >> >> > when
>> >> >> > running. I have run the sp_configure check on it's own and noticed
>> >> >> > that
>> >> >> > the
>> >> >> > config_value for AWE enabled is 1, but that the run_value is still
>> >> >> > 0.
>> >> >> >
>> >> >> > Could that be the problem? What do I have to do to fix this?
>> >> >> >
>> >> >> > I know I did restart the server after adding the settings (the
>> >> >> > setting
>> >> >> > for
>> >> >> > 'show advanced options' and 'max server memory' both show the same
>> >> >> > value for
>> >> >> > config and run_value. It is just the AWE enable setting that has a
>> >> >> > discrepancy.
>> >> >> >
>> >> >> > Should I set the 'max server memory' to less than 14 GB if the
>> >> >> > server
>> >> >> > has
>> >> >> > 16GB available? How much does the OS need?
>> >> >> >
>> >> >> > Thanks
>> >> >> >
>> >> >> > Fred
>> >>
>> >>
>> >>
>> >
>> >
>>|||Thanks!
Perf Mon shows a static number for the memory assigned to SQL Server and it
matches what I set the max server memory setting to.
Fred
"Tibor Karaszi" wrote:
> Task Manager isn't PAE aware. Use Perf Mon.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:3AB99BA7-408F-4B05-B44B-DA2D78FE0053@.microsoft.com...
> > OK, I did reboot the server and afterwards I could verify that the run_value
> > for 'AWE enabled' is now 1.
> >
> > The 'min server memory' is set to 1024 (1 GB) and the 'max server memory'
> > is set to 10240 (10GB). The server has 16GB of memory, but I will be sharing
> > that with the OS and with a SAS application, so I thought that 10GB for SQL
> > Server is a good number to start with.
> >
> > *HOWEVER* when I check the memory utilization on the server through the Task
> > Manager, it now shows that the process sqlservr.exe is only using 141,164 K
> > of memory - only about 138 MB!!
> >
> > Why would this be? Could this be correct? The server is being used pretty
> > heavily right now - it is not a transaction heavy load, more a batch type
> > load with millions of records being updated in a batch.
> >
> > My impression was that if AWE is enabled and PAE is used, that the memory
> > allocation is not dynamic anymore, but static, according to what you specify
> > in the max server memory setting.
> >
> > Why would it then show such a low memory utilization by SQL Server? (It used
> > to hover around 1.7 GB before I enabled PAE and AWE).
> >
> >
> > Fred
> >
> > "Andrew J. Kelly" wrote:
> >
> >> And check to make sure the account sql server is running under has Lock
> >> Pages In Memory rights.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> > The most obvious reason would be that you didn't actually restart SQL
> >> > Server. I would try that and see if it fixes it.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> >> > news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
> >> >> Andrew & Alejandro,
> >> >>
> >> >> I am running Server 2003 Enterprise Edition and SQL Server 2000
> >> >> Enterprise
> >> >> Edition.
> >> >>
> >> >> I think the problem is that the config_value for 'AWE enabled' is 1, but
> >> >> that the run_value is still 0.
> >> >>
> >> >> Why would that be? What do I have to do to fix this?
> >> >>
> >> >> I am fairly sure that I restarted the server after changing the AWE
> >> >> setting,
> >> >> but I can do it again after hours (it is a production server).
> >> >>
> >> >> Is there any reason why the 'AWE enabled' setting would stay at a
> >> >> run_value
> >> >> of 0, even after I try to set it with:
> >> >>
> >> >> sp_configure 'awe enabled', 1
> >> >> RECONFIGURE
> >> >>
> >> >>
> >> >> Fred
> >> >>
> >> >>
> >> >> "Andrew J. Kelly" wrote:
> >> >>
> >> >> Are you sure it is Enterprise Edition and not Std? What Edition is the
> >> >> OS?
> >> >> What does @.@.VERSION or xp_MSVer show? After you get it going I would
> >> >> start
> >> >> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
> >> >> and
> >> >> there is a little room to spare for other apps.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> >> >> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> >> >> > Alejandro,
> >> >> >
> >> >> > I did go through the instructions that are shown on the pages you
> >> >> > bookmarked
> >> >> > (see my original post).
> >> >> >
> >> >> > My question is: why is SQL Server still using less than 2GB of memory
> >> >> > even
> >> >> > after I have setup my boot.ini and enabled AWE and set the max server
> >> >> > memory?
> >> >> >
> >> >> > Fred
> >> >> >
> >> >> >
> >> >> > "Alejandro Mesa" wrote:
> >> >> >
> >> >> >> How to configure memory for more than 2 GB in SQL Server
> >> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >> >> >>
> >> >> >> Managing AWE Memory
> >> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >> >> >>
> >> >> >>
> >> >> >> AMB
> >> >> >>
> >> >> >>
> >> >> >> "fredscuba" wrote:
> >> >> >>
> >> >> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
> >> >> >> > Server
> >> >> >> > 2003
> >> >> >> > machine with 16 GB of memory. I am trying to use more than the
> >> >> >> > default
> >> >> >> > 2GB of
> >> >> >> > memory for SQL Server.
> >> >> >> >
> >> >> >> > I have added the /PAE switch to the boot.ini file on the server,
> >> >> >> > configured
> >> >> >> > SQL Server by issuing a
> >> >> >> >
> >> >> >> > sp_configure 'show advanced options', 1
> >> >> >> > RECONFIGURE
> >> >> >> >
> >> >> >> > and a
> >> >> >> >
> >> >> >> > sp_configure 'awe enabled', 1
> >> >> >> > RECONFIGURE
> >> >> >> > GO
> >> >> >> > sp_configure 'max server memory', 14336
> >> >> >> > RECONFIGURE
> >> >> >> > GO
> >> >> >> >
> >> >> >> > However, it looks like SQL Server is till using less than 2GB of
> >> >> >> > memory
> >> >> >> > when
> >> >> >> > running. I have run the sp_configure check on it's own and noticed
> >> >> >> > that
> >> >> >> > the
> >> >> >> > config_value for AWE enabled is 1, but that the run_value is still
> >> >> >> > 0.
> >> >> >> >
> >> >> >> > Could that be the problem? What do I have to do to fix this?
> >> >> >> >
> >> >> >> > I know I did restart the server after adding the settings (the
> >> >> >> > setting
> >> >> >> > for
> >> >> >> > 'show advanced options' and 'max server memory' both show the same
> >> >> >> > value for
> >> >> >> > config and run_value. It is just the AWE enable setting that has a
> >> >> >> > discrepancy.
> >> >> >> >
> >> >> >> > Should I set the 'max server memory' to less than 14 GB if the
> >> >> >> > server
> >> >> >> > has
> >> >> >> > 16GB available? How much does the OS need?
> >> >> >> >
> >> >> >> > Thanks
> >> >> >> >
> >> >> >> > Fred
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>