0

We are getting ready to upgrade our SQL Server 2014 systems to SQL Server 2019. As part of our due diligence we created a workload which we are testing against both systems. What we have observed was the following:

  1. The query performance on average is about 50% faster on SQL Server 2019 - good!
  2. The reads are about 40% less on SQL 2019 - good!
  3. The CPU utilization is about 30% higher on average - not good. This last point is what causes our concern. Does this means that we have to plan to increase our CPU capacity as part of our migration to SQL Server 2019?

To describe what we are seeing from slightly different angle: when we attempt to ramp up our workload by pushing higher number queries/sec, we are seeing lower throughput on SQL Server 2019 because we max out CPU earlier and start seeing errors as a result.

I hope this makes sense and I wonder if the others had similar experience?

Improve this question
2
  • Can you provide more information? The exact specs of both environments - physical or virtual, # of CPUs, # of sockets, SQL Edition, config settings on both, such as maxdop, trace flags, compat levels of your test DBs, execution plans of identical queries performing worse on 2019 due to CPU. Details of the errors you're seeing are also helpful, as is more information about the type of query workloads you're generating - are you using a SQL stress tool, or simply running some comparative queries. Also, if virtual, have you isolated both environments to similar hosts to ensure performance?
    – HandyD
    Apr 14, 2021 at 0:18
  • Also, details of how you're confirming CPU is worse - do you just open Task Manager and look at the Performance tab, or are you collecting statistics from PerfMon & SQL DMVs to validate exact performance variations between the two instances. What about storage, are they both connecting to similar storage subsystems and performance tiers?
    – HandyD
    Apr 14, 2021 at 0:19

1 Answer 1

Reset to default
3

Firstly: Is the CPU use really more for the same amount of work? As performance is 50% faster 33% more work is being done on average in any given period of time, so using 30% more CPU resource cancels down to using the same amount of CPU for the same amount of work, just over a shorter period of time. The fewer page accesses could explain this: the CPU is spending less time waiting for IO to complete between bits of work.

Of course this depends on how you are measuring CPU utilisation - remember that for modern CPUs some readings can be significantly inaccurate (see https://aaron-margosis.medium.com/task-managers-cpu-numbers-are-all-but-meaningless-2d165b421e43 amongst other similar articles). We need more details about how you are making measurements to give truly helpful answers.

we are seeing lower throughput on SQL 2019 because we max out CPU earlier

Again, we need to know how you are measuring throughput. Though as above you may be maxing out the CPU earlier because less IO is needed.

Also: have you compared query plans used between the runs on the different versions? It is possible that some of the queries being submitted are getting less optimal plans under the updated engine - the differences in things like cardinality estimates are usually beneficial but as they are only guestimates can backfire.

and start seeing errors as a result

Never simply report "I got an error". What errors are you seeing?

Improve this answer
1
  • Thank you David Spillett for the answer. The article about CPU utilization is very interesting - I had no idea! Regarding the errors we are seeing - these are SQL Server connection time-outs (the system is so busy that our test tool is unable to connect and gives up after 30 sec). It looks like you nailed it with the last point though - today I took a closer look at the SQL Porfiler trace I captured on both systems (2019 vs 2014) and compared the two. I narrowed things down to a handful of queries which perform much worse on SQL 2019. I will need to dig into those to see why.
    – SQL_Guy
    Apr 15, 2021 at 2:59

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.