SQL Server Performance

More than 3GB memory?

Discussion in 'Performance Tuning for DBAs' started by davidfarr, Jun 3, 2005.

  1. davidfarr Member

    I am running SQL Server 2000 EE (with SP3a) on Windows 2000 Advanced Server with the /PAE /3GB OS boot parameters.
    My server has 8GB of physical memory. I can confirm that the OS can definitely see all 8GB and the SQL log file reports "Address Windowing Extensions enabled."
    As can be seen in the config below, the following SQL Server memory configuration options are currently set:
    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    max server memory (MB) 4 2147483647 4096 4096
    min server memory (MB) 0 2147483647 4096 4096
    set working set size 0 1 1 1

    The value is reasonable, 4GB, half the physical memory available, the min and max are the same and the set size option is 1.
    According to BOL, the configuration above should work fine.
    However, I consistently get the following SQL log file warning on each reboot.
    initdata: Warning: Could not set working set size to 0 KB.
    0 KB? Surely that cant be right?

    I know some members will simply advise to set the working set size option to 0, but that then defeats the purpose of having a set size option, so it's not an acceptable solution.

    In addition, I recently read this article from Intel:
    http://www.intel.com/cd/ids/developer/asmo-na/eng/189869.htm?page=4
    It states there that the combination of the /PAE and /3GB OS boot parameters.. "App gets 3GB and benefits from full 8GB memory."
    Is SQL Server considered to be an application in the normal sense?
    If an application "gets 3GB" then how can it "benefit from the full 8GB"?
    It sounds like a contradiction. Surely an application can only benefit from what it gets?
    Also; if SQL Server only "gets 3GB", and I have a working set size of 4GB, could this be generating the log warning?
    Any insights on the above would be appreciated.
  2. pyao88 New Member

    http://www.sql-server-performance.com/awe_memory.asp

    Do you need to enable AWE to see full 4gig instead of 2.7gig w/o AWE?

    > If an application "gets 3GB" then how can it "benefit from the full 8GB"?

    It means the OS can see full 8 gig of memory. Without /PAE I guess OS only sees 4 gig of memory?
  3. gurucb New Member

    David, I have got a couple of questions for you:

    after you get this error message:

    have you had a chance to look at perfmon.

    In perfmon look at

    Process - Object
    Working Set - Counter
    Working set Maximum - counter.
    SQLServr

    What do these values say? If they are the values that you have specified then this message might be misleading as well.





  4. satya Moderator

    KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech to address basic instructins on configuring the Memory more than 2GB.

    'Set working set size' option used to reserve physical memory space for SQL Server. SQL Server based on workload and available resources configure the server memory setting automatically. It will vary dynamically between min server memory and max server memory.

    Setting set working set size means operating system do not swap out SQL Server pages even if they can be used more readily by another process when SQL Server is idle. It's recommended do not set working set size, i.e. allowing SQL Server to use memory dynamically

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. davidfarr Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by gurucb</i><br /><br />David, I have got a couple of questions for you:<br /><br />after you get this error message:<br /><br />have you had a chance to look at perfmon.<br /><br />In perfmon look at <br /><br />Process - Object<br />Working Set - Counter<br />Working set Maximum - counter.<br />SQLServr <br /><br />What do these values say? If they are the values that you have specified then this message might be misleading as well.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Working Set for sqlservr<br />Maximum: 93298688<br />Working Set Peak for sqlservr<br />Maximum: 93585408<br /><br />Confirms a low 90MB, not the 4GB I was hoping for.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<pre id="code"><font face="courier" size="2" id="code">name minimum maximum config_value run_value<br />----------------------------------- ----------- ----------- ------------ -----------<br />max server memory (MB) 4 2147483647 4096 4096<br />min server memory (MB) 0 2147483647 4096 4096<br />set working set size 0 1 1 1<br />awe enabled 0 1 1 1</font id="code"></pre id="code">The /PAE and /3GB OS boot parameters are set, the OS can see 8GB.<br />I wish I knew what else to be looking at.<br /><br />On a sidenote:<br />On a new pair of Itanium Servers running Windows 2003, I have successfully configured a SQL Server 2000 64-bit active/passive cluster and had no problems configuring the memory on those.<br />(12GB SQL Server allocation, confirmed by Perfmon [<img src='/community/emoticons/emotion-1.gif' alt=':)' />])<br />
  6. gurucb New Member

    There is something amiss here.

    Operating system is seeing 8Gigs of RAM, So , SQL Server should see it. You have enabled 4Gigs of SQL Server. Also /3GB is enabled.

    If AWE is enabled, SQL Server should committ the memory till Max Server memory. So this ammount of physical memory should be in sql server acccount.

    But it is not there (only 90MB) then atleast OS should have it. What is the Available MBytes consistently?

    Is the server busy or idle? If so can you try to over load the server and see if the memory utilizations increase.

    What about teh buffere cache hit ratio for SQL Server?

    What about removing /3GB switch from boot.ini?

  7. davidfarr Member

    The following additional Perfmon stats may tell a story:

    Object: Process
    Counter: Working Set
    Instance: _Total
    Maximum: 302264320
    If I understand correctly, that tells me that all the current processes on the server (including sqlservr) total to a 302MB memory usage. Correct?

    But then there are these stats aswell:

    Object: Memory
    Counter: Available Megabytes
    Average: 3395
    Only 3.3GB of the 8GB total is not currently in use?
    There should therefore be processes somewhere using 4GB+ memory.

    Object: Memory
    Counter: Committed Bytes
    Maximum: 4608835584

    Object: Memory
    Counter: %Committed Bytes in use
    Maximum: 44.202

    Object: SQLServer:Memory Manager
    Counter: Total Server Memory(KB)
    Maximum: 4213512
    Object: SQLServer:Memory Manager
    Counter: Target Server Memory(KB)
    Maximum: 4213512
    The above two counters indicate 4GB but SQL Server does not appear to be using it correctly.

    Object: SQLServer:Memory Manager
    Counter: SQL Cache Memory(KB)
    Maximum: 1608

    Object: SQLServer:Buffer Manager
    Counter: Buffer Cache Hit Ratio
    Maximum: 99.890
    Average: 98.333

    Object: SQLServer:Memory Manager
    Counter: Maximum Workspace Memory(KB)
    Maximum: 1986312

    Object: SQLServer:Memory Manager
    Counter: Granted Workspace Memory(KB)
    Maximum: 11232

    If the above stats can reveal a solution, I'd be happy to hear comments.
    I have not yet attempted removing the /3GB switch yet, will attempt this later in the week if the above stats do not reveal a solution.
  8. barrjm New Member

    Hi,

    1: /3GB only means SQL Server as a process will get to use 3GB of the 4GB address space allocated, and the OS will get 1 GB. On systems running between 8-12 Gig of RAM, you should not use it. According to Microsoft, this will cause negative system performance.

    2: Microsoft also states never to use the Reserve Physical Memory" setting unless you are running other applications on your SQL Server (Server) that are memory intensive. This will also have a negative impact on the system.

    3: You must have the /PAE switch in the Boot.INI file of the Operating System.

    4: Enable AWE in SQL Server.

    5: Microsoft also recommends to leave 1GB free on the server for the Operating system to use, and 1 processor free. If not, and SQL Server is processing a heavy workload, you may not be able to work on the server to perform trouble-shooting...etc.

    6: You should allow SQL Server to dynamically allocate memory from Min: 0 / Max: 7168.

    7: You are, I believe, getting the error because you have the "Reserve Physical Memory" setting set, and are trying to dynamically allocate memory. When you do use that setting, you should set the Min/Max values to the same, Or, set the Min value to at least half the amount of Physical RAM on the system.

    John Barr
  9. davidfarr Member

    quote:1: /3GB only means SQL Server as a process will get to use 3GB of the 4GB address space allocated, and the OS will get 1 GB. On systems running between 8-12 Gig of RAM, you should not use it. According to Microsoft, this will cause negative system performance.
    Do you perhaps have a website article you can refer me to regarding this?
    I am very surprised that Microsoft would say this as it appears to contradict Intel's explanation of the /3GB switch and Microsoft's SQL Server KB article on the /3GB switch makes no mention of excluding the switch on 8-12GB servers.
    Does "Negative system performance" refer to actual event log error messages that fail to reserve memory?

    quote:2: Microsoft also states never to use the Reserve Physical Memory" setting unless you are running other applications on your SQL Server (Server) that are memory intensive. This will also have a negative impact on the system.
    It's a dedicated SQL Server. The OS, SQL Server, Anti-Virus software and a mail client are the only installed software.
    As Intel explains; any application that does not have sufficient memory allocated to it will resort to paging to the hard disk, which is inefficient. Surely there are benefits to reserving physical memory for SQL Server on servers with plenty RAM (8GB+)?
    Again, I am keen to read the article that you sourced this from.

    quote:3: You must have the /PAE switch in the Boot.INI file of the Operating System.
    Done already.

    quote:4: Enable AWE in SQL Server.
    Done already

    quote:5: Microsoft also recommends to leave 1GB free on the server for the Operating system to use, and 1 processor free. If not, and SQL Server is processing a heavy workload, you may not be able to work on the server to perform trouble-shooting...etc.
    I have left 4GB free for the OS to use but not a processor.
    I am not convinced that assigning all processors to SQL Server is causing my memory error as this would then be a common problem for DBAs everywhere.

    quote:6: You should allow SQL Server to dynamically allocate memory from Min: 0 / Max: 7168.
    It is a common practise for DBA's to reserve physical memory for SQL Server as opposed to dynamic memory allocation. If Microsoft discourage reserving memory then I'm quite puzzled why they would even make the option available as a server setting. Surely there are performance benefits when reserving memory?

    quote:7: You are, I believe, getting the error because you have the "Reserve Physical Memory" setting set, and are trying to dynamically allocate memory. When you do use that setting, you should set the Min/Max values to the same, Or, set the Min value to at least half the amount of Physical RAM on the system.
    That statement doesn't make sense to me. I cannot be Reserving physical memory AND dynamicaly allocating memory. The two settings are mutualy exclusive. The one cancels the other. What I am doing is attempting to reserve memory, but when this attempt fails in the event log then SQL Server resorts to dynamic memory allocation.
    The "Reserve Physical Memory" setting is exactly the same the "set working set size" option in the server settings, and I have set the min/max to the same value as required.
    I am grateful for everyone's contributions to this thread but I am not convinced that dynamic memory allocation offers greater performance than reserved memory.
    I agree that dynamic memory allocation will make my error message go away but it then also defeats the purpose and performance advantages of reserved memory.
    I have 8GB physical memory on a server that SQL Server is currently not even using 500MB worth. Somehow I think there's still room for improvement there.
    In principle, I should be able to reserve memory for SQL Server without an error message.

    Intel does also mention in their article that the /3GB switch may not be supported by all server motherboard architectures, especialy older ones. The server I am having trouble with is an older Intel KOA board. In my opinion, I think this may be my problem but unfortunately Intel do not list the 3GB compatible/incompatible boards or chipsets so I cannot be certain.
  10. gurucb New Member

    quote:<br />--------------------------------------------------------------------------------<br />1: /3GB only means SQL Server as a process will get to use 3GB of the 4GB address space allocated, and the OS will get 1 GB. On systems running between 8-12 Gig of RAM, you should not use it. According to Microsoft, this will cause negative system performance.<br />--------------------------------------------------------------------------------<br /><br /><br />Do you perhaps have a website article you can refer me to regarding this?<br />I am very surprised that Microsoft would say this as it appears to contradict Intel's explanation of the /3GB switch and Microsoft's SQL Server KB article on the /3GB switch makes no mention of excluding the switch on 8-12GB servers.<br />Does "Negative system performance" refer to actual event log error messages that fail to reserve memory?<br /><br /><br />For this above question here is the explanation (i think it will be in Guru's Guide to SQL server) By the way I am not the author <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />Every Process in 32 bit systems can access 2^32 bits and that count to 4GB.<br />this 4Gb is by default split into 2. Kernel Mode and User Mode.<br /><br />Understandbly User Mode applications (SQL) work in user mode.<br /><br />Kernel Mode acts as intermediate between User and hardware resources. Since we are dealing with memory in this case will concentrate only on memory.<br /><br />When SQl Server has 2Gigs of User address space it says hey I am using from this byte say 0x1234 to 0x2222 address in my virtual address space and now it is the work of os to map these address from user address space to actual physical memory.<br /><br />How does it do it?<br /><br />It will have a small database in its address space called PTE/PDE that map the virtual pages to physical memory.<br /><br />when physical ram is high (8-12 gigs) obviously the database in needs to be big in Kernel mode <br /><br />hence it is not recommended to put /3gb switch in there.<br /><br /><br /><br /><br />
  11. gurucb New Member

    I am not sure about error messages but I have an expreince too the when awe is enabled perfmon does not reflect correct values.
  12. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /> think it will be in Guru's Guide to SQL server<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />To be precise here. "The Guru's Guide to SQL Server Architecture and Internals" by Ken Henderson. Just happen to reread that chapter yesterday for some research. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  13. gurucb New Member

  14. BennettGClark New Member

    We had similar situation, the following worked in a busy production environment:
    SQL Server 2000 sp3 Enterprise Edition
    Windows 2003 Enterprise Edition
    8 GB total installed memory
    Goal: 6GB for SQL Server, and 2GB for Windows OS.
    (Note: With 8GB total memory, could also allocate 7GB for SQL Server, and leave 1GB for Windows to use. This combination would give best overall performance for a pure database server, with no applications also running.)

    Recommendations:
    1. Windows boot.ini file should NOT have both /3GB and /PAE switches set.
    2. Remove /3GB switch from boot.ini
    3. Include /PAE switch in boot.ini
    4. Set 'AWE' in SQL Server (Address Windowing Extensions)
    5. SQL Server properties: NOT dynamically configured SQL Server memory.
    6. SQL Server properties: Set a fixed memory of 6 GB.

    Sample script:
    **********************************
    --------------------------------------------------
    -- Script name: awe_enable.sql
    -- Date: 6/24/2005
    -- Name: Bennett Clark
    -- Instructions: Run from SQL Query Analyzer on SQL2KP03.
    -- Description:
    -- Change SQL Server memory settings to use 6GB.
    -- 8 GB total memory is installed, Windows gets 2GB.
    -- Note:
    -- Setting changes are also needed in boot.ini,
    -- to remove the /3GB switch and add the /PAE switch.
    -- Then reboot the machine. SQL Server memory settings
    -- will take effect when SQL Server restarts.
    --------------------------------------------------
    --
    use master
    go
    --------------------------------------------------
    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    --DBCC execution completed.
    --Configuration option 'show advanced options' changed from 1 to 1.
    --Run the RECONFIGURE statement to install.
    --The command(s) completed successfully.
    --The command(s) completed successfully.
    --------------------------------------------------
    sp_configure 'awe enabled', 1
    RECONFIGURE
    GO
    --DBCC execution completed.
    --Configuration option 'awe enabled' changed from 0 to 1.
    --Run the RECONFIGURE statement to install.
    --The command(s) completed successfully.
    --The command(s) completed successfully.
    --------------------------------------------------
    sp_configure 'max server memory', 6144
    RECONFIGURE
    GO
    --DBCC execution completed.
    --Configuration option 'max server memory (MB)' changed from 6144 to 6144.
    --Run the RECONFIGURE statement to install.
    --The command(s) completed successfully.
    --The command(s) completed successfully.

    **********************************

    Hope this helps.

Share This Page