AWE working?

Hi all,

This is a question related to AWE settings. I am on windows 2003 ent version with SQL Server 2005 std version. I have configured SQL Server to use AWE and min memery as 22G and max as 26G (I have 32G in total as physical memory). I do see the message in log as "Windows Address ... Enabled", but when I am using performance monitor to view total memory in kb for SQL Server, it never starts at 22G (usually at 1G). And I doubt why? The account I am running SQL Server is an admin account, so it can definitely locked down the pages in memory. Also, I do think AWE is working somehow, after several hours in running, the total memory will up to 8, 9 or 11G. So, apparently, it is over 2G per process limitation. I still want to know.

1. Is my AWE setting working?

2. If not, how to fix that?

3. How to confirm how many memory is actaully using by SQL Server?

Thanks,

NIng

(The comment below assumes 32 bit OS and 32 bit SQL Server.)

1. Since you see that SQL Server's memory usage grows up to 11GB, it is obvious that AWE is working. SQL Server only requests memory from the OS as it is needed -once allocated, it usually keeps it.

2. Nothing to do

3. Performance Monitor, as you are using, indicates SQL Server's memory usage. For more detailed memory usage analysis, you can look at the various SQL Server Memory counters in Performance Monitor, and you can use some the the system functions that will give you memory data. Look at the System Views. (Click on Database, Views, System Views.) Try:

SELECT * FROM sys.dm_os_performance_counter

Looking specifically at the SQL Server Memory Manager objects.

|||Are you sure this is by design?

My performance is awful after I have upgraded. SQL Server 2000 used to locked the memory as it starts, so the production server (4 cpus and 32 G memory) was doing a lot better than my development server with (1cpu and 2G memory). However, after upgrade, the performance level of the production server is awful. For example, a query need 9 seconds on test server used to run only 2 seconds on live server. Now it needs 8 seconds ...

|||

After upgrading, it is NECESSARY to rebuild ALL the indexes, and update the statistics. If you have not done that, your performace will often be signicantly worse.

And even then, some queries will run faster on SQL 2005, and some (albeit few) will run slower. It may be necessary to 'revise' queries that are signicantly slower in order to use the new ways that SQL 2005 produces execution plans.

AWE on SQL Server 2000 & 2005

I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
2000 Enterprose I would like to use AWE,
to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
please let me know if any one have this information.
I have windows2003 server 32 bit version, 16Gb ram on it, running inder SQL
2005 Enterprose I would like to use AWE,
to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
please let me know if any one have this information.
SQL serer DBAYou need the /PAE switch although Win2003 may automatically add it for you.
The /3GB can not be used with 16GB but can with 8GB. It is up to you if you
need the extra directly addressable memory or not. IN either case you will
also need to enable AWE in SQL Server.
Andrew J. Kelly SQL MVP
"Srikanth" <Srikanth@.discussions.microsoft.com> wrote in message
news:61F51031-1B5D-454B-B88C-9D30442A5932@.microsoft.com...
>I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
> 2000 Enterprose I would like to use AWE,
> to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
> please let me know if any one have this information.
>
> I have windows2003 server 32 bit version, 16Gb ram on it, running inder
> SQL
> 2005 Enterprose I would like to use AWE,
> to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
> please let me know if any one have this information.
>
> --
> SQL serer DBA|||And if you enable AWE in SQL Server without the PAE switch being on, you
aren't going to get anything. The PAE switch causes a different NT kernel
to be loaded which contains the code to manipulate memory addresses above
the 32 bit address space. Keep in mind that AWE is for data only. No
executable code can exist outside of the 32 bit address space. It's
actually quite rare to have an application with memory pressure due to data
alone.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eoJ8YgtGGHA.312@.TK2MSFTNGP09.phx.gbl...
> You need the /PAE switch although Win2003 may automatically add it for
> you. The /3GB can not be used with 16GB but can with 8GB. It is up to you
> if you need the extra directly addressable memory or not. IN either case
> you will also need to enable AWE in SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "Srikanth" <Srikanth@.discussions.microsoft.com> wrote in message
> news:61F51031-1B5D-454B-B88C-9D30442A5932@.microsoft.com...
>

AWE on SQL Server 2000 & 2005

I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
2000 Enterprose I would like to use AWE,
to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
please let me know if any one have this information.
I have windows2003 server 32 bit version, 16Gb ram on it, running inder SQL
2005 Enterprose I would like to use AWE,
to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
please let me know if any one have this information.
SQL serer DBA
You need the /PAE switch although Win2003 may automatically add it for you.
The /3GB can not be used with 16GB but can with 8GB. It is up to you if you
need the extra directly addressable memory or not. IN either case you will
also need to enable AWE in SQL Server.
Andrew J. Kelly SQL MVP
"Srikanth" <Srikanth@.discussions.microsoft.com> wrote in message
news:61F51031-1B5D-454B-B88C-9D30442A5932@.microsoft.com...
>I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
> 2000 Enterprose I would like to use AWE,
> to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
> please let me know if any one have this information.
>
> I have windows2003 server 32 bit version, 16Gb ram on it, running inder
> SQL
> 2005 Enterprose I would like to use AWE,
> to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
> please let me know if any one have this information.
>
> --
> SQL serer DBA
|||And if you enable AWE in SQL Server without the PAE switch being on, you
aren't going to get anything. The PAE switch causes a different NT kernel
to be loaded which contains the code to manipulate memory addresses above
the 32 bit address space. Keep in mind that AWE is for data only. No
executable code can exist outside of the 32 bit address space. It's
actually quite rare to have an application with memory pressure due to data
alone.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eoJ8YgtGGHA.312@.TK2MSFTNGP09.phx.gbl...
> You need the /PAE switch although Win2003 may automatically add it for
> you. The /3GB can not be used with 16GB but can with 8GB. It is up to you
> if you need the extra directly addressable memory or not. IN either case
> you will also need to enable AWE in SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "Srikanth" <Srikanth@.discussions.microsoft.com> wrote in message
> news:61F51031-1B5D-454B-B88C-9D30442A5932@.microsoft.com...
>

AWE on SQL Server 2000 & 2005

I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
2000 Enterprose I would like to use AWE,
to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
please let me know if any one have this information.
I have windows2003 server 32 bit version, 16Gb ram on it, running inder SQL
2005 Enterprose I would like to use AWE,
to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
please let me know if any one have this information.
--
SQL serer DBAYou need the /PAE switch although Win2003 may automatically add it for you.
The /3GB can not be used with 16GB but can with 8GB. It is up to you if you
need the extra directly addressable memory or not. IN either case you will
also need to enable AWE in SQL Server.
--
Andrew J. Kelly SQL MVP
"Srikanth" <Srikanth@.discussions.microsoft.com> wrote in message
news:61F51031-1B5D-454B-B88C-9D30442A5932@.microsoft.com...
>I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
> 2000 Enterprose I would like to use AWE,
> to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
> please let me know if any one have this information.
>
> I have windows2003 server 32 bit version, 16Gb ram on it, running inder
> SQL
> 2005 Enterprose I would like to use AWE,
> to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
> please let me know if any one have this information.
>
> --
> SQL serer DBA|||And if you enable AWE in SQL Server without the PAE switch being on, you
aren't going to get anything. The PAE switch causes a different NT kernel
to be loaded which contains the code to manipulate memory addresses above
the 32 bit address space. Keep in mind that AWE is for data only. No
executable code can exist outside of the 32 bit address space. It's
actually quite rare to have an application with memory pressure due to data
alone.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eoJ8YgtGGHA.312@.TK2MSFTNGP09.phx.gbl...
> You need the /PAE switch although Win2003 may automatically add it for
> you. The /3GB can not be used with 16GB but can with 8GB. It is up to you
> if you need the extra directly addressable memory or not. IN either case
> you will also need to enable AWE in SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "Srikanth" <Srikanth@.discussions.microsoft.com> wrote in message
> news:61F51031-1B5D-454B-B88C-9D30442A5932@.microsoft.com...
>>I have windows2003 server 32 bit version, 8Gb ram on it, running inder SQL
>> 2000 Enterprose I would like to use AWE,
>> to enable AWE I need to add onlt \PAE or I need \3gb + \PAE in boot.ini?
>> please let me know if any one have this information.
>>
>> I have windows2003 server 32 bit version, 16Gb ram on it, running inder
>> SQL
>> 2005 Enterprose I would like to use AWE,
>> to enable AWE I need to add only \PAE or I need \3gb + \PAE in boot.ini?
>> please let me know if any one have this information.
>>
>> --
>> SQL serer DBA
>

AWE Memory fix (899761) problems

Hi all,
we have some problem installing the path KB899761 (Fix: Not All Memory
is Available When AWE is Enabled on a Computer Running 32-bit Version
of SQL Server 2000 SP4)
Our scenario is:
1 - we added a third node on an existing cluster
2 - The main differences are that the new node have 64 GB of RAM (the
existing ones have only 8 GB) and the "machine" is 64 bit
3 - On this new server we installed windows 2003 (32 bit) with sql
server 2000 SP4 (with "add node" procedure)
4 - To use all the 64 GB of memory we enabled the AWE feature but
we've seen that we could only use 32 GB. For this reason we've found
out that we need the 899761 patch.
Now we tried to install the patch but it seems that it just work on the
old (32 bit) nodes. If we try to install the patch on the new node (we
have the correct instance up and running) we get the following error:
"this machine does not have a product that matches this installation
package"
On the log file we see:
"No products were found which can be serviced by this package"
At the moment we don't know what to try to solve the problem. (maybe
problem with hardware at 64 bit (core 2 duo) and OS at 32 bit ?)
If someone as any idea it would be fantastic ...
Thanks very much in advance.
Kind regards,
Daniel HubeliHi Daniel
You don't say what edition you are using, but I guess from your combination
it can't be Datacenter!
Windows Server 2003, Enterprise Edition supports 32 GB of physical RAM and
Windows Server 2003, Datacenter Edition supports 64 GB of physical RAM
You will not be taking full advantage of your new hardware by running
mismatched products and you would also need to consider if failing over to
the lower spec machines is actually a viable alternative.
John
"daniel.hubeli@.aduno.ch" wrote:
> Hi all,
> we have some problem installing the path KB899761 (Fix: Not All Memory
> is Available When AWE is Enabled on a Computer Running 32-bit Version
> of SQL Server 2000 SP4)
> Our scenario is:
> 1 - we added a third node on an existing cluster
> 2 - The main differences are that the new node have 64 GB of RAM (the
> existing ones have only 8 GB) and the "machine" is 64 bit
> 3 - On this new server we installed windows 2003 (32 bit) with sql
> server 2000 SP4 (with "add node" procedure)
> 4 - To use all the 64 GB of memory we enabled the AWE feature but
> we've seen that we could only use 32 GB. For this reason we've found
> out that we need the 899761 patch.
> Now we tried to install the patch but it seems that it just work on the
> old (32 bit) nodes. If we try to install the patch on the new node (we
> have the correct instance up and running) we get the following error:
> "this machine does not have a product that matches this installation
> package"
> On the log file we see:
> "No products were found which can be serviced by this package"
> At the moment we don't know what to try to solve the problem. (maybe
> problem with hardware at 64 bit (core 2 duo) and OS at 32 bit ?)
> If someone as any idea it would be fantastic ...
> Thanks very much in advance.
> Kind regards,
> Daniel Hubeli
>

auto-number/Identity column

I am migrating a web application I wrote from ASP to ASP.Net, and from
Access to MS SQL server.

In the Access version, I did not use the auto number for creating
invoices and other documents, because I heard somewhere (perhaps
incorrectly) that if the db was ever compacted or otherwise changed,
it could change the values of the auto-numbers. Not a good thing.
So I wrote a routine that, just before creating a new record, would
look for the highest value in the table and create the new record with
the next number.

So my question is, am I safe in assuming that in MS SQL that I can set
a starting number for the next, let's say, invoice and that new
numbers will be issued in sequence, and that these numbers will never
change? What happens if an invoice is deleted? is the number gone
forever? Just wondering how others deal with these issues...thanks.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."Larry Rekow (larry@.netgeexdotcom) writes:
> So my question is, am I safe in assuming that in MS SQL that I can set
> a starting number for the next, let's say, invoice and that new
> numbers will be issued in sequence, and that these numbers will never
> change? What happens if an invoice is deleted? is the number gone
> forever? Just wondering how others deal with these issues...thanks.

If you need sequential numbers, and cannot accept gaps, you should not
use the IDENITY property. If you attempt to insert a row, and the
insert fails, that consumes a number. The whole point is that the number
is not transactional, so that it scales better. If you need a contiguous
series of numbers, roll your own.

What does not happen is that once the number has been given to a row,
the number will not change at whim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 31 Aug 2004 21:06:39 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Larry Rekow (larry@.netgeexdotcom) writes:
>> So my question is, am I safe in assuming that in MS SQL that I can set
>> a starting number for the next, let's say, invoice and that new
>> numbers will be issued in sequence, and that these numbers will never
>> change? What happens if an invoice is deleted? is the number gone
>> forever? Just wondering how others deal with these issues...thanks.
>If you need sequential numbers, and cannot accept gaps, you should not
>use the IDENITY property. If you attempt to insert a row, and the
>insert fails, that consumes a number. The whole point is that the number
>is not transactional, so that it scales better. If you need a contiguous
>series of numbers, roll your own.
>What does not happen is that once the number has been given to a row,
>the number will not change at whim.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
thanks; glad I asked.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."