SQL2019

Great New Features in SQL2019:

TEMPDB:

** The below test was run with cu3 installed. When I ran the test originally on RTM I DIDN’T SEE ANY IMPROVEMENT IN THE PAGELATCH WAITS RESULTS **

With the edition of sql2019 comes what can only be described as TEMPDB’s biggest improvement – Memory optimized Tempdb Metadata.

This will reduce the old problem of LATCH CONNTENTION amongst PFS and SGAM pages resulting in high waits stats for PAGELATCH XX.

Here is a demo of this cool new feature. It will show that when the setting is enabled it gets rid of the pagelatch wait types.

So the setting you need to change is:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON

To simulate a workload I used a utility names ‘Ostress‘ The code used to run this tool will generate 5000 connections to my sql server and run a .sql file that I saved to my file system.

The tsql code that i will run using Ostress is:

CREATE TABLE #pagelatchtest (id int, Col char(6000))

INSERT INTO #pagelatchtest VALUES (1, ‘PageLatchTest’)

I saved the above file to:

D:\Scripts.

Next, create a perfmon trace to capture the following:

Object:SQL SERVER:Wait Statistics

Counter: Page Latch waits

Instance:ALL

Average wait time (ms)

Cumulative wait time (ms) per second

Waits in Progress

With the new feature turned off:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF

I ran ostress like so:

When i looked at perfmon i saw the following:

Now make the change to turn on the new Memory Optimized Tempdb feature:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON

You will have to restart the SQL SERVER engine.

Now when I ran the same Ostress command again perfmon looked like this: