This is my first technical post, and I intend to keep it short and simple. Later I’ll be back with some more explanations as to why I believe this recommendation is correct.
The problem: high memory usage from SQLServer when running on a virtualized environment, even when performance monitors do not indicate memory pressure.
The solution: make sure SQLServer is NOT using the “Lock pages in memory” setting.
The reason: when SQLServer service starts up, it will check if it has the rights to lock pages in memory. If the right is there, it will enable it and use it. When this happens on a physical server it’s not a problem – actually it’s a good thing. But over VMware this is bad and will make SQLServer eat all of your VM memory and never release it.
This is not exactly a bug, because it works as it is intended to work, but if you have the right kind of workload it will bring your VM to it’s knees and crash it. This is not the kind of thing you want happening on a production environment right?
I will consolidate some links and explanations concerning the reasons you should look into this on the servers you manage too. When it’s done I’ll just update this post with more references.
Cya and happy DBA’ing!
Update: below are two links of other people having trouble with this setting under VMware. I will explore the “Lock pages in memory” in more depth on a folowing post.