Before we start to look at this from the Hyper-V perspective, let’s refresh our knowledge on SQL server and memory management.
It`s probably the biggest topic when it comes to SQL server, and you are facing it one way or another on every SQL server. I will not cover every detail here.
First of all: SQL server manages its memory resources almost completely dynamically.
The SQL server communicates constantly with the OS – to allocate enough memory.
(This one is very similar to the VSP/VMBus/VSC – communication between the enlightened VM and the Hyper-V host)
The main memory component in SQL server is the buffer pool. The memory that are not used by another memory component remains in this buffer pool to be used as a so called data cache for the pages read in from the database files on disk. One of the things that buffer manager manages, is disk I/O functions for bringing data and index pages into the data cache so data can be shared among users.
All data manipulation within SQL server occurs in memory within a set of buffers. If you are adding new data to a database, the new data is first written to a memory buffer, then written to the transaction log, and finally persisted to a data file via a background process called check pointing.
What happens when you modify or delete an existing row, is that if the row does not already exist in memory,, SQL server first reads the data off disk before making the modification. Similarly if you are reading data that has not been loaded into a memory buffer, SQL server must read it out of the data files on disk.
Memory is the most important thing when it comes to SQL servers, besides I/O.
In an ideal world - you could ensure that the machine hosting your databases had enough memory to hold all the data within your databases, SQL server could simply read all the data off disk into memory buffers when the SQL server instance started up – and give you a performance boost.
But sometimes the ideal is not always possible (I love that line) and the databases is most likely larger than memory capacity on any machine, so that SQL server retrieves data from disk only on an as-needed basis.
This brings us over to the data file design. Since accessing a disk drive is much slower than accessing memory, the design itself can have an impact on performance.
What about the Minimum server memory and the Maximum server memory in SQL Enterprise/Datacenter?
The Minimum server memory specifies that SQL server should start with at least the minimum amount of allocated memory and not release memory below this value.
The important thing here, is to set the option to a reasonable value to ensure that the OS does not request too much memory from SQL server.
The Maximum server memory specifies the maximum amount that SQL server can allocate when it starts and while it runs. If you run other applications at the same time as SQL server and want to guarantee that these applications have sufficient memory to run, this option can be set to specify the value.
So how should you configure the Dynamic Memory setting on a VM that is running SQL server?
Remember that SQL is very memory intensive, and uses memory extensively. You do not want your SQL server to use more I/O than necessary. This is very important when you have your VMs located on a SAN, where the throughput also is important. Again, it points us to the usual ‘it depends’ statement.
Personally, I would start to monitor the VM running SQL server after enabling Dynamic Memory.
If you have a lack of physical RAM installed on your host, you should at least configure the host to reserve some amount of RAM before the SQL server consumes it all.
One important thing to notice is the Memory Buffer. Since SQL server consumes memory in a large scale, you may want to adjust this setting – to a lower percentage. Dynamic Memory determines the amount of memory needed by a VM by calculating something called memory pressure. To perform this calculation, Hyper-V looks at the total committed memory of the guest operating system running in the VM and then calculates pressure as the ratio of how much memory the VM wants to how much it has. The amount of memory that Hyper-V then assigns to the VM equals total committed memory PLUS some additional memory to be used as a buffer. So if you have a buffer configured with 50% and a VM that needs 20GB RAM, Hyper-V can make up to 10GB of additional memory available to the VM for use by the file system cache. But, Dynamic Memory does not guarantee that the additional memory amount configured as a buffer value is always assigned to the virtual memory. All this depends upon the memory pressure being exerted upon the host by the memory needs of the other VMs running.
But generally speaking, the Dynamic Memory feature is fully supported by supported guest OS running SQL server, and it works pretty well.
If you have configured the memory settings within SQL server, this will limit the RAM usage of the VM – if it`s only running SQL servers. When you know your workloads, you also know which VM you should prioritize.
SQL server may be one solid candidate.
Cheers,
No comments:
Post a Comment