3

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.

Improve this question
4
  • 3
    You're asking for a world or hurt if you're oversubscribing memory on a hyper-v host running SQL Server workloads. No reason to down vote, just want to make sure you know what you're getting into.
    – Sean Gallardy
    Commented Apr 14, 2015 at 15:02
  • Sorry for my slow response - we have decided to build two VMs and set one to static and one to dynamic and replay a SQL workload against them an monitor their performance. We will also follow the other settings in the best practice guide around min\max and startup memory and monitor the perf counters and compare. - comments welcome.
    – Tom
    Commented Apr 16, 2015 at 7:51
  • Tom - That methodology will only work if that's how the server is going to stay. If you'll eventually load it up with a bunch of dynamic guests that's what you'll need to test. When there is no memory pressure from other sources it's not going to have any trouble keeping up......
    – Sean Gallardy
    Commented Apr 16, 2015 at 15:10
  • Hi Sean - yes agreed I think we are going to load up the two VMs on an isolated VHost just to track their performance, then start migrating other VMs on to it and see what happens.
    – Tom
    Commented Apr 17, 2015 at 18:24

2 Answers 2

Reset to default
4

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.

Improve this answer
3
  • The VMWare best practices guide for SQL Server is pretty clear about dynamic allocation. I do not think that there are any reason that HyperV does this much better than VMWare or Xen or any other hypervisor for the reasons @slinkoff mentions: 4.3.4.2. Tier 1 SQL Server workloads Achieving adequate performance is the primary goal. Consider setting the memory reservation equal to the provisioned memory, to avoid ballooning or swapping. vmware.com/files/pdf/solutions/…
    – Spörri
    Commented Apr 21, 2015 at 10:04
  • I don't believe your remark about LPIM is correct in regards to the hypervisor grabbing back memory. My understanding is that where as previously the hypervisor would grab memory back from processes not in the buffer pool first, now that in SQL 2012 most things are in the buffer pool SQL handles the paging out to disk in a controlled manner in response to low memory events. i.e. it still pages out things from the buffer pool even with LPIM just in an optimised way now.
    – Tom
    Commented Apr 21, 2015 at 19:26
  • yes you're correct but low memory events shouldn't be happening unless there are pressures on the VM from other processes (shouldn't be running anything other than SQL on the VM) or memory pressure on the host (shouldn't be running that tight and mem priority should be given to SQL over other VMs anyway). Upshot is the memory stays once SQL grows into it which it inevitably will. You can use dynamic with LPIM but don't expect it to move up AND down as SQL doesn't work like that. It's just going to move up so either expect that or use dynamic to find the sweetspot and set that level to static
    – slinkoff
    Commented Apr 22, 2015 at 9:34
-4

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.

Improve this answer
1
  • 3
    "does anyone have a good place to start other than the Best Practices Documentation for using Dynamic memory with SQL server." ....
    – Philᵀᴹ
    Commented Nov 4, 2016 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.