7

Recently our school district has upgraded several servers from version 2005 to 2016 and from physical servers to VM servers.

Our infrastructure team presented the VM servers for SQL Server installation with a single core.

I am attempting to explain to the head of that department that SQL Server 'hates' being on a single core and he wants to see 'proof'. None of the data that I have presented has satisfied his need for proof and so he has asked for some articles on why a VM SQL Server won't operate efficiently on a single core.

What articles to you use for this?

Any help for me making my case would be appreciated!

MDCCL
  • 8,530
  • 3
  • 32
  • 63
Deb
  • 73
  • 5

4 Answers4

11

Any non-SQL Server-process that consumes CPU may cause the entire SQL Server to be completely unusable.

As an example, Windows Update will bring SQL Server to a stop while it is unpacking .Net assemblies.

Any Anti-Virus process, even if it is configured to exclude SQL Server data files and folders may cause SQL Server to become unresponsive while it scans files and while it unpacks updated virus definition files.

If you have any management software installed, such as IBM Tivoli Server Monitor, and it decides to use 100% of one core, you probably won't be able to do anything other than cold shutdown the machine. Don't ask me why I'm bringing that up.

I could go on, but suffice it to say, any server with a single core is simply asking for trouble with performance and manageability. Check that, any computer with a single core should be considered unusable today. Heck, my phone has 8 cores.

While you're at it, convince the virtualization guy to give each SQL Server a reasonable memory reservation that will ensure SQL Server's memory doesn't get paged out of the hosts main memory to disk.

For serious VMware-hosted SQL Servers, you should configure VMware according to the Architecting SQL Server on VMware Best Practices guide.

It really boils down to a single-core VM can only ever run a single thread at any given time. Virtualization is not a silver bullet for that one-core-per-thread situation.

The above points take into account that Windows NT (the 1993 codebase which all modern versions of Windows are built on) utilizes preemptive multi-tasking. Preemptive multi-tasking allows the O/S to enforce multi-tasking by forcing context-switching at regular schedules. Even with preemptive multi-tasking, a server with a single core will still suffer from very poor performance if a single task decides to use as much CPU as possible, since the O/S will give that task as much CPU time as can be spared while still allowing higher-priority tasks to run, forcing CPU to 100%. If SQL Server is running at the time CPU goes to 100%, SQL Server's quantum of CPU will be reduced so much as to become very noticeable. In the above scenarios I outlined, I did not say, the machine (or even SQL Server) will completely "stop" or "die"; that would simply be untrue. However, the machine may become so busy that for all intents-and-purposes the machine becomes unusable. Adding a second core, while certainly not a panacea, reduces the likelihood that the machine will become completely unresponsive. Each additional core means another thread can run to 100% CPU without causing the machine to become unusable.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
6

I've had to prove this same thing before. What finally got through to our capacity planning team is that our databases were nearly unusable during a backup.

As a DBA, your biggest fear should be data loss. If you can't confidently run backups without taking or causing an outage, that's a big issue. Run some tests to make sure performance is impacted while a backup is happening, then explain that with more than one core the issue should go away.

Neghtasro
  • 482
  • 3
  • 11
2

Perhaps you should ask them why they are insisting it would be a good idea to only assign a single core per SQL Server VM.

Then, depending on their answer, you might try to convince them based on item 3.3.2 in this link:

SqlServer-on-vmware-best-pratices

VMware recommends that, for the initial sizing, the total number of vCPUs assigned to all the VMs be no more than the total number of physical cores

If that item isn't the reason for the infrastructure team to provision only 1 core per VM, then, arguably, the hardware is being wasted.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Rogerlr
  • 461
  • 4
  • 16
2

In order to provide convincing proof that your server needs more than one CPU, you will need to provide information about the workload and the queries being run on the server. If it only has one database of less than 100 MB, for example, and all of the queries are very simple and efficient, there may be almost no benefit to having more cores.

I would recommend that you start capturing query plans that go parallel and the time it takes for them to complete. This will demonstrate that the workload running on the system will benefit from having multiple CPUs.

If it's already in production in the VM environment, you also need to capture CPU usage performance data. If you can show that the CPU is maxing out at 100% for long periods of time, this would also provide good evidence that the system needs more CPUs.

Tony Hinkle
  • 8,062
  • 1
  • 24
  • 46