The BPool

The vast majority of SQL Server's memory allocations come from the BPool. The BPool consists of up to 32 separate memory regions organized into 8KB pages. You'll recall from Chapter 4 our discussion of VirtualAlloc and the way it can be used to reserve contiguous blocks of memory. After the server has computed the maximum size of the BPool, it reserves the MemToLeave region in an attempt to ensure that this region will be a contiguous address range. If possible, it will reserve this using a single call to VirtualAlloc. If that's not possible (highly unlikely), the server will make multiple calls to VirtualAlloc to reserve the MemToLeave region. The server then attempts to reserve the BPool region from the user mode address space. (Put aside how AWE affects this for now; we'll return to it in just a moment.) With DLLs, memory-mapped files, and other allocations already in the user mode space, it's very unlikely that the entire BPool will be able to be reserved with a single call to VirtualAlloc. Instead, the BPool will likely have to consist of several fragments spread across the user mode space. The server will call VirtualAlloc repeatedly, each time with a smaller reservation request size until it succeeds. It will do this up to 32 times in order to reserve as much of the BPool's maximum size as possible. Once this completes, the server releases the MemToLeave region so that it will be available to external consumers. Because it was reserved before the BPool reservations were made and then released afterward, the MemToLeave region normally starts out as a single, contiguous block of free virtual address space.

When AWE is involved, the algorithm is slightly different. The repetitive calls to VirtualAlloc for the user mode portion of the BPool still occur. However, Windows does not support reserving and committing AWE memory using separate operations. As we discussed in Chapter 4, when using AWE, either the memory is allocated or it isn't. AllocateUserPhysicalPages does not support the concept of reserving, but not allocating, physical memory—memory is reserved and committed in one fell swoop. Once it is, it must be mapped into a window in the user mode space so that a 32-bit pointer can access it.

So, in this scenario, all of the memory set aside for the BPool is locked into physical memory. This applies to both the user mode portion as well as the AWE portion. Because physical memory is being locked, this can cause serious performance problems for other applications running on the same machine, including other instances of SQL Server. You typically set up a SQL Server this way when it is the only significant app running on a machine.

When AWE support is enabled in SQL Server, the user account under which the server is running must have the lock pages in memory privilege. SQL Server's setup program will automatically grant this privilege to the startup account you choose for the server. If you start the server from the command line or change the startup account, you have to take care of this yourself.

Hashing

In order to make locating particular pages faster, SQL Server hashes pages within the BPool. This basically amounts to creating a hash table over the pages in the BPool such that, given the database ID, file number, and page number of a data page, the server can quickly determine whether the BPool contains the data page and where it is located if it does.

When the server needs to access a particular data page, it hashes the page's database ID, file number, and page number such that the page maps to a particular bucket within the hash table. That bucket consists of a linked list of pointers to BPool pages. It is checked to see whether the page in question is on the list. If it is, it can be quickly accessed in memory. If it is not, it must first be loaded from disk.