PAE & AWE Question

Hello:
I am evaluating performance issues on a SQL server and trying to understand
when to use the PAE switch.
I have a Windows 2000 Advanced server SP4 running MS SQL Server Enterprise
Edition SP4. The server has 4GB total physical RAM. Do I need to configure
PAE/AWE for SQL to take advantage of more than 2GB of RAM?
SQL services fail at times due to high memory utilization. Any assistance
would be appreciated!
Thanks,
WHello,
If your server has only 4GB of memory you can not use AWE. By default
Windows uses 2 GB and SQL Server can use the remaining 2 GB. By adding the
/3GB switch to the Windows boot.ini file you can have Windows to use only 1
GB so SQL Server can use the remaining 3 GB.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Walid" wrote:
> Hello:
> I am evaluating performance issues on a SQL server and trying to understand
> when to use the PAE switch.
> I have a Windows 2000 Advanced server SP4 running MS SQL Server Enterprise
> Edition SP4. The server has 4GB total physical RAM. Do I need to configure
> PAE/AWE for SQL to take advantage of more than 2GB of RAM?
> SQL services fail at times due to high memory utilization. Any assistance
> would be appreciated!
> Thanks,
> W|||If you visit the following link, you'll understand PAE better.
http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
And for AWE, PAE, 3GB:
http://support.microsoft.com/kb/274750/en-us
--
Ekrem Ã?nsoy
"Walid" <Walid@.discussions.microsoft.com> wrote in message
news:350CF984-8737-43F6-8570-189A7C81AFFD@.microsoft.com...
> Hello:
> I am evaluating performance issues on a SQL server and trying to
> understand
> when to use the PAE switch.
> I have a Windows 2000 Advanced server SP4 running MS SQL Server Enterprise
> Edition SP4. The server has 4GB total physical RAM. Do I need to
> configure
> PAE/AWE for SQL to take advantage of more than 2GB of RAM?
> SQL services fail at times due to high memory utilization. Any assistance
> would be appreciated!
> Thanks,
> W|||Thank you all very much for the info!!
"Ekrem Ã?nsoy" wrote:
> If you visit the following link, you'll understand PAE better.
> http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
> And for AWE, PAE, 3GB:
> http://support.microsoft.com/kb/274750/en-us
> --
> Ekrem Ã?nsoy
>
> "Walid" <Walid@.discussions.microsoft.com> wrote in message
> news:350CF984-8737-43F6-8570-189A7C81AFFD@.microsoft.com...
> > Hello:
> > I am evaluating performance issues on a SQL server and trying to
> > understand
> > when to use the PAE switch.
> >
> > I have a Windows 2000 Advanced server SP4 running MS SQL Server Enterprise
> > Edition SP4. The server has 4GB total physical RAM. Do I need to
> > configure
> > PAE/AWE for SQL to take advantage of more than 2GB of RAM?
> >
> > SQL services fail at times due to high memory utilization. Any assistance
> > would be appreciated!
> >
> > Thanks,
> >
> > W
>|||> If your server has only 4GB of memory you can not use AWE.
Not true. See
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
Linchi
"Ben Nevarez" wrote:
> Hello,
> If your server has only 4GB of memory you can not use AWE. By default
> Windows uses 2 GB and SQL Server can use the remaining 2 GB. By adding the
> /3GB switch to the Windows boot.ini file you can have Windows to use only 1
> GB so SQL Server can use the remaining 3 GB.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Walid" wrote:
> > Hello:
> > I am evaluating performance issues on a SQL server and trying to understand
> > when to use the PAE switch.
> >
> > I have a Windows 2000 Advanced server SP4 running MS SQL Server Enterprise
> > Edition SP4. The server has 4GB total physical RAM. Do I need to configure
> > PAE/AWE for SQL to take advantage of more than 2GB of RAM?
> >
> > SQL services fail at times due to high memory utilization. Any assistance
> > would be appreciated!
> >
> > Thanks,
> >
> > W|||Again, a nice work by Linchi... Thanks.
--
Ekrem Ã?nsoy
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:10FB2A3A-4BA1-4118-B376-D65B96BEB5D7@.microsoft.com...
>> If your server has only 4GB of memory you can not use AWE.
> Not true. See
> http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
> Linchi
> "Ben Nevarez" wrote:
>> Hello,
>> If your server has only 4GB of memory you can not use AWE. By default
>> Windows uses 2 GB and SQL Server can use the remaining 2 GB. By adding
>> the
>> /3GB switch to the Windows boot.ini file you can have Windows to use only
>> 1
>> GB so SQL Server can use the remaining 3 GB.
>> Hope this helps,
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>> "Walid" wrote:
>> > Hello:
>> > I am evaluating performance issues on a SQL server and trying to
>> > understand
>> > when to use the PAE switch.
>> >
>> > I have a Windows 2000 Advanced server SP4 running MS SQL Server
>> > Enterprise
>> > Edition SP4. The server has 4GB total physical RAM. Do I need to
>> > configure
>> > PAE/AWE for SQL to take advantage of more than 2GB of RAM?
>> >
>> > SQL services fail at times due to high memory utilization. Any
>> > assistance
>> > would be appreciated!
>> >
>> > Thanks,
>> >
>> > W

P&T

Can anyone recommend a good book or web-site to provide
me with more indepth information on performance & tuning
my sql servers as well as performance & tuning the
databases within my sql servers?
I have the SQL Server 2000 Performance Tuning book from
Microsoft Press but it does not give me near enough
detailed info on statistics, index information,
optimizations, etc.There's some good stuff at http://www.sql-server-performance.com
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:017001c3af8f$1591b2c0$a301280a@.phx.gbl...
Can anyone recommend a good book or web-site to provide
me with more indepth information on performance & tuning
my sql servers as well as performance & tuning the
databases within my sql servers?
I have the SQL Server 2000 Performance Tuning book from
Microsoft Press but it does not give me near enough
detailed info on statistics, index information,
optimizations, etc.

P

Hi Experts,

After performing some tests on my setup I recorded very large performance degradation between simple setup and mirrored setup (High Availability �C with witness). In some inserts tests (100K in a loop) it took up to 400% longer to complete for mirror setup compared to the simple one (40 seconds compared to 170 seconds).Is this performance hit normal? Is there something that can be done to make it perform better?

The PCs are connected using a 100MB switch and I'm using SQL Server Developer Edition.

Thanks,

Avi

Hi Avi,

The tests that you are running are bad scenario for high-availability mirroring. Each insert statement in the loop practically commits a single-statement transaction. This causes log to be sent to disk and mirror and waits for the mirror to harden that log. That way you are directly adding to the per-statement latency.

Here are a few things you can try:

- Don't run all inserts sequentially on a single connection. Rather, distribute them among multiple connections. That way you are increasing the chance that while one transaction is committing and thus waiting for the mirror to harden the log, others may run in parallel, thus amortising the wait time.

- If at all possible, make your transactions bigger (do several inserts in a single transaction). That way each log flush will carry several transactions.

- If at all possible, use high-performance mirroring mode (here you have the risk of having your log growing, though).

Hope this helps.

Thanks,

Kaloian.

|||

For better estimation of performance impact of database mirroring, your tests should be as close to the actual application as possible. For discussion on database mirroring performance, take a look at the paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

PCs?

Would you please give more details on the hardware of the mirrored servers?

|||

Thank you all for the replys.

I use two dual Xeon PCs with 2GB memory. The disks are fast too. The performance without the mirroring is great - over 2000 inserts per second. However with mirroring it goes down sharply.

When I run the inserts in a single transaction it flies. However I cannot always do that. My workaround for now is to suspend the mirroring for a few seconds do my inserts and run the resume command. This brings me back to the performance I used to have, but I hope to find a more "clean solution". It is not so good to suspend and resume the mirroring so many times��

Any suggestions (hardware, configuration or application implementation)

Thanks,

Avi

|||

Avi,

What you are doing (suspend/insert/resume) is essentially similar to high-performance mirroring mode with possibly more overhead, because of the resynchronisation.

If you can tolerate possible data loss on failure, then SAFETY = OFF is the better choice. Does that work for you? If not can you give some more details on what you want to accomplish.

Thanks,

Kaloian.

Overhead for Aliases

Hello,

I have a question, is there any performance overhead for using Aliases in a SQL query?

I mean would there be any difference in performance between the following two statements:

Select

Students.StudentID ,

Students.Name ,

Students.Code

from

Students inner join

Countries on Students.CountryID= Students.CountryID

Where

Students.IsDeleted=1

--

Select

std.StudentID ,

std.Name ,

std.Code

from

Students AS std inner join

Countries AS cntr on std.CountryID= cntr.CountryID

Where

std.IsDeleted=1

No, not as far as I have ever seen. There should be no performance impact|||Cool. But is there any explanation why? How does SQL Server treat aliases? does it affect the execution plan or something?|||What you have said is correct: an alias has no affect on the execution plan.|||

I also agree with you..

But I worried about the following BLOG. When I try to execute on Mac it is really Random result..

http://weblogs.sqlteam.com/brettk/archive/2007/03/27/60141.aspx

|||No, aliases do not cause a performance issue.

The blog referenced shows a difference when NOT using aliases. I would suspect, but don't know, the difference is in the compile time, not execution. The compiler has to figure out what database the field is coming from, if you don't qualify it with an alias. This takes time.

Also, the datetime variable has a maximum resolution of 3.33 ms. So a test as small as the blog describes is meaningless.

Over-allocating space for a database, impact on performance

SQL Server 2005:
Assume I have 4 GB of data in a single database. I expect to have 15 GB
after two years. I have a 300 GB logical drive. Is there any harm in
setting the default size of the database to 20 GB? This avoids
fragmentation as new space is allocated to the drive, but creates a "big
shell". Is there a performance hit by sizing it big initially? Data will
be on RAID 5, Logs on RAID 1, and OS on RAID 1.
Thanks,
Mark
Other than the cost of creating the initial file, or restoring a backup,
(both of which are negligible if you have instant file initialization on), I
can't think of anything. (Backups will only see pages with actual data on
them, so it won't affect those.) It's good to size the file appropriately,
if you have the space... ideally, you will NEVER have an unplanned autogrow
event. Things can always change, e.g. after six months you might revise
your two-year estimate and want to grow the file again at some point. But
it is much better to plan this growth for a planned maintenance window or
low volume period, as opposed to letting the file grow in the middle of a
busy day... users will be unhappy.
"Mark" <mark@.idonotlikespam.com> wrote in message
news:OJAsB%239mIHA.1368@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> Assume I have 4 GB of data in a single database. I expect to have 15 GB
> after two years. I have a 300 GB logical drive. Is there any harm in
> setting the default size of the database to 20 GB? This avoids
> fragmentation as new space is allocated to the drive, but creates a "big
> shell". Is there a performance hit by sizing it big initially? Data
> will be on RAID 5, Logs on RAID 1, and OS on RAID 1.
> Thanks,
> Mark
>
|||It sounds like you are taking a set-it-up -and-forget-it approach to space
management. I'd argue that you should always monitor your database space
usage. Leave enough free space so that you don't run into space shortage
unexpectedly. But just don't let over-allocation give you a false sense of
security.
One potential downside of over-allocation is in case you need to deattach,
copy, and attach the database, you'd need to move a lot more data than
otherwise you would.
Linchi
"Mark" wrote:

> SQL Server 2005:
> Assume I have 4 GB of data in a single database. I expect to have 15 GB
> after two years. I have a 300 GB logical drive. Is there any harm in
> setting the default size of the database to 20 GB? This avoids
> fragmentation as new space is allocated to the drive, but creates a "big
> shell". Is there a performance hit by sizing it big initially? Data will
> be on RAID 5, Logs on RAID 1, and OS on RAID 1.
> Thanks,
> Mark
>
>

Over-allocating space for a database, impact on performance

SQL Server 2005:
Assume I have 4 GB of data in a single database. I expect to have 15 GB
after two years. I have a 300 GB logical drive. Is there any harm in
setting the default size of the database to 20 GB? This avoids
fragmentation as new space is allocated to the drive, but creates a "big
shell". Is there a performance hit by sizing it big initially? Data will
be on RAID 5, Logs on RAID 1, and OS on RAID 1.
Thanks,
MarkOther than the cost of creating the initial file, or restoring a backup,
(both of which are negligible if you have instant file initialization on), I
can't think of anything. (Backups will only see pages with actual data on
them, so it won't affect those.) It's good to size the file appropriately,
if you have the space... ideally, you will NEVER have an unplanned autogrow
event. Things can always change, e.g. after six months you might revise
your two-year estimate and want to grow the file again at some point. But
it is much better to plan this growth for a planned maintenance window or
low volume period, as opposed to letting the file grow in the middle of a
busy day... users will be unhappy.
"Mark" <mark@.idonotlikespam.com> wrote in message
news:OJAsB%239mIHA.1368@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> Assume I have 4 GB of data in a single database. I expect to have 15 GB
> after two years. I have a 300 GB logical drive. Is there any harm in
> setting the default size of the database to 20 GB? This avoids
> fragmentation as new space is allocated to the drive, but creates a "big
> shell". Is there a performance hit by sizing it big initially? Data
> will be on RAID 5, Logs on RAID 1, and OS on RAID 1.
> Thanks,
> Mark
>|||It sounds like you are taking a set-it-up -and-forget-it approach to space
management. I'd argue that you should always monitor your database space
usage. Leave enough free space so that you don't run into space shortage
unexpectedly. But just don't let over-allocation give you a false sense of
security.
One potential downside of over-allocation is in case you need to deattach,
copy, and attach the database, you'd need to move a lot more data than
otherwise you would.
Linchi
"Mark" wrote:
> SQL Server 2005:
> Assume I have 4 GB of data in a single database. I expect to have 15 GB
> after two years. I have a 300 GB logical drive. Is there any harm in
> setting the default size of the database to 20 GB? This avoids
> fragmentation as new space is allocated to the drive, but creates a "big
> shell". Is there a performance hit by sizing it big initially? Data will
> be on RAID 5, Logs on RAID 1, and OS on RAID 1.
> Thanks,
> Mark
>
>