Sql-server – Best Practices to reconfigure SQL Server for VMware

sql servervmware

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 2×10 config on VMware ever, but wondering if things have changed in best practices since vSphere5 days.

Best Answer

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).

Related Question