TempDB enhancements

We have introduced another scalability enhancement with memory-optimized TempDB metadata. Historically, TempDB metadata contention has been a bottleneck to scalability for workloads running on SQL Server.

The system tables used for managing temp table metadata can be moved into latch-free non-durable memory-optimized tables.

Enabling memory-optimized TempDB metadata

Enabling this feature in SQL Server is a two-step process:

• First, alter the server configuration with T-SQL
• Restart the service

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED tempdb_METADATA = ON

The following T-SQL command can be used to verify whether tempdb is memory-optimized:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

Limitations of memory-optimized TempDB metadata

There are a few limitations associated with using this new feature.

• Toggling the feature on and off requires a service restart.
• A single transaction may not access memory-optimized tables in more than one database. This means that any transactions that involve a memory-optimized table in a user database will not be able to access TempDB System views in the same transaction. If you attempt to access TempDB system views in the same transaction as a memory-optimized table in a user database, you will receive the following error:

  A user transaction that accesses memory-optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

• Queries against memory-optimized tables do not support locking and isolation hints, so queries against memory-optimized TempDB catalog views will not honor locking and isolation hints. As with other system catalog views in SQL Server, all transactions against system views will be in READ COMMITTED (or, in this case, READ COMMITTED SNAPSHOT) isolation.
• There may be some issues with columnstore indexes on temporary tables when memory-optimized TempDB metadata is enabled. It is best to avoid columnstore indexes on temporary tables when using memory-optimized TempDB metadata.