|
Home > Archive > MS SQL Server > July 2005 > sql server memonry allocation
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sql server memonry allocation
|
|
| Inamori 2005-07-08, 3:23 am |
| Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
i run spconfigure
i see the max server memory is
max server memory (MB)
4 2147483647 2147483647 2147483647
how can i set this so that i want the sql server full utlize the ram ..lets
say 3GB
| |
| Mike Hodgson 2005-07-08, 3:23 am |
| It's already fully utilising as much memory as it's allowed. 32-bit
apps split the max addressable memory space into two parts - the
kernel-mode part and the user-mode part (2GB each). To allow 32-bit
apps to use 3GB (ie. limit the kernel to 1GB) you specify the /3gb
switch in the boot.ini file.
If you've only got 4GB RAM in the box then that's all you need to bother
with. There's no point in playing with AWE or PAE.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Inamori wrote:
>Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
>
>i run spconfigure
>
>i see the max server memory is
>
>max server memory (MB)
>4 2147483647 2147483647 2147483647
>
>how can i set this so that i want the sql server full utlize the ram ..lets
>say 3GB
>
>
>
>
>
| |
| Inamori 2005-07-08, 3:23 am |
| Hi Mike,
that mean it is impossible for me to set 4G because it will destroy the kernel running
For 3GB, I can set it in boot.ini....am I right??....does it affect the 1GB performance in Kernel?
Yes for AWE, it is for Adv Server and Datacenter
Correct me if i am wrong
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:O8VBzG2gFHA.2644@TK2MSFTNGP09.phx.gbl...
It's already fully utilising as much memory as it's allowed. 32-bit apps split the max addressable memory space into two parts - the kernel-mode part and the user-mode part (2GB each). To allow 32-bit apps to use 3GB (ie. limit the kernel to 1GB) you specify the /3gb switch in the boot.ini file.
If you've only got 4GB RAM in the box then that's all you need to bother with. There's no point in playing with AWE or PAE.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
Inamori wrote:
Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
i run spconfigure
i see the max server memory is
max server memory (MB)
4 2147483647 2147483647 2147483647
how can i set this so that i want the sql server full utlize the ram ...lets
say 3GB
| |
| Hari Prasad 2005-07-08, 7:23 am |
| Hi,
You cant have entire 4 GB for SQL Server, Only 3 GB can be given for SQL Server.
Yes, To give 3GB for your SQL SERVER application add /3GB in boot.ini. This will ensure that 3 GB can be used for programs and rest 1 GB for OS.
If you have more than 4GB Ram then you can enable AWE enabled option.In your case currently it is not required to enable AWE.
See the below article:-
http://www.sql-server-performance.com/awe_memory.asp
Thanks
Hari
SQL Server MVP
"Inamori" <test@test.com> wrote in message news:uOYl1U4gFHA.1052@TK2MSFTNGP10.phx.gbl...
Hi Mike,
that mean it is impossible for me to set 4G because it will destroy the kernel running
For 3GB, I can set it in boot.ini....am I right??....does it affect the 1GB performance in Kernel?
Yes for AWE, it is for Adv Server and Datacenter
Correct me if i am wrong
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:O8VBzG2gFHA.2644@TK2MSFTNGP09.phx.gbl...
It's already fully utilising as much memory as it's allowed. 32-bit apps split the max addressable memory space into two parts - the kernel-mode part and the user-mode part (2GB each). To allow 32-bit apps to use 3GB (ie. limit the kernel to 1GB) you specify the /3gb switch in the boot.ini file.
If you've only got 4GB RAM in the box then that's all you need to bother with. There's no point in playing with AWE or PAE.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
Inamori wrote:
Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
i run spconfigure
i see the max server memory is
max server memory (MB)
4 2147483647 2147483647 2147483647
how can i set this so that i want the sql server full utlize the ram ...lets
say 3GB
| |
| Inamori 2005-07-08, 7:23 am |
| yes
thank you so much
i understand more now....
so the sole question i need to ask myself
will 1GB be fair enough for OS only
the answer should be no
right
"Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message news:u07NM95gFHA.3608@TK2MSFTNGP12.phx.gbl...
Hi,
You cant have entire 4 GB for SQL Server, Only 3 GB can be given for SQL Server.
Yes, To give 3GB for your SQL SERVER application add /3GB in boot.ini. This will ensure that 3 GB can be used for programs and rest 1 GB for OS.
If you have more than 4GB Ram then you can enable AWE enabled option.In your case currently it is not required to enable AWE.
See the below article:-
http://www.sql-server-performance.com/awe_memory.asp
Thanks
Hari
SQL Server MVP
"Inamori" <test@test.com> wrote in message news:uOYl1U4gFHA.1052@TK2MSFTNGP10.phx.gbl...
Hi Mike,
that mean it is impossible for me to set 4G because it will destroy the kernel running
For 3GB, I can set it in boot.ini....am I right??....does it affect the 1GB performance in Kernel?
Yes for AWE, it is for Adv Server and Datacenter
Correct me if i am wrong
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:O8VBzG2gFHA.2644@TK2MSFTNGP09.phx.gbl...
It's already fully utilising as much memory as it's allowed. 32-bit apps split the max addressable memory space into two parts - the kernel-mode part and the user-mode part (2GB each). To allow 32-bit apps to use 3GB (ie. limit the kernel to 1GB) you specify the /3gb switch in the boot.ini file.
If you've only got 4GB RAM in the box then that's all you need to bother with. There's no point in playing with AWE or PAE.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
Inamori wrote:
Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
i run spconfigure
i see the max server memory is
max server memory (MB)
4 2147483647 2147483647 2147483647
how can i set this so that i want the sql server full utlize the ram ...lets
say 3GB
| |
| Hari Prasad 2005-07-09, 3:23 am |
| Hi,
If you have less than 16GB physical memory then 1 GB is more than enough for the OS. Incase if you have more than 16 GB RAM then
do not include /3GB switch in Boot.ini.
Tahnks
Hari
SQL Server MVP
"Inamori" <test@test.com> wrote in message news:uEKg4L6gFHA.3540@TK2MSFTNGP14.phx.gbl...
yes
thank you so much
i understand more now....
so the sole question i need to ask myself
will 1GB be fair enough for OS only
the answer should be no
right
"Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message news:u07NM95gFHA.3608@TK2MSFTNGP12.phx.gbl...
Hi,
You cant have entire 4 GB for SQL Server, Only 3 GB can be given for SQL Server.
Yes, To give 3GB for your SQL SERVER application add /3GB in boot.ini. This will ensure that 3 GB can be used for programs and rest 1 GB for OS.
If you have more than 4GB Ram then you can enable AWE enabled option.In your case currently it is not required to enable AWE.
See the below article:-
http://www.sql-server-performance.com/awe_memory.asp
Thanks
Hari
SQL Server MVP
"Inamori" <test@test.com> wrote in message news:uOYl1U4gFHA.1052@TK2MSFTNGP10.phx.gbl...
Hi Mike,
that mean it is impossible for me to set 4G because it will destroy the kernel running
For 3GB, I can set it in boot.ini....am I right??....does it affect the 1GB performance in Kernel?
Yes for AWE, it is for Adv Server and Datacenter
Correct me if i am wrong
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:O8VBzG2gFHA.2644@TK2MSFTNGP09.phx.gbl...
It's already fully utilising as much memory as it's allowed. 32-bit apps split the max addressable memory space into two parts - the kernel-mode part and the user-mode part (2GB each). To allow 32-bit apps to use 3GB (ie. limit the kernel to 1GB) you specify the /3gb switch in the boot.ini file.
If you've only got 4GB RAM in the box then that's all you need to bother with. There's no point in playing with AWE or PAE.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
Inamori wrote:
Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
i run spconfigure
i see the max server memory is
max server memory (MB)
4 2147483647 2147483647 2147483647
how can i set this so that i want the sql server full utlize the ram ...lets
say 3GB
| |
| Mike Hodgson 2005-07-09, 7:23 am |
| This is because to manage more than 16GB of addressable memory (the
figure is a little debatable - some experts say 12GB, some say 16GB) the
kernel needs more than 1GB. Only allowing 1GB for the kernel space
limits the size of some data structures stored in the kernel mode space,
such as the table Windows uses to manage the physical memory in the box.
For a box with only 4GB of physical RAM I'd just enable the /3gb switch
in boot.ini (and reboot). Assuming you're running SQL 2000 Enterprise
Edition (Standard Edition only supports 2GB of RAM) then that's all you
need. For more than 4GB of RAM, you'd need to enable PAE (Physical
Address Extension) in boot.ini. That will enable the OS to present more
than 4GB of physical RAM to applications. To allow SQL Server to
address more than the standard 4GB virtual address space (max
addressable memory of a 32-bit application) you'd need to use another
memory model - for SQL Server it is AWE (Address Windowing Extensions)
configured with sp_configure.
Then to address more than 16GB of RAM, the OS needs its full 2GB for the
table it uses to manage the physical memory in the box. So you'd have
to remove the /3gb switch, if it was enabled. You'd still have to use
AWE in SQL Server to access the memory over 4GB (i.e. from 4GB up to
64GB, or however much RAM you've got in your server).
It's a little confusing because of the kernel-mode/user-mode split in a
32-bit app. I hope this explanation helps you understand it a bit better.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
[color=darkred]
> Hi,
>
> If you have less than 16GB physical memory then 1 GB is more than
> enough for the OS. Incase if you have more than 16 GB RAM then
> do not include /3GB switch in Boot.ini.
>
> Tahnks
> Hari
> SQL Server MVP
>
> "Inamori" <test@test.com <mailto:test@test.com>> wrote in message
> news:uEKg4L6gFHA.3540@TK2MSFTNGP14.phx.gbl...
> yes
> thank you so much
> i understand more now....
>
> so the sole question i need to ask myself
> will 1GB be fair enough for OS only
>
> the answer should be no
> right
>
> "Hari Prasad" < hari_prasad_k@hotmai
l.com
> < mailto:hari_prasad_k
@hotmail.com>> wrote in message
> news:u07NM95gFHA.3608@TK2MSFTNGP12.phx.gbl...
> Hi,
>
>
> You cant have entire 4 GB for SQL Server, Only 3 GB can be
> given for SQL Server.
>
> Yes, To give 3GB for your SQL SERVER application add /3GB in
> boot.ini. This will ensure that 3 GB can be used for programs
> and rest 1 GB for OS.
>
> If you have more than 4GB Ram then you can enable AWE enabled
> option.In your case currently it is not required to enable AWE.
>
> See the below article:-
>
> http://www.sql-server-performance.com/awe_memory.asp
>
>
> Thanks
> Hari
> SQL Server MVP
>
> "Inamori" <test@test.com <mailto:test@test.com>> wrote in
> message news:uOYl1U4gFHA.1052@TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> that mean it is impossible for me to set 4G because it
> will destroy the kernel running
>
> For 3GB, I can set it in boot.ini....am I right??....does
> it affect the 1GB performance in Kernel?
>
> Yes for AWE, it is for Adv Server and Datacenter
>
> Correct me if i am wrong
>
> "Mike Hodgson" <mike.hodgson@mallesons.nospam.com
> <mailto:mike.hodgson@mallesons.nospam.com>> wrote in
> message news:O8VBzG2gFHA.2644@TK2MSFTNGP09.phx.gbl...
> It's already fully utilising as much memory as it's
> allowed. 32-bit apps split the max addressable memory
> space into two parts - the kernel-mode part and the
> user-mode part (2GB each). To allow 32-bit apps to
> use 3GB (ie. limit the kernel to 1GB) you specify the
> /3gb switch in the boot.ini file.
>
> If you've only got 4GB RAM in the box then that's all
> you need to bother with. There's no point in playing
> with AWE or PAE.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> Inamori wrote:
>
| |
| Inamori 2005-07-10, 8:23 pm |
| Dear all,
I am sorry that I cannot find any boot.ini file in the server by serach function...
any other options to push it to 3GB
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:%23IDyjjHhFHA.1252@TK2MSFTNGP09.phx.gbl...
This is because to manage more than 16GB of addressable memory (the figure is a little debatable - some experts say 12GB, some say 16GB) the kernel needs more than 1GB. Only allowing 1GB for the kernel space limits the size of some data structures stored in the kernel mode space, such as the table Windows uses to manage the physical memory in the box.
For a box with only 4GB of physical RAM I'd just enable the /3gb switch in boot.ini (and reboot). Assuming you're running SQL 2000 Enterprise Edition (Standard Edition only supports 2GB of RAM) then that's all you need. For more than 4GB of RAM, you'd need to enable PAE (Physical Address Extension) in boot.ini. That will enable the OS to present more than 4GB of physical RAM to applications. To allow SQL Server to address more than the standard 4GB virtual address space (max addressable memory of a 32-bit application) you'd need to use another memory model - for SQL Server it is AWE (Address Windowing Extensions) configured with sp_configure.
Then to address more than 16GB of RAM, the OS needs its full 2GB for the table it uses to manage the physical memory in the box. So you'd have to remove the /3gb switch, if it was enabled. You'd still have to use AWE in SQL Server to access the memory over 4GB (i.e. from 4GB up to 64GB, or however much RAM you've got in your server).
It's a little confusing because of the kernel-mode/user-mode split in a 32-bit app. I hope this explanation helps you understand it a bit better.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
If you have less than 16GB physical memory then 1 GB is more than enough for the OS. Incase if you have more than 16 GB RAM then
do not include /3GB switch in Boot.ini.
Tahnks
Hari
SQL Server MVP
"Inamori" <test@test.com> wrote in message news:uEKg4L6gFHA.3540@TK2MSFTNGP14.phx.gbl...
yes
thank you so much
i understand more now....
so the sole question i need to ask myself
will 1GB be fair enough for OS only
the answer should be no
right
"Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message news:u07NM95gFHA.3608@TK2MSFTNGP12.phx.gbl...
Hi,
You cant have entire 4 GB for SQL Server, Only 3 GB can be given for SQL Server.
Yes, To give 3GB for your SQL SERVER application add /3GB in boot.ini. This will ensure that 3 GB can be used for programs and rest 1 GB for OS.
If you have more than 4GB Ram then you can enable AWE enabled option.In your case currently it is not required to enable AWE.
See the below article:-
http://www.sql-server-performance.com/awe_memory.asp
Thanks
Hari
SQL Server MVP
"Inamori" <test@test.com> wrote in message news:uOYl1U4gFHA.1052@TK2MSFTNGP10.phx.gbl...
Hi Mike,
that mean it is impossible for me to set 4G because it will destroy the kernel running
For 3GB, I can set it in boot.ini....am I right??....does it affect the 1GB performance in Kernel?
Yes for AWE, it is for Adv Server and Datacenter
Correct me if i am wrong
"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:O8VBzG2gFHA.2644@TK2MSFTNGP09.phx.gbl...
It's already fully utilising as much memory as it's allowed. 32-bit apps split the max addressable memory space into two parts - the kernel-mode part and the user-mode part (2GB each). To allow 32-bit apps to use 3GB (ie. limit the kernel to 1GB) you specify the /3gb switch in the boot.ini file.
If you've only got 4GB RAM in the box then that's all you need to bother with. There's no point in playing with AWE or PAE.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
Inamori wrote:
Currently we use WIn 2000 server/ SQL server enterprise edition/4G Ram/.
i run spconfigure
i see the max server memory is
max server memory (MB)
4 2147483647 2147483647 2147483647
how can i set this so that i want the sql server full utlize the ram ....lets
say 3GB
| |
| Mike Hodgson 2005-07-12, 9:23 am |
| boot.ini is a *hidden system file *in the root directory of the system
partition on the server, usually _C:\boot.ini_. To change it you'll
need to change the file attributes to remove the /hidden/ & /system/
attributes with
C:\> attrib -s -h boot.ini
Then change the file with notepad.exe to add /3GB to the operating
system line.
Then change the /hidden/ & /system/ attributes back again with
C:\> attrib +s +h boot.ini
and reboot.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Inamori wrote:
[color=darkred]
> Dear all,
>
> I am sorry that I cannot find any boot.ini file in the server by
> serach function...
>
> any other options to push it to 3GB
>
> "Mike Hodgson" <mike.hodgson@mallesons.nospam.com
> <mailto:mike.hodgson@mallesons.nospam.com>> wrote in message
> news:%23IDyjjHhFHA.1252@TK2MSFTNGP09.phx.gbl...
> This is because to manage more than 16GB of addressable memory
> (the figure is a little debatable - some experts say 12GB, some
> say 16GB) the kernel needs more than 1GB. Only allowing 1GB for
> the kernel space limits the size of some data structures stored in
> the kernel mode space, such as the table Windows uses to manage
> the physical memory in the box.
>
> For a box with only 4GB of physical RAM I'd just enable the /3gb
> switch in boot.ini (and reboot). Assuming you're running SQL 2000
> Enterprise Edition (Standard Edition only supports 2GB of RAM)
> then that's all you need. For more than 4GB of RAM, you'd need to
> enable PAE (Physical Address Extension) in boot.ini. That will
> enable the OS to present more than 4GB of physical RAM to
> applications. To allow SQL Server to address more than the
> standard 4GB virtual address space (max addressable memory of a
> 32-bit application) you'd need to use another memory model - for
> SQL Server it is AWE (Address Windowing Extensions) configured
> with sp_configure.
>
> Then to address more than 16GB of RAM, the OS needs its full 2GB
> for the table it uses to manage the physical memory in the box.
> So you'd have to remove the /3gb switch, if it was enabled. You'd
> still have to use AWE in SQL Server to access the memory over 4GB
> (i.e. from 4GB up to 64GB, or however much RAM you've got in your
> server).
>
> It's a little confusing because of the kernel-mode/user-mode split
> in a 32-bit app. I hope this explanation helps you understand it
> a bit better.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> Hari Prasad wrote:
>
|
|
|
|
|