Over the past several SQL Server releases, Microsoft has improved the concurrency and scalability of the tempdb database. Starting in SQL Server 2016 several improvements address best practices in the setup process, i.e. when there are multiple tempdb data files all files autogrow and grow by the same amount.
Additionally, starting in SQL Server 2019 we added the memory optimized metadata capability to tempdb and eliminated most PFS contention with concurrent PFS updates.
In SQL Server 2022 we are now addressing another common area of contention by introducing concurrent GAM and SGAM updates.
In previous releases, we may witness GAM contention different threads want to allocate or deallocate extents represented on the same GAM pages. Because of this contention, throughput is decreased and workloads that require many updates to the GAM page will take longer to complete. This is due to the workload volume and the use of repetitive create-and-drop operations, table variables, worktables that are associated with CURSORS, ORDER BYs, GROUP BYs, and work files that are associated with HASH PLANS.
The Concurrent GAM Updates feature in SQL Server 2022 adds the concurrent GAM and SGAM updates capability to avoid tempdb contention.
With GAM and SGAM contention being addressed, customer workloads will be much more scalable and will provide even better throughput.
SQL Server has improved tempdb in every single release and SQL Server 2022 is no exception.
0:00 Introduction
2:21 tempdb Performance Critical for Scalability
3:48 What is stored in tempdb?
6:30 Traditional tempdb Best Practices
7:40 tempdb Performance Critical for Scalability
13:54 Demo: System Page Latch Concurrency Enhancements
19:45 Getting started
✔️ Resources:
tempdb database: docs.microsoft...
Recommendations to reduce allocation contention in SQL Server tempdb database: docs.microsoft...
Learn more about SQL Server 2022​: aka.ms/sqlserv...
Register to apply for the SQL Server 2022 Early Adoption Program and stay informed: aka.ms/EAPSignup
Watch technical deep-dives on SQL Server 2022:​ aka.ms/sqlserv...
📝 SQL Server 2022 Preview Playlist: aka.ms/dataexpo...
📌 Let's connect:
Twitter: Anna Hoffman, / analyticanna
Twitter: David Pless, / dpless
Twitter: AzureSQL, aka.ms/azuresqltw
🔔 Subscribe to our channels for even more SQL tips:
Microsoft Azure SQL: aka.ms/msazure...
Microsoft SQL Server: aka.ms/mssqlse...
Microsoft Developer: aka.ms/microso...
#AzureSQL #SQLServer #SQLServer2022