o
“When you start SQL Server, SQL Server
memory usage may continue to steadily increase and not decrease, even when
activity on the server is low. Additionally, the Task Manager and Performance
Monitor may show that the physical memory available on the computer is steadily
decreasing until the available memory is between 4 to 10 MB.This behavior alone
does not indicate a memory leak. This behavior is normal and is an intended
behavior of the SQL Server buffer pool.”
o
“By default, SQL Server dynamically grows
and shrinks the size of its buffer pool (cache) depending on the physical
memory load reported by the operating system. As long as enough memory is
available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool
will continue to grow. As other processes on the same computer as SQL Server
allocate memory, the SQL Server buffer manager will release memory as needed.
SQL Server can free and acquire several megabytes of memory each second,
allowing it to quickly adjust to memory allocation changes.”
o
You
can establish upper and lower limits to the amount of memory (buffer pool) used
by the SQL Server database engine with the min server memory and max server memory configuration options.
o
“Note that the max server memory option only limits the size of the SQL
Server buffer pool. The max
server memory option does not
limit a remaining unreserved memory area that SQL Server leaves for allocations
of other components such as extended stored procedures, COM objects, non-shared
DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is
normal for the SQL Server private bytes to exceed themax server memory configuration. ”
o
If
you are running 32-bit SQL Server, and need access to more than 2 GB of user
mode VAS (Virtual Address Space), then you have to use Lock
Pages in Memory; there is no debate there. You must configure the OS to use
Physical Address Extensions (PAE), enable Address Windowing Extensions (AWE),
and then assign the Lock Pages in Memory permission to the SQL
Server account so that it can allocate AWE memory, via calls to AllocateUserPhysicalPages()
(Windows API). So, in a typical 32-bit server using LPIM(Lock Pages In
Memory) you'd have, in addition to the 2 GB of pageable user mode VAS, a
separate AWE-mapped area, up to 64 GB (the PAE pointer was 36-bit), of
non-pageable memory. This AWE-mapped area is for exclusive use by the data
cache portion of the buffer pool. The rest of the buffer pool (mainly
the plan cache), and other non-buffer pool allocations are still mapped within
the 2 GB of user mode VAS.
o
However,
the advent of a 64-bit SQL Server process completely changed the dynamics of
memory allocation by the SQLOS. In place of the default 2 GB user mode VAS, for
a 32-bit process, a 64-bit process has access to up to 8 TB of user mode VAS
out-of-the-box, without any need for further configuration changes! 64-bit
users now have a potentially-vast amount of memory for the buffer pool, but all
of which is allocated via VirtualAlloc, and backed by user mode
VAS, and so is pageable i.e. in the absence of LPIM, the
memory allocated for the data and plan cache is pageable.
o
Since
the operating system still requires memory to operate, you should set the max
server memory configuration option to ensure that the operating system has
sufficient memory once SQL Server has committed its memory for use. This
is especially important on 64 bit servers where the larger VAS allows for the
SQL process to take all of the physical memory which can result in a paging
situation for the SQL processes, degrading performance. For this reason,
64 bit servers should use the Lock Pages in Memory right so that the AWE
Mechanism is used to allocate memory, preventing paging of the BPool.
No comments:
Post a Comment