Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

/3GB Switch Question Expand / Collapse
Author
Message
Posted Thursday, August 19, 2010 2:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 139, Visits: 351
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.
Post #972163
Posted Thursday, August 19, 2010 2:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
32 bit or 64 bit? (windows and SQL)?




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #972183
Posted Thursday, August 19, 2010 3:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 139, Visits: 351
GilaMonster (8/19/2010)
32 bit or 64 bit? (windows and SQL)?



32 bit
Post #972191
Posted Thursday, March 17, 2011 3:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:08 PM
Points: 1,356, Visits: 5,660
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
Post #1080093
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse