Yes, you should consider upgrade to SQL Server 2025. But if you use SQL Logins, check this first (and if you’re here to figure out why it’s suddenly slow, read on).
For the most part, upgrades to SQL Server 2025 go pretty smoothly. I’d even say upgrades to other versions of SQL Server have gone pretty smoothly too, although plenty of people suffered from performance pain from the change in cardinality estimation model when upgrading to SQL 2014 (well, the compatibility level 120). The ‘simple’ fix for that one was to put the compatibility level back to 110, and then figure out what was causing the pain. But with SQL Server 2025, there’s another pain point which isn’t quite so easy to get around.
Regular readers will know that I don’t like to write about real situations that customers have been through. It’s all well and good to say that I fixed the performance of a query by adding an index, or by changing the data type of a column, but I’m always reluctant to mention particular products or customers by name.
But this one has already been mentioned, including calling me out by name, so I’m fine.
David Musgrave is a Dynamics MVP in Perth (Adelaide’s nearest neighbour to the west, only 2000km away) who reaches out from time to time. A couple of months I got a call from Arthur Achilleos saying David had recommended me to figure out why a machine that had been upgraded from SQL Server 2019 to SQL Server 2025 had seen its CPU metrics skyrocket. Queries were struggling to run, and people were scrambling to figure out if rolling back to SQL Server 2019 was an option. The application was Dynamics GP.
SQL Server 2025 introduces a handful of new features. Intelligent Query Processing, for example, has seen a number of changes such as Parameter Sensitivity Optimization and Cardinality Feedback. A quick look at the queries that were the most expensive showed a lot of queries that had over a hundred parameters. My initial thoughts were that this could be painful if the engine is trying to assess the parameter sensitivity across all of that. But that wasn’t it. Not entirely.
Connecting via SSMS wasn’t much of a problem, and queries seemed to run well enough there.
We looked at the wait stats, and some of the usual suspects around parallelism were around, as well as some SQL Operating System ones (SOS_SCHEDULER_YIELD, for example). But there was one wait type that I hadn’t seen much of before. It was appearing third or fourth in the list of largest waits, and only occasionally jumping to the top, even when the long list of “ignorable” wait types were ignored (as per SQLskills’ list). It was the PREEMPTIVE_OS_CRYPTOPS wait type. I’ve linked to SQLskills’ post about the type, because they make it clear that this is typically more of a Windows problem than a SQL one (plus, SQLskills is where you should always go for information about wait types – it’s a great resource). Their page suggests to “have your infrastructure team investigate performance issues with servers that provide cryptographic services to the domain (for instance, the server for an Enterprise Key Management system)”. And while that’s really good advice, it wasn’t what was going on here.
This problem is actually inside SQL Server 2025, and what’s worse is that you probably won’t notice it in your test environment unless you’re testing at scale.
The issue is the way that SQL Server 2025 hashes the passwords for SQL Logins. Dynamics GP uses SQL Logins. Lots of older applications do. Essentially, the PBKDF2 algorithm improves on security by applying the SHA-512 hash 100,000 times. The idea is to slow down brute force attacks, but it will also slow down applications that re-authenticate a lot. Chatty ones. Connection pooling can help to a degree, but not as much as you might like. Best is to use a Windows login instead – the trusted account for your web service, or the user who’s running the application. It’s why SSMS might still run just fine – because SSMS probably connects as you. And because SSMS won’t be as chatty as your application.
Your test harness, making sure that your logic is correct in the new database environment, might not push enough new logins to notice. Maybe it reuses its connections really well. Either way, this is a problem that seems to avoid getting caught in test environments. Perfect.
Let me be clear… The fix is to use a Windows Login. But I do appreciate you might not be able to change your application.
You can’t just set the compatibility level back to SQL 2022 to make this problem go away, but there is a trace flag you can use. A couple of months ago, it was definitely undocumented. I’ve heard it’s now documented, but I’m not completely sure. I can’t see it described on anything official. So maybe check with Microsoft Support. (Even though when your CPU is high and your application is failing, you want a really urgent fix and don’t want to have to wait for Microsoft Support!)
But even the trace flag isn’t the whole fix.
You need to know – this trace flag was documented by Michael Howard for SQL Server 2022, for turning this behaviour ON. You can read it at Support for Iterated and Salted Hash Password Verifiers in SQL Server 2022 CU12 | Microsoft Community Hub. But in SQL Server 2025, the trace flag reverts you to the old one. You might want to be aware of that if you used the trace flag to become compliant with NIST SP 800-63b. Personally, I would’ve preferred a different trace flag for turning PBKDF2 off, that wasn’t the same one that turned it on before. The trace flag is 4671. There are a few blog posts about it, but not enough to convince me that it’s fully supported. If you turn it on in SQL 2022, it might make your application slow. It’s annoying.
The trace flag was in that last paragraph. But please keep reading for the full fix.
In SQL 2025, after you’ve applied the trace flag, each password hash will still be the PBKDF2 version until you reset it with ALTER LOGIN, so just applying the trace flag won’t fix the CPU right away. The login mechanism is smart, and checks to see which hash algorithm was used so that it can check it right way – either the fast method or the slow one. But in SQL 2025 without the trace flag, it’ll update a successful the hash to the PBKDF2 version for next time. So the trick is to turn on the trace flag, then reset all your passwords (let’s face it – probably only one), and only then will your performance return. Luckily, the logins will still work on either algorithm, but anyone logging in repeatedly with a PBKDF2 hash will hurt your CPU until you reset the password.
The clue for spotting which algorithm was used on each password is to look at the password_hash column in sys.sql_logins. The PBKDF2 ones start with 0x03, and the older/faster ones start with 0x02. Leave ‘sa’ disabled (always) and with a PBKDF2 hash, because that’s the one you really want to be more secure (same for all sysadmin ones), but any locked-down application logins could be 0x02 style, for the sake of your CPU.
And in case you’re curious, David’s post about it is here, including some instructions on configuring trace flags.
I really hope you’re reading this BEFORE you’ve experienced it. Otherwise, good luck.
@robfarley.com@bluesky (previously @rob_farley@twitter)
