locked
Multipage allocation vs Large Pageallocation RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    May I know Multipage allocation vs Large Pageallocation are the same concept ?

    Any example of what type of Multipage allocation are?

    I check in my sys.dm_os_memory_clerks that column page_sizes_in_bytes are all 8192 .

    Tuesday, August 11, 2020 3:32 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakarui_db,

    >May I know Multipage allocation vs Large Pageallocation are the same concept ?
    No.
    Large Pageallocation (select large_page_allocations_kb from sys.dm_os_process_memory) : Specifies physical memory allocated by using large page APIs. Not nullable.
    Multipage allocation : Multi-page allocations (MPA) also come from the MTL area, before SQL Server 2012. Whether an allocation made by SQL Server is made via the MPA or single page allocator (SPA) depends purely on the size of the allocation request. If it is 8KB or less, it is allocated via SPA and stolen from the buffer pool. If it is 8KB or more, MPA is used.

    >Any example of what type of Multipage allocation are?

    First, let's look at the overall architecture changes for SQL Server 2012 memory management, as shown below：(memory-management-architecture-guide)

    When SQL 2005/2008 is used, both 8KB and greater than 0. 0 memory allocation are strictly separate, performed separately by two memory allocation managers, so there is memory on Buffer Pool side, whereas there is insufficient memory for Multi-Page Allocator. And the importance of SQL 2012.
    Change is that all memory requests will be completed via a new memory manager:
    1.The new memory manager supports allocating all sizes. Single page allocator and Multi Page allocator will exit history stage.Regardless of how much memory size is allocated, memory managers are just three different memory page models, common pages, Locked Pages.And Large Pages. (large memory pages exist only in 64 bits)
    2.New memory managers have added additional optimizations for certain specific loads such as database relational engines and report services.
    3.NUMA is fully supported by the new memory manager.
    4.Virtual address space management is entirely dynamic especially for instances of 32 bits.
    5.Now all memory allocated through memory managers is controlled by "max server memory".
    6.AWE is no longer supported by 32 instances.

    Buffer Pool is now used as a client side of a pure memory manager

    More information: 

    when-sql-server-uses-multi-page-allocations , sql-server-large-memory-pages 

    BR,
    Mia
    If the reply helped, do "Accept Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 11, 2020 8:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ok. so what's the example of Large page allocation and MPA ?

    How to check the multipage allocation from DMV ?

    Tuesday, August 11, 2020 10:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    May I know Multipage allocation vs Large Pageallocation are the same concept ?

    Any example of what type of Multipage allocation are?

    I check in my sys.dm_os_memory_clerks that column page_sizes_in_bytes are all 8192 .

    Multi Page allocation is any request which needs more than 8 KB of memory allocation. Normally SQL Server buffer pool does allocation for <=8 KB but it also does (starting from SQL Server 2012) for > 8 KB request and this is termed as Multi Page allocator.

    More about MPA( SQL Server memory architecture guide)

    Large pages as such is a feature which SQL Server uses during startup to use large page allocator function to allocate large page (32 MB, 64 MB) during startup to warm the buffer pool. It helps on machine which instantly needs lot of memory just after startup. A good read below

    SQL Server Large Pages Explained

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    Tuesday, August 11, 2020 10:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ok. so what's the example of Large page allocation and MPA ?

    How to check the multipage allocation from DMV ?

    if you have SQL Server 2005 to SQL Server 2008 r2 Query sys.dm_os_memory_clerks you have column multi_pages_kb sum it and you would get the value. But starting from SQL Server 2012 since Any page allocator came you would not be able to get the value. In this case you would have toquery dbcc memorystaus you would get like

    Memory node Id = 1                       KB

    ---------------------------------------- -----------

    VM Reserved                              552418724

    VM Committed                             2945656

    Locked Pages Allocated                   514134528

    MultiPage Allocator                      212288

    SinglePage Allocator                     1076168

    (5 row(s) affected)

    Memory node Id = 2                       KB

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    • Proposed as answer by Naomi N Tuesday, August 11, 2020 6:24 PM
    Tuesday, August 11, 2020 11:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You have asked a lot of one liner questions about SQL Server. 

    I highly suggest you get a good "SQL Server Internals" book.

    Tuesday, August 11, 2020 5:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ok. so what's the example of Large page allocation and MPA ?

    How to check the multipage allocation from DMV ?

    if you have SQL Server 2005 to SQL Server 2008 r2 Query sys.dm_os_memory_clerks you have column multi_pages_kb sum it and you would get the value. But starting from SQL Server 2012 since Any page allocator came you would not be able to get the value. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    I can't see multi-page allocation from DBCC memorystatus() from SQL Server 2017. 

    And again , please give me some example of element of MPA and Large page allocation . 


    • Edited by Shanky_621MVP Thursday, August 13, 2020 7:26 AM removed incorrect output
    Tuesday, August 11, 2020 6:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    > I can't see multi-page allocation from DBCC memorystatus() from SQL Server 2017. 

    Do you notice this:

    When SQL 2005/2008 is used, both 8KB and greater than 0. 0 memory allocation are strictly separate, performed separately by two memory allocation managers, so there is memory on Buffer Pool side, whereas there is insufficient memory for Multi-Page Allocator. And the importance of SQL 2012.
    Change is that all memory requests will be completed via a new memory manager:
    1.The new memory manager supports allocating all sizes. Single page allocator and Multi Page allocator will exit history stage. Regardless of how much memory size is allocated, memory managers are just three different memory page models, common pages, Locked Pages. And Large Pages. (large memory pages exist only in 64 bits)                             Please reference:     changes-to-memory-management-starting-with-sql2012

     >And again , please give me some example of element of MPA and Large page allocation 

    1.what-causes-multi-page-allocations

    2. To enable large memory pages, add trace flag 834 to a startup parameter for the SQL Server service through the SQL Server Configuration Manager. Restart the instance once this trace flag is added.

    Please reference :sql-server-large-memory-pages

    BR,

    Mia

    If the response helped, do "Accept Answer" and upvote it. --Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 12, 2020 8:02 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Is the reply helpful?

    BR,

    Mia

    If the reply helped, please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 13, 2020 1:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    what-causes-multi-page-allocations  <<< I can see the example right here .

    but in  :sql-server-large-memory-pages....  I can't see any example talking about what's the user/consumption for large page ...

    so for large page , how large is the page ?

    Thursday, August 13, 2020 2:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    >how large is the page?

    How large is the page? This is one uncertain value.

    Quote from this blog, and I want to share the information with you from this blog:

    First, what is a “Large Page”?. Basically, the concept is to use a larger page size for memory as organized by the kernel. This makes the process of virtual address translation typically faster. Read the section titled “Large and Small Pages” from the book Microsoft Windows Internals by Mark Russinovich and David Solomon for more information. But just to give you an example, the normal page size for Windows memory is 4Kb on x64 systems. But with large pages, the size is 2Mb.

    ...

    So in summary:

    • Large page support is enabled on Enterprise Edition systems when physical RAM is >= 8Gb (and lock pages in memory privilege set)
    • SQL Server will allocate buffer pool memory using Large Pages on 64bit systems if Large Page Support is enabled and trace flag 834 is enabled
    • Large page for the buffer pool is definitely not for everyone. You should only do this for a machine dedicated to SQL Server (and I mean dedicated) and only with careful consideration of settings like ‘‘max server memory’. Furthermore, you should test out the usage of this functionality to see if you get any measureable performance gains before using it in production.
    • SQL Server startup time can be significantly delayed when using trace flag 834.

    BR,

    Mia

    If the reply helped, please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 13, 2020 6:14 AM
  • Question
    Sign in to vote
    0
    Sign in to vote


    I can't see multi-page allocation from DBCC memorystatus() from SQL Server 2017. 

    And again , please give me some example of element of MPA and Large page allocation . 

    Sorry I guess I missed it you would not see MPA because  because starting from SQL Server 2012 Single page and multi page allocator has been merged and made into "Any Page allocator". So what you will see is any page allocator

    For large page allocation if server is using it run sp_readerrorlog and you would see large pages. Please read the blog I shared. Something like

    2009-06-04 12:21:08.16 Server      Large Page Extensions enabled.
    2009-06-04 12:21:08.16 Server      Large Page Granularity: 2097152
    2009-06-04 12:21:08.21 Server      Large Page Allocated: 32MB

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    Thursday, August 13, 2020 7:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    what-causes-multi-page-allocations  <<< I can see the example right here .

    but in  :sql-server-large-memory-pages....  I can't see any example talking about what's the user/consumption for large page ...

    so for large page , how large is the page ?

    Users dont consume large pages at startup its SQL Server which reserves it taking large chunk of free memory to warm the buffer cache. Now this memory is nothing special, once warmed this memory will be part of buffer pool. There is NO example to see if some user page consumed the large page allocated because after warm up this becomes part of BP as stated above

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    Thursday, August 13, 2020 7:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Is the reply helpful?

    BR,

    Mia

    If the reply helped, please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 14, 2020 1:02 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Is the reply helpful?

    BR,

    Mia

    If the reply helped, please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 1:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    what-causes-multi-page-allocations  <<< I can see the example right here .

    but in  :sql-server-large-memory-pages....  I can't see any example talking about what's the user/consumption for large page ...

    so for large page , how large is the page ?

    Users dont consume large pages at startup its SQL Server which reserves it taking large chunk of free memory to warm the buffer cache. Now this memory is nothing special, once warmed this memory will be part of buffer pool. There is NO example to see if some user page consumed the large page allocated because after warm up this becomes part of BP as stated above

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    if we don't know what's are the consumer/operation use for large page memory , how can we know if enable this option will benefit for the sql server workload ??????????
    Tuesday, August 25, 2020 3:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    anyone has the idea ?
    Friday, August 28, 2020 8:39 AM