0

We want to set up DB server in cloud hosting for MS SQL Server Express edition - OLTP workload for "just" 6 concurent users - mostly short and small transactions.

Hosting company is charging each 1Ghz of CPU. Concerning CPU configuration, would you go 1 x 2Ghz or 2 x 1Ghz (logical cores in VM)?

Similar question is answered here: CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?

My question is a bit different / specific in a way I'm choosing between just one core with higher speed or two with lower speed. So the scale is far different.

  • 7
    I would probably try to find a less absurd cloud hosting provider. – Erik Darling Nov 14 '18 at 14:42
  • Which version of MS SQL you are getting there? if Express version then go for 1x2Ghz. – MarmiK Nov 14 '18 at 14:50
  • Yes, it's going to be Express edition. Any arguments why go 1x2Ghz? – jerik1 Nov 14 '18 at 15:00
  • 3
    Express edition will only support 1 socket, if the present the vCPU's as 2 cores for one socket express can use both, if they present is at 2 sockets with 1 core each express won't use it. – Tom V Nov 14 '18 at 15:10
  • 1
    1 2Ghz vCore should be better, as you have few users and so usually the database won't be processing multiple requests at the same time. With 2 slow cores, you'll just get slow, single-threaded queries and idle second core. And when you don have multiple concurrent requests, SQL Server will time slice between them. – David Browne - Microsoft Nov 14 '18 at 15:30
1

My question is a bit different / specific in a way I'm choosing between just one core with higher speed or two with lower speed. So the scale is far different.

Technically, more cores is always better if either the software is multithreaded, or multi-process (workers). As far as the hardware is concerned

  • More cores means more instructions are processed at the same time.

    • including instruction fetching and decoding, execution, memory access (cached, or out to bus), writes. You can read more about this on Instruction pipelining
    • you have a different pipeline for each core.
    • each core can with a full pipeline do more than one thing at a time. Modern Intel CPUs post-Sandy Bridge have a 14 stage pipeline.
  • Fewer cores, regardless of speed, mean fewer multi-stage pipelines to fill and fewer things can be done in parallel.

As far as the software side

  • Threads and processes can be managed by the kernel.
  • Most databases will run at the very least the WAL-log on another thread then the execution/query engine
  • Software that is multithreaded, will do very poorly on a single-threaded CPU.

For these reasons, the answer from the hardware and software side is clearly in favor of more cores (so long as your use case is provided for by the operating system and application as subject to Amdahl's law). Microsoft though isn't free, and brings to the table expensive licensing that needs to be factored into the total cost of ownership. This can tie your hands, but plays into the value of the product. As far as licensing requirements, check out this answer by Max.

  • 1
    While more, slower cores might be better when there are multiple concurrent requests, the penalty to single-threaded execution time is extreme when there is not. – David Browne - Microsoft Nov 14 '18 at 19:30
  • 1
    @DavidBrowne-Microsoft if there is a query that is CPU-bound that can't make use multiple cores, I agree. – Evan Carroll Nov 14 '18 at 19:37
  • 1
    In SQL Server all sufficiently-cheap queries will run single-threaded, because single-threaded queries are significantly more efficient. In OPs scenario it's unlikely that there will be queries that benefit from parallel plans. – David Browne - Microsoft Nov 14 '18 at 19:43

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.