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.
The post Tempdb Performance Improvements in SQL Server 2022 are Dramatic appeared first on A Shot of SQLEspresso.