2

My understanding:

In Sql Server,

  • Buffer Pool is where all the Data/Proc/Log Cache, etc are located.
  • Virtual Address Space is a logical location that refers to physical locations (either in RAM or page file).

But this statement is confusing to me:

"..MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool..."

What is the difference between Buffer Pool and VAS?

4
  • 1
    The VAS is the memory allocated to the SQLOS. "All SQL Server's components optimized for 8KB allocations so that they can allocate memory through SQLOS's single page allocator and consequently through Buffer Pool. However there are cases when a component requires large buffers. If it happens allocation will be either satisfied by memory node's multi page allocator or by virtual allocator. As you might guess that memory will be allocated outside of Buffer Pool." - Read blogs.msdn.com/b/slavao/archive/2005/02/11/371063.aspx
    – Spörri
    Commented May 21, 2015 at 14:30
  • 1
    @ToC An in-depth look at SQL Server Memory will help you.
    – Kin Shah
    Commented May 21, 2015 at 14:49
  • @Kin Thank you for the article. I'll definitely read it.
    – ToC
    Commented May 21, 2015 at 14:51
  • Here's an updated link to the post shared by @kin learn.microsoft.com/en-ca/archive/blogs/sqljourney/…
    – marclancy
    Commented Feb 24, 2020 at 17:01

1 Answer 1

2

No buffer pool and VAS are not the same thing. In SQL Server direct physical memory access is not allowed any memory request which comes is first mapped to process VAS and then if SQL Server find memory free it would map this VAS address to physical memory and then memory becomes committed.

Bufferpool is physical memory VAS is virtual memory.

VAS

VAS is Total amount of Virtual address space visible to process. Total VAS in windows OS is divided into VAS for OS process and VAS for application. SQL Server runs as an application. For 64 bit system total VAS is 16TB. So now both SQL sever and Windows process gets VAS of 8TB.

Whenever a new process wants to read data or write data into memory it would reference memory in its VAS region so the new process would see VAS of 8TB and then it would be mapped to physical memory. This physical meory would be your buffer pool

Buffer pool

A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory. SQL Server buffer manager manages the task of reading data pages into buffer pool and also writing it to disk. It is a reserved memory store for SQL Server and by default if you do not set value for it it will take as much memory as possible. Buffer pool only allocates memory to requests which requires less than 8 KB pages this feature changed in SQl Server 2012 where buffer pool has no meaning its just consumer. It is easy to allocate small contiguous amount of memory than large contiguous amount( Large contiguous amount might not be free or present to allocate).

32 Bit system

In 32 bit system SQL Server has VAS of 2G. If system is WOW it would have VAS of 4 G. So this becomes limitation for process which requires huge amount of continuous memory and such memory is not satisfied from buffer pool but outside buffer pool.

"..MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool..."

This statement is only true for SQL Server 2005 to 2008 R2. Page allocation >8KB is not done by buffer pool. The allocator which does page allocation for buffer pool is single page allocator while allocator which does allocation for memory pages >8KB is multi page allocator and memory to this is allocated outside of buffer pool.

For SQL Server 2012 there is Any page allocator which does allocation for both pages <=8KB and pages >=8KB

0

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.