April 24, 2021 at 12:00 am
Comments posted to this topic are about the item Looking Forward to SQL Server vNext
April 24, 2021 at 8:43 am
I am always looking forward to vNext and getting it into Production. For me it is all about enabling the business (and IT) to take advantage of the best technology available. The converse is a cost of loss of opportunity if IT have to find time to do an upgrade when the business needs a feature in a recent release. (I now work for a non-profit and even in 2021 the discounts we get for in-house deployment make moving everything to the cloud expensive. A real shame as I have done cloud elsewhere and see it as the best place to be.)
Keeping up with the releases worked out well with SQL2017 when we deployed Distributed Availability Groups and significantly simplified our DR process. SQL2019 function inlining has really helped performance in some areas. PowerBI and columnstore is getting heavily used but some features, eg Graph Table, are still solutions waiting for the right problem.
vNext? bring it on!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 24, 2021 at 10:45 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2021 at 6:13 pm
Jeff, you are right!
The claim by this article that Microsoft is listening is just false. I am of the same opinion on topics you have mentioned. These were logged on old 'connect' and new 'user voice' by numerous people, including myself.
In addition,
JSON feature is 1/2 baked. Formatting is missing, binary values cannot be transferred (binary base64 encoded binaries 4000 char limit and WITH(col varbinary(max)) does not work), OPEN JSON is missing index , JSON_VALUE value cannot be typed like XML value and so on.
Xpath, xquery and xml schema support are of circa 2005, forgotten.
New cardinality estimator is hit and miss, ever after all reworks.
In-memory tables are feature deficient, missing support for several key data types, like sql_variant, xml, clr types and lengthy list of other limitations. Natively compiled procedures still don't support MERGE. All announced with such fanfare and just forgotten, no more love.
Azure is unusable. No CLR, SQL Broker and lengthy list of other missing features.
SSMS gets no QA testing: release 18.9 just to discover that IntelliSense does not work.
I am watching issues that users log on 'user voice'. Some just cause me to shake my gray head in utter unbelief. 'User voice' itself is a disaster, dumping ground for support cries, not product issue reports, like the former connect was (at least recognized recently as an issue by MS).
All these, above, are logged, 'under review' for years by now, no action from Microsoft.
It seems that Microsoft has lost its way. It releases features but never finishes these; throws against the wall, and watches it go down.
This is what I have observed in 27+ years working with Microsoft SQL Server. Just sad.
Thanks
Vladimir
April 27, 2021 at 8:21 pm
While I understand at least one reason why CTEs don't automatically generate a spool file for re-use, I have always wondered why there wasn't an object that you could re-use multiple times in a session. A temporary #View would work perfectly for those situations where you don't want to write out to a #temp table.
Another thing that would be nice. The ability to define a partition/order by sequence by name, that could then be referenced by all windowed functions in the query. It's tiresome to cut and paste it for sum(), min, max, etc. over and over.
Also, I echo Jeff's sentiments about the need for an order column with String_Split() and a built-in object with the functionality of a tally table (that only shows up as a single box in the query execution plan.)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply