Freigeben über

Q and A: Virtual Address Space, VAS, reservation - why bother?

  • Artikel

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.

Comments

  • Anonymous
    December 15, 2005
    How does SQL 2005 calculate the vlaue for the target server memory counter? Is there a relation between this value and the VAS reservation at startup?

    Thanks.
  • Anonymous
    February 01, 2006
    The comment has been removed
  • Anonymous
    February 27, 2006
    Thanks. This is great information.

    I have a test environment with 16GB RAM on a server installed with SQL 2005 EE (32-bit) - AWE enabled. I left min and max server memory options to default. I have been consistently applying increasing loads to the order of 465 transactions/sec. The server still leaves about 1994MB (~1.9GB) unused as indicated by the OS available Mbytes counter. The total and target server counters are the same and are around 13.5GB.

    I read from http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx about the dynamic mem mgmt of SQL 2005. But since i have left the max server memory to be default, why is buffer manager not using the rest of available memory as indicated by the OS counter.

    I see the same behavior with x64 edition. I have not observed this with SQL 2000 EE, which just leaves around 256MB.
  • Anonymous
    March 05, 2006
    The comment has been removed