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.
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.