THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search


A blog for members of the SQL Server SQLOS team to share information and get your feedback.

What causes Multi-Page allocations?

Writing about changes in the Denali Memory Manager In his last post Rusi mentioned: " In previous SQL versions only the 8k allocations were limited by the ‘max server memory’ configuration option.  Allocations larger than 8k weren’t constrained."

In SQL Server versions before Denali single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the -g memory_to_reserve startup parameter. We'll follow up with more generic articles on the new Memory Manager structure, but in this post I want to clarify what might cause these larger allocations.

So what kinds of query result in MPA activity? I was asked this question the other day after delivering an MCM webcast on Memory Manager changes in Denali. After asking around our Dev team I was connected to one of our test leads Sangeetha who had tested the plan cache, and kindly provided this example of an MPA intensive query:

A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA.


Exec proc_name @p1, ….@p500

Exec proc_name @p1, ….@p500




Exec proc_name @p1, ….@p500



Another workload would be large adhoc batches of the form:

Select * from t where col1 in (1, 2, 3, ….500)

Select * from t where col1 in (1, 2, 3, ….500)

Select * from t where col1 in (1, 2, 3, ….500)


In Denali all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.

- Guy

Originally posted at
Published Wednesday, January 26, 2011 10:40 PM by SQLOS Team
Filed under: , , ,



yusuf said:

So, is it just the load that causes a switch from SPA to MPA, any inputs for where exactly the line is drawn ?

Using sys.dm_os_memory_nodes -> multi_pages_kb we can measure it but is it possible to find the count of allocations?

December 13, 2011 4:44 AM

mordechai danielov said:

what about CLR code, doesn't that rely on multi-page allocations?

May 24, 2012 10:41 AM said:

In his last post Rusi mentioned: HERE Last Post link is not working. please modify this.

October 4, 2012 6:59 PM

Guy Bowerman said:

October 4, 2012 10:56 PM

SQLOS Team said:

Fixed the link, thanks.

October 4, 2012 11:00 PM
New Comments to this post are disabled
Privacy Statement