MicrosoftPamLahoud Oops I missed this bit: -
> While you may not have encountered this problem frequently, we do see many customers facing this issue, particularly on servers with a high core count (e.g. 128 cores or more). The hope is that this new flow control mechanism may also be used to improve throughput in other areas of the engine that have a tendency to form convoys under contention.
I assure you I have seen this problem occur in production. We have always resolved the issue at the companies that I was at during that time. I have seen the issue in 2008 R2, 2012 and 2014 in various production environments. There are many solutions and I honestly still cannot see why Microsoft would spend time engineering a solution to a trivial problem. I mean you could simply use a UUID and the random nature of where the insert takes place would resolve the issue. Something like 128 partitions on a table where you use a modulus of the identity or sequence would also work really well (It would be even better if we had true partition level stats, I don't recall seeing that in 2019 yet, maybe I'm wrong here? I don't have 2019 in production so my caring is limited right now).
If you have customers that cannot architect a database correctly for performance then maybe it would be a good idea to have a proper high end qualification, like the MCM once was, again? There is nothing hard about scaling almost any workload. Just ignorance gets in the way. If Microsoft are planning on coding a universal solution to customer ignorance then they'll get frustrated after trying for a very long time indeed.
There is no need to code a shock absorber or anything like it. The tools are already available and have been since the introduction of a UUID (So a long time ago) to deal with this in almost any situation. For the rare cases where this isn't enough, there are other solutions available already. I wonder if you can scale the inserts with this new "Solution" to being superior to simply using a UUID? I mean I was able to drive over a million inserts a second on our test server back on SQL 2008 R2. If you then partition the table as well (Several solutions to this) you ended up pinning the log long before you hit latch contention on the table. Now that you can put the tail of the log on persistent memory this would most likely be resolved. At which point I have also been able to pin the log cache access spin lock on serveral occasions. Yes I've seen that in production as well. Also I've seen the log flush spin lock pinned in production.
Beyond any of that you would want to use in memory tables or avoid an RDBMS and write to a caching layer first and persist on a regular basis to batch your inserts. This would resolve the issue anyway. At that point, however, you might as well just simply move to NoSQL and the problem is resolved (Well as long as you have people who can architect a solution and the likes).
There are a load of problems in SQL Server and useful ways to expand it and yet, for some reason, Microsoft love to code fixes to non-issues. I don't get why you waste engineering effort there. Heaps have been around for ages and have never gotten any love from Microsoft despite them having some very useful properties. I mean how long did it take before you could rebuild a heap? Wasn't that in 2008 R2? So a long time. Then Microsoft managed to do a rebuild of a heap in such a way that with each rebuild not only does the heap retain all its previous IAM pages but it gets new ones. So in time you end up with an IAM chain in the thousands, even for a very small heap. If you don't access you heap for a while then the next time you try SQL Server will load the entire IAM chain into the cache before accessing a single record (Even with a select top (1) * from .....) which can take 20 seconds or even lead to a timeout just to load the IAM pages - stunning. Of course the solution there is to use a clustered index. A bit like preventing edge insert page latch contention is resolved with random inserts. However one gets attention from Microsoft and the other is ignored (And for a lot longer).
You also have an issue with the Query Store, I haven't checked to see if it's resolved with 2019 yet (Like I wrote above, we don't have it in production yet) where if you have a lot of dynamic SQL (Written long before I got to the company I am currently at) then you end up with the in memory hash table growing out of control. You cannot then clear the entries in this hash table with anything other than a restart of SQL Server - not a good idea in a production environment. Let's not even bother to look at the cluster f*** that is XML in SQL Server. However rather than sort that we got JSON (In a relational DB, why oh why oh why?). Why not just provide simple wrappers to decompose and recompose these so that they can be stored in normal tables. This would allow simple indexing (Awesome) by any DBA, developer or even the existing tools without any need to re-write anything. Oh yeah, that's basically how XML indexes, just you cannot add your own indexes because that would be stupid.
Other simple examples: -
This is what I mean with wasted engineering effort. Please don't just cater for people who don't design solutions, cater for the people who would actually like to use SQL Server as an RDBMS. Make it easy to for what it is not a one size fits all jack of all trades that slowly becomes irrelevant in most every possible scenario where you might want to use it. You have other products that can do that better in your Azure offerings.
