You Scaled Your VM to 32 Cores. Your TempDB Configuration Didn’t

TempDB file layout is a one-time setup decision unless you change it. Scale your VM from 4 to 32 cores and your TempDB still has 4 data files, creating page latch contention that tanks performance under concurrency.

Key Takeaways

• TempDB file layout is set at install and doesn’t auto-adjust when you add cores, memory, or storage.
• The symptom is page latch contention — PAGELATCH_UP / PAGELATCH_EX waits on allocation pages (PFS, GAM, SGAM).
• Generic “8 files” rules don’t work — monitor actual page latch statistics and adjust based on your workload.
• TempDB isn’t the only thing left behind — MaxDOP, Max Server Memory, and Cost Threshold for Parallelism also stay static after scaling.
• SQL Server 2022 improved TempDB allocation contention at the engine level — check what’s changed before applying old-era fixes.
• On IaaS (Azure VM, EC2, Compute Engine), TempDB configuration is your responsibility.

How This Happens

You provision a 4-core VM. Install SQL Server. TempDB gets 4 data files. On modern versions, setup may create multiple TempDB files (up to 8); older installs often have 1 file — and upgrades don’t retroactively fix it.

Three months later, you scale to 32 cores before go-live. More memory. More storage. Bigger instance.

Your TempDB configuration? Still set for 4 cores.

This happens constantly.

Why TempDB Doesn’t Scale Automatically

SQL Server configures TempDB file layout based on resources at installation time. On modern versions, the installer considers CPU count and may provision multiple TempDB data files by default, but it does not dynamically adjust them later.

SQL Server 2016+ improved TempDB defaults — less mixed-extent pain, saner multi-file defaults — but scaling the VM still doesn’t resize or re-balance your TempDB layout for you.

When you scale the VM — vertically in Azure, resize in AWS, change machine type in GCP (Google Cloud Platform) — SQL Server sees the new CPU count after the resize and restart. But the TempDB file layout written during installation doesn’t change. It’s a static setting. There’s no auto-scaler watching your VM size and adjusting files on the fly.

What Breaks: Page Latch Contention

TempDB is heavily used for:

• Temporary tables and table variables
• Sorting and hashing operations (ORDER BY, GROUP BY, joins)
• Row versioning (snapshot isolation, RCSI)
• Internal worktables created by the query optimizer

All of this activity writes to TempDB’s allocation pages. When multiple sessions try to allocate space at the same time, they compete for latches on those pages.

With 4 TempDB data files on a 32-core server running a high-concurrency workload, that contention becomes a bottleneck. Sessions pile up waiting for PAGELATCH_UP on allocation pages. Performance degrades.

You see it in wait stats:

• PAGELATCH_UP on TempDB allocation pages (PFS, GAM, SGAM)
• Growing wait times under load
• CPU sitting idle while threads queue for latches

The standard fix: add more TempDB data files to spread the allocation load.

If you’re on SQL Server 2022, check before reflexively adding files.

SQL Server 2022 includes engine-level improvements that reduce certain allocation map contention scenarios, particularly under high concurrency.

If your contention is specifically GAM/SGAM-related, you may get relief from the engine improvements — not just by throwing files at it. Don’t apply 2012-era fixes on 2022-era engines without checking what changed.

Version store is another major TempDB consumer. If you’re running with Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation enabled, SQL Server maintains row versions in TempDB. On a high-concurrency system, version store can rival temporary tables as the heaviest TempDB user. Scaling concurrency while enabling snapshot isolation can significantly increase TempDB allocation and version store pressure, which may expose existing contention.

Spills from sorts, hashes, and worktables also hit TempDB. Spills can show up as more TempDB I/O and CPU work; don’t expect them to always present as allocation-page latch waits.

Three Types of TempDB Bottlenecks

Before you start adding files or changing settings, identify which bottleneck you’re actually hitting:

• Allocation contention (PFS/GAM/SGAM). PAGELATCH_* waits with TempDB page resources. More files helps. SQL Server 2022 engine improvements help even more.
• Metadata contention (system table hotspots). PAGELATCH waits on system catalog tables like sys.sysschobjs. Memory-optimized tempdb metadata (SQL Server 2019+) is the fix — more files won’t help.
• I/O latency/throughput. TempDB is just slow. Storage placement, write latency, noisy neighbor problems (especially virtualized). No amount of file tuning fixes bad storage.

Knowing which type you’re dealing with determines whether the fix is more files, a configuration change, or a storage move.

The “Best Practice” Myth

You’ll see recommendations like:

• “1 file per CPU core up to 8, then add in increments of 4”
• “8 files for most workloads”
• “¼ of your CPU count”

These are starting points, not gospel.

Best practice says 8 files for 32 cores. But depending on your workload, you might need 16. Or you might need 4.

You find out by monitoring page latch statistics. Not by following generic recommendations.

We monitor this for every client. When we spot contention, we add files. When files sit idle, we remove them.

How to Monitor TempDB Page Latch Contention

Important: sys.dm_os_wait_stats is cumulative since last restart (or since stats were cleared). If you run this query on a server that’s been up 90 days, you’re seeing “what has happened,” not “what’s happening now.”

Use deltas over an interval (snapshot, wait under load, diff), or look at current waiters in sys.dm_os_waiting_tasks and check the wait resource format (2:x:y — dbid 2 = tempdb; page identifiers map to PFS/GAM/SGAM).

Check cumulative wait stats for TempDB-specific page latch waits:

SELECT wait_type
	,waiting_tasks_count
	,wait_time_ms
	,max_wait_time_ms
	,signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
	AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;

To see what’s happening right now, check current waiters and look for tempdb page resources:

SELECT session_id
	,wait_type
	,wait_duration_ms
	,resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
	AND resource_description LIKE '2:%'
ORDER BY wait_duration_ms DESC;

The resource_description format 2:fileId:pageId tells you it’s tempdb (database_id 2). PFS pages are at predictable intervals (every 8,088 pages), GAM at page 2, SGAM at page 3 in each file.

Look for waits on:

• PFS (Page Free Space) pages
• GAM (Global Allocation Map) pages
• SGAM (Shared Global Allocation Map) pages

High waits on these allocation structures indicate contention that more data files can relieve.

How to Add TempDB Data Files

Aim for low latency and isolate TempDB I/O where you can. On cloud VMs, that often means local/ephemeral SSD (Azure local temp disk, AWS instance store, GCP local SSD). On-prem, dedicated low-latency storage — but be aware that “separate volumes” on a shared SAN still share the same underlying pool. The goal is fast, consistent I/O for TempDB.

Check current TempDB configuration:

SELECT name
	,physical_name
	,size * 8 / 1024 AS size_mb
	,max_size
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type
	,file_id;

Add files matching the size and growth settings of existing data files:

USE master;
GO

ALTER DATABASE tempdb ADD FILE (
	NAME = tempdev5
	,FILENAME = 'T:\TempDB\tempdev5.ndf'
	,SIZE = 8192 MB
	,FILEGROWTH = 512 MB
	);

Repeat for as many files as needed. All TempDB data files should be:

• Same size and growth — keep files equal so allocations stay evenly distributed (see our TempDB best practices guide for full sizing recommendations)
• On fast storage — preferably local SSD or low-latency dedicated storage

No restart required. The new files are available immediately.

When More Files Isn’t Better

Over-provisioning TempDB files causes its own problems.

One production server we saw had 29 TempDB data files on a 16-core instance. Someone followed a script without understanding the target configuration. They kept adding files long after contention stopped improving, and it became pure configuration drag — more complexity, more things to keep equal-sized, diminishing returns.

The right number of files depends on:

• CPU core count (starting point)
• Workload concurrency (high-concurrency OLTP benefits from more files)
• Temporary object usage patterns (heavy temp table usage increases contention)
• Actual measured page latch waits

If page latch contention is low and files sit mostly idle, you don’t need more. Adding files “just in case” wastes resources and complicates management.

Memory-Optimized TempDB Metadata (SQL Server 2019+). If you’re experiencing metadata contention — PAGELATCH waits on system catalog tables like sys.sysschobjs rather than allocation pages — SQL Server 2019+ offers memory-optimized tempdb metadata. Enable it with ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (requires restart).

This moves TempDB system table metadata to memory-optimized tables, eliminating most metadata contention.

Trade-off: increased memory usage by the XTP memory clerk and potential additional memory pressure under heavy metadata workloads. If you enable it, watch the memory footprint — and if needed, bind it to a Resource Governor pool to cap usage. This is a separate issue from allocation page contention, but worth knowing if you’re troubleshooting TempDB performance.

Other Settings That Don’t Scale

TempDB isn’t the only configuration that gets left behind when you scale.

MaxDOP (Max Degree of Parallelism). One case involved a server initially provisioned with 2 cores. MaxDOP was set to 2 — not 0 (auto), but hard-coded to 2. When the server scaled to 32 cores, MaxDOP stayed at 2. Queries that should parallelize across 8 or 16 cores were capped at 2. Performance tanked. Here’s how to check and configure MaxDOP properly after scaling.

Max Server Memory. Installed on a 16 GB VM, max server memory might be set to 12 GB (leaving 4 GB for the OS). Scale to 128 GB and that limit doesn’t change. SQL Server is starved of buffer pool space it could be using — page life expectancy drops, and queries start reading from disk instead of cache.

See our guide on configuring the max server memory parameter to match your actual resources.

Cost Threshold for Parallelism. Often left at the default (5) because it was “fine” on a small dev server. On a scaled production box, that threshold is way too low — queries parallelize unnecessarily, consuming thread resources and creating CXPACKET waits. Increasing cost threshold for parallelism is one of the easiest performance wins after scaling.

Configuration isn’t set-it-and-forget-it. Your server changes. Your configuration should change with it.

Cloud Providers and TempDB Scaling

Cloud-managed SQL offerings handle TempDB differently — but none of them are fully hands-off.

Azure SQL Managed Instance creates TempDB data files (12 by default) and lets you configure file count, growth, and max size. If you’re sizing a Managed Instance, TempDB configuration should be part of that planning.

Those settings persist across restarts and failovers. It’s managed within platform limits, but don’t assume it auto-tunes your TempDB layout when you scale the service tier.

Azure SQL Database abstracts TempDB entirely. You don’t configure files directly. Microsoft manages capacity based on your service tier. Resource limits and platform-managed TempDB — but that doesn’t mean TempDB contention can’t happen.

AWS RDS for SQL Server provisions TempDB based on instance class. TempDB sizing behavior can change with instance class changes (especially in instance-store scenarios). You can create additional TempDB data and log files on the data volume — so it’s not fully “hands off.”

Google Cloud SQL for SQL Server creates TempDB files based on logical processors (minimum 8 when you have 8+ logical processors). You can perform common TempDB management operations. Managed platform, but you still have levers.

If you’re running SQL Server on IaaS (Azure VM, EC2, Compute Engine), you’re fully responsible for TempDB configuration. Scaling the VM doesn’t automatically adjust it.

Configuration Hygiene After Scaling

After any infrastructure change — vertical scaling, adding vCPUs, increasing memory — run a configuration audit:

Check TempDB file count and sizing:

SELECT name
	,size * 8 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
	AND type = 0;

Review Max Server Memory:

EXEC sp_configure 'max server memory (MB)';

Verify MaxDOP:

EXEC sp_configure 'max degree of parallelism';

Check Cost Threshold for Parallelism:

EXEC sp_configure 'cost threshold for parallelism';

Better yet: automate this. Glenn Berry’s diagnostic queries include checks for misconfigured TempDB, undersized max memory, and other common issues. Run them after every scaling event.

Or build a post-scaling checklist script that compares the current configuration to the current resources and flags mismatches.

Treat infrastructure changes as triggers for configuration review.

Bottom Line

SQL Server sees your new hardware after the resize and restart. It just won’t redesign TempDB (or your instance settings) automatically.

You scaled from 4 to 32 cores. Your TempDB file layout is still set for 4. That’s a bottleneck waiting to happen.

Identify which bottleneck you’re hitting — allocation contention, metadata contention, or I/O latency. Monitor page latch statistics. Add files when contention appears. Remove files if you over-provisioned. And if you’re on SQL Server 2022, check the engine improvements before reaching for old fixes.

Configuration isn’t static. Your workload changes. Your infrastructure changes. Your settings should change with them.