Windows Server VMWare Memory Over Subscription

VM Basics…

With Virtual Servers, they all sit on a Host server, this is a physical server. These physical servers are usually massive beasts, we are talking anywhere between 128gb ram+ with 32cores etc etc… specing out these massive servers allows you to run multiple “Guest” Servers on the host. this gives you the ability to scale out the resources easier to each guest operating system.

VMware Memory Ballooning

Well… infrastructure engineers like to get value out of their resources. and when a server is allocated 10gb ram, they assume that server isn’t going to be using that allocation constantly. so they allow the VM’s to share the resources, allowing the host to reclaim resources back when they are not used & are required elsewhere.

Okay so What Is Memory Over Subscription?

It’s the process of subscribing more memory out to your guest operating systems than you physically have on the server. Think if we had a 100gb memory server. with 10guests each with 10gb of ram allocated, we have used our 100gb. but that memory wont be used at the same time, so at host level, your infrastructure engineers might see that although 100% of the ram has been allocated, only 50% of the ram is actually being used.

Value for money

This is where infrastructure guys like to make their money back, they will see it’s safe to add a couple more Guests on the host, so our 100gb server, might go up to 12guests, with 10gb each, that’s a 120gb allocation, but it’s fine because still only 60% of the ram is actually being used right?…

How does this effect SQL Server?

SQL Server, it’s pretty selfish. it doesn’t like to give memory back, but it will, and it will trim down it’s memory allocation to the minimum working set to satisfy the request of the host. it’s got no choice in the matter after all…. and this is where you will start to see performance issues, plans will start spilling to tempdb, the working set with page to disk (if you haven’t configured LPIM) it all gets rather horrible.

So how do we avoid it?

This is completely infrastructure, avoid over subscribing resources!

Memory Grant Feedback (Intelligent Query Processing) – SQL Server 2019

Row Mode Memory Grant Feedback was a new feature introduced in SQL Server 2019, it expands on the existing Batch Mode Memory Grant Feedback which was introduced in SQL Server 2017.

Okay so what is it?

After a query executes, the query’s post-execution plan includes ideal memory grant size to have all rows fit in memory. Performance suffers when memory grant sizes are incorrectly sized. Excessive grants result in wasted memory and reduced concurrency. Insufficient memory grants cause expensive spills to disk.

With Memory Grant Feedback; Consecutive executions of a query will result in SQL Server adjusting the memory grant to better allocate it’s resources, reducing excessive grants, or preventing spills to disk.

There are now two new query plan attributes will be shown for actual post-execution plans IsMemoryGrantFeedbackAdjusted and LastRequestedMemory.

LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. Values surfaced in this attribute are as follows:

IsMemoryGrantFeedbackAdjusted Value	Description
No: First Execution	Memory grant feedback does not adjust memory for the first compile and associated execution.
No: Accurate Grant	If there is no spill to disk and the statement uses at least 50% of the granted memory, then memory grant feedback is not triggered.
No: Feedback disabled	If memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement.
Yes: Adjusting	Memory grant feedback has been applied and may be further adjusted for the next execution.
Yes: Stable	Memory grant feedback has been applied and granted memory is now stable, meaning that what was last granted for the previous execution is what was granted for the current execution.

This sounds like a great feature!

It is, for the most part. There are some things to be careful with; with parameterised query’s the the result set can change dramatically. resulting in quite large discrepancies for memory grants between executions. now as you can see “If memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement” This can cause some performance issues while the the database engine is figuring this part out, so there will be some executions where memory grants are either too large, or there are large spills to disk.

Then, you need to be careful for clearing your plan cache, once you clear your plan cache, SQL will forget about all the hard work it’s done for this parametrised query, resulting in poor performance for a short time.

Cant we just disable it and save us the trouble?

You can, but it’s my view that this feature, for the moment is giving us more help than hindrance. I haven’t seen any cases yet where disabling this option would be recommended for a server… I guess there are some edge cases, where absolutely every query you have is parametrised and your result sets are fluctuating consistently… then yes this feature wouldn’t be useful! but then you would be back to regular parameter sniffing issues.

Row mode memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. To disable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

To re-enable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

SQL Server: When to Enable Lock Pages in Memory (LPIM)

SQL Server listens to Physical Memory Notifications to trim its working set. the trimming is done by the working set manager which has its own set of rules for how and when to trim memory. If the currently available resources are insufficient to satisfy a new request for resources made by a driver or another application, the working set manager will trim the working set to satisfy the request.  Small but repeated working set trimming will eventually result in significant portion of SQL Server being paged out.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

This is where Min Memory comes in to play (sp_configure ‘min server memory‘) Windows Server will attempt to trim SQL Down to it’s minimum working set (Min Server Memory) there after it will page out to disk.

It’s important to configure both Min and Max memory correctly within SQL Server before enabling this option. setting aside enough memory for the OS and other running Processes like Antivirus, Server Monitoring Software…. Other instances of SQL!

There is an old debate among DBA’s whether this option should be enabled by default and i would say in earlier versions of windows server (2000,2003 etc.. ) that’s true. however Windows Server 2008 (and Onward) improves the contiguous memory allocation mechanism.  This improvement lets Windows Server 2008 reduce the side effects of paging out the working set of applications when new memory requests arrive. Hard Trims can still happen and you should be monitoring your SQL Server Error log for Error 17890 or the query text above.

How to enable

This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk

Locking pages in memory may boost performance when paging memory to disk is expected.

 

To enable the lock pages in memory option

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
7. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.

8. Restart the SQL Server Service for this setting to take effect.

Starting with SQL Server 2012 (11.x), trace flag 845 is not needed for Standard Edition to use Locked Pages.