Seems like today is a question day :-). I will attempt to answer several questions about VAS I got today:
Q1. There is one aspect of Virtual Memory that is just not clear yet. I think I understand about VAS, but maybe not as much as I could, but I don’t understand about ‘reserving memory’.
When we talk about SQL Server having a certain amount of reserved, but uncommitted, memory, are we talking about VAS?
A1: Yes, We are talking about Virtual Address Space, VAS. SQL Server also has a different, unrelated, notion of reservation. Whenever query execution engine needs buffers to run a complex query, it goes to Buffer Pool, BP, and reserves buffers from it. If there is not enough buffers in BP to serve the reservation, the query doesn’t get run until either buffers become available or time out expires. The bottom line is that VAS reservation and BP reservation are two different things.
Q2. What is the value of reserving before committing? Why not just commit at the beginning, or wait until we need to commit in order to reserve?
A2. I understand these questions are related to VAS not to BP’s reservations :-). There are several reasons why one would reserve VAS region first and commit it latter.
The first reason is that there are different ways of committing reserved VAS regions. In order to understand this, we need to understand the difference between reserving and committing. Reserving is a process of allocating VAS regions, committing is a process of binding of VAS’s regions to a physical memory. However committing is not the only way to perform the binding. Once VAS region is reserved it can be bound to physical memory by means such as committing, AWE mechanism, file mapping API’s and etc. In some cases, i.e. simple commit, OS allows to reserve VAS region and commit it in one shot using VirtualAlloc API. In other cases, i.e. AWE mapping, VAS regions have to be reserved upfront and only then they can be bound to physical memory allocated through AWE mechanism.
VAS’s limitation is another reason for reserving VAS upfront and then committing its regions latter on demand. On x86 VAS is limited resource – its size can’t exceed 4GB and in most of the case it is 2GB. Everyone requires VAS’s regions in order use memory. The high demand makes VAS valuable resource. Consequently many components, including SQL Server’s Buffer Pool, reserve VAS regions upfront and bind them to physical memory latter on demand. In most of the cases there is no reason to bind reserved VAS regions to physical memory right a way especially if only worry about VAS availability for latter use. Moreover in some cases the premature binding can introduce unnecessary physical memory pressure
Q3. Can the reserved memory and/or VAS grow or shrink the way committed memory can?
A3. Though reserved VAS regions can’t grow, they can be freed when a component no longer needs them. For example SQL Server 2005 when configured to use AWE mechanism reserves VAS regions in 4MB regions. If SQL Server notices that it runs out free VAS regions – basically it hits VAS pressure, SQL Server will attempt to free unused 4MB regions to release the pressure.
Q4. What happens if SQL Server can’t get the physical memory that it has reserved?
A4. In cases when system low on physical memory – SQL Server might fail binding giving VAS region to the physical memory. In most of the cases it will result in some kind of OOM error. It the error happens in the engine ERROLOG will have information that SQL Server failed to commit a region of the given size. In addition there is OOM ring buffer that will have a record about the failure. Keep in mind that SQL Server has internal mechanism to detect the condition up front so in normal scenario it will attempt to release its physical memory usage before it hits the failure. In most of the cases such failure means that there is either interaction problem between SQL Server, OS, and/or some other application; someone’s leaking physical memory or given configuration can’t sustain applied load.
Hope this helps. Let me know if you have more questions.