SQL 2008 R2 Config - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL 2008 R2 Config

Author

Topic

boarderyogi
Starting Member

4 Posts

Posted - 2011-05-23 : 14:37:27

Hello,

I'm pretty new to setting up sql servers and was after a bit of advice.
This is our current server setup:

32GB RAM
Windows Server 2008 Enterprise 32-bit
SQL Server 2008 R2

Currently there is no PAE switch enabled and AWE is not enabled in SQL.
The sql server memory settings are:

Minimum 1024
Maximum 4096

Other Memory options:

0 and 1024

The server is performing very badly with regard to running our in house software which runs on sql server.
I didn't set this server up but I'm seeing that it is paging badly and I'm thinking that if the sql server memory settings were configured better we might see a performance increase.

What do you guys think??
Thanks in advance

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-23 : 17:59:47

Do yo uhave the option to upgrade the OS to 64-bit?
The extra RAM via /PAE & /AWE is only used for data caching and not for plan caching.. with 32 gigs, you might be in much better shape with x64 and take full advantage of the hardware resources.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

boarderyogi
Starting Member

4 Posts

Posted - 2011-05-23 : 18:02:42

Hi there,

No we can't upgrade to x64 as our in house software won't run on it.
I have to try to tweak the 32bit installations.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-23 : 18:10:21

http://support.microsoft.com/kb/274750

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

boarderyogi
Starting Member

4 Posts

Posted - 2011-05-24 : 09:27:38

I've enabled awe with the "lock pages in memory" GPO setting.
I've set the sql max memory usage setting to 20480 and min to 0.
I've also set the advanced system options to "background services" rather than "programs".
Had to try something, the server was performing horribly and paging excessively even though it didn't appear to be using anywhere near it's full RAM.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-24 : 09:34:56

Have you considered performance tuning the application?

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-24 : 09:37:12

No, it wouldn't be. Without AWE SQL could use no more than 2GB of memory.

You need to ensure that the PAE switch is on too, otherwise AWE has no effect. I think it's on by default in Server 2008, but check.

--
Gail Shaw
SQL Server MVP

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-24 : 21:51:47

quote:
Originally posted by boarderyogi

I've enabled awe with the "lock pages in memory" GPO setting.
I've set the sql max memory usage setting to 20480 and min to 0.
I've also set the advanced system options to "background services" rather than "programs".
Had to try something, the server was performing horribly and paging excessively even though it didn't appear to be using anywhere near it's full RAM.

Is this server dedicated to SQL Server? Or, are you running the application on the same server?

If this is dedicated, then you can upgrade to x64 with no problems - unless you have one of the specific issues that would prevent it. Those issues would be either extended procedures that cannot be rebuilt and recompiled for x64 or drivers to other systems that don't exist for x64 (e.g. linked servers to Access/Excel, etc).

Also, if this is a dedicated server for SQL - you can increase the max memory up to 28GB.

Jeff

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-25 : 01:46:30

Even if the app is running on the same server, 32 bit software runs fine on 64 bit windows.

--
Gail Shaw
SQL Server MVP

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-25 : 04:28:21

If SQL Server is your priority then check the option BOOST SQL SERVER PRIORITY

64-bit option is better option but you have to enable PAE to use AWE

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-25 : 04:50:46

quote:
Originally posted by Jahanzaib

If SQL Server is your priority then check the option BOOST SQL SERVER PRIORITY

Do not enable the boost priority option.

It's deprecated and will be removed in a future version, it is not a 'turbo button' or a 'go faster' switch. If the server is dedicated to SQL, it will have little to no effect. Additionally, as per Books Online:

quote:
Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.

--
Gail Shaw
SQL Server MVP

boarderyogi
Starting Member

4 Posts

Posted - 2011-05-25 : 10:13:09

Thanks for the replies guys.
I'm trying to optimise sql and the hardware as best I can before passing it over to the development team and saying "the problem is with your code!"
i know most 32bit apps can run on x64 machines but ours doesn't so at the moment it aint an option.
It's not a dedicated sql server, that's why I've set the max memory to 20 out of the 32GB RAM so that the other apps have resources.
I'm deploying a new server with the same settings (AWE etc) and I'm deploying it with a RAID 10 setup instead of RAID 5. I ran sqlio and the RAID 10 was getting upto 50% better througput which should help as the servers are paging heavily at the moment as well which is strange as the server doesn't appear to be using it's full RAM.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-25 : 16:55:06

quote:
Originally posted by boarderyogi

Thanks for the replies guys.
I'm trying to optimise sql and the hardware as best I can before passing it over to the development team and saying "the problem is with your code!"
i know most 32bit apps can run on x64 machines but ours doesn't so at the moment it aint an option.
It's not a dedicated sql server, that's why I've set the max memory to 20 out of the 32GB RAM so that the other apps have resources.
I'm deploying a new server with the same settings (AWE etc) and I'm deploying it with a RAID 10 setup instead of RAID 5. I ran sqlio and the RAID 10 was getting upto 50% better througput which should help as the servers are paging heavily at the moment as well which is strange as the server doesn't appear to be using it's full RAM.

That is what I thought your response would be. This really should be split between at least 2 systems, one for the application and one just for the database. But, it is what you have to work with...

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources