Tempdb Performance Improvements in SQL Server 2022 are Dramatic

  By ,

Tempdb is always a topic for me whether it’s in my sessions or blogs I have written. However, I’ve never been so excited about it then I am when it comes to the dramatic performance changes introduced in SQL Server 2022. THEY HAVE SOLVED ONE OF OUR BIGGEST PERFORMANCE BOTTLE NECKS, System page latch concurrency.

In SQL Server 2019 they addressed what’s known as metadata contention, when pages that belong to systems object take page latches while updating tables that track table metadata by introducing memory optimized tempdb. Additionally, the product team made improvements to object allocation contention. This is the contention for metadata pages used to manage space allocation in data files known as page free space (PFS) pages. Creating or destroying temp tables and deallocating them in tables like sys.objvalues for example. Lastly, there were more improvements to temp table caching which allowed us to better reuse unaltered temp tables. All these changes delivered tempdb performance improvements, but still left us with the biggest bottleneck. Which is the way latches are created on system pages.

In 2022 SQL Server improvements addressed just that and changes the way global allocation map (GAM) and (shared global allocation map) SGAM page latches are done. Instead of updating these through an update latch they now use a shared latch which removes nearly all of the contention as seen in the images posted by Microsoft demonstrating the effect of this change.

BEFORE Upgrade Using SQL 2019

AFTER Upgrade Using SQL 2022

You can clearly see the massive impact of this. What’s even better is that these new enhancements will benefit us all especially those with heavy tempdb workload by default. If you simply upgrade to SQL Server 2022 you can immediately take advantage of these enhancements with no additional work or overhead by you. The results are amazing to say the least.

David Pless, Senior Program Manager, Microsoft does a fantastic job of explaining all the new changes in this article on the Microsoft SQL Server Blog. Along with reading that I highly recommend watching this video to see a demo on how drastically this changes everything.

https://www.youtube.com/watch?v=2FYyOk27ZxM

I cannot wait to see this in production as clients start to move to SQL Server 2022.

 

About Monica Rathbun

Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform and Microsoft Certified Solutions Expert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips. You can find Monica blogging at sqlespresso.com 
Tagged  , , .
Bookmark the permalink.

4 Comments

  1. Pingback: Tempdb Performance Improvements in SQL Server 2022 are Dramatic – SQLServerCentral

  2. Pingback: Improvements to tempdb Performance in SQL Server 2022 – Curated SQL

  3. LondonDBA
    February 3, 2023 at 5:21 am

    Thanks for this post, great tempdb improvements for sure. In David Pless’s blog post he says ‘With these improvements in SQL Server 2022 we allow concurrent updates to the GAM and SGAM under a shared latch rather than using the update latch.’ It’s great that a technical implementation can now exclusively update a resource by acquiring a shared latch but the natural follow up question is is there a blog post or research paper detailing exactly how this is done ? Always thought you had to acquire an exclusive latch if you wanted to update a resource.

  4. Pingback: Tales From The Field Weekly Wrap Up for the Week of 01-31-2023 – SQLServerCentral

Comments are closed