SQL Server Performance

Memory usage

Discussion in 'Performance Tuning for Hardware Configurations' started by Aviel, Nov 24, 2004.

  1. Aviel New Member

    Hi there,

    We are running SQL Server 2000 Standard edition sp3 on Win2k.
    The server has 3.98GB, but because of the standard edition only 2GB is used.

    SQL memory is configured as fixed sized to 2048MB and SET WORKING SIZE is true, ie "Reservre physical memory for SQL Serever" is checked in Enterprize manager.

    When I look at PerfMon on SQLServer:Memory Manager/Target Server Memory or Total Server Memory I see 1682MB. Even if I increase the fixed size memory to be even more than 2048MB I can't see any change in those counters.
    Also the counter "Working Set" of "Process" object for sqlsrvr shows 1798MB and the Available MB in the Memory object shows 1686MB


    Q1:
    where has the rest of the memory gone ? where are the other ~300mb ?
    Q2:
    what's the different between all the obove memory counters that show around 1682mb and the "Working Set" counter which shows 1798MB


    any explanantions ?

    thanks

    Aviel Iluz
    Database Administrator
    Pacific Micromarketing
    Melbourne Australia
    www.pacmicro.com.au
  2. satya Moderator

    Are you starting SQL server services with any particular switch such as -g?
    Are there any other applications sharing the server resources?

    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.
  3. Aviel New Member

    None of those.

    No -g switch and the server is dedicated to SQL Server.

    Add to mention again, the server has almost 4GB RAM.

    Aviel Iluz
    Database Administrator
    Pacific Micromarketing
    Melbourne Australia
    www.pacmicro.com.au
  4. satya Moderator

    To monitor the amount of memory being used by SQL Server, examine the following performance counters:

    -Process: Working Set
    -SQL Server: Buffer Manager: Buffer Cache Hit Ratio
    -SQL Server: Buffer Manager: Total Pages
    -SQL Server: Memory Manager: Total Server Memory (KB)

    The remaining amount of memory 384mb will be used by MemToLeave area that is default values in SQL 2000. MemToLeave is calculated as:


    max worker threads value (default is 255) * Stack Size (default is 512KB) + external needs setting (default is 128MB on SQL 7.0 and 256MB on SQL Server 2000)

    I feel you're not using AWE enabled.

    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. Aviel New Member

    Thank you very much Satya.
    That all makes sense.

    The Cache hit ratio is always around 100%.
    Total pages are 208K which is about 1670MB and that is around the value of Total server memory.

    You are right, I don't use AWE because we are running Standard edition.

    Can you please refer to some sotves about those 384MB for MemToLeave.





    Aviel Iluz
    Database Administrator
    Pacific Micromarketing
    Melbourne Australia
    www.pacmicro.com.au
  6. satya Moderator

    Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers that are running in-process such as COM objects, extended stored procedures, and linked servers.

    Any other allocations that occur in MemToLeave from these other components are not included because SQL Server has no knowledge of these memory requests. Memory that is used by general memory consumers in the server, including parsing or normalization, locks, transaction context, internal data structures describing the in-memory metadata for tables and indexes, and others. This value is the total number of 8 KB buffers. Some of these may be stolen buffers from the buffer pool, and others may be from MemToLeave.

    Run DBCC MEMORYSTATUS for more information on memroy allocation/usage on that server.



    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.
  7. mccabe New Member

    A client of ours has a server with 3gb of memory and SQL Server is configured in Enterprise Manager to use 0 - 3gb.

    Are you saying it may never use more than 2gb anyway because of limitations in windows?

    I've been checking this box quite frequently lately and it has never more than ~2gb of memory used. I figured it was because SQL Server thought it had enough and didn't allocate more, but perhaps that is not the case then?

    /linus

    --
    http://anticAPSLOCK.com
  8. chopeen Member

    quote:Originally posted by mccabe

    A client of ours has a server with 3gb of memory and SQL Server is configured in Enterprise Manager to use 0 - 3gb.

    Are you saying it may never use more than 2gb anyway because of limitations in windows?
    What edition of SQL Server is your client using?

    --

    Marek 'chopeen' Grzenkowicz

    'You're so cute when you're frustrated.' -- Interpol
  9. mccabe New Member


    I realized after I posted that it was the edition of SQL Server you were refering to in previous posts. They have standard edition, and from what I've found out that means max 2gb of mem?
    The pricing difference between Standard and Enterprise is quite a chunk of money, iirc.
    For the sole reason of allowing SQL Server to use more memory, it does not seem worth the cost.

    /linus

    --
    http://anticAPSLOCK.com
  10. chopeen Member

    quote:Originally posted by mccabe

    They have standard edition, and from what I've found out that means max 2gb of mem?
    Yes.

    quote:Originally posted by mccabe

    The pricing difference between Standard and Enterprise is quite a chunk of money, iirc.
    For the sole reason of allowing SQL Server to use more memory, it does not seem worth the cost.
    You know that there are more differences, don't you?
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6335

    --

    Marek 'chopeen' Grzenkowicz

    'You're so cute when you're frustrated.' -- Interpol
  11. satya Moderator

    And you will get more benefits if you spend more, and cannot compare between the SE & EE advantages.

    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.
  12. chopeen Member

    quote:Originally posted by satya

    And you will get more benefits if you spend more...
    What do you mean exactly?

    --

    Marek 'chopeen' Grzenkowicz

    'You're so cute when you're frustrated.' -- Interpol
  13. satya Moderator

    Like if you spend $$$ on acquiring Enterprise Edition you will get most of it to utilise and for standard edition you may not need much to spend as you don't get more as compared to Enteprise Edition.

    We've 2 services at our end one uses Standard Edition and has intermittent performance issues and whereas another system with Enterprise Edition which never had blip on performance basis. (hardware is same)

    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.
  14. Luis Martin Moderator

    Back to original question:

    Same case with one customer I work.
    OS: Advanced Server.
    SQL: Standard

    Month before, 1 GByte and a lot of pages/sec.(55 GB database size).
    No more than 5-6 Gbyte to OS.

    Now, 3 Gbytes. Sql standard see 3gb in properties.
    Only use 1680 no more no less. There is no others applications, no AWE, no -g.
    Pages/sec near 0 all the time, performance of course improve.

    The question is: Satya formula, explain actual situation. But what about previus situation?




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  15. satya Moderator

  16. Luis Martin Moderator

    All works fine, much better than before. <br />About Perform, I have to wait to week end. On last friday, before memory upgrade, integrity job fail. Database was set to single user. One guy try to change that.<br />This guy, confuse single user with simple recovery model, so he change simple to full[<img src='/community/emoticons/emotion-6.gif' alt=':(' />!], when is not backup policy. On monday night call me because no one can use database, there is no space at all. At customer, after back to simple and shrink log, etc., I find one big table with allocation errors. I fix it after 5 hours.<br />So, all manteinance jobs, will run on this week end.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />
  17. satya Moderator

    Check whether any disk related issues persists, you may never know the small issues might contributing a big problem which is an indirect process.

    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.
  18. rdjabarov New Member

    Satya, I think the default value for MemToLeave is 256MB for SQL2K. I've seen several cases where increase to 384MB made a difference by starting the service with -g384 switch. Is the documentation on MemToLeave incorrect in respect to 256MB being a default for 2K?
  19. satya Moderator

    Rdjabarov (good to see you here apart from DBforums)

    For the default values mentioned, the MemToLeave area evaluates to 256 MB on SQL 7.0 and 384 MB on SQL 2000. The -g startup parameter can be used to increase the external needs setting and this must be deployed carefully and prior testing to ensure no issue persists on production system.

    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.

Share This Page