July 31, 2024 at 9:40 am
I think we should keep in mind what the upgrade path looks like. Upgrades between X and X+1 I would expect to be relatively painless.
Upgrades from X that skip a number of versions is just asking for problems.
In all honesty I've seen a lot of current DBs that would not tax long gone versions of SQL Server and would run comfortably on 32bit hardware. For that reason I see the need to upgrade driven by
The worst case scenario is a forced migration involving many change points, all dependent on each other. Like Birmingham City Council migration from SAP to ORACLE.
July 31, 2024 at 2:42 pm
The post above is the first post on Page 2 and wasn't showing because of problem on this site. This post will make the post above appear.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2024 at 3:12 pm
I think we should keep in mind what the upgrade path looks like. Upgrades between X and X+1 I would expect to be relatively painless.
Upgrades from X that skip a number of versions is just asking for problems.
There are certain basics that should never be a problem no matter what the value of "N" is in the "X+N" upgrade/migration is. For example, things should not generally and widely run 30% slower (like they have for us) just because of an upgrade but that is precisely what happened in 2019 and continues to be an issue in 2022. And, changing Compatability Levels didn't help much. We're still bleeding profusely but surviving.
They pulled the same crap in 2014 when they supposed made improvements to the Cardinality Estimator. Very fortunately, they had a way back on that one.
The trouble with using software like any relational database engines is that people seem to have forgotten the primary rule of "Primum non nocere" (first, do no harm). It seems that many have taken the simple attitude of "Well, we have to do SOMETHING to continue to be relevant" when it may actually be better to do nothing.
Hence, the old adage of "Change is inevitable... change for the better is not".
In the case of SQL Server, it would be really nice if they fixed some previous "improvements" instead of breaking things with supposed new improvements and do those fixes a lot more quickly than what they did with the likes of STRING_SPLIT() (which should have never been released in the sorry condition it was originally release in). And then there are things like GENERATE_SERIES(), which took them 14 years to come to their senses on but still didn't release a version that's as capable as it is in other relational engines.
And then there's a whole list of "fun" things like how ONLINE index rebuilds would corrupt some clustered indexes and how index REBUILD and REORGANIZE have been behind the times since they day they were originally released as DBCC routines and later "modernized".
Yeah... I realize that making bullet-proof software is really difficult but the software shouldn't be firing new bullets at the people that bought it and it should take literally decades to realize that the software is actually firing bullets.
Another "good" example of all that is the "improvements" they made to this site by moving to WordPress. Many of us tested the hell out of the new site before they released it and identified the many problems that still persist today but, they still released it with a whole lot of those problems still occurring and some that have gotten worse. It has cost this site by driving away a whole lot of heavy hitters and a lot of posters in general.
A lot of us are now being told that we should send an email to the folks in marketing to try to get them to drive some repair time home. I've been "conditioned" to believe it wouldn't make any difference and would be a total waste of my time... just like it was when they first release the "improvements".
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2024 at 4:52 pm
I remember SQL Server build 2039 (SP4) where we all had to apply hotfix build 2040 as soon as it came out. I can't remember exactly what the problem was with 2039 though I think it had something to do with not recognising 50% of the available memory.
For software in general it is relatively easy to run a suite of tests for unit & integration. Performance tests introduce a whole new challenge. Not only do we have to try and replay a production load (somehow, God knows how) but we have to choose a day that is realistic but busy.
Then there's the interpretation of the results. If you test end-to-end then you will get to the point where the weakest component fails. You won't have the breaking point of the other components. If you try and test components in isolation you'll only have their individual performance metrics but not whether their output would overwhelm something else.
When SSDs 1st came out we got really excited at the prospect of putting them in our SAN. Yippee, all our IO problems are no more! Only, the header unit of the SAN shut the whole shebang down. It turned out that the SAN header unit to cope with 6 SSDs in the array but any more and the IO characteristics overwhelmed it.
Sometimes a slow component is acting as protector to things downstream of it. It puts me in mind of one of my sons thinking that he could user the Karcher pressure washer to clean slugs off the lettuce in half the time.
August 1, 2024 at 2:32 pm
I remember SQL Server build 2039 (SP4) where we all had to apply hotfix build 2040 as soon as it came out. I can't remember exactly what the problem was with 2039 though I think it had something to do with not recognising 50% of the available memory ...
Wow, I have no recollection of SQL Server 2039.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 1, 2024 at 2:42 pm
I assume SQL 2019 for David, but I do think it's hard to regression test. I don't know how many of us have a decent set of things to test from a workload. Most users don't. They'll click a screen, but that aren't really testing if it's faster or slower, or not necessarily going through all the steps they might perform on a regular basis.
However, having at least some smoke tests that look for certain functions to work well, check for known or expected outputs is important. Ideally, we'd be looking for some known number of reads/writes as well to gauge performance. Setting all that up, however, isn't simple.
I also think that for lots of core software (OS, SQL, .NEt, others), the vendors have a sales issue in that they need to keep building things, and also they build a lot for the general 80% in the middle. Lots of stuff works well and faster, despite what Jeff notes. However, it might not work in his environment and there might be poorer performance. The challenge of general, COTS v bespoke software is real.
What I more wish is that there were more knobs to help tweak or turn off things not working well for me. Over time I know those knobs likely need to go away across versions as that's tech debt, but I'd also hope people report, MS/ORCL/etc. investigates, and then finds a way to mitigate what they broke.
August 1, 2024 at 3:09 pm
David.Poole wrote:I remember SQL Server build 2039 (SP4) where we all had to apply hotfix build 2040 as soon as it came out. I can't remember exactly what the problem was with 2039 though I think it had something to do with not recognising 50% of the available memory ...
Wow, I have no recollection of SQL Server 2039.
Just for clarity, SQL Server 2000, build 2039 was SP4.
August 1, 2024 at 3:19 pm
It would be interesting to see whatever the modern equivalent of Perfmon and extended events reveals for Jeff.
I've been working with an open-source maintainer to diagnose a strange behaviour that I was observing. We both carried out detailed experiments sharing both method and results. They couldn't be reproduced at the maintainers end and, for a while, we were flummoxed.
All my recent work has me emphasising to my colleagued the importance for any apps we write to emit metrics, trap specific errors and write meaningful log messages. If corners must be cut, these are corners you don't cut.
August 1, 2024 at 4:03 pm
I have a slightly used copy of Foxpro I can let you have at low price......
There are rippling effects to staying on old software. It takes old (not safe) OS to run old SQL versions. Assuming you are not being audited or under any kind of PCI-DSS compliance, you still have huge security risks. Then you have issues of "no vendor support". Try calling Microsoft about your SQL 2000 issue....probably get routed to someone who was not even born when that software was released only to hear them laughing as they recite from their scripts. Then you decide to upgrade to latest (newer versions), good luck with all of your stored procedures and code that now have to be re-written due to the changes in syntax and database engines.
I am not one to incur or increase operational costs unnecessarily, but safety, security and stability are key factors to weigh before digging in one's heels to remain on old software/hardware.
Jon
August 2, 2024 at 2:54 pm
I assume SQL 2019 for David, but I do think it's hard to regression test. I don't know how many of us have a decent set of things to test from a workload. Most users don't. They'll click a screen, but that aren't really testing if it's faster or slower, or not necessarily going through all the steps they might perform on a regular basis.
However, having at least some smoke tests that look for certain functions to work well, check for known or expected outputs is important. Ideally, we'd be looking for some known number of reads/writes as well to gauge performance. Setting all that up, however, isn't simple.
I also think that for lots of core software (OS, SQL, .NEt, others), the vendors have a sales issue in that they need to keep building things, and also they build a lot for the general 80% in the middle. Lots of stuff works well and faster, despite what Jeff notes. However, it might not work in his environment and there might be poorer performance. The challenge of general, COTS v bespoke software is real.
If there were smoke tests for common functionality it would be a general improvement for backend systems imo. Custom code is always necessary but a lot of times with common functionality there's also no reference source code or open standard.
The vendors tend to make offerings around a single product (like os, database, server code). "Pre-integration" happens within product boundaries, probably because afaik that's how people buy software. .NET has the Entity Framework for database integration. EF is an implementation which offers basic functionality without creating SQL code. SQL Server has Microsoft.Data.SqlClient which is a library of code without any specific implementation. At least that's how I see it. Imagine a common set of unit tests across implementations of both approaches.
What I more wish is that there were more knobs to help tweak or turn off things not working well for me. Over time I know those knobs likely need to go away across versions as that's tech debt, but I'd also hope people report, MS/ORCL/etc. investigates, and then finds a way to mitigate what they broke.
What I wish there were more of is useful, basic functionality implementations, like WordPress I guess. If there were unit tests to go with the examples that would be amazing too
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 3, 2024 at 3:25 pm
I also think that for lots of core software (OS, SQL, .NEt, others), the vendors have a sales issue in that they need to keep building things, and also they build a lot for the general 80% in the middle. Lots of stuff works well and faster, despite what Jeff notes. However, it might not work in his environment and there might be poorer performance. The challenge of general, COTS v bespoke software is real.
Do you have a single, fairly decently equipped machine with an instance of either SQL Server 2016 or 2017 AND SQL Server 2022 on it? I have some additions to make to it but I'll be happy to put together a package to send you containing some code to test what has happened with SQL Server 2022. It could also be 2019 but I cannot make actual claims there because I never installed it due to all the claims and other's proofs that that's were the performance issues for 2022 actually started and still have NOT been address. Microsoft won't even acknowledge the problems!
Lots of stuff works well and faster, despite what Jeff notes.
I remember having to explain to MS people why they needed to do what Erland Somarskog requested for a series generator back in 2008 or 2009. They didn't know what such a thing would be used for. And so we waited for about 14 years for them to finally figure out what their competitors already knew. And then, what they came out with, is only half of what the competition has (the MS version will NOT do dates and times) AND it overrides minimal logging when being used as a row source to build multi-million row test tables.
How about Recursive CTEs that produce a series? They're slower than the well written While loops they were supposed to eliminate the need for AND the consume 8 times the number of logic reads, as well.
How about PIVOT? They have a wonderful PIVOT in MS ACCESS... the PIVOT is SQL Server is much more difficult to setup and use for anything with any sophistication. It won't ever allow for easy row totals and you can basically forget about column totals without jumping through a hoop.
FORMAT? Better than CONVERT? Only in the formats it offers. If you use it for anything big, even much more complicated CAST/CONVERT blows the doors off of FORMAT for performance.
And how long did it take the to come out with a string splitter? And, when they did, why didn't it have a key column? And, once we raised hell about it and propped it up on a stick, why did it take them 6 years to fix it and still not have native CSV capabilites?
And, how about BCP and BULK INSERT. Why did it take them a proverbial million years to have it natively handle one of the oldest and frequently used data transmission formats (CSV) ever?
And they broke the newer temporal datatypes. They made them even less ANSI/ISO compliant by removing the ability to do direct date math like DATETIME has always had the capability of doing. When MS realized the grand error there, they came out with DATEDIFF_BIG() to try to make up for that but... do you think they finish the job by releasing a DATEADD_BIG() at the same time?
And considering that people are actually trying to work with UNIX and MS is trying to bandwagon on the profits there, do you think that, since they don't have a DATEADD_BIG() that they'd come out with a function to convert UNIX time stamps to DATETIME2, etc?
And don't get me started on the pitiful functionalities of ALTER INDEX REBUILD and REORGANIZE. They've been broken since day one! They were rewritten a decade after Peter Norton's methods of sector defragmentation. Microsoft was incredibly short sighted there. When they did try to make up for it a bit in 2012 by coming out with an ONLINE ability, they fixed nothing else AND their ONLINE attempts were prone to corrupting Clustered Indexes.
And, how about 2012... it was regressively broken until SP3. And then there have been many SPs and CUs that broke some what I consider to be some easy stuff to test and apparently didn't test adequately or at all for.
And remember when MERGE first came out and the years after that? Wasn't THAT "fun"?
And how about when Extended Events first came out that the fact that they STILL convert the output to XML "tables" and the GUI still showed only a smidgen of the code on a single line as of 2016. I don't know if they ever fixed that because I stopped using it. It was proof of the adage that while "Change is inevitable, change for the better is not".
And does anyone actually have any proof that the "new" Cardinality Estimator actually improved anything? Every time I try turning off the Legacy CE, the system tanks for performance.
There's a whole lot more and Microsoft isn't the only company raking in more of our money while breaking things. I use the "Fishing Lure" example for MS and a lot of other software products. Why is it that they come out with new fishing lures every year? Do they actually help catch more and bigger fish?
The answer is no... and they're not being redesigned to do any such thing. Fish don't have any money... the new lures are actually designed to catch fishermen... every year... and without fail.
The software and cloud companies are doing the same thing. "New and improved" is usually the beginning description for "same shit, different day" except it's going to cost you to upgrade, cost you to learn different methods that didn't need to be changed, cost you more for features that were removed because marketing wasn't paying attention, or, marketing was paying attention and will add the feature back in to get you to buy the next "New and improved" version. 😉
Then, you have people that were trained by other people that don't know things like a 4 digit string being used for the starting date or a year, people that don't actually know how to convert a DATETIME2 value to a DATETIME without "silent failures" and that using an rCTE isn't actually a "Best Practice" for generating a series or that doing an XML conversion to split a string is actually one of the slowest methods ever because they also don't know how to make the right kind of test data, etc, etc, etc.
And then there's the failures of the forum software of this very site... even though many of us tested the hell out of it and told them everything that was wrong with it, they still released it with all those faults.
And, MS doesn't have a corner of the market of "insufficiencies"... After decades, they finally added a FROM clause to the UPDATE statement earlier this year. 😀
Rant complete... regard all further Technical Debt because almost no one wants to fix it and "experts" appear to be very happy to continue to pay for it over and over and over again. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply