SQL Server Performance

using memory more than 3gb

Discussion in 'Performance Tuning for DBAs' started by chinnis, Jun 21, 2003.

  1. chinnis New Member


    We have a sqlserver2000sp3/win2k advanced server machine which has 4gb of ram so
    we enabled awe and set the maxmemory to 3.5gb but when i look at the perfmon
    Sqlservermemorymanager-totalservermemory it's only using 3gb of ram can
    any body help me on this

  2. gaurav_bindlish New Member

    When you allocate more memory to SQL Server using /3GB switch to boot.ini file, the maximum memory that SQL Server can take is 3 GB as it has to leave 1 GB for the OS. You do not need to enable AWE for this. AWE is required when you need memory more than 4 GB.

    Seehttp://www.sql-server-performance.com/awe_memory.asp for more details.

  3. chinnis New Member

    Thanks Very much!
  4. chinnis New Member

    suppose my server has base memory of 4.5gb can i then use awe and set the maxmemory for
    the sql to use 4.25gb and leave the rest to os.

    is this possible

  5. gaurav_bindlish New Member

    In this case u'll use /PAE switch and still 1 GB will be used by OS. Effectively leaving 3.5 GB for SQL Server. This 1 GB is required by the server for AWE.

  6. chinnis New Member

    we are using win2k advanced server so how much ram we have ex(8gb)max 1gb of ram
    is used by OS what ever we do,is that correct

  7. gaurav_bindlish New Member

    Yes that is correct. Upto till 16 GB of RAM(This can be the case with Datacenter server only), U don't have to worry. After that we need to remove the /3GB switch from the file as now the OS needs 2 GB of memory to handle AWE.

  8. alzdba Member

    Can I still pick in on this issue [?]<br /><br />I have this 2-node w2000 cluster with 4 sql2000 instances.<br /><br />Both nodes have /3GB /PAE in boot.ini and 8GB of physical memory.<br />(is there any way to check these are active ?)<br /><br />On one node the first sql-instance grows up to 3GB of ram-usage, the second uses 2 GB.<br /><br />I have not enabled AWE and memory is managed by SQLServer without max or min settings.<br /><br />After +- 10 weeks the 3GB-instance starts throwing "insufficiant memory" errors.<br /><br />Overall this node has 5.4GB memory in use, so there should still be available for sqlserver. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Any hints ?<br /><br />What will happen if I enable AWE for 4 gb of ram for the current 3GB instance, when it will fail over to the second node , where the other 2 instances are running and are each taking up to 2,5 gb of ram. (also awe not enabled and memory is managed by SQLServer without max or min settings) ?
  9. satya Moderator

    What was the exact insufficient memory errors?
    May try capturing PERFMON counters during the session to asses.

    Satya SKJ
  10. alzdba Member

    sqlAgent job step history for the smtp-mail job says :
    Not enough storage is available to complete this operation. [SQLSTATE 42000] (Error 14) Associated statement is not prepared [SQLSTATE HY007] (Error 0) sp_OACreate has not yet been called successfully for this command batch. [SQLSTATE 42000] (Error 10019) Error creating object CDO.Message. [SQLSTATE 01000] (Error 0). The step failed.

    This also happend on jobs that only execute a xp_msver. This kind of jobs are used to "ping" the sqlserver.
  11. satya Moderator

    According to the error 'not enough storage...' it reflects running out of virtual memory. You can use Performance Monitor to determine what resources are being used.

    Are you using connection pooling?
    I believe there are some issues using SP_OACreate methods on the cluster.

    Satya SKJ

Share This Page