Sql server 2022 - Memory optimized Tempdb Metadata
Clement Huge
![Clement Huge]()
Clement Huge
SQL server Blackbelt ex-googler | (I)-[:LOVE]->(graph) Database migration and modernization partner with GCP | Retail expert @ Retail Cities
A more obscure and not used functionality is to Consider putting in-memory your system metadata of tempdb when you hit page waits on system objects within your tempdb.
This video explained you how and more , when you should consider it.
Senior Database Engineer at Zillow Group
1 年Tested this out and it works pretty well. However, I noticed that the default setting in SQL Server 2022 uses slightly more CPU (40%) than in SQL 2019 (30%), even while there is significantly less Latch Wait Time (22,000 ms in SQL 2022 vs. 100,000,00 ms for SQL 2019). But when I enabled tempdb metadata optimization, CPU utilization went up to 78%! Although there was orders of magnitude lower Latch wait contention: 897 ms. See perfmon results of each test in order (left to right): 1) SQL 2019 mode 2) After SQL 2022 upgrade (with no other config changes) 3) After enabling tempdb metadata optimization (ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;) Curious to know if anyone else has seen this behavior? NOTE: This was on a 16 CPU machine