Post reply

32 Bit editions of SQL Server are constrained to 2gb of ram.

  • September 6, 2011 at 12:04 am

    #247414

    I am confused regarding this Line:

    32 Bit editions of SQL Server are constrained to 2gb of ram or 32 bit system maximum use 4gb ram.

    If I have 24gb Ram on the server and sql server is installed as 32 bit.Still will it only use 2to4 gb or how much max ram it can use?

    Seniors Justify Please?

    Thanks

  • September 6, 2011 at 1:10 am

    #1377193

    Please go through this article this is good.

    http://blogs.msdn.com/b/john_daskalakis/archive/2009/04/22/9562252.aspx

  • September 6, 2011 at 1:11 am

    #1377194

    Depending on the OS platform and edition you are using, it's possible to use more RAM for SQL by enabling PAE and using AWE.

    visit:

    http://forums.techarena.in/tips-tweaks/979404.htm

    http://support.microsoft.com/kb/283037

  • September 6, 2011 at 1:17 am

    #1377196

    Without using AWE, a 32 bit system can only address 4GB of ram. This is the max amount that a 32 bit OS can use.

    By default, the OS will allow the user processes (like SQL Server, Word, Excel, Calculator, etc.) to use a total of 2GB, while the OS itself will use the other 2GB for processes like network drivers, video drivers, disk IO drivers, etc.

    Therefore, by default, SQL Server (a user process) can only address 2GB witting the user process space.

    There may be a few things you can do to adjust that. One is to add /3GB to the boot.ini file. This is good and bad and therefore you must test before implementing this on a production system. It basically tells the OS to use 3GB for user processes (like SQL Server) while only providing 1GB to the OS for system processes. This gives more for SQL server's buffer cache, but may potentially starve or even destabilize the system if it has a lot of network activity (or poor network drivers!). So use with care.

    I can't remember if Microsoft removed AWE from SQL Server 2008 or not, but if it is still there, you can increase the available ram another way. By adding /PAE to the boot.ini instead of /3GB, windows will be able to address 32GB of ram by basically using a huge address mapping table in memory (in the 4GB 'lower area') which points to the addresses in the additional area. Then SQL Server can use this 32GB of ram by an sp_configure setting.

    This is again good and bad. More for the buffer cache, but the other 4 sql caches can't access this 'AWE ram' (4GB-32GB), and the AWE buffer pages can't not be swapped to the OS page file (which they really shouldn't anyway), among other restrictions. Oh, and access to these memory addresses is slower since the request first needs to hit the 'PAE table' (in the lower 4GB area) to locate the memory address in the AWE area. I'm defining my own terms here to help explain the 2 different locations of ram.

    After I wrote all of this, it dawns on me that I'm sure someone wrote this up nicer and more clear and with proper defined terms. Like Technet? Maybe google for it or someone else may post it here.

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • September 6, 2011 at 1:47 am

    #1377205

    You can use sp_configure to adjust your SQL memory with PAE enabled.

  • September 7, 2011 at 1:03 am

    #1377793

    Thanx Babbhu,JIm and FHA

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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