You're reading from Introducing Microsoft SQL Server 2019 Reliability, scalability, and security both on premises and in the cloud

Product type Paperback

Published in Apr 2020

Publisher Packt

ISBN-13 9781838826215

Length 488 pages

Edition 1st Edition

Languages

SQL

Tools

SQL Server

Concepts

Database Administration

Authors (8):

Allan Hirt

Dustin Ryan

Mitchell Pearson

Kellyn Gorman

Dave Noderer

Buck Woody

Arun Sirpal

James Rowland-Jones

+4 more

View More author details

Table of Contents (15) Chapters

Preface

About Microsoft SQL Server 2019

1. Optimizing for performance, scalability and real‑time insights

2. Enterprise Security FREE CHAPTER

3. High Availability and Disaster Recovery

4. Hybrid Features – SQL Server and Microsoft Azure

5. SQL Server 2019 on Linux

6. SQL Server 2019 in Containers and Kubernetes

7. Data Virtualization

8. Machine Learning Services Extensibility Framework

9. SQL Server 2019 Big Data Clusters

10. Enhancing the Developer Experience

11. Data Warehousing

12. Analysis Services

13. Power BI Report Server

14. Modernization to the Azure Cloud

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.