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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE DATABASE TestCompat2016; GO USE TestCompat2016; ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130; DROP TABLE IF EXISTS #Numbers; GO CREATE TABLE #Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, TestString VARCHAR(100)); GO INSERT INTO #Numbers (TestString) SELECT TOP 5000000 'Hi' FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 CROSS JOIN sys.all_columns ac3; GO |
Then turn on statistics time, and run this query:
|
1 2 3 4 5 6 7 8 |
SET STATISTICS TIME ON; GO SELECT TOP 1 UPPER(LOWER(LTRIM(RTRIM(CAST(Number AS NVARCHAR(100)))))), SUM(1) AS recs FROM #Numbers GROUP 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:
|
1 2 |
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%.
72 Comments. Leave new
Hi Brent,
I ran your script on my laptop. The results:
SQL Server 2016 (13.0.6419.1): 4031ms CPU time average
SQL Server 2019 CU16: 4271ms CPU time average
Here is a side by side results screenshot: https://justpaste.it/c94su
All with compatibility level 130. I also did an extra test: I enabled QUERY_OPTIMIZER_HOTFIXES on my SQL 2019 database (still compat level 130). The result is even worse: 5065ms CPU time average.
Thanks for the confirmation! I appreciate it.
Brent, can you share some info regarding the config of the server? Windows Version, CPU (cores/amount of CPUs), ram config, something like that
Between 2016 and 2019, AMD launched its EPYC platform, so there’s a chance between Windows or SQL Server versions, someone included some optimizations for the new platform and those aren’t playing well between Windows & SQL versions, something like that (Windows Server 2016 + SQL Server 2016 vs Windows Server 2016 + SQL Server 2019, for example)
I can try to install this afternoon SQL 2016 & SQL 2019 on my PC (Windows 11 + AMD 5800X3D + 32GB ram), and I can try to log some additional performance details (clocks speeds, temps, etc, everything that could affect the CPUs performance)
The point isn’t for you to replicate the test on *my* hardware.
The point is for you to replicate the test in *your* environment.
For Windows versions, please read the post carefully. I do want help, but I need you to respect my time as well. Thanks for understanding.
Sorry Brent, didn’t want to sound disrespectful… I was reading on the fly between some meetings and skipped/didn’t see at first the parts that answered my questions: Cloud VM + Windows Server 2016 + SQL Server 2016 & SQL Server 2019 + 8 Core CPU
Strange, my results don’t really correlate. With just three executions they were all over the place and pretty inconsistent, so I tried a batch of ten executions. On my multi-instance D4ads v5 (4 vcpus, 16 GiB memory), with a warm buffer pool and procedure cache, here’s what I ended up with:
Ten executions:
2016 RTM: 5578-5890 ms
2016 SP3: 5953-6359 ms
2019 CU17: 4844-5156 ms
Last three of those executions:
2016 RTM: 5578-5641 ms
2016 SP3: 5953-6078 ms
2019 CU17: 4860-4922 ms
Only the instance I was querying was running at any given point, there’s no memory pressure. All of the instances are capped at 10 GB of memory. Query plans look exactly the same, including cardinality and memory grants.
Thanks sir! These results are much more along the lines of what I was expecting.
Havent checkd perf that close between versions as almost everything here is 2019 STD CU19 now
but one thing is a Given on ALL my 2019 boxen:
17883 – Non Yielding Worker Process
especially fun are the bare, fresh installs with nothing on them that throw that error too
But to Follow up
SQL 2016 SP3 = 6625
SQL 2019 CU19 = 7738
SQL 2022 CU2 = 7618
Thanks for the followup! Yeah, that confirms it as well. Interesting!
Man every time I get close to feeling comfortable with upgrading to 2019 I see something like this or a new bug haha. Would test regardless but due to the way my organization’s business cycles work if we end up having to discard a period of development and testing, the time loss is a large percentage of our available productivity for the year.
I don’t have access to two identical servers, but before upgrading to SQL 2019, one thing that I did notice (in our case we were also upgrading from Windows Server 2012 to Windows Server 2019), was that the server with Windows Server 2019 took more CPU than Windows Server 2012 (even if both were in SQL 2016).
In our case, since the CPU of the server was not a problem, we upgraded to SQL 2019 (and Windows Server 2019) and don’t have CPU issues. (Running 12 production servers with about 80 to 100 databases on each, an average of 4k queries per second on each servers.)
Hi Brent,
I’ve set up a VM with my usual config and installed all 3 instances on it (2016, 2019, and 2022).
When testing on one instance the other two are stopped.
I’ve done two test runs per instance, one with the original OPTION (MAXDOP 1) and one with it removed.
VM Specs:
CPU – 8 Cores
RAM – 16GB
OS – Windows Server 2022 V21H2 build 20348.587
General SQL Server config (applies to all 3 instances):
Max memory – 10GB
MAXDOP – 8
CTP – 60
TempDB – 8x1024MB data files
Results:
SQL Server 2016 (SP3) (KB5003279) – 13.0.6300.2 (X64)
MAXDOP 1
CPU time = 7234 ms, elapsed time = 7272 ms.
CPU time = 7094 ms, elapsed time = 7103 ms.
CPU time = 7266 ms, elapsed time = 7264 ms.
MAXDOP 8 (instance default)
CPU time = 7225 ms, elapsed time = 1211 ms.
CPU time = 6393 ms, elapsed time = 1012 ms.
CPU time = 6282 ms, elapsed time = 1021 ms.
SQL Server 2019 (RTM-CU19) (KB5023049) – 15.0.4298.1 (X64)
MAXDOP 1
CPU time = 7688 ms, elapsed time = 7831 ms.
CPU time = 7687 ms, elapsed time = 7694 ms.
CPU time = 7735 ms, elapsed time = 7735 ms.
MAXDOP 8 (instance default)
CPU time = 7322 ms, elapsed time = 1254 ms.
CPU time = 6218 ms, elapsed time = 1074 ms.
CPU time = 6531 ms, elapsed time = 1105 ms.
SQL Server 2022 (RTM-CU2) (KB5023127) – 16.0.4015.1 (X64)
MAXDOP 1
CPU time = 7672 ms, elapsed time = 7829 ms.
CPU time = 7641 ms, elapsed time = 7663 ms.
CPU time = 7656 ms, elapsed time = 7651 ms.
MAXDOP 8 (instance default)
CPU time = 6370 ms, elapsed time = 1304 ms.
CPU time = 6348 ms, elapsed time = 1113 ms.
CPU time = 6903 ms, elapsed time = 1112 ms.
Out of curiosity I’ve ran a test on SQL Server 2019 for Linux and it looks like the CPU time is a bit higher on Linux vs its Windows counterpart.
VM Specs:
CPU – 8 Cores
RAM – 16GB
OS – Linux (Ubuntu 20.04.6 LTS)
Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) – 15.0.4298.1 (X64)
MAXDOP 1
CPU time = 7993 ms, elapsed time = 8137 ms
CPU time = 8019 ms, elapsed time = 8034 ms.
CPU time = 7864 ms, elapsed time = 7877 ms.
MAXDOP 8
CPU time = 7564 ms, elapsed time = 1109 ms.
CPU time = 7512 ms, elapsed time = 1112 ms.
CPU time = 7699 ms, elapsed time = 1145 ms.
Power option setting?
Reread the post. It’s one Windows Server, not multiple.
Very interesting post; my testing results seem to confirm what you observed: https://imgur.com/a/wkPGkRU
Interestingly in most — though not all — of my tests even the INSERT INTO #Numbers statement was taking more CPU in the SQL 2019 instance than in the SQL 2016 one.
Env: Windows 2019 Datacenter 4x 2.40GHz CPU, 32GB RAM w/ SQL 2016 and SQL 2019 instances installed on it.
Thanks for chiming in!
Possibly the extra CPU cycles are being consumed by the work / checks SQL has to do to run the code at down-level versions?
Using your sample query (the maxdop 1 version):
– SQL 2016 (110 compatibility mode) uses ~7% more CPU time than direct SQL 2012 does
– SQL 2019 (130 compatibility mode) uses ~6% more CPU time than direct SQL 2016 does
– SQL 2022 (150 compatibility mode) uses ~4.5% more CPU time than direct SQL 2019 does
https://imgur.com/a/EWj2Jys
That even SQL 2016 suffers the observed 5-10% hit when forced to pretend it’s SQL 2012 (sorry, didn’t have the install media for SQL 2014) makes me think that this has been around for a while rather than being something just introduced by new enhancements to the engine.
Korritt – the problem there is that newer compat levels have *different* plans. For example, in this query’s case, the newer compat levels can introduce batch mode on rowstore.
My apologies if I wandered too far off the point of the post, but happydba’s findings got me wondering if changes between SQL 2016 and SQL 2017 (eg: PAL, etc) were a tipping point.
My test was for a selected SQL version to see if subsequent versions held at the selected version’s compatibility level showed the same CPU hit that the original SQL 2016 vs SQL 2019 (130 compat) test did.
In my case, the query plans generated for a given compatibility level were the same, regardless of the version of SQL running the query:
110 compatibility: https://imgur.com/a/TnTqoTr
130 compatibility: https://imgur.com/a/1OdkDvy
150 compatibility: https://imgur.com/a/J3LUxpV
The 150 compatibility plans are different from the 110 / 130 compatibility plans, but they are not different from each other yet SQL 2022 took ~4.5% more CPU time trying to pretend to be SQL 2019 than SQL 2019 natively took for the same query.
Looking to older versions of SQL, the 110 compatibility plans are all the same shape (though SQL 2012 / 2016 / 2019 had a spill to tempdb that SQL 2022 did not), but the higher versions all took more CPU time running the same plan than SQL 2012 natively did.
Unfortunately this doesn’t help anyone’s CPU usage if, like your client, they have to stay at a particular compatibility level, but it may indicate that the “higher CPU time when in compatibility mode” issue is not new in SQL 2017 / 2019.
Hello there, I’ll be very short.
both environments are identic from resource perspective:
/*
2016 latest cu
CM – 130
1. CPU time = 5985 ms, elapsed time = 6103 ms.
2. CPU time = 6188 ms, elapsed time = 6638 ms.
3. CPU time = 6406 ms, elapsed time = 6525 ms.
*/
/*
2019 cu-18
CM – 150
1. CPU time = 3844 ms, elapsed time = 4162 ms.
2. CPU time = 3890 ms, elapsed time = 3899 ms.
3. CPU time = 3953 ms, elapsed time = 3955 ms.
round 2
1. CPU time = 4187 ms, elapsed time = 4240 ms.
2. CPU time = 4172 ms, elapsed time = 4845 ms.
3. CPU time = 4078 ms, elapsed time = 4075 ms
*/
The different compatibility levels make the test irrelevant because it changes the plans. Stay in the same compat level.
For my OWN evil purposes, I ran the test on 2016, 2017, and 2019.
I was suspicious that maybe the PAL layer MS added in 2017 to accommodate Linux was bloating the product or something.
My results – I did10 runs as well like Daniel above.
Below are the average CPU times for the 10 runs:
2016 – 3170.3ms
2017 – 3268.8ms
2019 – 3384.3ms
Looks like 2017 was affected by the slowdown too! Interesting…
I updated all my instance to the latest CUs as of today (I don’t know what exactly they were before), thinking maybe it would bring them all more inline with each other, but it only made 2017 worse, closer inline with 2019.
10 runs again:
2016 – 3237.5ms
2017 – 3387.5ms
2019 – 3423.5ms
Curiously, I tried a VARCHAR version of the code instead of NVARCHAR like Thomas Kejser suggested, and 2017 seems better with that, with only 2019 being worse than 2016:
2016 – 3240.7ms
2017 – 3292.2ms
2019 – 3379.7ms
Hi Happy
This lends evidence to my suspicion.
How many times did you measure on the VARCHAR runs another 10 runs? With only ~90ms of difference we are looking at a very small difference. I highly suspect you are looking at a refresh of some internal varchar libraries. If this theory is correct, you should be able to exaggerated the difference by adding more upper/lower/left/right/ and observe a more solid difference.
Given what I happen to know about string libraries (from some other code I wrote) I would not be surprised if you can reproduce this problem purely with an ORDER BY of a set of strings (as long as the query does not spend too much time doing other stuff that drowns out the difference).
Something like this:
SELECT TOP 1 Number
FROM #Numbers
ORDER BY UPPER(LOWER(LTRIM(RTRIM(CAST(Number AS NVARCHAR(100))))))
OPTION (MAXDOP 1);
It could also be in the hashing of strings, in which case the difference should be larger if you only do the GROUP BY part of the query
PS: http://www.database-doctor.com
Hey hey hey, you’re writing again! Hot dog, subscribed. Great to see you back, sir!
CPU time also includes compile time, and I would not be too concerned about additional 2019 compile time overhead on queries that efficiently use the plan cache for subsequent executions – if that’s ultimately what proves to be going on.
It’s possible for a query to be “pennywise but pound foolish”, so when I have time I’ll use Adam Mechanic’s SQL Stress Tool to see how well 2016 vs 2019 can handle a load of something like 25 parallel threads and 100 repetitions. That’s what really matters in production.
Eric – no, CPU time does not include parse and compile time. Look more closely at my screenshots and you’ll see that SQL Server breaks out parse and compile time separately first.
Sounds like it’s time for somebody to attend my query Tuning classes. WINK WINK
Having looked at y’all’s results, I would hazard the guess that somebody found a buffer overrun possibility or some such case, causing some program path to use a couple of cycles on ensuring that the condition is handled correctly.
As Bob Ward wold probably do: Break out the debugger!
It’s expected for some workloads. Read the performance advisory section of https://support.microsoft.com/en-us/topic/kb4073225-sql-server-guidance-to-protect-against-spectre-meltdown-and-micro-architectural-data-sampling-vulnerabilities-58b08eb9-c546-1637-090c-7c6f2096885c
Sounds compelling, but wouldn’t the 2016 SP3 results (e.g. George Walkey’s run) have been inline with the others?
“All subsequent SQL Server 2014, SQL Server 2016, and SQL Server 2017 Service Packs and Cumulative Updates will contain the fixes. For example, SQL Server 2016 SP2 already contains the Spectre and Meltdown fixes.”
Exactly – my 2016 instance was SP3 as well.
And the Non-Yielding workers on a fresh install?
Is it my understanding that Spectre mitigation includes L1 cache flush after context switch between connections? that would strong affect high volume queries? I ran TPC-H queries at SF10 and saw no difference. Unfortunately, I was not able to run a transactional load test at the time.
SQL manages its own threads so would not expect that to cause that unless there is also pressure on CPU from outside of SQL.
Out of the several hundred servers I had at that time of all sorts of roles (other than the absolutely horrible linux fix but most of those were improved within a couple months) I only had 2-3 that actually slowed down. All of them had extreme network i/o and heavy reads and writes of many small files.
what is memory grant in plan between versions?
The reason I post everything publicly is so that *you* can answer your own questions for stuff like this. I don’t even require an existing database to test – the script creates a new database for you. Please stop asking me to do additional work here and on LinkedIn – the idea of this post is so that *you* can do your *own* testing. Thanks for understanding.
Do an xperf/ Windows Performance Toolkit trace of the SQL Server while it runs the query. Since the symbols are available publicly, the trace will show you what functions inside the Engine is burning CPU time. It should give you a good hint to what has changed between the two versions. Given that you see >10% more time, the trace SHOULD show what is going on.
Not sure if this reproduces on VARCHAR (vs NVARCHAR). But Unicode libraries are VERY performance sensitive – even small changes and upgrades in the library could introduce significant overhead if you are not careful.
OMG it’s Thomas Kejser!
I miss reading your blog posts! Do you have a new blog/anything you can be followed on?
that Makes two of us
Thanks you two.. I may pop back into existence. Have been a little busy building a new database engine 🙂
???
Running on Windows 2016 on Hyper-V. 4 virtual processors.
Everything patched to the hill.
SQL 2016 SP3:
CPU time = 4343 ms
CPU time = 4328 ms
CPU time = 4360 ms
SQL 2019 CU19:
CPU time = 4328 ms
CPU time = 4437 ms
CPU time = 4391 ms
SQL 2022 CU02:
CPU time = 4687 ms
CPU time = 4844 ms
CPU time = 4656 ms
What about Query Store? Is it set the same in all your instances? Any other new functionality that can consume additional cycles?
Query Store is a database-level option. The database creations are in the script above, and you can see that it’s not turned on.
sorry, but why 5-10%? with the numbers you showed it is 3% max.
what are the numbers that give you 10%?
Great question! Don’t apologize. The more complex the demo query gets, the bigger the overhead appears to be. The client queries I was dealing with went as high as 40% difference, but I couldn’t show those publicly. To get the blog post out and have the public discussion, I went for the simplest possible queries that folks like you could run to compare your instances.
And we just moved from 2012 to 2022 🙁 🙂
SQL 2012 SP4
CPU time = 1875 ms
CPU time = 1938 ms
CPU time = 1922 ms
SQL 2016 SP3:
CPU time = 2047 ms
CPU time = 2125 ms
CPU time = 2031 ms
SQL 2019 CU19:
CPU time = 2125 ms
CPU time = 2078 ms
CPU time = 2000 ms
SQL 2022 CU02:
CPU time = 2328 ms
CPU time = 2485 ms
CPU time = 2453 ms
So far on a couple of different bare metal machines that have named instances of SQL Server 2016 SP3 and SQL Server 2019 CU19, I am not seeing a difference that is outside the margin of error.
— Ryzen 9 5950X MAXDOP = 1
— SQL Server 2016 SP3
SQL Server Execution Times:
CPU time = 3281 ms, elapsed time = 3348 ms.
CPU time = 3266 ms, elapsed time = 3269 ms.
CPU time = 3297 ms, elapsed time = 3288 ms.
— SQL Server 2019 CU19 (using compat 130)
SQL Server Execution Times:
CPU time = 3281 ms, elapsed time = 3353 ms.
CPU time = 3266 ms, elapsed time = 3296 ms.
CPU time = 3297 ms, elapsed time = 3299 ms.
— Ryzen 9 5950X — Ryzen 9 5950X MAXDOP = 0
— SQL Server 2016 SP3
SQL Server Execution Times:
CPU time = 3281 ms, elapsed time = 240 ms.
CPU time = 3266 ms, elapsed time = 252 ms.
CPU time = 3297 ms, elapsed time = 266 ms.
— SQL Server 2019 CU19 (using compat 130)
SQL Server Execution Times:
CPU time = 3281 ms, elapsed time = 254 ms.
CPU time = 3266 ms, elapsed time = 260 ms.
CPU time = 3297 ms, elapsed time = 249 ms.
Ooo, interesting – you don’t happen to have an Intel processor you can try, do you? It’d be interesting if the difference is only down to slower Spectre-type handling on 2017/2019, even though there are mitigations in 2016 SP3.
It’s interesting, I will try it on my environment, but if both has exactly same execution plan, maybe it worth trying to find the cpu time for the operators
Ok, I have run Brent’s code on an Intel Core i7-13700K (with the E-cores disabled in the BIOS). This is one of the fastest Intel client CPUs, that is much faster than any Intel server CPU. There is a named instance of SQL Server 2016 SP3 and a named instance of SQL Server 2019 CU19. Both instances are Developer Edition, and the instance-level MAXDOP is 0 for both instances.
For MAXDOP = 1, I don’t see any significant difference in elapsed times using 130 compat level. With 150 compat level, SQL Server 2019 is significantly faster than 2016. I did not see this big difference from using 150 compat on the AMD processors that I have tested so far.
For MAXDOP = 0, SQL Server 2016 is about 7% faster than 2019 on this CPU. With 150 compat level, SQL Server 2019 is about 15% faster than 2016. Using 150 compat on the AMD processors I have tested also helps quite a bit.
Here are the results:
— MAXDOP = 1
— SQL Server 2016 SP3
–SQL Server Execution Times:
— CPU time = 1469 ms, elapsed time = 2393 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 1484 ms, elapsed time = 2298 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 1563 ms, elapsed time = 2288 ms.
–Batch execution completed 3 times.
— MAXDOP = 1
— SQL Server 2019 CU19
–SQL Server Execution Times:
— CPU time = 812 ms, elapsed time = 2411 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 1109 ms, elapsed time = 2360 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 922 ms, elapsed time = 2327 ms.
–Batch execution completed 3 times.
— MAXDOP = 1
— SQL Server 2019 CU19 (using 150 compat)
–SQL Server Execution Times:
— CPU time = 641 ms, elapsed time = 1572 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 640 ms, elapsed time = 1595 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 547 ms, elapsed time = 1579 ms.
–Batch execution completed 3 times.
— Start of MAXDOP 0 Runs ****************************************
— MAXDOP = 0
— SQL Server 2016 SP3
–SQL Server Execution Times:
— CPU time = 2938 ms, elapsed time = 281 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 3396 ms, elapsed time = 284 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 2996 ms, elapsed time = 275 ms.
–Batch execution completed 3 times.
— MAXDOP = 0
— SQL Server 2019 CU19
— SQL Server Execution Times:
— CPU time = 3598 ms, elapsed time = 314 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 3122 ms, elapsed time = 292 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 3457 ms, elapsed time = 292 ms.
–Batch execution completed 3 times.
— MAXDOP = 0
— SQL Server 2019 CU19 (using 150 compat)
–SQL Server Execution Times:
— CPU time = 2232 ms, elapsed time = 246 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 2250 ms, elapsed time = 236 ms.
–SQL Server parse and compile time:
— CPU time = 0 ms, elapsed time = 0 ms.
— SQL Server Execution Times:
— CPU time = 2233 ms, elapsed time = 237 ms.
–Batch execution completed 3 times.
150 compat should be discarded, though, because it gets different execution plans. (Batch mode on rowstore.) At that point, you’re not comparing apples to apples. That’s a different test.
Something’s odd in your test results – note the tests where elapsed time is higher than CPU:
CPU time = 812 ms, elapsed time = 2411 ms.
That indicates the query is waiting on something other than CPU. Something else is going on in that box. Might wanna check the wait stats on that query when it runs – you’re bottlenecked on something.
I just added the extra test runs using 150 compat mode because I was curious about the results. I know you are not interested in them.
My theory about your client’s issue is that they are probably using an older generation Intel Xeon CPU (such as a Cascade Lake, Skylake or older) that does not have the Spectre/Meltdown mitigations built into the CPU. If that is the case, Windows and SQL Server have to do software-level mitigations, which are slower. It is possible that the software-level mitigations for SQL Server 2019 are less efficient than the 2016 one were.
Based on your MAXDOP 1 elapsed times, it looks like the new VM you used for testing is using a relatively slow (for single-threaded performance) CPU, which probably means it is an older generation SKU.
Most of the cloud providers are still offering some pretty ancient Intel SKUs for some VM types. You might try your tests on an Intel Ice Lake VM comparing SQL Server 2016 and 2019. The Intel Xeon Platinum 8370C is the newest and fastest CPU that Microsoft offers right now for Azure VMs.
I agree with you about 150 compat – I was interested in it too. At the end of the day, if the newer compat levels are faster, I can’t get angry that the old compat levels are gradually slower. The new approach of letting compat levels stay around forever is fantastic. It’s one of the things I’m thankful for as a database person – we have it really easy, and we don’t have to do constant app changes in order to stay online. The SQL Server team is kicking butt there.
I love your theory about the newer CPUs having faster hardware Spectre/Meltdown mitigations, but we did test on the 8370C. (The client happened to be on Azure, coincidentally.) With their app code, we were actually seeing 20-40% slowdowns on 2019! Wild.
We ended up fixing the problem by tuning the code, and saw a bigger improvement than SQL Server 2019’s overhead – but it was a great starting point for investigation.
This isn’t directly related to your post, but we’re also seeing slower times when hitting the cluster api since moving to 2019. Thus has been impacting our backups as Ola checks the ag for primary or secondary
I vaguely recall Stack Overflow hitting something similar to that as well.
I had some of the same issues and since the Query Store was enabled, noticed that since SQL 2017 Auto-Tuning (new) is set to default, which in this instance was running. Not sure the two correlate – but an interesting factoid.
Auto-tuning is only on for new databases, not restored ones.
We saw dramatically higher CPU after upgrading to SQL 2019 on better hardware. Applying trace flag 8101 resolved it for us. I don’t know if it applies to this particular issue, but thought I’d mention it.
https://support.microsoft.com/en-us/topic/kb4538688-fix-severe-spinlock-contention-occurs-in-sql-server-2019-43faea65-fdcb-6835-f7fe-93abdb235837
Found your post because I noticed the same thing after looking through 3 years worth of benchmark data that we had been warehousing on a very large farm of SQL Instances. We tracked a regression in performance down post upgrade for all of our 2016 instances on the benchmark results. Its all fully virtualized running intel on VMware. On the same VM with a 2019 instance and a 2016 instance, 2019 is consistently 300 ms slower for me. I’m curious if anyone can actually track this one down.
Initially I thought about lightweight query profiling being enabled by default could be the cause but disabled it and no luck. Killed the system health and telemetry CEIP xevents and they both benefitted but never got 2019 to run as fast as 2016. I’m curious if anyone ever found / finds the answer to this.
For anyone interested in using the benchmark query below, the SET NOCOUNT ON is actually necessary if you run these remotely. A good learning experience if you haven’t experienced the reason why. (hint try running it locally vs over a link that has higher latency). Keep up the great work Brent.
SET NOCOUNT ON;
DECLARE
@n numeric(16,6) = 0,
@a DATETIME,
@b DATETIME;
DECLARE @f int = 1;
SET @a = CURRENT_TIMESTAMP;
WHILE @f <= 3000000
BEGIN
SET @n = @n % 999999 + sqrt(@f);
SET @f = @f + 1;
END;
SET @b = CURRENT_TIMESTAMP;
SELECT DATEDIFF(MS, @a, @b) as execution_time_ms
I mean, they’re fine. Just switch to bloody Oracle already.
Yeah, it’s not like Oracle costs more.
Oh wait
Hang on
Both on vmware with a Intel(R) Xeon(R) Gold 6144 CPU @ 3.50GHz
Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) – 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
(1 row affected)
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 6437 ms, elapsed time = 6487 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5828 ms, elapsed time = 5907 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5563 ms, elapsed time = 5611 ms.
Batch execution completed 3 times.
Completion time: 2023-11-08T06:26:00.9181827-08:00
(1 row affected)
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 6437 ms, elapsed time = 6487 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5828 ms, elapsed time = 5907 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5563 ms, elapsed time = 5611 ms.
Batch execution completed 3 times.
Completion time: 2023-11-08T06:26:00.9181827-08:00
(1 row affected)
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5453 ms, elapsed time = 5458 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5344 ms, elapsed time = 5348 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5656 ms, elapsed time = 5680 ms.
Batch execution completed 3 times.
Completion time: 2023-11-08T06:26:24.3556663-08:00
Microsoft SQL Server 2016 (SP3) (KB5003279) – 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Messed up the last post with copy pasta,
Microsoft SQL Server 2016 (SP3) (KB5003279) – 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
(1 row affected)
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5453 ms, elapsed time = 5458 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5344 ms, elapsed time = 5348 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5656 ms, elapsed time = 5680 ms.
Batch execution completed 3 times.
Completion time: 2023-11-08T06:26:24.3556663-08:00
Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) – 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
(1 row affected)
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 6437 ms, elapsed time = 6487 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5828 ms, elapsed time = 5907 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
SQL Server Execution Times:
CPU time = 5563 ms, elapsed time = 5611 ms.
Batch execution completed 3 times.
Completion time: 2023-11-08T06:26:00.9181827-08:00
Had to try it for myself. All tests run on a VMWare Workstation 17.5 VM, Windows Server 2022 fully patched. Each version of SQL was patched to the latest available CU and security update.
All 3 versions of SQL were installed as named instances but the services disabled in startup. For each test the version tested was started, DB created, tests run and VM rebooted to move on to the next version. I did 3 runs of each tests, all results were within the margin of error between the runs, no outliers.
VM specs:
4vCPU, 32GB RAM, local NVME storage on each machine.
AMD Zen 2
SQL 2022 –
4935, 4417, 4277
SQL 2019 –
4308, 4261, 4235
SQL2016 –
4188, 4187, 4094
Intel Raptor Lake
SQL 2022 –
2893, 2678, 2756
SQL 2019 –
2796, 2579, 2640
SQL 2016 –
2562, 2594, 2453
Intel Coffee Lake
SQL 2022 –
6390, 5156, 4891
SQL 2019 –
6152, 4921, 4521
SQL 2016 –
5690, 4501, 4481
Just some additional information. When we updated from SQL Server 2016 Expensive Edition to 2022 Expensive Edition, we suffered catastrophic slowdowns. Switching to the 2016 compatibility helped a little but we still have overnight jobs that easily finished prior to 8AM now taking until 10 or 11 AM.
Yes… it’s on a different machine so I thought I’d do a test on my laptop so we can have a “same machine comparison”. It has 2 – 6 Core 8th Gen I7’s in it with 32 GB of RAM (max memory set to 24,000 MB = 24GB) an 2TB nVME drive. I also have the 2017 and the 2022 Developers Edition on it. When I do the performance testing coming up, I have only the 2017 service running or the 2022 service running and both instances share the same drive but not the same folders, although the folder structure is similar, including TempDB. I’ve also carefully checked everything in the sp_configure proc and, except for the new stuff in 2022, everything is identical. I’ve also tried the testing on both instances with the Legacy CE enabled and disabled with no changes. I DO have “Query Store” turned of in both instances and both instances are up to the latest CU.
Here’s the code I run for the test. Notice that is does NOT use any disk nor does it use a logical reads.
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
CHECKPOINT;
GO
DECLARE @BitBucket BIGINT
,@MaxN BIGINT = 100000000
;
SET STATISTICS TIME,IO ON
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
SELECT TOP(@MaxN)
@BitBucket = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E1 a,E1 b,E1 c,E1 d,E1 e,E1 f,E1 g,E1 h –1 to 16^8 or 4,294,967,296 rows max
;
SET STATISTICS TIME,IO OFF
;
GO 5
===============================================================================
Here are the results from the 2017 instance:
===============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 6719 ms, elapsed time = 6719 ms.
SQL Server Execution Times:
CPU time = 6687 ms, elapsed time = 6687 ms.
SQL Server Execution Times:
CPU time = 6734 ms, elapsed time = 6743 ms.
SQL Server Execution Times:
CPU time = 6704 ms, elapsed time = 6719 ms.
SQL Server Execution Times:
CPU time = 6687 ms, elapsed time = 6685 ms.
Batch execution completed 5 times.
===============================================================================
Here are the results from the 2022 instance:
===============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 9141 ms, elapsed time = 9214 ms.
SQL Server Execution Times:
CPU time = 9234 ms, elapsed time = 9270 ms.
SQL Server Execution Times:
CPU time = 9250 ms, elapsed time = 9255 ms.
SQL Server Execution Times:
CPU time = 9172 ms, elapsed time = 9181 ms.
SQL Server Execution Times:
CPU time = 9172 ms, elapsed time = 9193 ms.
Batch execution completed 5 times.
To summarize in a quantitative manner, SQL Server 2022 uses about 37.1% more CPU and takes about 37.4%
longer to run an identical task even though it uses no logical or physical reads.
To summarize in a HR-Compliant but still highly accurate qualitative manner…
“SQL Server 2022: It just runs a lot slower”!
To summarize in a surely-seaman-like manner that people in IT will totally understand…
“Performance in SQL Server 2022 sucks”! 😉
I imagine the same issue occurs in 2019 because that’s where they stated adding a bunch of the automatic tuning stuff for people that think they don’t need a good DBA even once in a while (plug for you, Brent!). And don’t get me started about TF1117 in TempDB. 🙁
We recently upgraded our SQL Server 2014 instance to SQL Server 2019 (in place upgrade). Since the upgrade, we are experiencing memory and CPU contentions. Queries that were running faster on the SQL Server 2014 version now are consuming CPU/Memory resources and running about four times longer than before. All the databases have been set to 130 compatibility level right after the upgrade. Execution plans for most of the top queries have been changed since the upgrade which are not optimal. Is this a normal behavior to see after SQL Server upgrades?
First off, you shouldn’t ever upgrade in place, and here’s why: https://www.brentozar.com/archive/2015/03/why-you-shouldnt-upgrade-sql-server/
Second, you shouldn’t randomly change compatibility levels until you’ve tested those either. Try setting them back.