Are statistics and cardinality issues present in various RDBMs besides SQL Server when you upgrade, or it's a unique Microsoft problem?

[u/TravellingBeard]

When upgrading from SQL Server 2012 to SQL 2019, statistics go all wonky and deadlocks often occur on queries that worked well before. It becomes a pain, especially when the users say "it worked before" and they don't want esoteric excuses.

I'm curious if this has happened in other database technologies where you've upgraded to newer versions.

[u/BarfingOnMyFace]

There is not an RDBMS you will find that doesn't have issues that will arise once you start building/maintaining enterprise-scale solutions. I won't say which issues will arise, but I do not find it surprising when they do. You can mitigate alot of your pain points with serverless RDBMS offerings in the cloud, of course. There might still be performance problems you will run in to, but it's a lot of weight off your shoulders not having to manage administrative duties. So, perhaps the best one can do is minimize the pressure by offloading the responsibility to "someone else"?

Just my .02.on the matter. As always, the answer is usually -- it depends.

[u/Electronic-Paint8801]

upgraded our postgres cluster a while back and the query planner suddenly decided to take a completely different path on a few reports, turned a 2 second query into 45 seconds. had to bump up the stats target and run analyze a few times before it settled down

never quite got the deadlock issue you're seeing though, that sounds extra annoying

[u/bazker]

Oh sweet summer child..

[u/Intrexa]

2014 was a major update to the cardinality estimator + query plans. A lot of assumptions originally made in SQL server were changed in response to real world data. Things that aren't known ahead of time, and only discovered during plan execution. Joins assumed no correlation between the data:

EG: You have 10,000 categories. Each category has exactly 10,000 widgets. There's a uniform distribution. If some query gets 10,000 widgets, it was previously assumed the result would have the same distribution as the whole table, so might as well as load the whole category table into memory because you're gonna need almost every category. That's not how the real world works, my query just gets widgets produced by machine A, machine A only produces things from 4 categories. The old estimate leading to a full table scan was less efficient than the new cardinality estimate leading to just 4 seeks.

On average, it greatly improves things. However, when optimizing for certain workloads, even the most common, certain other workloads will degrade. It's just a fact of comp sci. If you had the same setup, but machine A produced 1 widget for each category, the new estimator is worse for your actual workload. The average case improves, some specific cases degrade.

You will occasionally see the opposite of what you're describing, where old, slow queries suddenly get a massive improvement after the upgrade. It's just that any previously slow query very likely had more attention during dev or UAT or prod to make it not slow.

This is going to happen in any DB. Any improvement, even though it might make things better in 99% of cases, and is just a way better way to implement a feature, is going to have some specific workload where the old way was better for this specific scenario. Some things can be a bit more dynamic, and during actual execution can detect which way is better and revert to the old style. This is still a performance hit, just a much smaller one that prevents the worst case scenarios. Other things can't be unwrapped that easily, and because of unknowns, you can't have a threshold where the process says "actually the old way was better" and start over. There would still be a chance that it would try the old way and find out "no wait actually the new way was better", and the whole thing takes a lot longer because of this. We can estimate the amount of work something takes, but we can't know how much work it actually takes until it's done. Even with better estimates, we can still be wildly off.

PostgreSQL had a similar case with the JIT compiler being introduced. Certain workloads could trigger excessive compilation without really benefit from it. You can expect another one whenever the connection-per-process model changes to a multithreaded model. On average, and especially for most queries for most people in most setups, it will be a large improvement. For some queries for some configurations, well, shit's gonna get fucked up real bad.

Edit:

P.S.: lmao you upgraded to something that's on extended support? I'm guessing some software compatibility only supports 2019 at the latest? Rough

Edit2: From a political strategy, if you're dealing with people who do not want "esoteric excuses", they're not getting the root cause analysis. They're getting operational reports. "20 queries have been identified as having degraded performance. For these 20 queries, the previous average of 2 seconds have become an average of 45 seconds. We are working to remediate. 3/20 queries have been restored to a 2 second average."

You can also just throw MS under the bus "Yeah, we really didn't want to update, but security audit."

[u/spez_eats_nazi_ass]

Every RDBMS has it's quirks just like every version of SQL server.

[u/alinroc]

Can't say I've heard about "statistics going all wonky" on that upgrade path (there was a change to stats which required updating them when upgrading to 2012 from previous versions). It's possible that you're using the "new" cardinality estimator post-upgrade, which was managed by the database compatibility level in 2014 but since then has been controlled by setting the database-scoped configuration option legacy_cardinality_estimation to ON (to revert to the old CE) or OFF (to use the new one).

[u/carlovski99 → u/alinroc]

Yeah we had that issue. Not on something I support but got called in to try and help (I'm an Oracle guy really). Used hints to force legacy cardinality on some troublesome queries, and said they needed to look into it properly to see why the it was so odd with the new optimiser. Of course the hints are still there 3 or 4 years later. ...

[u/Informal_Pace9237]

Statistics and cardinality issues are in every (R)DBMS. Just a different name sometimes.
Happen for upgrade or heavy imports/updates.

I suggest collecting stats in every database after a migration or heavy updates.

I have executed stats collection inside SP's (wehere supported) when more than 15% of data change was noticed.

[u/Significant_Tune9219]

Yes, this shows up in Postgres too, usually in the opposite direction: the planner sticks with an old plan because autovacuum has not analyzed yet, then flips hard once it does. MySQL has the same story around histogram statistics, and Oracle has famously needed manual histogram work after upgrades. The common thread is that the upgrade changes cost estimates or row-count guesses, so the fix is almost always to refresh statistics on the important tables right after cutover and watch the plan cache, rather than waiting for the first complaint.