4

Screenshot of 2 Processors, 10 cores each on VMware SQL Server

What I want to know is why would someone configure a server this way.

3 instances of SQL Server on the machine, 64GB RAM, no affinity mask settings and MAXDOP 0 with CostThreshold 5.

I want to give better recommendations for the SQL Server and I'm still analyzing the cause of the performance issues and have collections enabled, as well as using DPA (formerly Ignite) I'll have more info tomorrow.

In my previous infrastructure architecture work, I would have never gone with a 2x10 config on VMware ever, but wondering if things have changed in best practices since vSphere5 days.

  • Does the VMWare configuration for the VM reserve both CPU and Memory for the VM? – Max Vernon Dec 29 '15 at 22:16
  • You'd likely want the virtual core and processor count to match the layout of physical hardware to enable NUMA to function optimally. For instance, if the physical hardware has 4 cores per processor, you'd not want to have more than 4 virtual cores per processor. – Max Vernon Dec 29 '15 at 22:20
  • Are they using processor affinity for the SQL Instances? Licensing this must be expensive as there are 10 cores to pay for. I would check max and min memory settings for the instances as well, make sure they aren't fighting over memory and that the OS has enough to live on. In this case, I would leave at least 6 GB of memory for the OS (and adjust depending on other services). But as @MaxVernon says, make sure the host is reserving the CPU and memory for this guest. – Jonathan Fite Dec 30 '15 at 17:07
  • Just a guess, but perhaps they combined three servers into one VM, and the original boxes had 8, 8, and 4 CPU, so they went with 20 vCPU here. – SQLRockstar Dec 30 '15 at 17:16
  • No Affinity Mask settings as mentioned, working on tuning heavy SQL Statements, but as I gather more information I'll add more. It's not 1:1 physical to guest architecture, we went 3:1 Phys to VM (VM farm though not standalone), but I'm not sure why it was spec'd this way. Hence wondering if this would be a reasonable configuration anyways. – tkrussy Dec 30 '15 at 19:47
1

What I want to know is why would someone configure a server this way.

Is there a technical reason? I highly doubt it. Is there a different reason? Likely.

  1. They didn't care what they were doing.
  2. They didn't know what they were doing.
  3. They weren't allowed to do what they were meant to do.
  4. The system is so low-use that it doesn't matter what they do.

Either way it's a little irrelevant. There are a few schools of thought on what to do next when it gets reported to you as broken.

  1. Always standardise to minimal best practices before troubleshooting further (max mem, max dop, cost threshold, trace flags, affinity, backups, index maintenance).
  2. As above but only after taking before/after baselines in a way that will let you compare whether it's working better or worse.
  3. Only change what's necessary to get it working again.

It's up to you and they're all justifiable (except maybe in banking and health care).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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