Performance Issues and Sql Server Memory

Question

Performance Issues and Sql Server Memory

JonJon

Say Hey Kid

Points: 676
More actions
August 25, 2009 at 2:51 pm

#138328

I've looked at a few posts and I need some clarification please.
You have the ability to set the SQL Server Memory and I have mine set
MIN: 0
MAX 3839
and I have about 3.75GB of memory. Is is recommended that I decrease the MAX amount?
If it is recommended, is there a percentage I should set the MAX to?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Amit Singh Ten Centuries Points: 1295 More actions · Answer 1

whats the SQL Server version ? 2000 or 2005, 32 bit or 64 bit ?

as a general guideline with the given amount of memory i would leave SQL to manage things on its own.... but if you DO have to set the max and min values, i would leave some room for the OS.

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

JonJon Say Hey Kid Points: 676 More actions · Answer 2

JonJon

Say Hey Kid

Points: 676

August 25, 2009 at 3:03 pm

#1044547

Sql 2000, 32 bit.

How much should I leave the OS?

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 3

leave 1gb

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

JonJon (8/25/2009)
Sql 2000, 32 bit.

Is either /3GB or /PAE set in boot.ini? Is AWE enabled?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit Singh Ten Centuries Points: 1295 More actions · Answer 5

JonJon (8/25/2009)
I have about 3.75GB of memory.

I dont think PAE and AWE need to be enabled ... or What am i thinking :doze:

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Amit Singh (8/25/2009)
JonJon (8/25/2009)
I have about 3.75GB of memory.
I dont think PAE and AWE need to be enabled ... or What am i thinking :doze:

If neither of them is enabled, SQL can only use 2GB of memory as that's the max memory allowed for a process on 32 bit.

In this situation, I would recommend /3GB, not setting /PAE, not using AWE and setting the max memory to 3GB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JonJon Say Hey Kid Points: 676 More actions · Answer 7

how do I set /3GB and where do I check AWE?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

/PAE and /3GB in boot.ini, AWE is a SQL setting, check sp_configure.

In your situation, I would suggest /3GB to be in boot.ini, /PAE not to be there and AWE to be disabled.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Edit: double post

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JonJon Say Hey Kid Points: 676 More actions · Answer 10

How do I enter the /3GB into the boot.ini file?

do I just add /3GB

[boot loader]

timeout=30

default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut /3GB

JonJon Say Hey Kid Points: 676 More actions · Answer 11

ok I think I found on how to set it up...

Thanks for all the help.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

1) I didn't notice anyone else asking what else is running on the server. If this is not a dedicated SQL Server box beware using /3GB.

2) If you do use /3GB you still need to set max memory to 3GB or a tad less. If you don't use /3GB, set max memory to a bit under 2GB, maybe 1.5-1.7. Need to leave room for non-buffer pool memory usage.

3) Have you been monitoring for memory pressure?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

JonJon Say Hey Kid Points: 676 More actions · Answer 13

1) Its not a dedicated Sql Box. I have a reporting solution running on the box.

3) I have been monitoring the memory pressure, and that is how I noticed why SQL wasnt using more of the RAM.

I guess I can test out the /3GB switch and monitor the box. Worst case scenario I take the switch out.

Thanks for the heads up.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

If you have other stuff running you likely won't be happy with /3gb.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service