SQL server 2005 memory settings


  • Hi

    Could some one tell me the details of the Maximum Server Memory Setting? We have two production servers and on one of them has set up the maximum server memory to 2147483647 MB and another one is set to 10000 MB and AWE is enabled on this server. Both servers has 12 GB of Ram. What is that number? Is that is KB or MB it says it is in MB. Please let me know the difference for both servers in memory allocation. Thanks!
    • Moved by VidhyaSagarModerator Tuesday, January 06, 2009 10:04 AM Database engine related query (Moved from SQL Server Setup & Upgrade to SQL Server Database Engine)
    Monday, January 05, 2009 6:14 PM

All replies

  •   The first server which has 2147483647 MB is set to default memory configurations hence the memory allocation depends on the sql edition & version dependent (32 bit or 64 bit ) etc. In the second server the max memory is set to 10 GB out of 12 GB hence SQL Server will allocate 10 GB for its use and the remaining 2 GB for OS. AWE has been enabled because you are pointing sql server to address virtual memory space. Check Memory settings subtopic in the link below.

    I'm moving this thread to database engine where you can get some more good responses.
    Vidhya Sagar. Mark as Answer if it helps!
    Tuesday, January 06, 2009 10:03 AM
  • The value of 2147483647 MB for the first server is the default value for SQL Server, which basically just lets SQL Server dynamically manage how much memory is allocated to the Buffer Pool until it gets a low memory signal from the operating system. The second server has its buffer pool limit set at 10GB, allowing 2GB for other parts of SQL Server and other applications and the operating system.

    Can you run SELECT @@VERSION and report the results?  This will tell us the exact version and edition of SQL Server that you are running on both servers.
    Tuesday, January 06, 2009 4:36 PM
  •  Here are the results. It is SQL 2005 on windows 2003 64 bit. if we change the memory setting in the server, do we need to reboot the server? Is AWE is only for 32 bit or We can unable that for 64 Bit also?

    In our server the page file was set to the default size which is 1.5 times of the ram. But we had some space issues on the local drive with the temp db and we chaged the page file size to 2 GB and use the maximum server memory and enabled the AWE to see how it goes. Please suggest me on this issue. Can we move the temp db to the other drive (LUN)? Is it going to be effect on performance.?

    Microsoft SQL Server 2005 - 9.00.3068.00 (X64)   Feb 26 2008 23:02:54   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
    Wednesday, January 07, 2009 4:01 PM
  • Hi,

    The purpose of using AWE/Lock Pages in Memory on 64-bit is to work around potential issues with working set trimming (as per whereas on 32-bit the purpose of using AWE APIs is to be able to access more memory than 3GB (and "Lock Pages in memory" is required to use the AWE APIs).

    You need to recycle the SQL Server service after you enable AWE (if you look up "Setting Server Configurations Options" in BoL it will list all options and whether they require restart of the service or not, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9f38eba6-39b1-4f1d-ba24-ee4f7e2bc969.htm).

    I don't quite get the question about tempdb - are you worried that the performance is going to be worse on some other disk than on the local disk?


    Elisabeth Rédei | | MCITP, MCT |
    Wednesday, January 07, 2009 5:18 PM
  • You are running a fairly old build of SQL Server 2005 Standard Edition. Lock Pages in Memory only works with Enterprise Edition. You can change the max server memory setting without restarting SQL Server.

    I would also consider getting either CU11 for SQL Server 2005 SP2 or SP3 and then CU1 for SP3 installed. There were a lot of memory related fixes in builds after what you have installed.

    It is a good idea to have TempDB on a dedicated, fast drive array. You can move it, but you will have to restart SQL Server for the change to take effect.


    Wednesday, January 07, 2009 7:37 PM
  • So the temp db don't have to be in the local drive right? Is it going to have any performance issues if I move it from local drive to the LUN? please let me know. Thanks!
    Friday, January 09, 2009 3:56 PM
  •  A fast drive array, whether it is direct attached storage or on a SAN is a good place for TempDB. If you allocate enough spindles to the LUN for TempDB to support its I/O, it will be fine. How much I/O you need depends on your workload. Please mark as the answer if this post solved your issue.
    Friday, January 09, 2009 4:03 PM
  • Thanks! I have one more question. We have set up the maximum memory on SQL server to 9 GB out of 12 GB of total memory. when I looked at the task bar I am able to see how much memory is left and how much is used out of the total memory. How do I know whow much memory the SQL server is using.

    When I go to the performance monitor also it is showing the available memory and used memory out of totla server memory. Is there any way how to tell how much memory is SQL server is using?
    Wednesday, January 14, 2009 6:59 PM
  • Is there a limit on how large the Maximum Server Memory Setting Can be?

    The environment:
    Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86) Enterprise Edition
    Running on Windows Server 2003 R2 Enterprise Edition (5/2), Build 3790, Service Pack 2

    This is a 32-bit environment with AWE enabled.  I am running 3 instances on this server.

    Based on AWE limitations we have 32 GB of physical memory on the server.

    Assuming I max out the physical memory and set two of the instances to a 1 GB a pieces and had all of them start with the -g (related to Memory-to-Leave) startup parameter set to 1024.

    Is there any limitation on what I can set the max server memory to?

    Would there be any limitations that would stop me from setting the maximum server memory to 24 GB?

    The reason to increase this memory area would be due to large reports that are heavily using memory areas related to sort,hash,index, optimizer and Procedure Cache.

    Any guidenance will be appreciated.
    Wednesday, January 14, 2009 8:18 PM