SQL Server Performance

MemToLeave area of memory

Discussion in 'Performance Tuning for DBAs' started by gaurav_bindlish, Jul 29, 2003.

  1. gaurav_bindlish New Member

    This is a part of the transcript from Chat on Performance Tuning of SQL Server
    quote:BillC_MS:

    Q: WaldenL: We're in an ASP (application service provider not Application Server Pages) environment where each customer has their own database. Each of these databases are identical in structure and design (tables, views, stored procs, etc.) are their either any "gotchas" to look out for, or any specific enhancements we can make in this environment over the normal tuning enhancements?

    A: If your application has one database per customer be careful with your MemToLeave area of memory. For example if you get over 700 databases. Add the -g switch. For each database the MemToLeave needs 64K to manage the transaction log.

    Can anybody guide me as to what is MemToLeave area of memory, its performance implication and how do we configure it?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  2. gaurav_bindlish New Member

    Actually I found some answers in that script only but somehow not able to understand whole of it...

    - SQL Server memory is broken up into two parts, The BPool, which is what is dynamic and what BooksOnline talks about. There is also a MemToLeave area that for SQL 2K is set at 265MB. The -g switch will increase the MemToLeave area. See SP3 readme for more information. The MemToLeave is used for memory allocations over 8K.
    - If a memory allocation is over 8K it will be from the MemToLeave area. Typically the MemToLeave area is used for things like extended stored procedures, OLEDB (linked server), sp_oa* create, PSS structure for logins, transaction log, etc.
    - On SQL Server 2000 the MemToLeave area is set at 256MB. You can use the startup parameter -g to increase at startup. So you can add the -g384 for example. See the service pack readme and search on -g. Usually, we see memory error if there are MemToLeave pressure.


    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. satya Moderator

    From my library where this was explained by one of the MS expert
    There are two main areas of memory within SQL Server's address space, the buffer pool (BPool) and a second memory pool sometimes called the "MemToLeave" area.

    The significant memory area is sometimes called MemToLeave, and it is primarily used by non-SQL Server code that happens to be executing within the SQL Server process. The MemToLeave area is memory that is left unallocated and unreserved, primarily for code that is not part of the core SQL Server and therefore does not know how to access memory in the SQL Server buffer pool.

    Some examples of components that may use this memory include extended stored procedures, OLE Automation/COM objects, linked server OLEDB providers and ODBC drivers, MAPI components used by SQLMail, and thread stacks (one-half MB per thread). This does not just include the .EXE and .DLL binary images for these components; any memory allocated at runtime by the components listed above will also be allocated from the MemToLeave area.

    Non-SQL Server code makes its memory allocation requests directly from the OS, not from the SQL Server buffer pool. The entire SQL Server buffer pool is reserved at server startup, so any requests for memory made directly from the operating system must be satisfied from the MemToLeave area, which is the only source of unreserved memory in the SQL Server address space.

    SQL Server itself also uses the MemToLeave memory area for certain allocations; for example, SQL Server 7.0 stores procedure plans in the MemToLeave area if they are too large for a single 8KB buffer pool page.

    A representation of sql in 1GB physical, 2GB virtual

    -----2gb
    Space for mem to leave
    -----1GB
    Space for sql
    -----0
    This is because the buffer pool will not exceed the amount of physical memory.

    A representation of SQL Server 7 in 2GB physical and 2 GB virtual and max memory maxed at 1.6GB
    -----2GB
    Space for mem to leave 128MB
    -----1.9GB
    Space for SQL and space for mem to leave
    -----1.6 approx
    Space for sql
    -----0

    Obviously if the memory used in the main part of the memtoleave area is not released opportunely then this too can become fragmented.

    The -g option is used to specify more than the default 128MB for SQL Server. Investigation of the memtoleave area is quite complicated because the way that the memory is allocated is in general via NT and not via SQL Server.

    HTH


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. gaurav_bindlish New Member

    Thank you very much Satya.

    Is there any reason to change this setting?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. satya Moderator

    For more detailed information please reference the Books Online articles "Server Memory Options", "Memory Architecture", and (SQL Server 2000 only) "Effects of min and max server memory".


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. Luis Martin Moderator

    Someone can tell me what "gotchas" means?

    Thanks.

    Luis
  7. satya Moderator

    A misfeature of a system, especially a programming language or environment, that tends to breed bugs or mistakes because it both tempting easy to invoke and completely unexpected and/or unreasonable in its outcome.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. Luis Martin Moderator

    Thank you very much, Satya.

    Luis

Share This Page