Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 12 '12 at 13:51

This question came from our site for professional and enthusiast programmers.

    
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....?? –  SQL Learner Jun 12 '12 at 13:15

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Jun 12 '12 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 Jul 2 '12 at 12:42
    
Multi page allocation means set of contiguous 8 KB pages -- Ref SQL 2005 book by late Ken Henderson. –  SQL Learner Jul 26 '12 at 19:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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