1

I recently upgraded my SQL Server 2014 hardware. The CPU profile is looking weird. I have 24 CPUs, all CPUs are VISIBLE ONLINE, MAXDOP is 0. The SQL CPU usage is peaking at 60% and not going beyond that although the queries are highly CPU intensive and mostly in running/runnable state. I have checked the session log dumps that the Total Request Time is only slightly above the CPU time so have ruled out io waits, etc. Also checked the OS CPU usage is tracking only slightly higher than the SQL CPU usage. Wondering why CPU is not fully utilized. Previous hardware had 16 CPUs and used to saturate at ~95%

migrated from stackoverflow.com Apr 27 '17 at 7:43

This question came from our site for professional and enthusiast programmers.

  • What edition of SQL Server are you running? There are limits on the number of CPUs/Cores for every edition other than Enterprise. See msdn.microsoft.com/en-us/library/… – Thomas Rushton Apr 27 '17 at 8:00
  • 2
    Further to my previous comment... if you're running SQL2014 Enterprise but using the old Server/CAL (Client Access Licence) method, you're limited to 20 cores. See the SQL Server 2014 Licensing Datasheet - download.microsoft.com/download/6/6/F/… – Thomas Rushton Apr 27 '17 at 8:08
  • Its SQL 2014 Enterprise - Core based License (Unlimited) – user3354850 Apr 27 '17 at 17:04
5

A few things:

Check licensing, depending on edition and how you are licensed it might be ignoring some of the extra cores.

It might be that your load can't efficiently use more cores. Using multiple cores for any statement doesn't necessarily make it faster, in fact for many it can make them slower due to the work to split the data and recombine the output, so the processing engine might simply not be seeing an advantage in using more cores right now. If your general workload is that it is running ~16 processes that it thinks won't benefit from multiple threads then it is only going to be using ~16 cores worth of CPU power.

You might be hitting an IO bottleneck that was previously masked by the CPU bottleneck.

Each of those cores may be more powerful than each of the ones in the previous chip(s), which will further reduce the likely peak % CPU use if you are hitting IO bottlenecks.

If your workload involves a lot of table/index scanning on objects that are of significant size compared to the CPU's cache (which most objects in a large DB will be) but not compared to the machine's RAM, then you might be hitting a memory bandwidth bottleneck instead of an IO bottleneck.

Finally, though perhaps most importantly: Is anything actually being too slow? It might be that your workload simply does not need more hardware power at this time.

  • Thanks David. Memory bandwidth bottleneck - is there a way to check if that is the problem. I am sure IO is not a problem or else Total-CPU time would be lower than Request-total-time for most sessions. Yes things are still slow - a lot of queries are just running and taking tens of minutes. Queries are not written in a good way but that's a different matter. I was expecting decent gains from hardware upgrade but seeing hardly any – user3354850 Apr 27 '17 at 17:17

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.