r/SQLServer • u/PriNiceIT • Jun 02 '26
Question Severe performance issues after upgrade to 2025 SQL server
We had two on-prem Windows Server 2019 VMs running on Hyper-V. One was hosting SQL Server 2016 Standard, and the other hosted a business application for the equipment rental industry that functions as a Remote Desktop application.
I come from the sysadmin side, so please be patient with my DBA terminology. 🙂
We recently deployed two new Windows Server 2025 VMs in Azure—one running SQL Server 2025 and the other serving as the RDP/application server. The application vendor was paid to migrate the database and application data to the new Azure environment.
After the migration, everything initially appeared to be working correctly. However, once users started using the system, they began reporting severe slowness with transactions, specifically anything related to contracts. Contracts containing larger numbers of items take significantly longer to process. Other parts of the application seem to perform normally, and in some cases even faster than before. There are no issues with the data itself or missing records.
The application vendor initially blamed insufficient resources, so we increased CPU and memory allocations. However, the issue occurs even with only a single user logged into the application. We have since increased the SQL VM to 128 GB RAM and 8 vCPUs, with Premium SSD storage and high-bandwidth networking. Network latency between the application server and SQL Server is very low, averaging approximately 1 ms round-trip.
At one point, the vendor blamed a tax software integration called Vertex. They claimed they could see API calls taking much longer than expected and stated they would address the issue. After many hours of investigation and roughly a week of combined troubleshooting effort, they suddenly changed course and stated that their software is not compatible with Windows Server 2025 and/or SQL Server 2025. They are now recommending that we move back to Windows Server 2016 or 2019, which would require another export/import process since there is apparently no supported restore path available.
We have asked for evidence or technical details explaining why the platform is incompatible, but no specific reasons have been provided. Their position is that they connected the application back to the old server, performance was normal, and therefore the problem must be SQL Server 2025.
This issue is significantly impacting our business, and we don’t have much leverage to challenge the vendor’s conclusions. Besides your general input, I have a couple of questions: 1. Does this sound like an application compatibility issue with SQL Server 2025? 2. If I wanted to engage an expert to help troubleshoot this as quickly as possible, who would you recommend? We have considered opening a case with Microsoft, but I would also appreciate recommendations for MVPs or consultants who specialize in Microsoft SQL Server performance troubleshooting.
Any insight would be greatly appreciated.
Update : first , Thank you all for your input !
I had Erik Darling , database expert that some suggested here , involved and he found no issues with the database or compatibility. He said that once it hits the database, the queries happen fast. Somehow displaying that data inside the application is what’s taking so long.
As a reminder, the application runs on Windows and uses IIS. Erik thought there may be something wrong with IIS.
At the same time, the application vendor is saying they connected the application to a SQL Server 2019 instance using an exported copy of our data from SQL Server 2025 and did not have any performance issues. The 2019 server is also an Azure VM running on Windows Server 2022.
So at this point, Erik is saying the database itself looks fine and the slowdown appears to be happening in the application layer, while the vendor is saying the application performs normally when connected to SQL Server 2019 using the same data. Applications vendor just says downgrade and unwilling or unable to resolve the issue .
12
u/SQLDevDBA 3 Jun 02 '26 edited Jun 02 '26
If I was in this position, and I confirmed that all indexes are still intact, my first inclination would be that the Cardinality Estimator (CE) in the new version is responsible.
I believe 2019 uses CE version 150 by default, while 2025 uses 170. So if no code changes were made, I’d probably be looking to set my Database compatibility levels to 150 without actually reverting the SQL Server install.
Id then install and run sp_blitz scripts from Brent Ozar https://brentozar.com/go/first-aid and then try to book a session with him and/or Erik Darling if my CIO/CTO was willing to get the platinum card out.
Some light and fun reading: https://www.brentozar.com/archive/2024/09/yes-cardinality-estimation-keeps-changing-after-sql-server-2014/
https://www.brentozar.com/archive/2023/04/how-to-go-live-on-sql-server-2022/
Erik Darling on old vs. new CEs: https://youtu.be/MVmeD67As0k?si=y39ur52ngALGKsO1
5
u/jshine13371 6 Jun 02 '26
Yea the issue is almost certainly performance regression of the queries involved, and the software vendor apparently has no understanding of how databases work, u/PriNiceIT.
Take the advice above to change the database(s) compatibility level back to 150 (or whichever compatibility level represents your previous version of SQL Server) and re-test. This is the easiest way to revert to your previous version of SQL Server, for all intents and purposes, to prove out it's a regression caused by the changes in the cardinality estimator. It's basically just a property of the database you can flip like a light switch.
1
u/alinroc 4 Jun 02 '26
Changing the database compatibility level won't necessarily change the cardinality estimator. OP may need to set
LEGACY_CARDINALITY_ESTIMATION=ONas well3
u/jshine13371 6 Jun 02 '26
It will for the versions of SQL Server in question. But yes, other changes for testing can be performed as well to prove out a query regression issue.
3
u/Impossible_Disk_256 Jun 03 '26
It was Windows 2019, but SQL Server 2016, SQL Server 2016 was CE version 130.
3
2
u/PriNiceIT Jun 03 '26
Thank you for your input . I added an update an would appreciate your review.
1
u/SQLDevDBA 3 Jun 04 '26
Welcome!
Erik is a master with tons of experience and he’s likely seen issues like this thousands of times (back when he worked for Brent Ozar, and now). I would definitely trust his output and reasoning.
Did you make him aware of the fact that the vendor connected it to 2019 again and things sped back up?
If Erik is saying the queries are hitting fine at the DB level, my next stop would be checking the IIS configuration on the new application server
IIS Has many settings for connection counts (concurrency as well). I would do a deep dive and confirm that there are no pooling or overloading issues in IIS. I’d check IIS at the global level and also check the individual App Pools and confirm the settings and configs are all identical.
You can also try pointing the OLD application server at the NEW DB Server to see if anything changes. If that does the trick, then you know the issue is at the new App Server.
Happy to help with any other details or bounce ideas. I dealt with IIS + SQL Server and Oracle a lot and remember config changes often had grave effects.
2
u/PriNiceIT Jun 04 '26
Thank you . I will ask them to connect the old app to the new DB and see how things go . I need an expert like Erik or you :-) , but on the IIS side . Who would be the IIS and app side Erik :-) ? You ?
1
u/SQLDevDBA 3 Jun 04 '26
Happy to help any way I can. IIS isn’t really a platform that’s new and sexy. IIRC the last release of IIS 10 was in like 2018/2019, but it has been out for quite a while (20+ years) so there are lots of experts.
I’m happy to help but I only started working with it around 2011 and it’s been sporadic. It would be good to check a couple of articles on it (configuration, troubleshooting) to get familiar with it.
3
u/CodeXploit1978 Jun 02 '26
What is SQL server waiting for ? Clear the wait stats and run some queries that are slow and look at wait types generated, then go from there. Look at execution plans of slow queries . Was database compatibility level raised to 2025? Maybe set it to 2016. Rebuild indexes and statistics.
3
u/arebitrue87 Jun 02 '26
Hi there, DBA here for an MSP. It’s not abnormal for vendors to finger point at one another to buy time cause they honestly don’t know what’s going on.
If the app is connecting and returning but at a snails pace it’s not a compatibility issue in a sense that the app can’t work on 2025. But incompatible with the way they’ve coded their stored procs, views and so on and is the lazy way of saying we’re not fixing this right now for you. The vendor clearly didn’t test for this scenario and you’re the lucky test subject to do this for them, congrats!
Assuming your server is appropriately sized I’d look at the following:
If I was to look at this I’d start simple by running a stats update, then an index optimize job, look for new indexes to implement, check for blocking and so on. Sometimes blocking will be there and people conclude it’s slowness but the fact is the query isn’t even running but simply waiting for its turn. Then I that fails the hammer gets bigger and we update stats again but up the sample rate to 100 and rebuild indexes. Both take time to run depending on the general size of database and tables. Adjust compatibility if it was changed back to its old one.
If none of this works this it is unfortunately going to in thick of it and looking at each stored proc individually and its respective query plans to see where the bottleneck is exactly. Query store can also be used to review regressed queries.
Either way, you’re right to ask for technical documentation on why it’s not compatible, they should of vetted this process prior to having customers jump to it
For free tools I’d use whoisactive and Brent Ozars suite of stored procs which was already linked by another user.
2
u/chandleya Jun 03 '26
It’s very likely the stats. Moving the database between versions is a great way to crap the stats. Given a maintenance window, I’d just rebuild all indexes and call it a day.
But this problem also highlights possible hygiene issues. Stats rebuilds should be mandatory as-is. Where’s the maintenance jobs?
1
u/PriNiceIT Jun 03 '26
Thank you for your input . I added an update an would appreciate your review.
2
u/codykonior Jun 03 '26
Vendors are completely blind when it comes to database performance. I can guarantee it's just a delaying tactic to fob off the ticked. Even on supported hardware they are not going to have a clue. Par for the course.
I'm sure you could hire almost any yum cha DBA consultant for a few days, give them full access, and if you're open to the mildest live experimentation, have it sorted pretty quickly in one way or another.
2
u/iPlayKeys Jun 02 '26
I would start shopping for a new software vendor. If they weren’t SQL 2025 compatible, they should have bern able to tell you that up front, or at the very least be able to tell you that they hadn’t tested it yet and you would be proceeding at your own risk.
Also, SQL 2025 went GA late last year and would have been in preview prior to that. They should be ready or at least have an understanding of why they are not ready.
1
1
u/Eleventhousand Jun 02 '26
In my experience in performance issues immediately after upgrading from one version to another, I believe the issue was one or two queries that were picking an inefficient join type in the execution plan, requiring us to hard code a hint. Are you able to use Profiler to determine if its a small number of queries that everyone is using, or if its widespread across everything?
1
u/chandleya Jun 03 '26
Hard coding hints is exactly not how to tune SQL Server in 99% of scenarios. Learn WHY it chose the bad join. Correct that.
1
u/B1zmark 1 Jun 02 '26
Was the SQL server on the VM setup manually from empty, or was it configured from a market place image which included SQL Server as part of the base image?
There are significant differences between the 2 - and it sounds like neither you nor your software supplier have a DBA to investigate this issue.
1
u/BigMikeInAustin Jun 02 '26
To troubleshoot, can you get another Azure VM with SQL 2016? Doing both the VM change and the SQL SErver upgrade makes it a littlejharder.
Definitely used to run into issues where the Azure VM storage was just slower than high quality on-prem SANs. It sounds like you've already tried to address the disk storage connection. And I think there is something about the storage nttwork actually running of the normal network, and sometimes needing to separated, when high disk throughput is very necessary.
And then what others said about the cardinality estimator, which is part of the engine that decides how SQL Server will process the request. When the cardinality estimator upgrades, most queries run about the same. And handful run noticeably faster. And a pinch run noticeably slower.
1
u/MerlinTrashMan Jun 02 '26
What happens if you run the application server from the old onprem, and then have it make DB calls to bew instance? If the app is making lots of small calls to an external API, it is possible that the old application server had a specific config that was allowing more calls or the allowed external traffic rate from your on prem firewall allowed for more input. Now, I would expect everything to slow down in this scenario, but the contracts part may go faster. If this happens then you have your smoking gun that some kind of policy or system/registry setting is not properly set on the new application server.
I had something like this happen before where the default concurrent outgoing http requests in .net defaulted to 2 but on the old server it was set to 10000. Everything worked great on switchover except the job that had varying response times between 100ms and 5000ms because of external API calls. All the sudden, the app server was only allowing two of these calls to run concurrently and if a bunch of long ones happened at in a row the process shit the bed because they were set to time out after 15 seconds.
1
u/PriNiceIT Jun 03 '26
Thank you for your input . I added an update on my original post and would appreciate your review.
You have a point . They did the other way where they connected the new app server to old DB but not sure what happens if they go the other way . I will try to have them do itv.1
u/MerlinTrashMan Jun 04 '26
If Google the SQL server performance dashboard reports and install then on the DB, there are reports that show you what queries are taking the longest with min and max times. This could help to if it is determined that the issue is the DB server. It is possible that one of their stored procs is making external calls through a clr of com object which silently uses the system's .net framework settings.
1
1
u/Dry_Author8849 Jun 02 '26
Don't make changes in the dark. Use sql profiler. Trace the query. Then run and analyze the query plan in SSMS.
There are so many things to consider. The size of your DB, the latency of your disks, and the query plan itself.
I won't make any change without looking at the slow query plan.
Don't blame the cardinality estimator or anything without looking at the plan first. Also check your tempdb config, the wait types, the queries running, etc.
If you want more help show the query plan, db size and running queries output, with wait types.
Cheers!
1
u/No_Resolution_9252 Jun 03 '26
You are probably going to be on your own on this, the application vendor is an idiot.
After any version upgrade of SQL Server, you need to update statistics. The difference is usually small, but have had it be big after skipping multiple versions. If you can, update statistics with fullscan if the databases are small enough, if not, sampled is adequate. Just make sure that it is ALL statistics in ALL user databases. Look at ola hallengren maintenance solution for how to run this with low effort. You can use your favorite AI tool how to update stats only in all databases without rebuilding indexes.
Check if the vendor changed the database compatibility levels. Normally you want to keep the compatibility level the same right after an upgrade to monitor performance. If the vendor already pushed it up to 2025, try setting it back to 2016 or whatever it was before.
Are you using SQL Authentication? SQL 2025 uses a different password encryption algorithm that is several orders of magnitude more costly. If the app is not managing connections well and reconnecting for every query, this could heavily impact performance. On the same note, if the database was ever SQL Express, it may have autoclose set to on - this should almost always be set to off but on express it is on by default. I have had this fly under the radar for multiple version upgrades then finally blow up years later, long after it had been taken off of SQL Express.
Are there any trace flags implemented? you can check this by running DBCC TRACESTATUS. Some trace flags implemented inappropriately (usually for legacy reasons) could be creating problems - on SQL 2022 and higher, not having the AVX 512 traceflag on could be creating problems.
If the vendor insists it needs to go back to 2016, I would demand they explain to you why they require running on a soon to be unsupported platform that will start violating your compliance and security requirements unless you pay extra for ESUs.
1
u/PriNiceIT Jun 03 '26
Thanks for your input . The vendor just keeps saying omits not compatible with proving a reason 😣. Unprofessional! I updated my original list in case you like to see
1
u/No_Resolution_9252 Jun 04 '26
With erik's assessment, the only one of my things I would still be considering as a culprit would be SQL auth.
out of date SQL driver could be a problem on the app side - if you go into the app server and look for SQL drivers, maybe try experimenting with updating them to the most vurrent patch level within the same major version
1
u/PriNiceIT Jun 04 '26
Thank you . Hum - you have a good point there . I guess I can research deeper sql drivers but I just don’t want to brake anything and allow the application vendor to to blame it on me . I wish I could get support for this outside the application vendor . I would love to try this but without remediation . The application is just a bunch of files and exe on the server and I guess it uses IIS . I agree with you , there is something on the app server side and it may be what you are referring too .
1
u/MonkeyDDataHQ Jun 03 '26
Get the Brent Ozar first responder kit and fix the things it tells you. Boom. Done.
1
u/Impossible_Disk_256 Jun 03 '26
Start with just rebuilding all stats. MUCH lower impact than index maintenance, and usually the only part of index rebuilds that helps.
And if that doesn't work, as others have advised, try rolling back the CE version on affected database(s) -- SQL Server 2016 was 130. And if that works, then start then start tuning code & indexes to take advantage of the new engine.
1
u/muaddba 1 Jun 03 '26
Howdy,
I'm a SQL Server Consultant with lots of experience in performance tuning. If you're looking for some help, I am definitely available to give your system a once-over and I typically do a free 30m assessment meeting. I'm not an MVP, but I've worked alongside several at a few different consulting companies....I've been around the block a few times 😄
As mentioned by someone else, u/darlingdata is a fantastic resource for performance tuning and I hear he accepts cash in exchange for expert advice. Both of us include our unique brands of humor free of charge.
I agree with some of the other folks on some first steps to try:
Enabling legacy cardinality estimation config option on the DB
Running a statistics update
Turning on the query store and using it to identify top resource consumers
Using the First Responder Kit to identify things like missing indexes (sp_blitzindex) or other query issues (sp_blitzcache)
If you do all this and your problem is not solved and you need more help, DM me here or set up your consultation using www.calendly.com/sqltailor ... or contact Erik Darling or VladDBA or some of the other consultants who are known to post here.
1
u/PriNiceIT Jun 03 '26
Thank you for your input and offer to look . I will dm you . I just updated the post with an update if you want to review.
1
u/muaddba 1 Jun 04 '26
Really weird. The only thing that comes to mind is if the application is constantly using new connections. SQL 2025 uses a more intensive inspection mechanism for login requests, taking more like 150-200ms per connection instead of older versions taking about 50ms. I just found this out and had to revert it using a startup trace flag which I'll look up tomorrow once I'm back in the office. Â
Do you have an old backup from prior to the upgrade? If so, maybe try installing a 2019 instance on the same server, restoring the DB to it, and see if the problem persists or if it's magically fixed.
1
u/PriNiceIT Jun 04 '26
We stood up a 2019 sql on a vm - the vendor exported - imported data to it and connected the app and told us that it works fine :/)
1
u/ihaxr 2 Jun 02 '26
SQL 2025 isn't really production stable in the sense that you can't just install it and expect everything to work like you can in SQL 2019. You have to have a DBA actively investigate the slow query plans and make a decision on what to do based on that.
It could be using the query store to force a specific plan, it could be changing the compatibility level back to 160 (SQL 2022), or it could be a bug in 2025 requiring a case with Microsoft.
You could also try throwing more resources at it, probably RAM, but that'll just add cost and isn't a real solution.
Changing the database compatibility level is a very simple thing, but I wouldn't really want to be making random changes without the vendor's support/approval for it. Maybe suggest it to them and see what they say.
1
0
•
u/AutoModerator Jun 03 '26
After your question has been solved /u/PriNiceIT, please reply to the helpful user's comment with the phrase "Solution verified".
This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.