I will probably get voted down for this, but does anyone have a good place to start other than the Best Practices Documentation for using Dynamic memory with SQL server.
We are experiencing issues and keep having to set to static for it to work. The best practices guide basically says it should all work, but I am looking for some real world issues anyone might have faced.
Dynamic memory with SQL is good for letting the memory expand as required and taking the pressure off the disk subsystem as SQL can hold more in memory. It's a bit of a swizz though in the real world as you are supposed to set the "Lock Pages In Memory" (LPIM) setting to stop the OS from taking memory back from SQL, which can be bad, so you end up creating a one-way street. You set the max server memory setting (in SQL) to be higher than the startup memory (Hyper-V setting), ideally you match it to the Max mem setting (Hyper-V) of the VM. Thus SQL starts and takes whatever you set at startup and then it grows over time to reach the maximum setting. The thing is it always does this. It's not like a safety net, having the extra headroom "just in case" SQL needs it, it will always take it eventually and then because of LPIM it won't give it back. You might as well have set the memory to static at the max memory level because that's what it's going to get anyway.
I'm still not sure of the benefit of Dynamic memory for SQL and I've read a lot of white papers and articles, everyone affiliated with MS seems to recommend it but with LPIM and my own real-world observations, I can't see the point of it. The behaviour with SQL is not "dynamic" it just takes and keeps.
SQL Server with VMware has limited support from Microsoft Support. Keeping that in Mind, the startup memory is the key and it needs to be optimized. See more on SQL Server Best Practices with Dynamic Memory in Hyper-V.
