Problem
You have installed a new three-tier, SharePoint 2010 farm to virtual machines hosted on Microsoft Hyper-V. The farm employs SQL Server 2008 R2. After some time has elapsed, your systems admin informs you that the VM hosting the database server is using 32 GB RAM and this usage continues to climb. Traffic to the farm is steady and not increasing.
Discussion
SQL Server 2008 R2 can dynamically adjust its buffer pool (cache) size in order to minimize paging. It will continue to use memory up to that available. This is normal behavior.
By default, SQL Server 2008 R2 maximum server memory is set to 2147483647 MB. If the VM has not been configured for maximum memory, SQL Server will continue to increase its usage of virtual memory to the maximum it can.
The Maximum Server memory setting configures the buffer pool memory limit. It does not affect the amount of memory that SQL Server allocates for other processes.
Solution
References
You have installed a new three-tier, SharePoint 2010 farm to virtual machines hosted on Microsoft Hyper-V. The farm employs SQL Server 2008 R2. After some time has elapsed, your systems admin informs you that the VM hosting the database server is using 32 GB RAM and this usage continues to climb. Traffic to the farm is steady and not increasing.
Discussion
SQL Server 2008 R2 can dynamically adjust its buffer pool (cache) size in order to minimize paging. It will continue to use memory up to that available. This is normal behavior.
By default, SQL Server 2008 R2 maximum server memory is set to 2147483647 MB. If the VM has not been configured for maximum memory, SQL Server will continue to increase its usage of virtual memory to the maximum it can.
The Maximum Server memory setting configures the buffer pool memory limit. It does not affect the amount of memory that SQL Server allocates for other processes.
Solution
- Launch SQL Server Management Studio.
- In Object Explorer, right-click the server name in the tree (top item).
- Choose Properties (at bottom of list). The Server Properties dialog appears.
- In the selection pane of the dialog, select Memory.
- In the Maximum server memory (in MB) field, enter the maximum memory you want to set. Set this to less than the actual memory allocated to the VM or physical memory available. Leave two GB or so for other SQL Server and Windows processes. See the References for guidance on memory allocation. You can adjust SQL Server Maximum server memory without restarting the server.
After you make this adjustment, server memory usage will drop initially, but then gradually increase again. This second increase is due to Hyper-V locking the available memory
- Click OK.
References
- Server Memory Options
- SQL SERVER – Minimum Maximum Memory – Server Memory Options
- How to adjust memory usage by using configuration options in SQL Server
- Hyper-V: Windows Server 2008 R2 should be configured with the recommended amount of memory
- Suggested Max Memory Settings for SQL Server 2005/2008
- Minimum and Maximum Memory set in SQL Server
- Driver Locked Memory on Hyper-V Guests
- Hyper-V Dynamic Memory Overview
No comments:
Post a Comment