This article is contributed. See the original author and article here.
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.
Resources:
Recommendations to reduce allocation contention in SQL Server tempdb database
Learn more about SQL Server 2022
Register to apply for the SQL Server 2022 Early Adoption Program and stay informed
Watch technical deep-dives on SQL Server 2022
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments