data-konzept

'Problem:
For as long as I have worked with SQL Server, and on almost every system I've ever managed, tempdb has been a bottleneck in one way or another. Microsoft has made a lot of improvements over the years to help resolve bottlenecks with access to PFS, GAM, SGAM, and other resources in tempdb. In no particular order, a few of these enhancements include:

• Optimized number of data files during setup
• Eliminated Trace Flag 1117 and 1118 to help with object allocation contention
• Eager writes to reduce impact of bulk operations
• Multiple optimizations to improve metadata contention
• Accelerated Database Recovery, which shifts version store overhead to user databases

While they have removed some of these bottlenecks, and generally this makes tempdb less of a performance drain, certain workloads may introduce bottlenecks elsewhere. One area where contention is still common, even after all of these improvements, is with tempdb system table metadata, where too many sessions are trying to write to system tables (like a heavy workload that creates, alters, and drops a lot of #temp tables).

Solution:
Memory-Optimized TempDB Metadata is a new feature in SQL Server 2019, as part of the "In-Memory Database" feature umbrella. Much like "Always On" isn't a specific feature, In-Memory Database is a term describing the set of capabilities that allow you to take advantage of memory to help make databases faster.'...

https://www.mssqltips.com/sqlservertip/6230/memoryoptimized-tempdb-metadata-in-sql-server-2019/