Sql-server – When SQL Server uses Multi Page Allocations

sql serversql-server-2008-r2

What are the consumers of MPA (multi page allocations) in SQL Server?

I know data pages are always 8K. Is there a situation where data/index page make use of MPA? It makes sense that execution plans can use MPA as they can exceed 8 KB.

There is a blog here that suggests use of MPA but it refers to stored procedures (one with 500 parameters). In the attached screenshot I see an execution plan using around 11 MB; does this use MPA? Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

enter image description here

My confusion is what actually uses MPA (multi page allocator). The example in the link I posted shows a complex execution plan which will require contiguous allocation over 8 KB.

An answer below suggests that there are many things like linked server or extended stored procedures that can use MPA. Which for some reason I am not able to agree. For example Extended Stored procedures can be C++ code using HeapAlloc which SQL server has little control over to manage its memory (it has to be at Windows OS level).

Extended stored procedures even in SQL 2012 still consumes memory outside buffer pool so it has to nothing to do with multi page allocation. The same applies to Linked servers especially if you use third party providers (e.g. ORACLE).

Best Answer

In SQL Server 2008 R2 and earlier, the following allocations come from the MemToLeave (MTL) area:

  • Linked servers
  • XML documents
  • Extended stored procedures
  • sp_OACreate calls
  • Query plans that exceed 8KB
  • SQLCLR
  • Backups that use a large value for MAXTRANSFERSIZE
  • Using a network packet size over 8192 bytes

Information extracted from this article by Microsoft support engineer Amit Banerjee.

SQL Server 2012 uses the Any Size Page Allocator.

Further information is included in this Microsoft Knowledge Base article.

KB extract

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.

I know data page is always 8K so is there a situation where data/index page make use of MPA?

No, data and index pages are always cached in the buffer pool.

Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

Cached plans have a complex structure. Individual allocations within that structure are generally 8KB allocations, but certain queries (generally those that contain long IN lists or many parameters to a stored procedure call) can require an MPA allocation. These are comparatively rare. It is possible to query SQL Server DMVs to determine the split between SPA and MPA for a cached plan.

Related Question