Thursday, 15 December 2011

Manage Memory Limits SQL Server Enterprise 2008

Currently on our network is a server that's running SQL Server Enterprise 2008 running on our Hyper-V cluster.  On the cluster, dynamic memory is enabled.  Now, normally this isn't a problem, but when configured incorrectly SQL Server Enterprise will eat up as much data as it needs and so you suddenly have a server (low usage) that is eating up 14gb of memory.

The solution is to go to SQL Server Management Studio and right click on the server in the task pane on the left and go to properties.  Under memory on the left is an option to set max server memory in megabytes.  By default this is set to 2147483647mb (maaaaaaaassive).  The minimum you can set this to is 4mb but that would be silly now and so after a bit of a debate we decided that 6144mb (6gb) would be a nice number.  It now means we can continue to use dynamic memory on the VM without having to set static amounts of memory for the server itself.

No comments:

Post a Comment