From the Article:
"The startup flags (1117 and 1118) recommended for tempdb are no longer needed as that functionality is now baked in."
Yeah... that little "trick" of theirs killed some of our stuff because there's a problem with SET IDENTITY_INSERT ON... it forces a sort of the entire table/Clustered Index (all rows, all columns, at the leaf level) in TempDB. We never had a problem with it before because only one TempDB file would grow and we had code to find and fix that little problem. Now, thanks to the "genius" move by MS to try to save the bloody world from themselves, instead of it growing 1 file to 50GB, it grows all 8 files to 50GB, which runs the tempdb drive out of room.
What I'd like to see is for MS to stop this kind of irreversible nonsense and start fixing stuff that's broken and start adding stuff that's actually useful. For example, STRING_SPLIT() has been broken since it came out (no elemental ordinal to sort by and no guarantee of order). PIVOT has sucked since the day it came out. Go look at what PIVOT in ACCESS can do and you'll agree. FORMAT() is a horrible performance issue in the even simple formatting is 43 times slower than even some very complex CONVERTs with SUBSTRING() and the like. Partitioned Tables haven no easy way to restore only the latest partitions for smaller test environments. REORGANIZE doesn't work the way most people envision it should and it is on of the primary reasons why people think that Type 4 GUIDs are a fragmentation problem. FILE SHRINK doesn't even come close to working correctly (Hello! Ever hear of Peter Norton???!!!). There's still no sequence generation function even though that request has been and still is open after 12 years. The newer temporal datatypes (DATE, TIME, DATETIME2) are no longer ISO compliant and they made DATEDIFF_BIG() to overcome those problems instead of fixing the real problem.
And they still haven't fixed the unnecessary sorting problem with SET IDENTITY_INSERT!!!
There's a ton of other fairly major issues but you get the idea.
And, for the love of Pete! Can they finally build an RTM that isn't fraught with danger? Remember things like 2014 SP1 destroying SSIS instances? Remember 2012 corrupting Clustered Indexes if you rebuilt them ONLINE and certain other common conditions existed? I cringe every time a new CU or new version comes out. 2017 RTM sucked for performance (and took several CU's to "get better") and, to be totally honest, all the supposed "automatic performance improvement" junk in 2019 has me scared to death and I'm not the only one that feels that way (go read about all the problems with the RTN and early CUs).
And, as we speak, the highest voted "feedback" item is for a bloody SSMS "Dark Mode" and so I have to say that the general public isn't helping with any of this either. In the mean time, it took them more than 2 decades to fix BCP and BULK INSERT so that they would work correctly with REAL CSVs. And then the SSMS team came out with release 17 and broke a whole bunch of stuff that still doesn't work like it used to.
MS needs to start fixing stuff that could be useful but isn't. They need to stop breaking stuff that used to work just fine. They need to fix what's broken and has been broken. And when they do release something new, they need to make sure it works correctly first time every time.
And how about Temporal Tables??? What an incredible idea! Except there's no way to automatically include WHO made a bloody update! And to make the default end date the very last instant of what DATETIME2 can handle is just nuts because there's no way to do some of the more common and totally correct criteria like WHERE SomeDTColumn >= @StartDT and SomeDTColumn < @EndDT+1 even if they were to fix the broken ISO functionality that states that direct date math must be allowed!
Yep... I realize that T-SQL and SQL Server are complex but that should be even more incentive to do it right the first time instead of this stupid march to some bloody drum-beat that says to release often. MS currently epitomizes everything that is wrong with the current and seriously incorrect concept of DevOps and the really stupid CD/CI concepts that have arisen because of the fervor to like like they're doing something. It's become almost like their new mantra is "Well... we have to do something even if it's wrong". MS has become the poster child for "living on the bleeding edge".
If you're gonna do something, do it right the first time and, for the stuff that does manage to slip through the cracks, fix it correctly and soon instead of waiting a decade or two. Be careful what you deprecate like database diagrams (which they thankfully brought back) and the debug mode in SSMS. And stop imposing limits on us to help people that don't know things, like not being able to override the mandatory imposition of the equivalent of TF 1117 on tempdb. That was a really stupid idea (especially since they haven't fixed the SET IDENTITY_INSERT problem) and every MS-MVP that voted for that silliness needs a serious session with the pork chop cannon.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)