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.
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Thursday, 15 December 2011
Wednesday, 3 August 2011
SQL Server and Dynamic Memory
Since our move to Hyper-V and the addition of Service Pack 1 for Windows Server 2008r2, the addition of Dynamic memory has helped us vastly manage the resources within our virtual servers much better. For those who don't know, Dynamic Memory is where the memory utilised in virtual environments can be changed dynamically while the virtual machine is running. For this to work the OS needs to be aware and it is something that SP1 does nicely for us.
I came across something odd a few weeks ago after deploying a new virtual machine running SQL Server 2008 Standard Edition. The server had plenty of resources as the OS was able to utilise much more memory as and when it was needed. The problem was that the SQL Server would fall over without notice. After reading through the logs, it soon became apparent that the SQL Server would continuously keep running out of memory. The virtual machine has its settings set to a minimum of 512mb of memory, which will balloon when it is required. The problem is that the standard edition of SQL Server uses a fixed amount of memory from the OS when it's running and it's not able to ask for more resources. The only editions that can utilise Hot Memory are the Enterprise and Datacenter editions.
So the moral of the story is that the next time you install your SQL Server and you are running on the Hyper-V platform, it always almost certainly will benefit you to install the Enterprise edition so that you don't need to set your virtual machine to use a much higher fixed memory that the other virtual machines running within that pool can't use.
I came across something odd a few weeks ago after deploying a new virtual machine running SQL Server 2008 Standard Edition. The server had plenty of resources as the OS was able to utilise much more memory as and when it was needed. The problem was that the SQL Server would fall over without notice. After reading through the logs, it soon became apparent that the SQL Server would continuously keep running out of memory. The virtual machine has its settings set to a minimum of 512mb of memory, which will balloon when it is required. The problem is that the standard edition of SQL Server uses a fixed amount of memory from the OS when it's running and it's not able to ask for more resources. The only editions that can utilise Hot Memory are the Enterprise and Datacenter editions.
So the moral of the story is that the next time you install your SQL Server and you are running on the Hyper-V platform, it always almost certainly will benefit you to install the Enterprise edition so that you don't need to set your virtual machine to use a much higher fixed memory that the other virtual machines running within that pool can't use.
Subscribe to:
Posts (Atom)