8

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!

  • 7
    When you license SQL on a VM, you pay for a two pack of licenses anyway. You're throwing out money by not giving it at least two cores, even if you get an educational discount. – Erik Darling May 3 '18 at 17:58
  • 4
    You could reference Glenn Barry's (10+ year Microsoft MVP) article from 2015 which lists recommended CPUs for SQL 2014, of which the smallest has six cores. – LowlyDBA May 3 '18 at 18:04
  • 2
    Also, a while back I wrote a whitepaper for Google about sizing VMs in the cloud. You can apply that to on-prem as well. – Erik Darling May 3 '18 at 18:06
  • Expanding on @sp_BlitzErik comment, the minimum core licenses that can be applied to a single SQL Server, be it VM or physical, is 4 licences (see the Licensing Guide). The alternative is to license the physical cores of the host. Under that model, you are free to run as many VMs with as many vCores as you choose. – Mark Storey-Smith May 8 '18 at 19:46
12

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.

  • Doesn't windows server have (and always had) pre-emptive multi-tasking? Unless I'm mistaking what you are describing is co-operative multi-tasking, processes yielding to each other when they've done their high level task. Sure contention might delay a response but it shouldn't bring things to a complete stop. I agree mutli-core is a good idea if not best practice, but the dooms day scenario you lay out doesn't seem correct. – Mâtt Frëëman May 4 '18 at 9:26
  • There's also sometimes a good reason to remain single core, if you were previously physical single core and the apps that talk to your single core instance broke best practices such as not specifying a sort order, and considered ordering to be implicitly stable in absence of such then switching to multi-core can show up bugs that were previously suppressed by nature of single core. If you don't have the budget to re-QA these apps or little faith in devs or maybe they've already gone, matching existing physical servers in terms of cores exposed can sometimes be a safe bet – Mâtt Frëëman May 4 '18 at 9:49
  • Hi @MâttFrëëman - I've added a section to my answer dealing with preemptive multitasking. You reminded me of how much better the NT code-base is compared to the DOS based cooperative-multitasking used by the original Windows codebase, up to and including Windows ME. – Max Vernon May 4 '18 at 15:52
  • I agree with your much of your commentary about remaining on single-core sometimes being a necessary evil. My answer presumes the physical machines that have been virtualized probably were multi-core servers, based on the desire of the OP to have multi-core VMs. – Max Vernon May 4 '18 at 15:55
  • @MaxVernon Thanks, this is exactly what we needed! – Deb May 7 '18 at 14:29
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.

  • 3
    In fact, any serious amount of I/O whether SQL Server or non-SQL-Server-related can cause a problem. – Max Vernon May 3 '18 at 19:42
  • 3
    @MaxVernon Absolutely- I just specifically brought up backups because it's a problem I've dealt with before (at 3 AM, natch), it's easy to prove, and "we can't do backups without an outage!" should be enough to spur any reasonable infrastructure manager to spare one measly core. – Neghtasro May 3 '18 at 19:46
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.

  • 5
    Of course, there won't be any parallel plans on a single-core VM. You'd need to capture those plans on a box that has more than a single core. – Max Vernon May 3 '18 at 19:44
  • 3
    To add to Max's comment, if you look in the plan XML on a server with a single core, you'll see NonParallelPlanReason="EstimatedDOPIsOne". – Erik Darling May 3 '18 at 23:34
  • @Tony I already have provided them some data but was told that a single core was a best practice with a VM machine. hence my need for help! – Deb May 4 '18 at 20:13
  • 1
    @Deb Oh my--never heard of that best practice! I can hear Brent Ozar's sad trombone all the way from here. I would be concerned about someone who makes statements like that being in charge of a VM infrastructure. – Tony Hinkle May 4 '18 at 20:28
  • @TonyHinkle - I agree. A single core VM is never a good idea, let a lone a "best practice". Ok, maybe 99.999% of the time instead of "never" :-/ – Max Vernon May 7 '18 at 15:46
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.

  • Not sure how the quote helps the case for more CPUs? – LowlyDBA May 3 '18 at 18:08
  • Yes, but is not so clear why infraestruture team didn't put more cpus per VM. – Rogerlr May 3 '18 at 18:36

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.