Optimizing SQL Server Memory Utilization in Hyper-V
You’ll realize that I do not normally do postings like this. But I came to this is through working with System Center. So before I go on, here’s a disclaimer; This post is not meant to be about SQL Server best practices on Hyper-V, but rather I just wanted to share with you guys a thing or two when running your System Center on Hyper-V. For optimizing your SQL Server with Dynamic Memory, go here!
Alright, now that’s out of the way. Almost all System Center products have some instance of SQL Server running in it. Since the birth of the Dynamics Memory feature that came with Windows Server 2008 R2 SP1 hypervisors, it took a lot of the guess work out about how much memory should I allocate to a particular VM. That sounds like a good thing, until you realize (at least myself) when you turn on Dynamic Memory on your System Center VMs (any SQL Server VM for the matter of fact) that it takes most if not all of the memory you give it.
It is then natural to think that System Center is a really memory hungry application. But the truth that lies beneath it is the SQL Server. Since SQL 2005 (and later), the product has been designed to take up as much memory it can until the OS has not much more to offer. It holds all of it and is reluctant to give it back until the OS signals to give it back for other workloads. This is the default behaviour, nothing wrong with it because a maximum limit wasn’t configured.
A simple configuration at the SQL Server Management Studio will do the trick. All you need to set is the minimum amount of memory that the SQL Server must take and the maximum amount of memory the SQL Server can take. If you’ve got your SQL Server Management Studio console already opened, right-click on your local SQL Server and then select Properties.
At the Server Properties window, go to the Memory node on the left pane.
This is where you will configure the minimum and maximum amount of memory that the SQL Server can take. The question now is, how much memory should I allocate as the minimum and maximum? Well, that depends on how much your server needs. To know this you need to find out how much memory your SQL is currently using. MSDN already has a great article about Monitoring Memory Usage. Essentially this are the counters that you have to take note:-
- Process: Working Set
- SQL Server: Buffer Manager: Buffer Cache Hit Ratio
- SQL Server: Buffer Manager: Total Pages
- SQL Server: Memory Manager: Total Server Memory (KB)
Now that you’ve done that, do not forget to restart your SQL Server for the changes to take effect. I’d just reboot 🙂 The other thing you need to do now is to configure your VM’s Dynamic Memory buffer from the default of 20% to 5%. This is so that Windows can send a “low-resource-notification” to SQL Server in order to reclaim the memory back for other workloads. Yes, you can do this while the VM is running 🙂