When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer pool. This behavior is changed in SQL 2012 and MPA and SPA are part of buffer pool. But I am using SQL 2008 R2 standard edition.

Does multi page allocation means two or more 8K page will be allocated at once or page larger than 8K in size will be allocated to save execution plan for the query?

  • Martin, info from here got me confused...from this link sqlservercentral.com/articles/Memory/74867 The Multipage allocator stores objects requested by any SQL Server component that are larger than one 8k page. This means any memory request made by any of the SQL Server components that is larger than one 8k page.... I am seeking confirmation....?? Commented Jun 12, 2012 at 13:15

1 Answer 1


In this context, multi-page allocations means SQL Server needs more than 8K of memory, and it will allocate multiple 8K pages to hold that data.

  • The OP's question made me wonder - is it ever possible to have a page of a larger size than 8k? I'm assuming not but am curious.
    – JNK
    Commented Jun 12, 2012 at 16:57
  • Not exactly. There's a concept of large pages, but it's at the OS and hypervisor layer, not SQL Server.
    – Brent Ozar
    Commented Jul 2, 2012 at 12:42
  • Multi page allocation means set of contiguous 8 KB pages -- Ref SQL 2005 book by late Ken Henderson. Commented Jul 26, 2012 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.