Autonumber field: physical

Showing posts with label physical. Show all posts

Monday, March 19, 2012

Back Up question

I currently have my database backed up to the same physical drive where the data resides. In the event of drive failure, I would lose everything. Due to space considerations on a tape, I was wondering if I can copy only the database backup file (.BAK) and not the transaction log backup (.TRN). Can I go ahead with this approach if I do not want point-in-time recovery and I am satisfied with only what resides on .BAK file? Will I able to restore the database from the tape if the tape contains only the (.BAK) file ?

Thanks.Sure...

How often do you dump the transaction log?

But I don't think it's advisable...

How big a database are we talking about...

Ever consider compression?|||I often dump my backups across the network to a share on another server. It won't work with very large databases very well, but I've had pretty good luck so far. I find that writing backups to tape can be extremely slow.

As for not keeping the transaction logs, I have to agree with Brett; I would prefer to keep mine.

Regards,

hmscott|||Do you dump across a network, or locally the copy?

I've always thought a dump over a network could corrupt the dump

Never seen it...just always believed it could happen...|||I had all kinds of problems with it in 6.5 and 7.0. I've never seen any problems with it in 2000 though. We do it all the time. All of our servers are dumped to a central repository. We even do restores from there, including log shipping restores. I've never had any corruption doing it this way. Of course, we do have a dual 16gb core with fiber to all servers. We're upgrading to dual 64gb. A lot has changed over the years I guess.

I would definitely consider compression if I were that strapped on space and bandwidth. Many times SQL Backup files will shrink to 70-80 percent of the original size.

Thursday, March 8, 2012

AWE Question

What would be the optimal max server memory setting for the following
machine?
Dedicated SQL Server:
Intel Xeon MP 3.3 GHz (4 physical Processors)
136 GB (C Drive)
8.0 GB RAM
Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
SQL Server Enterprise 2000, with SP3
The machine has following database sizes. All databases are being accessed
all the time by around 300+ concurrent users.
DB#1 - 536.44 MB
DB#2 - 1.09 GB
DB#3 - 3.27 GB
DB#4 - 4.92 GB
DB#5 - 5.29 GB
DB#6 - 15.09 GB
DB#7 - 50.13 GB
I am still learning the ins and outs of configuring higher end db servers.
Are there any other non-default server settings that I should change?"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uqJkBN9uGHA.4756@.TK2MSFTNGP04.phx.gbl...
> What would be the optimal max server memory setting for the following
> machine?
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
> Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
> SQL Server Enterprise 2000, with SP3
> The machine has following database sizes. All databases are being accessed
> all the time by around 300+ concurrent users.
> DB#1 - 536.44 MB
> DB#2 - 1.09 GB
> DB#3 - 3.27 GB
> DB#4 - 4.92 GB
> DB#5 - 5.29 GB
> DB#6 - 15.09 GB
> DB#7 - 50.13 GB
> I am still learning the ins and outs of configuring higher end db servers.
>
Does each user access all databases or does each database have its own set
of users?
If the different databsaes represent seperate applications, you should
consider dividing the server into multiple SQL instances to divide up the
memory on the box and optionally the CPU's into 2 or 3 different resource
pools. 32bit SQL Server works more efficiently when it doesn't have to use
AWE to access memory, and multiple instances allow you to easilly partition
memory and CPU resources between the workloads.
David|||>
> Does each user access all databases or does each database have its own set
> of users?
All databases are the same application (different clients on each db similar
to an ASP service).
Each user usually accesses only a single database. Admin users would need
access to all.
> If the different databsaes represent seperate applications, you should
> consider dividing the server into multiple SQL instances to divide up the
> memory on the box and optionally the CPU's into 2 or 3 different resource
> pools. 32bit SQL Server works more efficiently when it doesn't have to
> use AWE to access memory, and multiple instances allow you to easilly
> partition memory and CPU resources between the workloads.
>
Interesting option. Your making me think about that.
You mention that 32bit SQL Server works more efficiently when it doesn't use
AWE. Does it affect performance that much?
My concern is the 50 GB database itself would benefit from more then 2 GB of
RAM.
You also mention that you can configure CPUs into different resource pools.
How does this work? Do you have a knowledge base link?|||"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
> >
>> Does each user access all databases or does each database have its own
>> set of users?
> All databases are the same application (different clients on each db
> similar to an ASP service).
> Each user usually accesses only a single database. Admin users would need
> access to all.
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up the
>> memory on the box and optionally the CPU's into 2 or 3 different resource
>> pools. 32bit SQL Server works more efficiently when it doesn't have to
>> use AWE to access memory, and multiple instances allow you to easilly
>> partition memory and CPU resources between the workloads.
> Interesting option. Your making me think about that.
> You mention that 32bit SQL Server works more efficiently when it doesn't
> use AWE. Does it affect performance that much?
> My concern is the 50 GB database itself would benefit from more then 2 GB
> of RAM.
Yes it would, but you have 7 databses and only 8G of ram. How much ram can
you afford to allocate for that one workload. And using the /3GB switch you
can give a SQL instance access to 3GB of memory without using AWE.
Also you will have one procedure cache per instance, and on 32bit SQL is
strictly limited in size. Multiple instances would have multiple procedure
caches.
"On 32-bit platforms configured with 4 GB or more of physical memory, you
can either use Address Windowing Extensions (AWE) or use multiple instances
of SQL Server as a way to fully utilize the large amount of physical memory.
AWE may work well for some scenarios; however, you should be aware that AWE
memory can only be used for data cache. The memory for procedure cache,
connections, locks, and other internal resources of SQL Server must come
from the 2 GB (or 3GB, depending on the settings used) portion of the
virtual memory. On systems needing to support a large number of databases
and user connections, multiple instances of SQL Server may be a better
approach to fully alleviate the 2 GB or 3GB memory constraint imposed by the
32-bit platform for these data structures."
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> You also mention that you can configure CPUs into different resource
> pools. How does this work? Do you have a knowledge base link?
>
Simple partition of CPU's to SQL Instances should be done with the "affinity
mask" server option.
SQL Server 2005 Books Online
affinity mask Option
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Dynamic CPU allocation policies can be implemented with WSRM:
Windows System Resource Manager
http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would need
>> access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it doesn't
>> have to use AWE to access memory, and multiple instances allow you to
>> easilly partition memory and CPU resources between the workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2 GB
>> of RAM.
> Yes it would, but you have 7 databses and only 8G of ram. How much ram
> can you afford to allocate for that one workload. And using the /3GB
> switch you can give a SQL instance access to 3GB of memory without using
> AWE.
> Also you will have one procedure cache per instance, and on 32bit SQL is
> strictly limited in size. Multiple instances would have multiple
> procedure caches.
> "On 32-bit platforms configured with 4 GB or more of physical memory, you
> can either use Address Windowing Extensions (AWE) or use multiple
> instances of SQL Server as a way to fully utilize the large amount of
> physical memory. AWE may work well for some scenarios; however, you should
> be aware that AWE memory can only be used for data cache. The memory for
> procedure cache, connections, locks, and other internal resources of SQL
> Server must come from the 2 GB (or 3GB, depending on the settings used)
> portion of the virtual memory. On systems needing to support a large
> number of databases and user connections, multiple instances of SQL Server
> may be a better approach to fully alleviate the 2 GB or 3GB memory
> constraint imposed by the 32-bit platform for these data structures."
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
> Simple partition of CPU's to SQL Instances should be done with the
> "affinity mask" server option.
> SQL Server 2005 Books Online
> affinity mask Option
> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>
> Dynamic CPU allocation policies can be implemented with WSRM:
> Windows System Resource Manager
> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
> David
>
Thanks David. This info is very helpful.|||Isaac
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
I'd consider (based on your description) MAX Memory 7GB
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23$AxxK%23uGHA.4752@.TK2MSFTNGP02.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
>> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
>> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would
>> need access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it
>> doesn't have to use AWE to access memory, and multiple instances allow
>> you to easilly partition memory and CPU resources between the
>> workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2
>> GB of RAM.
>> Yes it would, but you have 7 databses and only 8G of ram. How much ram
>> can you afford to allocate for that one workload. And using the /3GB
>> switch you can give a SQL instance access to 3GB of memory without using
>> AWE.
>> Also you will have one procedure cache per instance, and on 32bit SQL is
>> strictly limited in size. Multiple instances would have multiple
>> procedure caches.
>> "On 32-bit platforms configured with 4 GB or more of physical memory, you
>> can either use Address Windowing Extensions (AWE) or use multiple
>> instances of SQL Server as a way to fully utilize the large amount of
>> physical memory. AWE may work well for some scenarios; however, you
>> should be aware that AWE memory can only be used for data cache. The
>> memory for procedure cache, connections, locks, and other internal
>> resources of SQL Server must come from the 2 GB (or 3GB, depending on the
>> settings used) portion of the virtual memory. On systems needing to
>> support a large number of databases and user connections, multiple
>> instances of SQL Server may be a better approach to fully alleviate the 2
>> GB or 3GB memory constraint imposed by the 32-bit platform for these data
>> structures."
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
>>
>> Simple partition of CPU's to SQL Instances should be done with the
>> "affinity mask" server option.
>> SQL Server 2005 Books Online
>> affinity mask Option
>> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>>
>> Dynamic CPU allocation policies can be implemented with WSRM:
>> Windows System Resource Manager
>> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
>> David
> Thanks David. This info is very helpful.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYF1sPEvGHA.724@.TK2MSFTNGP05.phx.gbl...
> Isaac
>> Dedicated SQL Server:
>> Intel Xeon MP 3.3 GHz (4 physical Processors)
>> 136 GB (C Drive)
>> 8.0 GB RAM
>
> I'd consider (based on your description) MAX Memory 7GB
>
Thanks Uri.

Saturday, February 25, 2012

AWE

Hi,
Imagine that my server has 4GB physical memory. I have added /3GB switch to
boot.ini.
Should I enable AWE configuration for my SQL Server to use 3 GB address
space? Or AWE must only be used when I have more than 4GB memory?
Thanks in advance,
LeilaHello,
AWE can be used only if you have more than 4 GB physical memory. But The
/3GB switch is used to tell SQL Server to take advantage of 3GB out of the
base 4GB
of RAM that Windows OS supports natively. If you don't specify this option,
then SQL Server will only take advantage of 2GB of the first 4GB of RAM in
the
server, essentially wasting 1GB of RAM
Please post the OS and SQL Server edition. This is because For SQL Server
2005 - 64 bit running in Windows 2003 you donot want any changes in BOOT.INI
to use the additional RAM.
Thanks
Hari
"Leila" <Leilas@.hotpop.com> wrote in message
news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Imagine that my server has 4GB physical memory. I have added /3GB switch
> to boot.ini.
> Should I enable AWE configuration for my SQL Server to use 3 GB address
> space? Or AWE must only be used when I have more than 4GB memory?
> Thanks in advance,
> Leila
>|||> AWE can be used only if you have more than 4 GB physical memory.
That's not correct. Take a look at some empirical data at
http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
Linchi
"Hari Prasad" wrote:
> Hello,
> AWE can be used only if you have more than 4 GB physical memory. But The
> /3GB switch is used to tell SQL Server to take advantage of 3GB out of the
> base 4GB
> of RAM that Windows OS supports natively. If you don't specify this option,
> then SQL Server will only take advantage of 2GB of the first 4GB of RAM in
> the
> server, essentially wasting 1GB of RAM
> Please post the OS and SQL Server edition. This is because For SQL Server
> 2005 - 64 bit running in Windows 2003 you donot want any changes in BOOT.INI
> to use the additional RAM.
> Thanks
> Hari
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
> > Hi,
> > Imagine that my server has 4GB physical memory. I have added /3GB switch
> > to boot.ini.
> > Should I enable AWE configuration for my SQL Server to use 3 GB address
> > space? Or AWE must only be used when I have more than 4GB memory?
> > Thanks in advance,
> > Leila
> >
>
>|||Hello Linchi,
If I remember correct for a server with 4 GB RAM running in SQL Server 2005
32 bit and Windows 2003 Advanced, all I did is just /3GB switch in Boot.ini
to
use 3 GB RAM. I will take a look into the server on Moday and keep you
posted
Thanks
Hari
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>> AWE can be used only if you have more than 4 GB physical memory.
> That's not correct. Take a look at some empirical data at
> http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
> Linchi
> "Hari Prasad" wrote:
>> Hello,
>> AWE can be used only if you have more than 4 GB physical memory. But The
>> /3GB switch is used to tell SQL Server to take advantage of 3GB out of
>> the
>> base 4GB
>> of RAM that Windows OS supports natively. If you don't specify this
>> option,
>> then SQL Server will only take advantage of 2GB of the first 4GB of RAM
>> in
>> the
>> server, essentially wasting 1GB of RAM
>> Please post the OS and SQL Server edition. This is because For SQL Server
>> 2005 - 64 bit running in Windows 2003 you donot want any changes in
>> BOOT.INI
>> to use the additional RAM.
>> Thanks
>> Hari
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>> > Hi,
>> > Imagine that my server has 4GB physical memory. I have added /3GB
>> > switch
>> > to boot.ini.
>> > Should I enable AWE configuration for my SQL Server to use 3 GB address
>> > space? Or AWE must only be used when I have more than 4GB memory?
>> > Thanks in advance,
>> > Leila
>> >
>>|||Hari;
My comment had nothing to do with whether /3GB is used, how it should be
used, or what impact it may have. I was commenting on the statement that AWE
can be used only if you have more than 4GB physical memory. As the data at
the link indicate that setting AWE has an impact on SQL Server memory
consumption even with 4GB physical memory. And if you want your bpool to
consume a certain amount of memory under 4GB, you may need to use AWE to
accomplish that.
Now, whether or not you should use AWE with less than 4GB of physical memory
or whether there is any compelling rason for doing so are entirely different
issue.
Linchi
"Hari Prasad" wrote:
> Hello Linchi,
> If I remember correct for a server with 4 GB RAM running in SQL Server 2005
> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in Boot.ini
> to
> use 3 GB RAM. I will take a look into the server on Moday and keep you
> posted
> Thanks
> Hari
>
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
> >> AWE can be used only if you have more than 4 GB physical memory.
> >
> > That's not correct. Take a look at some empirical data at
> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
> >
> > Linchi
> >
> > "Hari Prasad" wrote:
> >
> >> Hello,
> >>
> >> AWE can be used only if you have more than 4 GB physical memory. But The
> >> /3GB switch is used to tell SQL Server to take advantage of 3GB out of
> >> the
> >> base 4GB
> >> of RAM that Windows OS supports natively. If you don't specify this
> >> option,
> >> then SQL Server will only take advantage of 2GB of the first 4GB of RAM
> >> in
> >> the
> >> server, essentially wasting 1GB of RAM
> >>
> >> Please post the OS and SQL Server edition. This is because For SQL Server
> >> 2005 - 64 bit running in Windows 2003 you donot want any changes in
> >> BOOT.INI
> >> to use the additional RAM.
> >>
> >> Thanks
> >> Hari
> >>
> >>
> >>
> >> "Leila" <Leilas@.hotpop.com> wrote in message
> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
> >> > Hi,
> >> > Imagine that my server has 4GB physical memory. I have added /3GB
> >> > switch
> >> > to boot.ini.
> >> > Should I enable AWE configuration for my SQL Server to use 3 GB address
> >> > space? Or AWE must only be used when I have more than 4GB memory?
> >> > Thanks in advance,
> >> > Leila
> >> >
> >>
> >>
> >>
>
>|||Thanks Hari and Linchi!
Based on documents that I have read, /3GB causes that kernel mode use only
1GB and Virtual Address Space for applications therefore can reserve up to
3GB. Whereas 32-bit based Windows can address up to 4GB of memory, therefore
I think by using /3GB switch we don't need to enable AWE for SQL Server to
use 3GB. Because we are not beyond the 4GB limit of addressing in this
situation.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
> Hari;
> My comment had nothing to do with whether /3GB is used, how it should be
> used, or what impact it may have. I was commenting on the statement that
> AWE
> can be used only if you have more than 4GB physical memory. As the data at
> the link indicate that setting AWE has an impact on SQL Server memory
> consumption even with 4GB physical memory. And if you want your bpool to
> consume a certain amount of memory under 4GB, you may need to use AWE to
> accomplish that.
> Now, whether or not you should use AWE with less than 4GB of physical
> memory
> or whether there is any compelling rason for doing so are entirely
> different
> issue.
> Linchi
> "Hari Prasad" wrote:
>> Hello Linchi,
>> If I remember correct for a server with 4 GB RAM running in SQL Server
>> 2005
>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>> Boot.ini
>> to
>> use 3 GB RAM. I will take a look into the server on Moday and keep you
>> posted
>> Thanks
>> Hari
>>
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >
>> > That's not correct. Take a look at some empirical data at
>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>> >
>> > Linchi
>> >
>> > "Hari Prasad" wrote:
>> >
>> >> Hello,
>> >>
>> >> AWE can be used only if you have more than 4 GB physical memory. But
>> >> The
>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB out of
>> >> the
>> >> base 4GB
>> >> of RAM that Windows OS supports natively. If you don't specify this
>> >> option,
>> >> then SQL Server will only take advantage of 2GB of the first 4GB of
>> >> RAM
>> >> in
>> >> the
>> >> server, essentially wasting 1GB of RAM
>> >>
>> >> Please post the OS and SQL Server edition. This is because For SQL
>> >> Server
>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes in
>> >> BOOT.INI
>> >> to use the additional RAM.
>> >>
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >>
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>> >> > Hi,
>> >> > Imagine that my server has 4GB physical memory. I have added /3GB
>> >> > switch
>> >> > to boot.ini.
>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB
>> >> > address
>> >> > space? Or AWE must only be used when I have more than 4GB memory?
>> >> > Thanks in advance,
>> >> > Leila
>> >> >
>> >>
>> >>
>> >>
>>|||That is correct and the most recommended approach with 4GB.
--
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Thanks Hari and Linchi!
> Based on documents that I have read, /3GB causes that kernel mode use only
> 1GB and Virtual Address Space for applications therefore can reserve up to
> 3GB. Whereas 32-bit based Windows can address up to 4GB of memory,
> therefore I think by using /3GB switch we don't need to enable AWE for SQL
> Server to use 3GB. Because we are not beyond the 4GB limit of addressing
> in this situation.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>> Hari;
>> My comment had nothing to do with whether /3GB is used, how it should be
>> used, or what impact it may have. I was commenting on the statement that
>> AWE
>> can be used only if you have more than 4GB physical memory. As the data
>> at
>> the link indicate that setting AWE has an impact on SQL Server memory
>> consumption even with 4GB physical memory. And if you want your bpool to
>> consume a certain amount of memory under 4GB, you may need to use AWE to
>> accomplish that.
>> Now, whether or not you should use AWE with less than 4GB of physical
>> memory
>> or whether there is any compelling rason for doing so are entirely
>> different
>> issue.
>> Linchi
>> "Hari Prasad" wrote:
>> Hello Linchi,
>> If I remember correct for a server with 4 GB RAM running in SQL Server
>> 2005
>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>> Boot.ini
>> to
>> use 3 GB RAM. I will take a look into the server on Moday and keep you
>> posted
>> Thanks
>> Hari
>>
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >
>> > That's not correct. Take a look at some empirical data at
>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>> >
>> > Linchi
>> >
>> > "Hari Prasad" wrote:
>> >
>> >> Hello,
>> >>
>> >> AWE can be used only if you have more than 4 GB physical memory. But
>> >> The
>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB out
>> >> of
>> >> the
>> >> base 4GB
>> >> of RAM that Windows OS supports natively. If you don't specify this
>> >> option,
>> >> then SQL Server will only take advantage of 2GB of the first 4GB of
>> >> RAM
>> >> in
>> >> the
>> >> server, essentially wasting 1GB of RAM
>> >>
>> >> Please post the OS and SQL Server edition. This is because For SQL
>> >> Server
>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes in
>> >> BOOT.INI
>> >> to use the additional RAM.
>> >>
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >>
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>> >> > Hi,
>> >> > Imagine that my server has 4GB physical memory. I have added /3GB
>> >> > switch
>> >> > to boot.ini.
>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB
>> >> > address
>> >> > space? Or AWE must only be used when I have more than 4GB memory?
>> >> > Thanks in advance,
>> >> > Leila
>> >> >
>> >>
>> >>
>> >>
>>
>|||Thanks indeed Andrew :-)
Now these two conditions:
1) 5GB RAM + /PAE switch + /3GB switch
2) 6GB RAM + /PAE switch
Can I say that neither of these situations need AWE for SQL Server?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uW%23DbP3fHHA.4936@.TK2MSFTNGP04.phx.gbl...
> That is correct and the most recommended approach with 4GB.
> --
> Andrew J. Kelly SQL MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Thanks Hari and Linchi!
>> Based on documents that I have read, /3GB causes that kernel mode use
>> only 1GB and Virtual Address Space for applications therefore can reserve
>> up to 3GB. Whereas 32-bit based Windows can address up to 4GB of memory,
>> therefore I think by using /3GB switch we don't need to enable AWE for
>> SQL Server to use 3GB. Because we are not beyond the 4GB limit of
>> addressing in this situation.
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>> Hari;
>> My comment had nothing to do with whether /3GB is used, how it should be
>> used, or what impact it may have. I was commenting on the statement that
>> AWE
>> can be used only if you have more than 4GB physical memory. As the data
>> at
>> the link indicate that setting AWE has an impact on SQL Server memory
>> consumption even with 4GB physical memory. And if you want your bpool to
>> consume a certain amount of memory under 4GB, you may need to use AWE to
>> accomplish that.
>> Now, whether or not you should use AWE with less than 4GB of physical
>> memory
>> or whether there is any compelling rason for doing so are entirely
>> different
>> issue.
>> Linchi
>> "Hari Prasad" wrote:
>> Hello Linchi,
>> If I remember correct for a server with 4 GB RAM running in SQL Server
>> 2005
>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>> Boot.ini
>> to
>> use 3 GB RAM. I will take a look into the server on Moday and keep you
>> posted
>> Thanks
>> Hari
>>
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >
>> > That's not correct. Take a look at some empirical data at
>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>> >
>> > Linchi
>> >
>> > "Hari Prasad" wrote:
>> >
>> >> Hello,
>> >>
>> >> AWE can be used only if you have more than 4 GB physical memory. But
>> >> The
>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB out
>> >> of
>> >> the
>> >> base 4GB
>> >> of RAM that Windows OS supports natively. If you don't specify this
>> >> option,
>> >> then SQL Server will only take advantage of 2GB of the first 4GB of
>> >> RAM
>> >> in
>> >> the
>> >> server, essentially wasting 1GB of RAM
>> >>
>> >> Please post the OS and SQL Server edition. This is because For SQL
>> >> Server
>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes in
>> >> BOOT.INI
>> >> to use the additional RAM.
>> >>
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >>
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>> >> > Hi,
>> >> > Imagine that my server has 4GB physical memory. I have added /3GB
>> >> > switch
>> >> > to boot.ini.
>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB
>> >> > address
>> >> > space? Or AWE must only be used when I have more than 4GB memory?
>> >> > Thanks in advance,
>> >> > Leila
>> >> >
>> >>
>> >>
>> >>
>>
>>
>|||Depends on how much you want for SQL Server. If you want more than 3GB you
must use AWE (in addition to PAE) on 32 bit systems. In either case you
should always leave at least a GB for the OS when using AWE since it is not
dynamic. 2GB if going over 12GB total. The /3GB is optional depending on
how much you need for non-buffer pool memory such as procedure cache,
connections etc.
--
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%2399LSA4fHHA.2396@.TK2MSFTNGP04.phx.gbl...
> Thanks indeed Andrew :-)
> Now these two conditions:
> 1) 5GB RAM + /PAE switch + /3GB switch
> 2) 6GB RAM + /PAE switch
> Can I say that neither of these situations need AWE for SQL Server?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uW%23DbP3fHHA.4936@.TK2MSFTNGP04.phx.gbl...
>> That is correct and the most recommended approach with 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Thanks Hari and Linchi!
>> Based on documents that I have read, /3GB causes that kernel mode use
>> only 1GB and Virtual Address Space for applications therefore can
>> reserve up to 3GB. Whereas 32-bit based Windows can address up to 4GB of
>> memory, therefore I think by using /3GB switch we don't need to enable
>> AWE for SQL Server to use 3GB. Because we are not beyond the 4GB limit
>> of addressing in this situation.
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>> Hari;
>> My comment had nothing to do with whether /3GB is used, how it should
>> be
>> used, or what impact it may have. I was commenting on the statement
>> that AWE
>> can be used only if you have more than 4GB physical memory. As the data
>> at
>> the link indicate that setting AWE has an impact on SQL Server memory
>> consumption even with 4GB physical memory. And if you want your bpool
>> to
>> consume a certain amount of memory under 4GB, you may need to use AWE
>> to
>> accomplish that.
>> Now, whether or not you should use AWE with less than 4GB of physical
>> memory
>> or whether there is any compelling rason for doing so are entirely
>> different
>> issue.
>> Linchi
>> "Hari Prasad" wrote:
>> Hello Linchi,
>> If I remember correct for a server with 4 GB RAM running in SQL Server
>> 2005
>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>> Boot.ini
>> to
>> use 3 GB RAM. I will take a look into the server on Moday and keep you
>> posted
>> Thanks
>> Hari
>>
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >
>> > That's not correct. Take a look at some empirical data at
>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>> >
>> > Linchi
>> >
>> > "Hari Prasad" wrote:
>> >
>> >> Hello,
>> >>
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >> But The
>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB out
>> >> of
>> >> the
>> >> base 4GB
>> >> of RAM that Windows OS supports natively. If you don't specify
>> >> this
>> >> option,
>> >> then SQL Server will only take advantage of 2GB of the first 4GB of
>> >> RAM
>> >> in
>> >> the
>> >> server, essentially wasting 1GB of RAM
>> >>
>> >> Please post the OS and SQL Server edition. This is because For SQL
>> >> Server
>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes in
>> >> BOOT.INI
>> >> to use the additional RAM.
>> >>
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >>
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>> >> > Hi,
>> >> > Imagine that my server has 4GB physical memory. I have added /3GB
>> >> > switch
>> >> > to boot.ini.
>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB
>> >> > address
>> >> > space? Or AWE must only be used when I have more than 4GB memory?
>> >> > Thanks in advance,
>> >> > Leila
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
>|||If I have 5GB memory and I use both /PAE and /3GB, then SQL Server can use
up to 4GB memory and this is not beyond 32-bit addressing. Why should I
enable AWE for SQL Server?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:utisfW8fHHA.668@.TK2MSFTNGP05.phx.gbl...
> Depends on how much you want for SQL Server. If you want more than 3GB you
> must use AWE (in addition to PAE) on 32 bit systems. In either case you
> should always leave at least a GB for the OS when using AWE since it is
> not dynamic. 2GB if going over 12GB total. The /3GB is optional depending
> on how much you need for non-buffer pool memory such as procedure cache,
> connections etc.
> --
> Andrew J. Kelly SQL MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%2399LSA4fHHA.2396@.TK2MSFTNGP04.phx.gbl...
>> Thanks indeed Andrew :-)
>> Now these two conditions:
>> 1) 5GB RAM + /PAE switch + /3GB switch
>> 2) 6GB RAM + /PAE switch
>> Can I say that neither of these situations need AWE for SQL Server?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW%23DbP3fHHA.4936@.TK2MSFTNGP04.phx.gbl...
>> That is correct and the most recommended approach with 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Thanks Hari and Linchi!
>> Based on documents that I have read, /3GB causes that kernel mode use
>> only 1GB and Virtual Address Space for applications therefore can
>> reserve up to 3GB. Whereas 32-bit based Windows can address up to 4GB
>> of memory, therefore I think by using /3GB switch we don't need to
>> enable AWE for SQL Server to use 3GB. Because we are not beyond the 4GB
>> limit of addressing in this situation.
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>> Hari;
>> My comment had nothing to do with whether /3GB is used, how it should
>> be
>> used, or what impact it may have. I was commenting on the statement
>> that AWE
>> can be used only if you have more than 4GB physical memory. As the
>> data at
>> the link indicate that setting AWE has an impact on SQL Server memory
>> consumption even with 4GB physical memory. And if you want your bpool
>> to
>> consume a certain amount of memory under 4GB, you may need to use AWE
>> to
>> accomplish that.
>> Now, whether or not you should use AWE with less than 4GB of physical
>> memory
>> or whether there is any compelling rason for doing so are entirely
>> different
>> issue.
>> Linchi
>> "Hari Prasad" wrote:
>> Hello Linchi,
>> If I remember correct for a server with 4 GB RAM running in SQL
>> Server 2005
>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>> Boot.ini
>> to
>> use 3 GB RAM. I will take a look into the server on Moday and keep
>> you
>> posted
>> Thanks
>> Hari
>>
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >
>> > That's not correct. Take a look at some empirical data at
>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>> >
>> > Linchi
>> >
>> > "Hari Prasad" wrote:
>> >
>> >> Hello,
>> >>
>> >> AWE can be used only if you have more than 4 GB physical memory.
>> >> But The
>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB
>> >> out of
>> >> the
>> >> base 4GB
>> >> of RAM that Windows OS supports natively. If you don't specify
>> >> this
>> >> option,
>> >> then SQL Server will only take advantage of 2GB of the first 4GB
>> >> of RAM
>> >> in
>> >> the
>> >> server, essentially wasting 1GB of RAM
>> >>
>> >> Please post the OS and SQL Server edition. This is because For SQL
>> >> Server
>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes
>> >> in
>> >> BOOT.INI
>> >> to use the additional RAM.
>> >>
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >>
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>> >> > Hi,
>> >> > Imagine that my server has 4GB physical memory. I have added
>> >> > /3GB
>> >> > switch
>> >> > to boot.ini.
>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB
>> >> > address
>> >> > space? Or AWE must only be used when I have more than 4GB
>> >> > memory?
>> >> > Thanks in advance,
>> >> > Leila
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
>>
>|||> If I have 5GB memory and I use both /PAE and /3GB, then SQL Server can use up to 4GB memory and
> this is not beyond 32-bit addressing.
No, the usable address space would be 3GB (thanks to the 3GB switch). Even though a 32 bit process
can theoretically see 4GB, 1 GB of those would be reserved for the system.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OzE9z9FgHHA.3632@.TK2MSFTNGP02.phx.gbl...
> If I have 5GB memory and I use both /PAE and /3GB, then SQL Server can use up to 4GB memory and
> this is not beyond 32-bit addressing. Why should I enable AWE for SQL Server?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:utisfW8fHHA.668@.TK2MSFTNGP05.phx.gbl...
>> Depends on how much you want for SQL Server. If you want more than 3GB you must use AWE (in
>> addition to PAE) on 32 bit systems. In either case you should always leave at least a GB for the
>> OS when using AWE since it is not dynamic. 2GB if going over 12GB total. The /3GB is optional
>> depending on how much you need for non-buffer pool memory such as procedure cache, connections
>> etc.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message news:%2399LSA4fHHA.2396@.TK2MSFTNGP04.phx.gbl...
>> Thanks indeed Andrew :-)
>> Now these two conditions:
>> 1) 5GB RAM + /PAE switch + /3GB switch
>> 2) 6GB RAM + /PAE switch
>> Can I say that neither of these situations need AWE for SQL Server?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW%23DbP3fHHA.4936@.TK2MSFTNGP04.phx.gbl...
>> That is correct and the most recommended approach with 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Thanks Hari and Linchi!
>> Based on documents that I have read, /3GB causes that kernel mode use only 1GB and Virtual
>> Address Space for applications therefore can reserve up to 3GB. Whereas 32-bit based Windows
>> can address up to 4GB of memory, therefore I think by using /3GB switch we don't need to
>> enable AWE for SQL Server to use 3GB. Because we are not beyond the 4GB limit of addressing in
>> this situation.
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>> Hari;
>> My comment had nothing to do with whether /3GB is used, how it should be
>> used, or what impact it may have. I was commenting on the statement that AWE
>> can be used only if you have more than 4GB physical memory. As the data at
>> the link indicate that setting AWE has an impact on SQL Server memory
>> consumption even with 4GB physical memory. And if you want your bpool to
>> consume a certain amount of memory under 4GB, you may need to use AWE to
>> accomplish that.
>> Now, whether or not you should use AWE with less than 4GB of physical memory
>> or whether there is any compelling rason for doing so are entirely different
>> issue.
>> Linchi
>> "Hari Prasad" wrote:
>>> Hello Linchi,
>>>
>>> If I remember correct for a server with 4 GB RAM running in SQL Server 2005
>>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in Boot.ini
>>> to
>>> use 3 GB RAM. I will take a look into the server on Moday and keep you
>>> posted
>>>
>>> Thanks
>>> Hari
>>>
>>>
>>>
>>>
>>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>>> >> AWE can be used only if you have more than 4 GB physical memory.
>>> >
>>> > That's not correct. Take a look at some empirical data at
>>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>>> >
>>> > Linchi
>>> >
>>> > "Hari Prasad" wrote:
>>> >
>>> >> Hello,
>>> >>
>>> >> AWE can be used only if you have more than 4 GB physical memory. But The
>>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB out of
>>> >> the
>>> >> base 4GB
>>> >> of RAM that Windows OS supports natively. If you don't specify this
>>> >> option,
>>> >> then SQL Server will only take advantage of 2GB of the first 4GB of RAM
>>> >> in
>>> >> the
>>> >> server, essentially wasting 1GB of RAM
>>> >>
>>> >> Please post the OS and SQL Server edition. This is because For SQL Server
>>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes in
>>> >> BOOT.INI
>>> >> to use the additional RAM.
>>> >>
>>> >> Thanks
>>> >> Hari
>>> >>
>>> >>
>>> >>
>>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>>> >> > Hi,
>>> >> > Imagine that my server has 4GB physical memory. I have added /3GB
>>> >> > switch
>>> >> > to boot.ini.
>>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB address
>>> >> > space? Or AWE must only be used when I have more than 4GB memory?
>>> >> > Thanks in advance,
>>> >> > Leila
>>> >> >
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>>
>>
>>
>>
>|||Hello Leila,
I'll walk through how windows allocates the RAM and how SQL Server uses the
RAM and let me know if you have further questions.
In a 32-bit OS only 4GB is directly addressable. The standard configuration
is for the OS to use 2GB and SQL to use 2GB of RAM. To increase the
available RAM for SQL you can enable the /3GB, PAE (physical address
extensions), or AWE (address windowing extensions).
The /3GB switch tells the OS to use 1GB of RAM leaving the rest to SQL if
you so choose. With the addition of the /PAE switch with server gets still
gets 1GB of RAM but SQL only gets 2GB, the other 1GB is used to map RAM
above the 4GB limit. At this point you use AWE on the SQL server to enable
it to see the RAM above the 4GB limit.
Note you should start thinking about replacing the server with a 64-bit
server as I believe Windows will be shipping only 64-bit in 3 years.
John Vandervliet.
"Leila" <Leilas@.hotpop.com> wrote in message
news:OzE9z9FgHHA.3632@.TK2MSFTNGP02.phx.gbl...
> If I have 5GB memory and I use both /PAE and /3GB, then SQL Server can use
> up to 4GB memory and this is not beyond 32-bit addressing. Why should I
> enable AWE for SQL Server?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:utisfW8fHHA.668@.TK2MSFTNGP05.phx.gbl...
>> Depends on how much you want for SQL Server. If you want more than 3GB
>> you must use AWE (in addition to PAE) on 32 bit systems. In either case
>> you should always leave at least a GB for the OS when using AWE since it
>> is not dynamic. 2GB if going over 12GB total. The /3GB is optional
>> depending on how much you need for non-buffer pool memory such as
>> procedure cache, connections etc.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%2399LSA4fHHA.2396@.TK2MSFTNGP04.phx.gbl...
>> Thanks indeed Andrew :-)
>> Now these two conditions:
>> 1) 5GB RAM + /PAE switch + /3GB switch
>> 2) 6GB RAM + /PAE switch
>> Can I say that neither of these situations need AWE for SQL Server?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW%23DbP3fHHA.4936@.TK2MSFTNGP04.phx.gbl...
>> That is correct and the most recommended approach with 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Thanks Hari and Linchi!
>> Based on documents that I have read, /3GB causes that kernel mode use
>> only 1GB and Virtual Address Space for applications therefore can
>> reserve up to 3GB. Whereas 32-bit based Windows can address up to 4GB
>> of memory, therefore I think by using /3GB switch we don't need to
>> enable AWE for SQL Server to use 3GB. Because we are not beyond the
>> 4GB limit of addressing in this situation.
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>> Hari;
>> My comment had nothing to do with whether /3GB is used, how it should
>> be
>> used, or what impact it may have. I was commenting on the statement
>> that AWE
>> can be used only if you have more than 4GB physical memory. As the
>> data at
>> the link indicate that setting AWE has an impact on SQL Server memory
>> consumption even with 4GB physical memory. And if you want your bpool
>> to
>> consume a certain amount of memory under 4GB, you may need to use AWE
>> to
>> accomplish that.
>> Now, whether or not you should use AWE with less than 4GB of physical
>> memory
>> or whether there is any compelling rason for doing so are entirely
>> different
>> issue.
>> Linchi
>> "Hari Prasad" wrote:
>>> Hello Linchi,
>>>
>>> If I remember correct for a server with 4 GB RAM running in SQL
>>> Server 2005
>>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>>> Boot.ini
>>> to
>>> use 3 GB RAM. I will take a look into the server on Moday and keep
>>> you
>>> posted
>>>
>>> Thanks
>>> Hari
>>>
>>>
>>>
>>>
>>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in
>>> message
>>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>>> >> AWE can be used only if you have more than 4 GB physical memory.
>>> >
>>> > That's not correct. Take a look at some empirical data at
>>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>>> >
>>> > Linchi
>>> >
>>> > "Hari Prasad" wrote:
>>> >
>>> >> Hello,
>>> >>
>>> >> AWE can be used only if you have more than 4 GB physical memory.
>>> >> But The
>>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB
>>> >> out of
>>> >> the
>>> >> base 4GB
>>> >> of RAM that Windows OS supports natively. If you don't specify
>>> >> this
>>> >> option,
>>> >> then SQL Server will only take advantage of 2GB of the first 4GB
>>> >> of RAM
>>> >> in
>>> >> the
>>> >> server, essentially wasting 1GB of RAM
>>> >>
>>> >> Please post the OS and SQL Server edition. This is because For
>>> >> SQL Server
>>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes
>>> >> in
>>> >> BOOT.INI
>>> >> to use the additional RAM.
>>> >>
>>> >> Thanks
>>> >> Hari
>>> >>
>>> >>
>>> >>
>>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>>> >> > Hi,
>>> >> > Imagine that my server has 4GB physical memory. I have added
>>> >> > /3GB
>>> >> > switch
>>> >> > to boot.ini.
>>> >> > Should I enable AWE configuration for my SQL Server to use 3 GB
>>> >> > address
>>> >> > space? Or AWE must only be used when I have more than 4GB
>>> >> > memory?
>>> >> > Thanks in advance,
>>> >> > Leila
>>> >> >
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>>
>>
>>
>>
>|||Really helpful! Thanks :)
"John Vandervliet" <jvandervliet@.SJRB.AD> wrote in message
news:AC095E1D-35EF-4CED-9644-B437DE3787E2@.microsoft.com...
> Hello Leila,
> I'll walk through how windows allocates the RAM and how SQL Server uses
> the RAM and let me know if you have further questions.
> In a 32-bit OS only 4GB is directly addressable. The standard
> configuration is for the OS to use 2GB and SQL to use 2GB of RAM. To
> increase the available RAM for SQL you can enable the /3GB, PAE (physical
> address extensions), or AWE (address windowing extensions).
> The /3GB switch tells the OS to use 1GB of RAM leaving the rest to SQL if
> you so choose. With the addition of the /PAE switch with server gets still
> gets 1GB of RAM but SQL only gets 2GB, the other 1GB is used to map RAM
> above the 4GB limit. At this point you use AWE on the SQL server to enable
> it to see the RAM above the 4GB limit.
> Note you should start thinking about replacing the server with a 64-bit
> server as I believe Windows will be shipping only 64-bit in 3 years.
> John Vandervliet.
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OzE9z9FgHHA.3632@.TK2MSFTNGP02.phx.gbl...
>> If I have 5GB memory and I use both /PAE and /3GB, then SQL Server can
>> use up to 4GB memory and this is not beyond 32-bit addressing. Why should
>> I enable AWE for SQL Server?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:utisfW8fHHA.668@.TK2MSFTNGP05.phx.gbl...
>> Depends on how much you want for SQL Server. If you want more than 3GB
>> you must use AWE (in addition to PAE) on 32 bit systems. In either case
>> you should always leave at least a GB for the OS when using AWE since it
>> is not dynamic. 2GB if going over 12GB total. The /3GB is optional
>> depending on how much you need for non-buffer pool memory such as
>> procedure cache, connections etc.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%2399LSA4fHHA.2396@.TK2MSFTNGP04.phx.gbl...
>> Thanks indeed Andrew :-)
>> Now these two conditions:
>> 1) 5GB RAM + /PAE switch + /3GB switch
>> 2) 6GB RAM + /PAE switch
>> Can I say that neither of these situations need AWE for SQL Server?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW%23DbP3fHHA.4936@.TK2MSFTNGP04.phx.gbl...
>> That is correct and the most recommended approach with 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:ucNNgjyfHHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Thanks Hari and Linchi!
>> Based on documents that I have read, /3GB causes that kernel mode use
>> only 1GB and Virtual Address Space for applications therefore can
>> reserve up to 3GB. Whereas 32-bit based Windows can address up to 4GB
>> of memory, therefore I think by using /3GB switch we don't need to
>> enable AWE for SQL Server to use 3GB. Because we are not beyond the
>> 4GB limit of addressing in this situation.
>>
>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>> news:F6FB03D7-3B0F-4CD1-B2A2-BBFD660502EE@.microsoft.com...
>>> Hari;
>>>
>>> My comment had nothing to do with whether /3GB is used, how it
>>> should be
>>> used, or what impact it may have. I was commenting on the statement
>>> that AWE
>>> can be used only if you have more than 4GB physical memory. As the
>>> data at
>>> the link indicate that setting AWE has an impact on SQL Server
>>> memory
>>> consumption even with 4GB physical memory. And if you want your
>>> bpool to
>>> consume a certain amount of memory under 4GB, you may need to use
>>> AWE to
>>> accomplish that.
>>>
>>> Now, whether or not you should use AWE with less than 4GB of
>>> physical memory
>>> or whether there is any compelling rason for doing so are entirely
>>> different
>>> issue.
>>>
>>> Linchi
>>>
>>> "Hari Prasad" wrote:
>>>
>>> Hello Linchi,
>>>
>>> If I remember correct for a server with 4 GB RAM running in SQL
>>> Server 2005
>>> 32 bit and Windows 2003 Advanced, all I did is just /3GB switch in
>>> Boot.ini
>>> to
>>> use 3 GB RAM. I will take a look into the server on Moday and keep
>>> you
>>> posted
>>>
>>> Thanks
>>> Hari
>>>
>>>
>>>
>>>
>>> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in
>>> message
>>> news:45A920F2-86E6-4A86-A1B1-8306C3077E8B@.microsoft.com...
>>> >> AWE can be used only if you have more than 4 GB physical memory.
>>> >
>>> > That's not correct. Take a look at some empirical data at
>>> > http://sqlblog.com/blogs/linchi_shea/default.aspx?p=2
>>> >
>>> > Linchi
>>> >
>>> > "Hari Prasad" wrote:
>>> >
>>> >> Hello,
>>> >>
>>> >> AWE can be used only if you have more than 4 GB physical memory.
>>> >> But The
>>> >> /3GB switch is used to tell SQL Server to take advantage of 3GB
>>> >> out of
>>> >> the
>>> >> base 4GB
>>> >> of RAM that Windows OS supports natively. If you don't specify
>>> >> this
>>> >> option,
>>> >> then SQL Server will only take advantage of 2GB of the first 4GB
>>> >> of RAM
>>> >> in
>>> >> the
>>> >> server, essentially wasting 1GB of RAM
>>> >>
>>> >> Please post the OS and SQL Server edition. This is because For
>>> >> SQL Server
>>> >> 2005 - 64 bit running in Windows 2003 you donot want any changes
>>> >> in
>>> >> BOOT.INI
>>> >> to use the additional RAM.
>>> >>
>>> >> Thanks
>>> >> Hari
>>> >>
>>> >>
>>> >>
>>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>>> >> news:OEze$NnfHHA.2432@.TK2MSFTNGP03.phx.gbl...
>>> >> > Hi,
>>> >> > Imagine that my server has 4GB physical memory. I have added
>>> >> > /3GB
>>> >> > switch
>>> >> > to boot.ini.
>>> >> > Should I enable AWE configuration for my SQL Server to use 3
>>> >> > GB address
>>> >> > space? Or AWE must only be used when I have more than 4GB
>>> >> > memory?
>>> >> > Thanks in advance,
>>> >> > Leila
>>> >> >
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>

Monday, March 19, 2012

Back Up question

Thursday, March 8, 2012

AWE Question

AWE Question

Saturday, February 25, 2012

AWE

AWE

AWE

AWE

Autonumber field

Blog Archive

About Me