AWE Memory

I am having a problem with SQL using the memory that should be available to it.
I have the following configuration:
Hardware:
CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
MEMORY: 8 GB RAM
OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
SQL: SQL 2000 Enterprise
AWE is enabled
The user account for SQL has Lock Pages in Memory rights
My issue:
I have review the related threads on this site and compared the recommended
settings to my system. I have not found any differences. However the size of
the SQL working memory set will not increase beyond 1.7 GB.
I found a reference on MSDN that stated that when AWE is enabled an entry
"Address Windowing Extension Enabled" is added to the SQL Error Log. I find
no such entry in my log.
I seems as though AWE is not actually running even though all my setting
seem to be ok.
What have I missed?
--
AtvarAtvar,
You also have to set the /3GB switch in boot.ini for SQL Server EE to use
the extra 1GB below the 4GB boundary. Even then it will report something
like 2.7GB used.
See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> I am having a problem with SQL using the memory that should be available
to it.
> I have the following configuration:
> Hardware:
> CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> MEMORY: 8 GB RAM
> OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> SQL: SQL 2000 Enterprise
> AWE is enabled
> The user account for SQL has Lock Pages in Memory rights
> My issue:
> I have review the related threads on this site and compared the
recommended
> settings to my system. I have not found any differences. However the size
of
> the SQL working memory set will not increase beyond 1.7 GB.
> I found a reference on MSDN that stated that when AWE is enabled an entry
> "Address Windowing Extension Enabled" is added to the SQL Error Log. I
find
> no such entry in my log.
> I seems as though AWE is not actually running even though all my setting
> seem to be ok.
> What have I missed?
> --
> Atvar|||I thought the /3GB switch was not required and was just an option. Is this
not correct?
"Ron Talmage" wrote:
> Atvar,
> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
> the extra 1GB below the 4GB boundary. Even then it will report something
> like 2.7GB used.
> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> > I am having a problem with SQL using the memory that should be available
> to it.
> >
> > I have the following configuration:
> >
> > Hardware:
> >
> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> > MEMORY: 8 GB RAM
> >
> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> >
> > SQL: SQL 2000 Enterprise
> >
> > AWE is enabled
> > The user account for SQL has Lock Pages in Memory rights
> >
> > My issue:
> >
> > I have review the related threads on this site and compared the
> recommended
> > settings to my system. I have not found any differences. However the size
> of
> > the SQL working memory set will not increase beyond 1.7 GB.
> >
> > I found a reference on MSDN that stated that when AWE is enabled an entry
> > "Address Windowing Extension Enabled" is added to the SQL Error Log. I
> find
> > no such entry in my log.
> >
> > I seems as though AWE is not actually running even though all my setting
> > seem to be ok.
> >
> > What have I missed?
> >
> > --
> > Atvar
>
>|||It is not required but a good idea in this case. It is not the reason you
are seeing what you say though. I would go back and start from scratch and
ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE turned
on and lock pages in memory for the right account. Verify each, don't just
assume.
--
Andrew J. Kelly SQL MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
>I thought the /3GB switch was not required and was just an option. Is this
> not correct?
>
> "Ron Talmage" wrote:
>> Atvar,
>> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
>> the extra 1GB below the 4GB boundary. Even then it will report something
>> like 2.7GB used.
>> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
>> Hope this helps,
>> Ron
>> --
>> Ron Talmage
>> SQL Server MVP
>> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
>> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
>> > I am having a problem with SQL using the memory that should be
>> > available
>> to it.
>> >
>> > I have the following configuration:
>> >
>> > Hardware:
>> >
>> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
>> > MEMORY: 8 GB RAM
>> >
>> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
>> >
>> > SQL: SQL 2000 Enterprise
>> >
>> > AWE is enabled
>> > The user account for SQL has Lock Pages in Memory rights
>> >
>> > My issue:
>> >
>> > I have review the related threads on this site and compared the
>> recommended
>> > settings to my system. I have not found any differences. However the
>> > size
>> of
>> > the SQL working memory set will not increase beyond 1.7 GB.
>> >
>> > I found a reference on MSDN that stated that when AWE is enabled an
>> > entry
>> > "Address Windowing Extension Enabled" is added to the SQL Error Log. I
>> find
>> > no such entry in my log.
>> >
>> > I seems as though AWE is not actually running even though all my
>> > setting
>> > seem to be ok.
>> >
>> > What have I missed?
>> >
>> > --
>> > Atvar
>>|||Thanks for the response.
I did review things as you suggested. The server is running WIn2003
Enterprise (not called advanded in 2003) and the PAE and AWE are set.
However it turn out that the SQL is standard and not Enterprise. This site
uses several SQL servers and the install was done by their IT department.
This server was specified for SQL Enterprise and the license was purchased.
It appears that the wrong license was installed by the IT department. I
think this is my issue as I understand that SQL standard is limited to 2 GB
of memory.
"Andrew J. Kelly" wrote:
> It is not required but a good idea in this case. It is not the reason you
> are seeing what you say though. I would go back and start from scratch and
> ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE turned
> on and lock pages in memory for the right account. Verify each, don't just
> assume.
> --
> Andrew J. Kelly SQL MVP
>
> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
> >I thought the /3GB switch was not required and was just an option. Is this
> > not correct?
> >
> >
> > "Ron Talmage" wrote:
> >
> >> Atvar,
> >>
> >> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
> >> the extra 1GB below the 4GB boundary. Even then it will report something
> >> like 2.7GB used.
> >>
> >> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
> >>
> >> Hope this helps,
> >> Ron
> >> --
> >> Ron Talmage
> >> SQL Server MVP
> >>
> >> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> >> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> >> > I am having a problem with SQL using the memory that should be
> >> > available
> >> to it.
> >> >
> >> > I have the following configuration:
> >> >
> >> > Hardware:
> >> >
> >> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> >> > MEMORY: 8 GB RAM
> >> >
> >> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> >> >
> >> > SQL: SQL 2000 Enterprise
> >> >
> >> > AWE is enabled
> >> > The user account for SQL has Lock Pages in Memory rights
> >> >
> >> > My issue:
> >> >
> >> > I have review the related threads on this site and compared the
> >> recommended
> >> > settings to my system. I have not found any differences. However the
> >> > size
> >> of
> >> > the SQL working memory set will not increase beyond 1.7 GB.
> >> >
> >> > I found a reference on MSDN that stated that when AWE is enabled an
> >> > entry
> >> > "Address Windowing Extension Enabled" is added to the SQL Error Log. I
> >> find
> >> > no such entry in my log.
> >> >
> >> > I seems as though AWE is not actually running even though all my
> >> > setting
> >> > seem to be ok.
> >> >
> >> > What have I missed?
> >> >
> >> > --
> >> > Atvar
> >>
> >>
> >>
>
>|||Yes that is it. Standard SQL will only use 2GB regardless of how much the
server has.
--
Andrew J. Kelly SQL MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:433230CD-B250-4CB0-AEAE-9ED0A3F954C8@.microsoft.com...
> Thanks for the response.
> I did review things as you suggested. The server is running WIn2003
> Enterprise (not called advanded in 2003) and the PAE and AWE are set.
> However it turn out that the SQL is standard and not Enterprise. This
> site
> uses several SQL servers and the install was done by their IT department.
> This server was specified for SQL Enterprise and the license was
> purchased.
> It appears that the wrong license was installed by the IT department. I
> think this is my issue as I understand that SQL standard is limited to 2
> GB
> of memory.
>
> "Andrew J. Kelly" wrote:
>> It is not required but a good idea in this case. It is not the reason
>> you
>> are seeing what you say though. I would go back and start from scratch
>> and
>> ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE
>> turned
>> on and lock pages in memory for the right account. Verify each, don't
>> just
>> assume.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
>> news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
>> >I thought the /3GB switch was not required and was just an option. Is
>> >this
>> > not correct?
>> >
>> >
>> > "Ron Talmage" wrote:
>> >
>> >> Atvar,
>> >>
>> >> You also have to set the /3GB switch in boot.ini for SQL Server EE to
>> >> use
>> >> the extra 1GB below the 4GB boundary. Even then it will report
>> >> something
>> >> like 2.7GB used.
>> >>
>> >> See KB 274750, "How to configure memory for more than 2 GB in SQL
>> >> Server"
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
>> >>
>> >> Hope this helps,
>> >> Ron
>> >> --
>> >> Ron Talmage
>> >> SQL Server MVP
>> >>
>> >> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
>> >> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
>> >> > I am having a problem with SQL using the memory that should be
>> >> > available
>> >> to it.
>> >> >
>> >> > I have the following configuration:
>> >> >
>> >> > Hardware:
>> >> >
>> >> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
>> >> > MEMORY: 8 GB RAM
>> >> >
>> >> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
>> >> >
>> >> > SQL: SQL 2000 Enterprise
>> >> >
>> >> > AWE is enabled
>> >> > The user account for SQL has Lock Pages in Memory rights
>> >> >
>> >> > My issue:
>> >> >
>> >> > I have review the related threads on this site and compared the
>> >> recommended
>> >> > settings to my system. I have not found any differences. However the
>> >> > size
>> >> of
>> >> > the SQL working memory set will not increase beyond 1.7 GB.
>> >> >
>> >> > I found a reference on MSDN that stated that when AWE is enabled an
>> >> > entry
>> >> > "Address Windowing Extension Enabled" is added to the SQL Error Log.
>> >> > I
>> >> find
>> >> > no such entry in my log.
>> >> >
>> >> > I seems as though AWE is not actually running even though all my
>> >> > setting
>> >> > seem to be ok.
>> >> >
>> >> > What have I missed?
>> >> >
>> >> > --
>> >> > Atvar
>> >>
>> >>
>> >>
>>

AWE and Memory Configuration

We have a database server running Windows 2000 Advanced Server and SQL 2000
Enterprise with 4 CPU and 8MB of memory. PAE has been configured in boot.ini.
AWE has been enabled and 5120 MB of memory allocated to SQL Server default
instance. Everything has been running fine for the last two years.
Now we are planning to add a named instance on the same server for housing
Accounting database. My question is:
1) Do I need to enable AWE on both instances of SQL Server.
2) Set upper memory for both instances. Say 4GB for default instance and
1GB for the new named instance based on their usage ratio. Leave 3GB for OS.
3) Do I need to do anything with CPU allocations?
Any suggestion. Thanks...Fraz
Hi
With 8GB, can configure 1 GB and 5.5GB without starving the OS (as long as
it is a dedicated SQL Server).
/3GB must be set in boot.ini
Set AWE for the instance with 5.5GB, and a fixed memory setting.
Do not set AWE for the 1GB instance, but have a fixed memory setting for it
too.
Leave OS and SQL server to manage CPU allocations and leave the defaults.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> We have a database server running Windows 2000 Advanced Server and SQL
> 2000
> Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> boot.ini.
> AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> instance. Everything has been running fine for the last two years.
> Now we are planning to add a named instance on the same server for housing
> Accounting database. My question is:
> 1) Do I need to enable AWE on both instances of SQL Server.
> 2) Set upper memory for both instances. Say 4GB for default instance and
> 1GB for the new named instance based on their usage ratio. Leave 3GB for
> OS.
> 3) Do I need to do anything with CPU allocations?
> Any suggestion. Thanks...Fraz
>
|||"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> We have a database server running Windows 2000 Advanced Server and SQL
> 2000
> Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> boot.ini.
> AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> instance. Everything has been running fine for the last two years.
> Now we are planning to add a named instance on the same server for housing
> Accounting database. My question is:
Why a new instance instead of just adding the database to the default
instance?
How large are these databases? How busy?

> 1) Do I need to enable AWE on both instances of SQL Server.
I would not do that. Either configure the accounting instance to use 2 or
max memory, or mount the database in the main instance. Either one big
instance with AWE, or one big instance with AWE and one small one without.
> 2) Set upper memory for both instances. Say 4GB for default instance and
> 1GB for the new named instance based on their usage ratio. Leave 3GB for
> OS.
The OS doesn't need 3GB. 200-400MB is fine. An instance will "want" a
certian amount of memory depending on its workload and database size.
Giving it more is doesn't help at all; giving it less causes increased
physical IO and a decreased cache hit ratio. AWE is slower than normal
memory, so your smaller instance will run better without AWE. You should
evaluate how much memory your instances really need. Perhaps both instances
will run better with the /3GB switch and AWE off.

> 3) Do I need to do anything with CPU allocations?
If you don't then both instances will share all 4 CPU's, which is lets them
share resources (good), but makes is more difficult to guarantee QOS to
either workload (bad).
If you put the database in the default instance you will share memory and
CPU; in a seperate instance it will not share memory, but will share CPU
unless you set CPU affinity for the instances. So assuming you want to
segregate the instances, you should probably do something with the CPU
affinity.
David
|||I really appreciate your prompt feedback. This satisfies all the questions I
had in my mind. Thanks and cheers. Fraz
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> With 8GB, can configure 1 GB and 5.5GB without starving the OS (as long as
> it is a dedicated SQL Server).
> /3GB must be set in boot.ini
> Set AWE for the instance with 5.5GB, and a fixed memory setting.
> Do not set AWE for the 1GB instance, but have a fixed memory setting for it
> too.
> Leave OS and SQL server to manage CPU allocations and leave the defaults.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
>
>
|||The new Accounting database (new named instance) will use different collation
setting i.e. Latin1_General, Sort order Binary whereas the default instance
is using Latin1-General, case-insensitive,for use with 1252 Character Set.
The database size in the new named instance is 5GB, it will be more busy in
the month-end than other days. The test system performance statistics (Total
Server Memory) shows that currently this instance is using 300-350MB.
The default instance in production is a busy instance as we have about 25
databases of different sizes from 5GB to 200MB. The performance statistics
for this shows that it is currently using 1.7 - 2.0GB out of allocated 5GB.
For CPU affinity, would it be okay to leave it to the Default setting.
Thanks...Fraz
"David Browne" wrote:

> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> Why a new instance instead of just adding the database to the default
> instance?
> How large are these databases? How busy?
>
> I would not do that. Either configure the accounting instance to use 2 or
> max memory, or mount the database in the main instance. Either one big
> instance with AWE, or one big instance with AWE and one small one without.
> The OS doesn't need 3GB. 200-400MB is fine. An instance will "want" a
> certian amount of memory depending on its workload and database size.
> Giving it more is doesn't help at all; giving it less causes increased
> physical IO and a decreased cache hit ratio. AWE is slower than normal
> memory, so your smaller instance will run better without AWE. You should
> evaluate how much memory your instances really need. Perhaps both instances
> will run better with the /3GB switch and AWE off.
>
> If you don't then both instances will share all 4 CPU's, which is lets them
> share resources (good), but makes is more difficult to guarantee QOS to
> either workload (bad).
> If you put the database in the default instance you will share memory and
> CPU; in a seperate instance it will not share memory, but will share CPU
> unless you set CPU affinity for the instances. So assuming you want to
> segregate the instances, you should probably do something with the CPU
> affinity.
> David
>
>

AWE and Memory Configuration

We have a database server running Windows 2000 Advanced Server and SQL 2000
Enterprise with 4 CPU and 8MB of memory. PAE has been configured in boot.ini.
AWE has been enabled and 5120 MB of memory allocated to SQL Server default
instance. Everything has been running fine for the last two years.
Now we are planning to add a named instance on the same server for housing
Accounting database. My question is:
1) Do I need to enable AWE on both instances of SQL Server.
2) Set upper memory for both instances. Say 4GB for default instance and
1GB for the new named instance based on their usage ratio. Leave 3GB for OS.
3) Do I need to do anything with CPU allocations?
Any suggestion. Thanks...FrazHi
With 8GB, can configure 1 GB and 5.5GB without starving the OS (as long as
it is a dedicated SQL Server).
/3GB must be set in boot.ini
Set AWE for the instance with 5.5GB, and a fixed memory setting.
Do not set AWE for the 1GB instance, but have a fixed memory setting for it
too.
Leave OS and SQL server to manage CPU allocations and leave the defaults.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> We have a database server running Windows 2000 Advanced Server and SQL
> 2000
> Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> boot.ini.
> AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> instance. Everything has been running fine for the last two years.
> Now we are planning to add a named instance on the same server for housing
> Accounting database. My question is:
> 1) Do I need to enable AWE on both instances of SQL Server.
> 2) Set upper memory for both instances. Say 4GB for default instance and
> 1GB for the new named instance based on their usage ratio. Leave 3GB for
> OS.
> 3) Do I need to do anything with CPU allocations?
> Any suggestion. Thanks...Fraz
>|||"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> We have a database server running Windows 2000 Advanced Server and SQL
> 2000
> Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> boot.ini.
> AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> instance. Everything has been running fine for the last two years.
> Now we are planning to add a named instance on the same server for housing
> Accounting database. My question is:
Why a new instance instead of just adding the database to the default
instance?
How large are these databases? How busy?
> 1) Do I need to enable AWE on both instances of SQL Server.
I would not do that. Either configure the accounting instance to use 2 or
max memory, or mount the database in the main instance. Either one big
instance with AWE, or one big instance with AWE and one small one without.
> 2) Set upper memory for both instances. Say 4GB for default instance and
> 1GB for the new named instance based on their usage ratio. Leave 3GB for
> OS.
The OS doesn't need 3GB. 200-400MB is fine. An instance will "want" a
certian amount of memory depending on its workload and database size.
Giving it more is doesn't help at all; giving it less causes increased
physical IO and a decreased cache hit ratio. AWE is slower than normal
memory, so your smaller instance will run better without AWE. You should
evaluate how much memory your instances really need. Perhaps both instances
will run better with the /3GB switch and AWE off.
> 3) Do I need to do anything with CPU allocations?
If you don't then both instances will share all 4 CPU's, which is lets them
share resources (good), but makes is more difficult to guarantee QOS to
either workload (bad).
If you put the database in the default instance you will share memory and
CPU; in a seperate instance it will not share memory, but will share CPU
unless you set CPU affinity for the instances. So assuming you want to
segregate the instances, you should probably do something with the CPU
affinity.
David|||I really appreciate your prompt feedback. This satisfies all the questions I
had in my mind. Thanks and cheers. Fraz
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> With 8GB, can configure 1 GB and 5.5GB without starving the OS (as long as
> it is a dedicated SQL Server).
> /3GB must be set in boot.ini
> Set AWE for the instance with 5.5GB, and a fixed memory setting.
> Do not set AWE for the 1GB instance, but have a fixed memory setting for it
> too.
> Leave OS and SQL server to manage CPU allocations and leave the defaults.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> > We have a database server running Windows 2000 Advanced Server and SQL
> > 2000
> > Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> > boot.ini.
> > AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> > instance. Everything has been running fine for the last two years.
> > Now we are planning to add a named instance on the same server for housing
> > Accounting database. My question is:
> > 1) Do I need to enable AWE on both instances of SQL Server.
> > 2) Set upper memory for both instances. Say 4GB for default instance and
> > 1GB for the new named instance based on their usage ratio. Leave 3GB for
> > OS.
> > 3) Do I need to do anything with CPU allocations?
> > Any suggestion. Thanks...Fraz
> >
> >
>
>|||The new Accounting database (new named instance) will use different collation
setting i.e. Latin1_General, Sort order Binary whereas the default instance
is using Latin1-General, case-insensitive,for use with 1252 Character Set.
The database size in the new named instance is 5GB, it will be more busy in
the month-end than other days. The test system performance statistics (Total
Server Memory) shows that currently this instance is using 300-350MB.
The default instance in production is a busy instance as we have about 25
databases of different sizes from 5GB to 200MB. The performance statistics
for this shows that it is currently using 1.7 - 2.0GB out of allocated 5GB.
For CPU affinity, would it be okay to leave it to the Default setting.
Thanks...Fraz
"David Browne" wrote:
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> > We have a database server running Windows 2000 Advanced Server and SQL
> > 2000
> > Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> > boot.ini.
> > AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> > instance. Everything has been running fine for the last two years.
> > Now we are planning to add a named instance on the same server for housing
> > Accounting database. My question is:
> Why a new instance instead of just adding the database to the default
> instance?
> How large are these databases? How busy?
> > 1) Do I need to enable AWE on both instances of SQL Server.
> I would not do that. Either configure the accounting instance to use 2 or
> max memory, or mount the database in the main instance. Either one big
> instance with AWE, or one big instance with AWE and one small one without.
> > 2) Set upper memory for both instances. Say 4GB for default instance and
> > 1GB for the new named instance based on their usage ratio. Leave 3GB for
> > OS.
> The OS doesn't need 3GB. 200-400MB is fine. An instance will "want" a
> certian amount of memory depending on its workload and database size.
> Giving it more is doesn't help at all; giving it less causes increased
> physical IO and a decreased cache hit ratio. AWE is slower than normal
> memory, so your smaller instance will run better without AWE. You should
> evaluate how much memory your instances really need. Perhaps both instances
> will run better with the /3GB switch and AWE off.
> > 3) Do I need to do anything with CPU allocations?
> If you don't then both instances will share all 4 CPU's, which is lets them
> share resources (good), but makes is more difficult to guarantee QOS to
> either workload (bad).
> If you put the database in the default instance you will share memory and
> CPU; in a seperate instance it will not share memory, but will share CPU
> unless you set CPU affinity for the instances. So assuming you want to
> segregate the instances, you should probably do something with the CPU
> affinity.
> David
>
>

AWE and Memory Configuration

We have a database server running Windows 2000 Advanced Server and SQL 2000
Enterprise with 4 CPU and 8MB of memory. PAE has been configured in boot.ini
.
AWE has been enabled and 5120 MB of memory allocated to SQL Server default
instance. Everything has been running fine for the last two years.
Now we are planning to add a named instance on the same server for housing
Accounting database. My question is:
1) Do I need to enable AWE on both instances of SQL Server.
2) Set upper memory for both instances. Say 4GB for default instance and
1GB for the new named instance based on their usage ratio. Leave 3GB for OS
.
3) Do I need to do anything with CPU allocations?
Any suggestion. Thanks...FrazHi
With 8GB, can configure 1 GB and 5.5GB without starving the OS (as long as
it is a dedicated SQL Server).
/3GB must be set in boot.ini
Set AWE for the instance with 5.5GB, and a fixed memory setting.
Do not set AWE for the 1GB instance, but have a fixed memory setting for it
too.
Leave OS and SQL server to manage CPU allocations and leave the defaults.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> We have a database server running Windows 2000 Advanced Server and SQL
> 2000
> Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> boot.ini.
> AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> instance. Everything has been running fine for the last two years.
> Now we are planning to add a named instance on the same server for housing
> Accounting database. My question is:
> 1) Do I need to enable AWE on both instances of SQL Server.
> 2) Set upper memory for both instances. Say 4GB for default instance and
> 1GB for the new named instance based on their usage ratio. Leave 3GB for
> OS.
> 3) Do I need to do anything with CPU allocations?
> Any suggestion. Thanks...Fraz
>|||"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> We have a database server running Windows 2000 Advanced Server and SQL
> 2000
> Enterprise with 4 CPU and 8MB of memory. PAE has been configured in
> boot.ini.
> AWE has been enabled and 5120 MB of memory allocated to SQL Server default
> instance. Everything has been running fine for the last two years.
> Now we are planning to add a named instance on the same server for housing
> Accounting database. My question is:
Why a new instance instead of just adding the database to the default
instance?
How large are these databases? How busy?

> 1) Do I need to enable AWE on both instances of SQL Server.
I would not do that. Either configure the accounting instance to use 2 or
max memory, or mount the database in the main instance. Either one big
instance with AWE, or one big instance with AWE and one small one without.
> 2) Set upper memory for both instances. Say 4GB for default instance and
> 1GB for the new named instance based on their usage ratio. Leave 3GB for
> OS.
The OS doesn't need 3GB. 200-400MB is fine. An instance will "want" a
certian amount of memory depending on its workload and database size.
Giving it more is doesn't help at all; giving it less causes increased
physical IO and a decreased cache hit ratio. AWE is slower than normal
memory, so your smaller instance will run better without AWE. You should
evaluate how much memory your instances really need. Perhaps both instances
will run better with the /3GB switch and AWE off.

> 3) Do I need to do anything with CPU allocations?
If you don't then both instances will share all 4 CPU's, which is lets them
share resources (good), but makes is more difficult to guarantee QOS to
either workload (bad).
If you put the database in the default instance you will share memory and
CPU; in a seperate instance it will not share memory, but will share CPU
unless you set CPU affinity for the instances. So assuming you want to
segregate the instances, you should probably do something with the CPU
affinity.
David|||I really appreciate your prompt feedback. This satisfies all the questions I
had in my mind. Thanks and cheers. Fraz
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> With 8GB, can configure 1 GB and 5.5GB without starving the OS (as long as
> it is a dedicated SQL Server).
> /3GB must be set in boot.ini
> Set AWE for the instance with 5.5GB, and a fixed memory setting.
> Do not set AWE for the 1GB instance, but have a fixed memory setting for i
t
> too.
> Leave OS and SQL server to manage CPU allocations and leave the defaults.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
>
>|||The new Accounting database (new named instance) will use different collatio
n
setting i.e. Latin1_General, Sort order Binary whereas the default instance
is using Latin1-General, case-insensitive,for use with 1252 Character Set.
The database size in the new named instance is 5GB, it will be more busy in
the month-end than other days. The test system performance statistics (Total
Server Memory) shows that currently this instance is using 300-350MB.
The default instance in production is a busy instance as we have about 25
databases of different sizes from 5GB to 200MB. The performance statistics
for this shows that it is currently using 1.7 - 2.0GB out of allocated 5GB.
For CPU affinity, would it be okay to leave it to the Default setting.
Thanks...Fraz
"David Browne" wrote:

> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:CD4267EB-FF45-4272-B737-6997046F38EC@.microsoft.com...
> Why a new instance instead of just adding the database to the default
> instance?
> How large are these databases? How busy?
>
> I would not do that. Either configure the accounting instance to use 2 or
> max memory, or mount the database in the main instance. Either one big
> instance with AWE, or one big instance with AWE and one small one without.
> The OS doesn't need 3GB. 200-400MB is fine. An instance will "want" a
> certian amount of memory depending on its workload and database size.
> Giving it more is doesn't help at all; giving it less causes increased
> physical IO and a decreased cache hit ratio. AWE is slower than normal
> memory, so your smaller instance will run better without AWE. You should
> evaluate how much memory your instances really need. Perhaps both instanc
es
> will run better with the /3GB switch and AWE off.
>
> If you don't then both instances will share all 4 CPU's, which is lets the
m
> share resources (good), but makes is more difficult to guarantee QOS to
> either workload (bad).
> If you put the database in the default instance you will share memory and
> CPU; in a seperate instance it will not share memory, but will share CPU
> unless you set CPU affinity for the instances. So assuming you want to
> segregate the instances, you should probably do something with the CPU
> affinity.
> David
>
>

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreasyes.
Greg Jackson
PDX, Oregon|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers.
Thanks
Andreas
"pdxJaxon" wrote:
> yes.
>
> Greg Jackson
> PDX, Oregon
>
>|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreas
yes.
Greg Jackson
PDX, Oregon
|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers.
Thanks
Andreas
"pdxJaxon" wrote:

> yes.
>
> Greg Jackson
> PDX, Oregon
>
>
|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreasyes.
Greg Jackson
PDX, Oregon|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers
.
Thanks
Andreas
"pdxJaxon" wrote:

> yes.
>
> Greg Jackson
> PDX, Oregon
>
>|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ