I’m running into something that I’m having a hard time believing.

A client was hitting CPU issues during load testing, and they swore all things were equal between their SQL Server 2016 and 2019 environments. The 2019 box was having CPU pressure issues that didn’t show up on the 2016 box. I’ve played this game before, and every time, the root cause has been different configurations between the two servers.

However, this time, not only were the servers the same, but I’m even seeing this same behavior with a simple query that I can reproduce on any 2016 vs 2019 setup. I haven’t tested on any other versions yet, but after a day of banging my head against the wall, I figured it was time to bring in the smart people – and that means you, dear reader.

Take any two identical servers, and I do mean identical – same CPU speeds, same power savings settings – and run this setup script. We’re creating a database in 2016 compat level just to compare the exact thing across all versions:

CREATE DATABASE TestCompat2016;GOUSE TestCompat2016;ALTER DATABASE CURRENT SET COMPATIBILITY\_LEVEL = 130;DROP TABLE IF EXISTS #Numbers;GOCREATE TABLE #Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,TestString VARCHAR(100));GOINSERT INTO #Numbers (TestString)SELECT TOP 5000000 'Hi'FROM sys.all\_columns ac1CROSS JOIN sys.all\_columns ac2CROSS JOIN sys.all\_columns ac3;GO Then turn on statistics time, and run this query:

SET STATISTICS TIME ON;GOSELECT TOP 1 UPPER(LOWER(LTRIM(RTRIM(CAST(Number AS NVARCHAR(100)))))), SUM(1) AS recsFROM #NumbersGROUP BY UPPER(LOWER(LTRIM(RTRIM(CAST(Number AS NVARCHAR(100))))))ORDER BY SUM(1) DESC, UPPER(LOWER(LTRIM(RTRIM(CAST(Number AS NVARCHAR(100))))))OPTION (MAXDOP 1);GO 3 The query’s terrible, of course, but it’s designed to do a fixed amount of CPU work every time. We’re not disk-bottlenecked – the tiny numbers table fits easily in memory. You’re going to be tempted to change the table design or query design, and you’re absolutely welcome to, but make sure the query is CPU-bottlenecked, not read-bottlenecked.

Compare the CPU time (not duration) across SQL Server versions. Because I’m paranoid, I built a brand new Windows Server 2016 box from scratch up in the cloud, and installed two instances of SQL Server on it. Left hand window is SQL Server 2016, right hand window is 2019 RTM – don’t run them at the same time, obviously, because that would screw up the CPU availability:

SQL Server 2019 uses 5-10% more CPU time to execute the same query.It’s not just single-threaded queries, either – if I let the query go parallel by removing the MAXDOP 1 hint, 2019 is still slower:

You’re also going to be tempted to say, “Just change the compat level, query, or indexes to make the whole thing go faster on 2019” – but that’s not the point, because often we can’t tune an entire running workload. (In this demo case, 2019 compat level actually works beautifully, dropping the CPU time down by about 1/3, and I wish the client’s case was that easy. They already tried that before they called me. Bummer.)

You’re also going to be tempted to say, “I bet it’s fixed in a 2019 Cumulative Update,” in which case, check out this wider screenshot. The far right window is 2019 CU19, the most current one, and it exhibits the same higher CPU usage as 2019 RTM:

You might even be tempted to say it’s the new lightweight query profiling – try turning that off:

ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT\_QUERY\_PROFILING = OFF; And at least in my tests, it makes no difference.

That’s where you come in.If you have access to absolutely identical environments (or different versions installed on the same base hardware), are you able to replicate these findings? Does the same query use more CPU time on 2019 than it did on 2016? The best evidence for this is a side-by-side screenshot of the same query’s output across the different versions.

For our own evil purposes, 2017 doesn’t really matter (because you’ve gotta get to current versions anyway), but if you want to test on, say, 2016 vs 2022, you’re welcome to. In our brief testing, we’ve seen 2022 exhibit the same CPU problems as 2019.

I wouldn’t use this case as evidence that 2019/2022 are “bad” by any means – they’re fine. It’s just helpful for folks to understand, when they’re doing capacity planning for new versions, that they may have to buy more licensing for the same server at upgrade time. In this particular client’s case, we’re probably going to have to bump from 8 cores to 10 cores in order to handle the same workloads – in their cases, the CPU difference is closer to 20%.