AWE and 3GB switches

I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?
If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
MG
"Terri" wrote:

> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>
|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...[vbcol=seagreen]
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
Server[vbcol=seagreen]
|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
MG
"Terri" wrote:

> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.com/sql_server_performance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> Server
>
>
|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>
|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> "you
> the
>

AWE and 3GB switches

I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
--
MG
"Terri" wrote:

> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Serv
er
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.c...ance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...[vbcol=seagreen]
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
>
Server[vbcol=seagreen]|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
--
MG
"Terri" wrote:

> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.c...ance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> Server
>
>|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> "you
> the
>

Average Latch Wait Time (ms)

SQL Server 2000 EE SP3
Win 2000
6-Way PIII 700Mhz
8GB RAM
I have a question regarding the Average Latch Wait Time (ms) counter :
We have had this server for 3 years running a very critical application
(200+ Transactions per sec)
The business has duplicated in the past year and we are thinking about
getting a new server because this one is slow and we want to increase memory
(Windows 2003 EE)
The average Latch wait time was normally around 385 and some days up to 415.
However this morning when i came to my office the counter was 35. I find the
server a lot faster today but we haven't made any significant changes.
This is the only thing that I can think of:
We have an archive process and in the last couple days we have moved to
archived Database millions of records (We do this every month)
We have a couple of huge tables (This month for example, table 1: 34
millions before archive - went to 24 millions, table 2: 5 millions before
archive - went to 3 millions)
Do you think that deleting those records from those big tables made the
difference?
Do you have any comment on why the average latch wait time dropped
dramatically?
Thanks in advanceNo where near enough data to know.
Can cutting your biggest tables close ot 1/2 have a big impact? Sure.
Will dropping the latch waits as decribed potentially have a big impact.
Sure.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Seal" <sealtielp@.afex.com> wrote in message
news:%23dMAKm2EEHA.3344@.tk2msftngp13.phx.gbl...
> SQL Server 2000 EE SP3
> Win 2000
> 6-Way PIII 700Mhz
> 8GB RAM
>
>
> I have a question regarding the Average Latch Wait Time (ms) counter :
> We have had this server for 3 years running a very critical application
> (200+ Transactions per sec)
>
> The business has duplicated in the past year and we are thinking about
> getting a new server because this one is slow and we want to increase
memory
> (Windows 2003 EE)
>
> The average Latch wait time was normally around 385 and some days up to
415.
> However this morning when i came to my office the counter was 35. I find
the
> server a lot faster today but we haven't made any significant changes.
>
> This is the only thing that I can think of:
> We have an archive process and in the last couple days we have moved to
> archived Database millions of records (We do this every month)
> We have a couple of huge tables (This month for example, table 1: 34
> millions before archive - went to 24 millions, table 2: 5 millions before
> archive - went to 3 millions)
>
>
> Do you think that deleting those records from those big tables made the
> difference?
> Do you have any comment on why the average latch wait time dropped
> dramatically?
>
>
> Thanks in advance
>|||Hello Seal,
I believe Brian has pointed it out that the operation on the data archive pr
ocess can have impact on
dropping the average Latch Wait Time (Performance Counter).
Here I follow up with additional information as below:
1) Average latch wait time represents the time for latch requests that had t
o wait.
As you'll see later, pages of an index use a slightly different locking mech
anism than regular data pages. A
lightweight lock - Latch lock is used to lock upper levels of indexes.
2) The SQL Server lock manager provides two separate locking systems.
The first system affects all fully shared data and provides row locks, page
locks, and table locks for tables,
data pages, text pages, and leaf-level index pages. The second system is use
d internally for index
concurrency control, controlling access to internal data structures, and ret
rieving individual rows of data
pages. This second system uses latches, which are less resource intensive th
an locks and provide
performance optimization.
3) Latches ensure the physical consistency and locks ensure the logical cons
istency of the data.
Latching happens when you place a row physically on a page or move data in o
ther ways, such as
compressing the space on a page. SQL Server must guarantee that this data mo
vement can happen
without interference.
Above all, we can see that if we lessen the operations to place a row physic
ally on a page, or move data in
other ways, the time for request a latch/latches are also shortened. I belie
ve the latest archive process have
made this big impact on the performance.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.