System Page Latch Concurrency Enhancements in SQL Server 2022 (Ep. 6) | Data Exposed

Published Mar 03 2022 06:00 AM 885 Views
Microsoft

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.

 

Watch on Data Exposed

 

Resources:

 

View/share our latest episodes on Microsoft Docs and YouTube!

%3CLINGO-SUB%20id%3D%22lingo-sub-3243244%22%20slang%3D%22en-US%22%3ESystem%20Page%20Latch%20Concurrency%20Enhancements%20in%20SQL%20Server%202022%20(Ep.%206)%20%7C%20Data%20Exposed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3243244%22%20slang%3D%22en-US%22%3E%3CP%3EOver%20the%20past%20several%20SQL%20Server%20releases%2C%20Microsoft%20has%20improved%20the%20concurrency%20and%20scalability%20of%20the%20tempdb%20database.%20Starting%20in%20SQL%20Server%202016%20several%20improvements%20address%20best%20practices%20in%20the%20setup%20process%2C%20i.e.%20when%20there%20are%20multiple%20tempdb%20data%20files%20all%20files%20autogrow%20and%20grow%20by%20the%20same%20amount.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdditionally%2C%20starting%20in%20SQL%20Server%202019%20we%20added%20the%20memory%20optimized%20metadata%20capability%20to%20tempdb%20and%20eliminated%20most%20PFS%20contention%20with%20concurrent%20PFS%20updates.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20SQL%20Server%202022%20we%20are%20now%20addressing%20another%20common%20area%20of%20contention%20by%20introducing%20concurrent%20GAM%20and%20SGAM%20updates.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20previous%20releases%2C%20we%20may%20witness%20GAM%20contention%20different%20threads%20want%20to%20allocate%20or%20deallocate%20extents%20represented%20on%20the%20same%20GAM%20pages.%20Because%20of%20this%20contention%2C%20throughput%20is%20decreased%20and%20workloads%20that%20require%20many%20updates%20to%20the%20GAM%20page%20will%20take%20longer%20to%20complete.%20This%20is%20due%20to%20the%20workload%20volume%20and%20the%20use%20of%20repetitive%20create-and-drop%20operations%2C%20table%20variables%2C%20worktables%20that%20are%20associated%20with%20CURSORS%2C%20ORDER%20BYs%2C%20GROUP%20BYs%2C%20and%20work%20files%20that%20are%20associated%20with%20HASH%20PLANS.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Concurrent%20GAM%20Updates%20feature%20in%20SQL%20Server%202022%20adds%20the%20concurrent%20GAM%20and%20SGAM%20updates%20capability%20to%20avoid%20tempdb%20contention.%3C%2FP%3E%0A%3CP%3EWith%20GAM%20and%20SGAM%20contention%20being%20addressed%2C%20customer%20workloads%20will%20be%20much%20more%20scalable%20and%20will%20provide%20even%20better%20throughput.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%20has%20improved%20tempdb%20in%20every%20single%20release%20and%20SQL%20Server%202022%20is%20no%20exception.%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2F2FYyOk27ZxM%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%3CFONT%20size%3D%225%22%3EWatch%20on%20Data%20Exposed%3C%2FFONT%3E%3C%2FA%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CEM%3EResources%3A%3C%2FEM%3E%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fdatabases%2Ftempdb-database%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3Etempdb%20database%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Ftroubleshoot%2Fsql%2Fperformance%2Frecommendations-reduce-allocation-contention%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3ERecommendations%20to%20reduce%20allocation%20contention%20in%20SQL%20Server%20tempdb%20database%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Faka.ms%2Fsqlserver2022%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3ELearn%20more%20about%20SQL%20Server%202022%20%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Faka.ms%2FEAPSignup%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3ERegister%20to%20apply%20for%20the%20SQL%20Server%202022%20Early%20Adoption%20Program%20and%20stay%20informed%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Faka.ms%2Fsqlserver2022mechanics%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3EWatch%20technical%20deep-dives%20on%20SQL%20Server%202022%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Faka.ms%2Fdataexposed-sqlserver2022%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3ESQL%20Server%202022%20Playlist%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EView%2Fshare%20our%20latest%26nbsp%3Bepisodes%20on%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fchannel9.msdn.com%2FShows%2FData-Exposed%3FWT.mc_id%3Ddataexposed-c9-niner%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMicrosoft%20Docs%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fplaylist%3Flist%3DPLlrxD0HtieHieV7Jls72yFPSKyGqycbZR%26amp%3BWT.mc_id%3Ddataexposed-c9-niner%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EYouTube%3C%2FA%3E!%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3243244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MSDataExposed.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F262847i3424FA40DEB7BE91%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MSDataExposed.jpg%22%20alt%3D%22MSDataExposed.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECatch%20the%20next%20episode%20in%20the%20SQL%20Server%202022%20series%2C%20this%20episode%20focusing%20on%26nbsp%3B%3CSPAN%3ESystem%20Page%20Latch%20Concurrency%20Enhancements.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3243244%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQL%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Mar 03 2022 06:00 AM
Updated by: