When otherwise simple queries wait together, tempdb can be the shared bottleneck. Spotting tempdb contention starts with wait evidence, then moves to allocation pages and file layout.
Separate tempdb Contention Waits From Diagnosis
PAGELATCH waits protect in-memory database pages. They are not the same as PAGEIOLATCH waits, which involve reading pages from storage. That distinction matters because moving tempdb to faster disks does not directly fix an allocation-page latch bottleneck. Start with current requests and their wait resources. Repeated waits on tempdb allocation pages make the case stronger than one historic counter.
I have seen a storage upgrade proposed after someone read only the word page in a wait name. The price of that guess is usually memorable. Check whether the waits are concentrated, how long they last, and what workload runs at the same time. A short burst during a known job calls for different work than sustained contention during normal traffic.
See Current Latch Waits Behind tempdb Contention
The first query looks at requests currently waiting on latch types commonly associated with allocation pressure. It reports the session, statement context, and wait resource. Database ID 2 identifies tempdb inside the resource string. A request that has moved on will disappear before your query runs, so capture several samples during the slow period rather than treating an empty result as proof of health.
The text is the current batch, not always the exact statement. Use statement offsets when you need to isolate the active statement. Keep the query handy in an incident notebook. It is small enough to run while users report the delay.
SELECT r.session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
t.text AS batch_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE N'PAGELATCH_%'
ORDER BY r.wait_time DESC;Read the Wait Resource
A page resource identifies a database, file, and page. In tempdb, allocation map pages have recognizable patterns, but do not diagnose from page number alone. Use sys.dm_db_page_info on supported versions to inspect the page type where the resource can be parsed reliably. PFS, GAM, and SGAM are the allocation map types relevant to this question. A user-data page points to a different line of investigation.
I compare multiple blocked workers before naming the cause. If they converge on the same allocation map, the pattern is persuasive. If waits scatter across different objects or databases, calling it tempdb allocation contention is premature. What page are your sessions actually waiting for? The answer should come from the resource, not from the loudest dashboard label.
Review tempdb File Layout
Equal-sized tempdb data files allow allocation work to spread more predictably. Check the count, size, and growth settings before adding files. Uneven files can defeat the intended distribution. The following query is a quick inventory. It does not tell you that a specific number of files is right; workload, version, and existing wait evidence decide that.
Keep log files out of the data-file count. Multiple tempdb log files do not solve allocation-page contention. If you change file count, use a supported incremental plan and measure the same waits afterward. Adding files until a chart looks calmer is not a capacity strategy.
SELECT name,
type_desc,
size * 8.0 / 1024 AS size_mb,
growth,
is_percent_growth
FROM tempdb.sys.database_files
ORDER BY type_desc, file_id;
Check Existing Server Improvements
Modern SQL Server releases include changes that reduce some historical tempdb allocation pressure. The server’s exact build and configuration therefore matter. Do not paste a decade-old trace-flag list into a current installation. Read the release guidance for your version, check what is already enabled, and observe the current wait pattern before changing a server setting.
Tempdb metadata contention is another problem with a similar user symptom. Memory-optimized tempdb metadata addresses specific metadata bottlenecks, not every tempdb wait. It has its own support and restart considerations. First identify whether workers wait on allocation pages, metadata structures, or ordinary user objects. The fix follows the measured mechanism.
Look at the Workload
Temporary tables, sorts, hash operations, spills, version store, and index maintenance can all put pressure on tempdb. File layout treats distribution. It does not remove excessive work. Check the top queries running during the wait period, execution plans for spills, and any recent workload change. A query that creates and drops temporary objects in a tight loop deserves attention even when extra files reduce its visible waits.
I ask whether the problem appeared after a deployment or after data volume changed. That question is more useful than immediately resizing tempdb. If a plan regression suddenly spills to tempdb, fix the plan or its statistics and memory grant. The shared database will thank you quietly, which is the only kind of thanks a DBA gets from tempdb.
Plan a Controlled File Change
When allocation-map contention is clear and the current data-file layout is thin, add equal-sized files in a planned change. Set matching growth increments. Verify that the storage volume has room for all files and that restart behavior leaves the files at the intended size. Document the before state so the result can be judged against the original waits.
The syntax below illustrates adding one file. Replace names and paths with reviewed values for your Windows server. A file operation changes storage, so run it through the normal change process. Avoid pasting sample paths into production. The useful result is reduced tempdb contention under the same workload, not merely a larger file list.
ALTER DATABASE tempdb
ADD FILE
(
NAME = N'tempdev_extra',
FILENAME = N'D:\SQLData\tempdev_extra.ndf',
SIZE = 1024MB,
FILEGROWTH = 256MB
);Verify tempdb Contention After the Change
Compare latch waits during comparable workload windows. Record request samples, file sizes, and the application symptom. If workers now wait elsewhere, decide whether the change solved the reported delay or merely moved the bottleneck. Keep an eye on free space and growth events while the new layout settles. A clean change includes a rollback plan and an owner who knows what was modified.
Do not judge from cumulative wait statistics alone. They include time before the change unless you capture a baseline or reset them under an approved process. Current requests and timed samples make a clearer comparison. If contention persists, revisit the page types and workload. The first hypothesis deserves evidence, not loyalty.
Related reading on this blog: TempDB Troubles: Identifying and Resolving TempDB Contentions and 3 Ways to Know Count of TempDB Data Files.
A PAGELATCH wait is not a disk verdict, it is a clue about contested memory pages.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
