When SQL Server starts, it computes the size of
the virtual address space (VAS) of the SQL Server process. Each process running
on Windows has its own VAS. The set of all virtual addresses available for
process use constitutes the size of the VAS. The size of the VAS depends on the
architecture (32- or 64-bit) and the operating system. VAS is just the set of
all possible addresses; it might be much greater than the physical memory on
the machine.
A 32-bit machine can directly address only 4 GB
of memory and, by default, Windows itself reserves the top 2 GB of address
space for its own use, which leaves only 2 GB as the maximum size of the VAS
for any application, such as SQL Server. You can increase this by enabling a /3GB fl ag in the system’s Boot.ini fi le, which allows applications to
have a VAS of up to 3 GB. If your system has more than 3 GB of RAM, the only
way a 32-bit machine can get to it is by enabling AWE. One benefit of using AWE
in SQL Server 2008 is that memory pages allocated through the AWE mechanism are
considered locked pages and can never be swapped out.
On a 64-bit platform, the AWE Enabled
configuration option is present, but its setting is ignored. However, the
Windows policy option Lock Pages in Memory is available, although it is
disabled by default. This policy determines which accounts can make use of a
Windows feature to keep data in physical memory, preventing the system from
paging the data to virtual memory on disk. It is recommended that you enable
this policy on a 64-bit system.
On 32-bit operating systems, you have to enable the Lock Pages in
Memory option when using AWE. It is recommended that you don’t enable the Lock
Pages in Memory option if you are not using AWE. Although SQL Server ignores
this option when AWE is not enabled, other processes on the system may be
affected.
Note Memory management is much more straightforward on a 64-bit
machine, both for SQL Server, which has so much more VAS to work with, and for
an administrator, who doesn’t have to worry about special operating system flags
or even whether to enable AWE. Unless you are working only with very small
databases and do not expect to need more than a couple of gigabytes of RAM, you
should definitely consider running a 64-bit edition of SQL Server 2008.
In addition to the VAS size, SQL Server also calculates
a value called Target Memory, which is the number of 8-KB pages that it expects to be able
to allocate. If the configuration option Max Server Memory has been set, Target
Memory is the lesser of these two values. Target Memory is recomputed
periodically, particularly when it gets a memory notification from Windows. A
decrease in the number of target pages on a normally loaded server might
indicate a response to external physical memory pressure. You can see the
number of target pages by using the Performance Monitor—examine the Target
Server Pages counter in the SQL Server:
Memory Manager object. There is also a DMV called sys.dm_os_sys_info that
contains one row of general-purpose SQL Server configuration information,
including the following columns:
physical_memory_in_bytes The amount of physical memory available.
virtual_memory_in_bytes The amount of virtual memory available to the process in user
mode. You can use this value to determine whether SQL Server was started by
using a 3-GB switch.
bpool_commited The
total number of buffers with pages that have associated memory. This does not
include virtual memory.
bpool_commit_target The optimum number of buffers in the buffer pool.
bpool_visible The number of 8-KB buffers in the buffer pool that are
directly accessible in the process
virtual address space. When not using AWE, when the buffer pool has obtained
its memory target (bpool_committed = bpool_commit_target),
the value of bpool_visible equals the value of bpool_committed. When using AWE on a
32-bit version of SQL Server, bpool_visible represents the size of the AWE mapping window used to access physical memory
allocated by the buffer pool. The size of this
mapping window is bound by the process address space and, therefore, the
visible amount will be smaller than the
committed amount and can be reduced further by internal components
consuming memory for purposes other than database pages. If the value of bpool_visible is
too low, you might receive out-of-memory errors.
Although the VAS
is reserved, the physical memory up to the target amount is committed only when
that memory is required for the current workload that the SQL Server instance
is handling. The instance continues to acquire physical memory as needed to
support the workload, based on the users connecting and the requests being
processed. The SQL Server instance can continue to commit physical memory until
it reaches its target or the operating system indicates that there is no more
free memory. If SQL Server is notified by the operating system that there is a
shortage of free memory, it frees up memory if it has more memory than the
configured value for Min Server Memory. Note that SQL Server does not commit
memory equal to Min Server Memory initially. It commits only what it needs and
what the operating system can afford. The value for Min Server Memory comes
into play only after the buffer pool size goes above that amount, and then SQL
Server does not let memory go below that setting.
As other applications are started on a computer running an
instance of SQL Server, they consume memory, and SQL Server might need to
adjust its target memory. Normally, this should be the only situation in which
target memory is less than commit memory, and it should stay that way only
until memory can be released. The instance of SQL Server adjusts its memory
consumption, if possible. If another application is stopped and more memory becomes
available, the instance of SQL Server increases the value of its target memory,
allowing the memory allocation to grow when needed. SQL Server adjusts its
target and releases physical memory only when there is pressure to do so. Thus,
a server that is busy for a while can commit large amounts of memory that will
not necessarily be released if the system becomes quiescent.
http://www.mybasicknowledge.com/2012/09/sql-server-configurations-and.html
http://www.mybasicknowledge.com/2012/09/sql-server-configurations-and.html
