Discover the latest scalability enhancements in SQL Server 2022, the most cloud-enabled release to date, in this 51-minute conference talk from SQLBits. Explore default improvements and new capabilities that database administrators can enable to create a highly available and performant platform. Delve into tempdb performance optimization, buffer pool parallel scan operations, columnstore index improvements, and optimized transactional locking mechanisms. Learn about critical aspects such as object allocation contention, metadata contention, and various types of tempdb contention. Gain insights into current best practices for tempdb and understand how SQL Server 2022 addresses these challenges. Presented by Ajay Jagannathan, Principal Group Program Manager for Azure SQL Database and SQL Server Engine PM team, and David Pless, this talk provides valuable knowledge for database professionals looking to leverage the latest scalability features in SQL Server 2022.
Overview
Syllabus
Intro
SQL Server 2022 Engine Scalability Improvements
tempdb Performance Critical for Scalability
What is stored in tempdb?
Object Allocation Contention
Metadata Contention - The Sequel
tempdb-related enhancements in SQL Server 2019
Types of tempdb Contention
SQL Server 2022 Improvements
Current traditional tempdb Best Practices
Current Buffer Pool Scan Operations
SQL Server 2022 - Buffer Pool Parallel Scan
Buffer Pool Parallel Scan Diagnostics
Columnstore Index Improvements
What is Transactional Locking in SQL?
Optimized Locking An improved transaction locking mechanism that reduces lock memory consumption and blocking amongst concurrent transactions
Lock After Qualification (LAQ)
Additional Engine Improvements
Taught by
SQLBits
