0

I am using Microsoft SQL Server 2017 Web Edition in a virtualized environment hosted on Amazon EC2. Per Microsoft's Editions and supported features of SQL Server 2017 page, the "Maximum compute capacity used by a single instance - SQL Server Database Engine" is "Limited to lesser of 4 sockets or 16 cores."

Per Windows Task Manager in the virtual environment I have 4 virtual processors. But when I run a long-running, taxing SQL query the CPU utilization maxes out at 25%.

If the limitation for Web Edition is the lesser of 4 sockets or 16 cores, shouldn't I be allowed to use 100% of my virtual processors? Or am I misunderstanding how Windows or SQL Server is classifying virtual processors?

Thanks

  • 1
    Most software runs a single task (here, a query) on a single core. This lines up with the 25% usage you're seeing when you have 4 processors. I wouldn't expect to see 100% usage unless you have 4 long running queries that don't block each other. – yoonix Jul 25 '19 at 18:51
  • @yoonix - your explanation makes sense, thanks. If you'd like to post this as an answer, I'll mark it as solved. – Scott Mitchell Jul 26 '19 at 15:05
3

Per Windows Task Manager in the virtual environment I have 4 virtual processors. But when I run a long-running, taxing SQL query the CPU utilization maxes out at 25%.

Which means that even if the VM were showing as 1 core per socket (which would be bad) it would still be able to use all 4 per licensing. Thus, the query is executing a serial plan and not a parallel one.

The fact that it's a "long running query" and it takes "25% CPU" means that the query is probably not written very well or the schema design is not up to par. Either way, running it via a parallel plan (if even possible given the query) would only server to make the server have higher cpu usage than normal.

It would be the best to look at the query, schema, and supporting objects and tune it.

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.