seniorT-SQL

What is SQL Server tempdb allocation contention (PFS, GAM, SGAM bottlenecks)?

Updated May 17, 2026

Short answer

tempdb contention occurs when multiple sessions compete for allocation pages like PFS, GAM, and SGAM.

Deep explanation

tempdb uses special system pages to manage space allocation. PFS tracks page free space, GAM tracks global extent allocation, and SGAM tracks mixed extent allocation. Under high concurrency, these pages become hot spots because many sessions try to allocate temporary objects simultaneously. This leads to latch contention (PAGELATCH_UP). SQL Server mitigates this by recommending multiple tempdb data files with equal sizing and proportional fill behavior.

Unlock with a Pro subscription to view this section.

View pricing

Real-world example

No real-world example available yet.

Unlock with a Pro subscription to view this section.

Upgrade to Pro

Common mistakes

No common mistakes listed yet.

Unlock with a Pro subscription to view this section.

Upgrade to Pro

Follow-up questions

No follow-up questions available yet.

Unlock with a Pro subscription to view this section.

Upgrade to Pro

More T-SQL interview questions

View all →