TempDB Metadata Contention

Overview

Memory optimized tempdb metadata arrived in SQL Server 2019. It makes the tempdb system tables memory optimized, which removes latch waiting on them. It is off by default and it needs an instance restart, so nobody turns it on speculatively, and the only honest way to argue for a restart window is to show how much of that waiting has already happened on this instance.

The page has a second job that matters more than the first. Two different problems both arrive as PAGELATCH waits in tempdb, and they are routinely treated as one:

	Waiting on	Fixed by
Allocation contention	PFS, GAM and SGAM pages	The number of tempdb data files
Metadata contention	Pages of sysschobjs and its neighbours	This setting, and nothing else

Sending somebody into a restart window for contention that another data file would have fixed is the one failure mode that would make this page worse than nothing. Telling the two apart is the core of the report, not a refinement of it, and it is why the report exists as a page rather than as a paragraph in a checklist.

---

Where to find it

Route	How
Server tree	Right-click the server → Instance Level Reports → TempDB Metadata Contention

The page title reads TempDB Metadata Contention for <server name>.

The menu item is not hidden on older instances. Instance level reports are registered against a server name with no version behind them, so unlike the database level pages this one cannot hide itself. On anything older than SQL Server 2019 it opens and explains itself rather than failing.

---

Requirements

• SQL Server 2019 or newer, for sys.dm_db_page_info to exist. Without it a page number cannot be turned into an object and no classification is possible at all.
• VIEW SERVER STATE on the instance, for sys.dm_os_waiting_tasks.
• VIEW DATABASE STATE on tempdb, for page resolution. Without it the sample still counts what it caught, loses the classification, and says so.
• The DBHealthHistory repository, for the history strip. Without it the strip is replaced by a line saying no history is configured.

The version test asks the instance two independent questions rather than reading a cached version number, and either one answering is enough: whether SERVERPROPERTY recognises IsTempdbMetadataMemoryOptimized, and whether sys.dm_db_page_info exists. Both test the actual capability rather than a version string. Asking twice is deliberate, because a single misremembered name would turn the page away everywhere while looking exactly like a correctly gated report.

Nothing on this page runs anything. Enabling the setting needs an instance restart and it carries real restrictions, columnstore indexes on temporary tables among them. That is somebody’s change window and an application decision, not a button on a report.

---

Nothing is sampled until you ask

Opening the page reads the setting and the history. It does not start sampling.

That is deliberate. The sampler is the only part of this report that costs the monitored instance anything, and a page that began a sixty second poll on arrival would poll every time somebody clicked past it in the tree.

Press Sample. The toolbar chooses the window: 10, 30 or 60 seconds.

Why a window rather than a snapshot. A single look at sys.dm_os_waiting_tasks on a busy instance catches something perhaps one time in five. That is not a report, it is a coin toss, and an empty grid that means “we blinked” reads identically to one that means “you are fine”. So the page polls about four times a second for the window you chose, accumulating what it sees.

Four times a second, not as fast as possible. An unthrottled loop of the same query measures about 6,800 passes a second, which would make the report a heavier load than whatever it was sent to measure. A quarter second interval also makes the counts comparable between one sample and the next, which a loop running as fast as the server happened to answer would not be.

Stop keeps what it has. Pressing Stop partway through a sixty second window keeps everything collected so far rather than discarding it. Ten seconds of a real queue is a real result.

---

The bands

The verdict is for the instance, and it is the headline rather than a column.

Band	What it means
On	SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') returns 1. Nothing to enable.
Restart pending	sys.configurations holds a value that differs from the value in use. The setting has been changed and the instance has not been restarted since.
Metadata contention	The setting is off and the sample caught waiters on tempdb system table pages. This is what it is for.
Wrong problem	The setting is off and the waiters are on allocation pages. Real contention, and this setting will not fix it.
Quiet	The setting is off and the sample caught nothing in the window.
No answer	The setting could not be read at all, which is usually permissions rather than an old build.

Restart pending is a real state, and finding it took a check. SERVERPROPERTY reports the state in effect and can say nothing about a setting that has been changed and not yet restarted into, which is exactly where somebody lands between running the ALTER and the change window. Reporting that instance as “off” would send them round the loop a second time. sys.configurations carries both halves under the name tempdb metadata memory-optimized, on SQL Server 2019 and 2022 alike, so value against value in use is the pending test. The row is still looked up by name rather than by configuration id, and a build without it reports not knowing rather than not pending.

---

Reading the chart

Each row is one page that sessions were caught waiting on, and the bar is how many times they were caught, not how long they waited. The kind is written on the chip as well as coloured, because the distinction between metadata and allocation is the one thing on this page that must not depend on telling two colours apart.

Kind	Meaning
Metadata	A tempdb system table page. What memory optimized tempdb metadata removes.
Allocation	A PFS, GAM or SGAM page. Data file count fixes this, not the setting.
Other page	An ordinary temp or internal object page. A hot temp table, not a metadata problem.
Unresolved	The engine would not describe the page. Counted on its own and never folded into anything.

The axis is linear from zero. It is deliberately not a square root axis like some of the other bar charts in the product, because the question being asked is whether one page dominates the others and a bent axis would flatter exactly the row the page is arguing about.

The strip underneath is a different measurement

The band across the bottom has its own heading and its own scale, and it is never added to the bars above it. It has two possible sources and the heading says which one you are looking at.

Heading	Source	What it can say
Page latch waits caught per day, all databases	waitsNormalized	That page latch waiting happened, and roughly when. Not where.
TempDB metadata contention caught per day	pageLatchWaits	Tempdb system table contention specifically.

The first heading is not being coy. The wait collector reads master..sysprocesses, which carries no resource_description at all, so nothing in that history can be attributed to a page. Worse, its database column is the session’s current database context, which for a session latching a tempdb system page is normally the user database it happens to be running in. Filtering that history on tempdb would miss most of the population the report exists to find, so it is not filtered on tempdb and it does not claim to be.

The second heading appears once trackPageLatchWaits has been collecting. See Where the data comes from.

---

Reading the grid

One row per page the sample caught. The grid is empty until you press Sample, which is why the documentation capture has no picture of it: there is nothing to photograph on a page nobody has sampled.

Column	What it holds
Object	The system table name where the engine resolved one, the page type otherwise
Kind	The band, with the same colour the chart uses
Page	The page itself, as database:file:page
Page type	DATA_PAGE, INDEX_PAGE, PFS_PAGE and so on, or the reason it could not be resolved
Sessions caught	Total across every pass, with a gauge against the worst page on the report
Passes seen	How many passes saw this page at all. Sessions divided by this is the average queue
Deepest queue	The most sessions any single pass found on this page
Longest wait	The longest single wait duration seen
Wait type	PAGELATCH_EX, PAGELATCH_UP and so on

The rows sort by sessions caught, largest first, with the kind used only to break a tie. Sorting by kind first would put a metadata page nobody waited on above an allocation page half the instance was queued behind, which is the wrong way round: the point of the page is the size of the contention and the kind is what to do about it.

PAGEIOLATCH waits are not included. Those are waiting on a read from disk, which is a storage problem and a different report, and including them here would inflate the one number this page argues from with waiting no tempdb setting has ever affected.

---

Right-click actions

Item	What you get
Copy enable script	The statement for this row’s kind, with what it costs, commented out
Copy sample script	The sampler itself, so you can run it and check the page is right
Copy page script	sys.dm_db_page_info for this exact page, plus the object that owns it
Copy page	Just the database:file:page identifier

An allocation row gets no ALTER. The statement would be irrelevant to what you are looking at, and offering it anyway is how a report teaches people that its advice does not need reading. That row’s script explains what allocation contention is, says how many data files tempdb currently has, and points at the TempDB Files advisor.

The enable script for a metadata row lists the three costs above the statement rather than below it:

1. It needs an instance restart. There is no online path.
2. The tempdb system tables move into memory and stay there.
3. Columnstore indexes on temporary tables are not supported while it is on, so code that creates one will fail. This needs checking against the application before the change window.

---

How to read the report

The number is sessions caught, not time lost. A single metadata latch wait is frequently under a millisecond and no sampler will catch it. A queue of sessions waiting on one is caught on every pass for as long as it lasts. So the number tracks the depth and duration of the contention, which is the thing worth knowing, and it is not a total of waiting.

An empty result is a window, not a verdict. “No contention caught in 30 seconds from 10:04” is a finding. “No contention” is a claim the page cannot support, and the page never makes it: every empty result states how long was sampled and when it started.

A large Unresolved row is worth investigating, not ignoring. It usually means VIEW DATABASE STATE was denied on tempdb. The footer says so when nothing at all could be resolved.

---

Where the data comes from

Source	Window	Used for
SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')	Now	Whether the setting is in effect
sys.configurations	Now	Whether a restart is pending
sys.dm_os_waiting_tasks	The sample window	Which pages have waiters, and how many
sys.dm_db_page_info	After the sample	What each page actually is
DBHealthHistory.dbo.pageLatchWaits	30 days	The attributed daily strip
DBHealthHistory.dbo.waitsNormalized	30 days	The unattributed daily strip, when the above is empty

Page resolution runs once per page, after the sample

Not joined into each pass, for three separate reasons:

• sys.dm_db_page_info raises error 2561 for a page id past the end of the file rather than returning no row, and inside an APPLY that kills the whole statement.
• It can return no row at all for some inputs, which an APPLY would silently drop. Resolved one at a time, a missing row becomes a visible Unresolved row instead of nothing.
• Resolving inside the loop would mean a page info call per waiting task per pass, which on a busy instance is the report generating more load than the contention it is measuring.

DETAILED mode is used rather than LIMITED, and not for the extra detail: LIMITED returns a NULL page_type_desc for every page, including the allocation bitmaps, which are exactly the ones worth naming. The classification itself still comes off the numeric page_type, which both modes fill in.

How a page is classified

In this order:

1. Page type 8, 9 or 11 is Allocation. The bitmaps carry no owning object, so they are tested first or they would fall through.
2. In tempdb, an object id between 1 and 99 is Metadata.
3. A resolved page with no type and no owner is Unresolved.
4. Anything else is Other page.

The closed range 1 to 99 is not a rounding of “under 100”. Temp tables carry large negative object ids, so the shorthand of “object id below 100” classifies every hot temp table page as metadata contention and then argues for an instance restart on the strength of it. Every object in tempdb inside the closed range is an is_ms_shipped system table on SQL Server 2019 and 2022 alike, and there are none in that range that are not.

An index or IAM page belonging to a system table is metadata contention as much as a data page is, which is why the object test is not narrowed to data pages. sysallocunits is routinely the second heaviest row on this report and most of its pages are index pages.

The history collector

trackPageLatchWaits is added to the DBHealthHistory repository at database version 1464 and runs from the fifteen minute job. Each run takes a five second burst of the same sampling the page does, resolves the pages, and stores one row per kind per object. It is what lets the daily strip say tempdb and say metadata.

It is gated on SQL Server 2019 the same way the report is, and the gate is structural rather than conditional: the procedure is created as an empty stub on every repository and only given a body on 2019 and newer, because naming sys.dm_db_page_info outside dynamic SQL would stop it compiling on an older instance. On a 2017 instance the call is a no-op rather than an error every fifteen minutes.

At fifteen minute intervals the strip is a presence signal, not a magnitude one. Five seconds in every nine hundred is a sample, and a day with a busy cell had contention in at least one of those bursts. It is not a count of everything that happened that day and the report does not treat it as one.

---

Settings

Setting	Where it lives	What it does
TempDbContentionSampleSeconds	The application settings file	Remembers 10, 30 or 60 between sessions
HistoricRetentionDays	DBHealthHistory.dbo.Settings	How long pageLatchWaits rows are kept

---

Messages you may see

On an instance older than SQL Server 2019:

The TempDB Metadata Contention report requires SQL Server 2019 or newer. This instance is running 14.0.3485.1. Neither the IsTempdbMetadataMemoryOptimized server property nor the sys.dm_db_page_info function exists here, and memory optimized tempdb metadata was introduced in SQL Server 2019.

Naming both things that were looked for is the whole diagnosis when this turns up on an instance that should support the feature.

Before anything has been sampled:

Nothing has been sampled yet on this instance. Press Sample to poll sys.dm_os_waiting_tasks for 30 seconds and classify whatever it catches.

When the sample caught nothing:

No tempdb page latch contention was caught in this sample. Sampled for 30 seconds over 118 passes starting at 10:04:11. That is a window, not a verdict about the rest of the day.

When there is no history database:

No history database is configured, so nothing has been recorded for this instance.

Reporting a quiet month for an instance nobody has been recording would be the one genuinely misleading outcome, so the strip says this instead of drawing zeros.

---

Related reports

Report	Why you would go there
TempDB Files advisor	Where allocation contention is actually worked out
TempDB use by Hour by Day	Space in tempdb, which is a different tempdb problem
Waits	Whether page latch waiting is really where this instance’s time goes
Optimized Locking	The other SQL Server feature this product measures before recommending

---

Frequently asked questions

Does this tell me how much the setting would save? No, deliberately. The bars are contention that already happened. The feature removes latch waiting on the tempdb system tables, not all tempdb waiting, and no report can honestly predict your saving.

Why is my biggest bar an Allocation row? Because your tempdb contention is on the free space bitmaps, and this setting does nothing for it. The number of tempdb data files is what addresses that. The page says so rather than letting you book a restart that would not have helped.

Why does the strip say “all databases” when the page is about tempdb? Because the wait collector stores no page and no reliable database for a latch wait, so that strip genuinely cannot be narrowed to tempdb. Once trackPageLatchWaits has been collecting, the strip changes source and the heading changes with it.

Can I add the strip to the bars? No. They count different things over different windows: one is a five second poll of pages in tempdb, the other is days of sampled sessions across the instance. Adding them would imply an attribution the data does not support.

Why does the sample need to run for so long? Because a single snapshot catches a queue about one time in five. Thirty seconds is roughly 120 passes, which is enough to establish whether a queue exists and cheap enough to run during the day.

Why is there no button to enable it? Because it needs an instance restart and it disallows columnstore indexes on temporary tables. Neither belongs behind a click on a monitoring page.

What does an Unresolved row mean? The engine would not describe the page, either because the call raised, because it returned nothing, or because the permission was denied. It is counted separately because a page nobody could identify is not evidence for a restart.

---