3gig or 4?

Hey all,
Ok I have a client that currently have 3gig of memory in their SQL
Server (SQL2005).
They are using the /3gb switch in their boot.ini so in theory SQL
server should use greater than 2 gig.
Ok from my understanding, if they put in an extra 1 gig of ram to make
the server now have 4 gig, would this mean that SQL Server would use 3
gig, and the OS 1gig?
So are their any advantages from going from 3gig to 4gig? (would it
help paging for example)
Look forward to your comments
Thanks!Hi,
Read this article..
http://www.sql-server-performance.com/awe_memory.asp
Thanks
Hari
SQL Server MVP
<davconts@.gmail.com> wrote in message
news:1155079279.501553.287040@.i42g2000cwa.googlegroups.com...
> Hey all,
> Ok I have a client that currently have 3gig of memory in their SQL
> Server (SQL2005).
> They are using the /3gb switch in their boot.ini so in theory SQL
> server should use greater than 2 gig.
> Ok from my understanding, if they put in an extra 1 gig of ram to make
> the server now have 4 gig, would this mean that SQL Server would use 3
> gig, and the OS 1gig?
> So are their any advantages from going from 3gig to 4gig? (would it
> help paging for example)
> Look forward to your comments
> Thanks!
>|||That article is not exactly accurate or clear in places. For instance, it
states, "AWE memory technology is used only for the RAM that exceeds the bas
e
4GB of RAM". That is not correct. You can enable AWE with less than 4GB of
RAM.
Linchi
"Hari Prasad" wrote:

> Hi,
> Read this article..
> http://www.sql-server-performance.com/awe_memory.asp
> Thanks
> Hari
> SQL Server MVP
> <davconts@.gmail.com> wrote in message
> news:1155079279.501553.287040@.i42g2000cwa.googlegroups.com...
>
>|||Thanks for the replies.
I read the article but it doesn't really answer my question on whether
it is worth going from 3Gig to 4gig... will SQL server be able to use
that extra gig placed in the machine or will the OS take hold of the
1gig?
Sounds like using AWE will take all the memory (besides 128 meg) unless
a max memory setting is specified in SQL.. so in theory this could
force it to use say 3.5gig of memory.
But the article also suggests not using AWE for a 4gig machine.. so bit
confused now
I assume there would be advantages going from 3 to 4gig... anyone have
any experiences in this?|||Using AWE on a 4GB box just doesn't make much sense.
PAE (/3GB) will use 3 GB for SQL Server, so in a 4 GB box, AWE uses a fair
amount of resources (including memory) to manage the extended memory,
thereby negating most of the expected performance gain.
My experience is that you have to approach 5 GB + before AWE really shows
any gains.
Of course, it could be hardware related and YMMV.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<davconts@.gmail.com> wrote in message
news:1155086938.963563.37190@.m79g2000cwm.googlegroups.com...
> Thanks for the replies.
> I read the article but it doesn't really answer my question on whether
> it is worth going from 3Gig to 4gig... will SQL server be able to use
> that extra gig placed in the machine or will the OS take hold of the
> 1gig?
> Sounds like using AWE will take all the memory (besides 128 meg) unless
> a max memory setting is specified in SQL.. so in theory this could
> force it to use say 3.5gig of memory.
> But the article also suggests not using AWE for a 4gig machine.. so bit
> confused now
> I assume there would be advantages going from 3 to 4gig... anyone have
> any experiences in this?
>|||You have no need to use AWE, there are many disadvantages to using AWE as
well.
To determine if the 3GB switch will allow SQL Server to use more memory can
you tell us what the LEAST amount of free memory you see on the system?
What is that MAX amount of memory you see the SQL Server process consume?
On a side note you might not need to add the additional RAM is your buffer
cache hit ratio is already at 99%+
Paul
<davconts@.gmail.com> wrote in message
news:1155086938.963563.37190@.m79g2000cwm.googlegroups.com...
> Thanks for the replies.
> I read the article but it doesn't really answer my question on whether
> it is worth going from 3Gig to 4gig... will SQL server be able to use
> that extra gig placed in the machine or will the OS take hold of the
> 1gig?
> Sounds like using AWE will take all the memory (besides 128 meg) unless
> a max memory setting is specified in SQL.. so in theory this could
> force it to use say 3.5gig of memory.
> But the article also suggests not using AWE for a 4gig machine.. so bit
> confused now
> I assume there would be advantages going from 3 to 4gig... anyone have
> any experiences in this?
>|||Well we are actually doing a benchmark session tomorrow.
First we are going to run with 3gig of ram, and benchmark our app/db
performance.
Then, we will restart the SQL server with an additional gig of ram (now
4 gig) and compare.
FYI It's a 20gig database. CPU for the server looks adequate from what
I can see (dual xeon). The SQL server only runs SQL 2005, and it is
exclusive to our app.

>From a benchmark we did a month or so ago, I thought the memory usage
seemed ok.
The disks seemed to be a possible bottle neck - and there seemed to be
a fair bit of paging although how much is normal paging I'm not 100%
sure.
I'm hoping that the extra ram might help reduce disk usage/paging.
Oh I should also mention that we are runing raid 10, have the mdf on
d:, ldf file on e: and tempdb on c:\
David
Paul Widner wrote:[vbcol=seagreen]
> You have no need to use AWE, there are many disadvantages to using AWE as
> well.
> To determine if the 3GB switch will allow SQL Server to use more memory ca
n
> you tell us what the LEAST amount of free memory you see on the system?
> What is that MAX amount of memory you see the SQL Server process consume?
> On a side note you might not need to add the additional RAM is your buffer
> cache hit ratio is already at 99%+
> Paul
> <davconts@.gmail.com> wrote in message
> news:1155086938.963563.37190@.m79g2000cwm.googlegroups.com...|||I think that you will find no change or perhaps even a decreased performance
for AWE.
/AWE sets aside 1 GB for the OS AND AWE memory management extensions, and
adds a lot of processing overhead.
With /3GB, the OS still gets 1 GB, but you don't have the AWE overhead.
AWE Net change: Memory about the same, available CPU cycles reduced.
Depending upon the database, number of users, etc., I usually try to have
enough memory so that the majority of active tables are at all times in
cache. Main thing to watch for is memory page I/O. A lot of activity
indicates a need for more memory.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<davconts@.gmail.com> wrote in message
news:1155170138.095989.131980@.m73g2000cwd.googlegroups.com...
> Well we are actually doing a benchmark session tomorrow.
> First we are going to run with 3gig of ram, and benchmark our app/db
> performance.
> Then, we will restart the SQL server with an additional gig of ram (now
> 4 gig) and compare.
> FYI It's a 20gig database. CPU for the server looks adequate from what
> I can see (dual xeon). The SQL server only runs SQL 2005, and it is
> exclusive to our app.
>
> seemed ok.
> The disks seemed to be a possible bottle neck - and there seemed to be
> a fair bit of paging although how much is normal paging I'm not 100%
> sure.
> I'm hoping that the extra ram might help reduce disk usage/paging.
> Oh I should also mention that we are runing raid 10, have the mdf on
> d:, ldf file on e: and tempdb on c:\
> David
>
> Paul Widner wrote:
>