Post reply

/3GB Switch Question

  • August 19, 2010 at 2:13 pm

    #226392

    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.

  • August 19, 2010 at 2:55 pm

    #1209377

    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

  • August 19, 2010 at 3:26 pm

    #1209383

    GilaMonster (8/19/2010)

    32 bit or 64 bit? (windows and SQL)?

    32 bit

  • March 17, 2011 at 3:54 pm

    #1300271

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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