SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


/3GB Switch Question


/3GB Switch Question

Author
Message
ntran777
ntran777
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 384
Hello,

So I have a SQL server that is experiencing some memory bottlenecks. O/S Memory Utilization is over 90%, Buffer cache hit ratios are tanking to below 50% and Procedure cache hit ratio is consistently at around 40%.

After going through queries and optimizing them the best I could, I have decided to try the /3GB switch.

My server is on Windows Server 2003 Enterprise and is a SQL 2005 Standard Edition. I have 8GB RAM in it right now and it is running 2 instances.

I have a few questions about using the switch.

1. How exactly is the virtual memory allocated to user/kernel mode operations?

For example, I have 8GB and I want to use /3GB switch. The system would reserve 4GB blocks of virtual memory for each instance of SQL server. 3GB would be reserved for one SQL and 1GB would be reserved for kernel mode. In the other block, 3GB would be reserved for that SQL and 1GB would be for kernel mode. Does this mean that the kernel mode operations get 2GB of memory or 1GB?

2. How much memory is sufficient to run kernel operations? Should I use the /USERVA switch to allocate a bit more to kernel mode?

3. If I wanted to add a third instance with the switch, how much more RAM would I need?

Assuming the case is true where each user program will reserve 1GB for the kernel mode operations could I for instance have 10GB of RAM with two of the SQL servers set to use 3GB max and the third to use 2GB? This would leave 2GB for kernel mode.

Thanks


EDIT: I also would like to know if SQL Server has the /LARGEADDRESSAWARE linker enabled by default or if I have to set that manually.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87413 Visits: 45272
32 bit or 64 bit? (windows and SQL)?

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


ntran777
ntran777
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 384
GilaMonster (8/19/2010)
32 bit or 64 bit? (windows and SQL)?



32 bit
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2746 Visits: 9900
As far I remember is /3GB -> 3GB to share amonst all programs, 1GB for OS.
If you have 2 instances, AWE could be a solution as it allows a buffer increase for both instances
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search