SQL Server manages
memory for databases, including memory requirements for data and query
execution plans, in a large pool of memory called the buffer pool. The
memory pool used to consist of a collection of 8KB buffers to manage memory.
Now there are multiple page allocations for data pages and plan cache pages,
free pages, and so forth. The buffer pool is usually the largest portion of SQL
Server memory. SQL Server manages memory by growing or shrinking its memory
pool size dynamically. You can configure SQL Server for dynamic memory
management in SQL Server Management Studio (SSMS). Go to the Memory folder of
the Server Properties dialog box.
Microsoft recommends
that you use dynamic memory configuration for SQL Server, where min server memory
is 0 and max server memory is set to allow some memory for the
operating system, assuming a single instance on the machine. The amount of
memory for the operating system depends on the system itself. For most systems
with 8-16GB of memory, you should leave about 2GB for the OS. Note that the
default value for the min server memory setting is 0MB and for the max
server memory setting is 2147483647MB. Also, max server memory can’t
be set to less than 64MB on a 32-bit machine and 128MB on a 64-bit machine.
No comments:
Post a Comment