SQL Server Memory Usage Myths

Eric Blinn has a few myths regarding memory usage in SQL Server:

My VM administrator says that I’m not using all the memory I asked for.  In fact, 70% of it is idle at any given time.  We’re going to return that memory to the resource pool to better utilize it on other VMs.

The VM administrators are not lying or misinterpreting what is on their screen.  The metrics displayed on their management tool (Microsoft Hyper V Manager or VMWare vSphere Client) are lying to them.  When the VM management tool is checking on memory activity it asks the OS.  The OS only knows that this memory was allocated to SQL Server a long time ago and that it hasn’t moved since.  It appears to the OS to be stagnant and unused and this is what it reports to the VM management tool.  However, this memory, once allocated to SQL Server, is in the domain of SQLOS which is likely very actively using this memory in a way that is largely invisible to the OS, and by extension, the VM management tool and eventually the VM administrators themselves.  The VM tools are not yet smart enough to ask SQLOS what it is going on with the memory and report falsely that the memory is not being effectively utilized.

Worth reading, particularly if your sysadmins are trying to free up some of that “unused” memory.

Related Posts

Separating Data And Log Files

Brent Ozar looks at an old chestnut: So it’s time for a quiz: If you put all of a SQL Server’s data files & logs on a single volume, how many failures will that server experience per year? Bonus question: what kinds of data loss and downtime will each of those failure(s) have? If you […]

Read More

47 Incorrect Deployment Assumptions

Brent Ozar has a list of 47 assumptions regarding database deployments that turn out not always to be true: 30. The deployment person wouldn’t dream of only highlighting some of it and running it. 31. The staff who were supposed to work with you during the deployment will be available. 32. The staff, if available at […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930