SQL Server 2022 Preview Storage Engine Capabilities (Ep. 4) | Data Exposed

In this episode of Data Exposed with David Pless and Anna Hoffman, we will walk through a quick overview of the storage engine improvements that improves performance and scalability such as tempdb improvements, advances in capacity management and maintenance, and enhancements to SQL Server scalability for systems with large memory and processing power.

0:00 Introduction
1:00 The next step for SQL Server
2:51 SQL Server 2022
4:19 tempdb Performance Critical for Scalability
7:01 Shrink DB With Low Priority
9:22 XML Compression
11:36 Accelerate Database Recovery Improvements
14:20 Current Buffer Pool Scan Operations

✔️ Resources:
Introducing SQL Server 2022:
What’s new in SQL Server 2022:

📌 Let's connect:

🔔 Subscribe to our channels for even more SQL tips:

#AzureSQL #SQLServer #SQLServer2022

[starmole]

Bravo. So excited by what MS are doing with SQL Server and Azure!

[joechang]

database file shrink is single threaded, and one very large files, it can take forever. Index (re)builds are parallel, including rebuilds to different filegroups. On powerful storage systems, not Azure, the index rebuild method to move the larger objects out of the file you want to shrink allows the shrink to run in a reasonable period. On xml, I have been converting to varchar(max) to apply compress. The real penalty for fat xml is on the client. Many people do not bother to implement the five parameter SqlDataReader GetBytes & GetChars method, whereas the GetString method implements successive doubling of buffer until the access succeeds?

[nilsfrahm]

Can someone explain why do we need to tune Sql for certain queries, in 2022? Sql comes with machine learning but what about self-tuning the db with help from AI?
You should configure some space limits for extra indexes/statistics, hit a button to record a reference workload, let it do its magic and self tune. Then you can use for real apps.
You don't see people driving cars with debugger on dashboard tuning their motor timings, right? It's already optimized when you buy the car...

[heathbruce]

Does anyone ever discuss the huge size of bother the data stored on disk and the applications size (yes more than one) vs it's open source counterpart?

I know from my own experience it took 2019 to come out to equal the speed and performance (on a single device) to equal that of it's open source cousin.

Any tweaks that can be shared would be appreciated.

[nilsfrahm]

I don't understand why so late. Was SQL 2016 not tested on large servers and were those issues not known? How is it possible to sell licences for MsSQL per CORE and then have the db slow because some operation is single thread, or it locks on some shared object? It's a server software supposed to scale to the limits of the machine it runs on.
How can we face the clients and tell them, oh, SQL is slow because it locks, it's slow because many concurrent queries stop waiting in line a shared resource etc...
Clients do not care, and it seems even more hardware doesnt solve the problem.
Features added to SQL2022 are just what is known today, who knows what bottlenecks will be found in 2024.
Remember when SQL2016 was out, it was advertized as being greatest and fastest, now (or before) we discover otherwise...
There should be a standard benchmark and they should publish results for each SQL version, to understand better.