Post reply

Using 3 GB of RAM

  • January 7, 2004 at 2:51 pm

    #358793

    Hi all!

    Sorry if this is a straightforward question, but there is something I really don't understand...

    My customer has an SQL 2000 EE running on a Windows 2000 Advanced Server box with 4 GB of RAM.  Since nothing special has been done, SQL Server is using 2 GB of RAM; we are considering allowing it 3 GB.

    I have to give some general guidelines about that (so, no way to test: I don't have a 4 GB test server at hand).  First, I know I need to put the /3GB switch in the boot.ini file.

    Fine, but now how do I instruct SQL Server to use these 3 GB instead of the usual 2 GB?  Indeed, the 'max server memory' has a maximum of 2147483647, or 2 GB, and that's the only parameter I noticed.

    My questions are:

    - With the /3GB switch and no modification to max server memory, will SQL Server still be limited to 2GB of memory?

    - If yes, what is the trick to overcome this limitation?

    Thanks,

    Xavier

     

  • January 7, 2004 at 2:58 pm

    #489052

    This may help:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;274750

    The reason you can't see more than 2 GB in the server properties dialog box is because SQL Server is only able to see 2 GB until the /3GB switch is activated.

    K. Brian Kelley
    @kbriankelley

  • January 7, 2004 at 3:17 pm

    #489055

    <quote>With the /3GB switch and no modification to max server memory, will SQL Server still be limited to 2GB of memory?</quote>

    No; SQL Server 2000 EE on Windows 2000 AS will use 3GB user-mode addressing when you enable this by adding the /3GB switch.



    --Jonathan

  • January 8, 2004 at 12:41 am

    #489081

    Hi!

    Thanks a lot for your replies!

    See you,

    Xavier

  • January 9, 2004 at 5:19 am

    #489328

    I have a W2K-Advanced Server/SP3 machine (in an active/passive cluster) with 3584Mb RAM with the /3Gb switch set. The SQL2KEE/SP3 only uses 2.6Gb, and the OS is only using another 0.25Gb, making a total of 2.85Gb used and 662Mb free. How can I get SQL to utilise this extra RAM?


    Tony

  • January 9, 2004 at 5:47 am

    #489333

    That's normal.  SQL Server 2000 will not reserve the last 384M for its memory pool.  It still uses memory outside its pool for processes like extended stored procedures, distributed queries, and tran log formatting blocks.  If you have few databases and don't use many extended stored procedures or automation objects, you can increase the SQL Server memory pool by using the -g option; see Using Startup Options in BOL.  No application can use kernel-mode addresses, so the memory above the 3GB boundary is only available to the OS internals.



    --Jonathan

  • January 9, 2004 at 6:18 am

    #489338

    Thanks Jonathon, that's really helpful

    Checking in BOL suggests that restarting SQL with the default -g switch will make it reserve 256Mb, giving me another 150Mb for the memory pool. But as we don't use many DLLs or OA objects, I could probably grab even more by using something like -g 128.

    I will try it next time I have to restart the server.


    Tony

  • January 9, 2004 at 6:49 am

    #489350

    I can't restart a server with 4GB right now to verify this, but I believe that using just -g will be the same as not using the switch.  You will instead need to reduce the value to less than the default 256 (which is what we do). 

    BTW, lowercase b, as you have been using, means bit, not byte. 



    --Jonathan

  • January 9, 2004 at 7:04 am

    #489354

    Just as well you pointed out the difference between Bytes and bits or I wouldn't have been leaving much memory free!!! 

     


    Tony

  • January 9, 2004 at 7:55 am

    #489379

    Do you not also need to grant the service account right to lock pages in memory or do you do this only if you go beyond 3GB?


    Terry

  • January 9, 2004 at 8:04 am

    #489383

    No; that's only when using AWE memory, i.e. memory greater than 4GB by using the /PAE option.



    --Jonathan

  • January 9, 2004 at 9:06 am

    #489395

    Jonathan, we're trying to utilize 4GB and can't quite get it to work. Unfortunately it's a production box that we can't play around with much (we have a new server with same setup on the way for development where will attempt this again). Our server has 6GB and we want to use 4GB for SQL 2Ksp3, which is the only thing running on this server. When we first attempted this, we had the /3GB and /PAE in the boot.ini file. We sized the max memory in SQL to 4GB. Performance absolutely tanked so we panicked and went back to using our original setup, no switches in the ini file, max memory back to 2.8GB. Until we get the new server, we really can't play around to determine where we went wrong. I'm starting to think it's because we had both the /3GB and /PAE in the ini file but I'm not sure. Any ideas on this? I've got this same issue on a couple of other forum items but nobody else seems to have experienced this. Thanks for any help you may provide.


    Terry

  • January 9, 2004 at 9:25 am

    #489406

    Terry--

    What versions and editions of SQL Server and Windows Server?



    --Jonathan

  • January 9, 2004 at 9:35 am

    #489411

    SQL 2000, sp3 Enterprise Edition

    Windows 2000, sp4


    Terry

  • January 9, 2004 at 9:42 am

    #489415

    You must use Windows 2000 Advanced Server or Datacenter Server, or Windows Server 2003 Enterprise Edition or Datacenter Edition.



    --Jonathan

Viewing 15 posts - 1 through 15 (of 22 total)

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