100% Free
🎯 3200+ Questions. One Goal: Your Success Sign In Sign Up

What Is TempDB Contention and What Causes It?

Senior MS SQL
Quick Answer TempDB contention typically hits PFS, GAM, and SGAM pages — special allocation pages that every new temp object allocation touches. In high-concurrency workloads, many sessions compete for the same pages. Fix: add multiple TempDB data files (one per CPU core up to 8), enable trace flag 1118, use SQL Server 2016+ with automatic improvement.

Answer

TempDB contention occurs when multiple threads compete for the same allocation pages (PFS, GAM, SGAM) or metadata structures.

Typical causes include:

  • Too few TempDB data files
  • Heavy use of temp tables/table variables
  • Large sorting or hashing operations
  • High row versioning pressure

Fixes: increase file count, equal-size files, optimize workload, reduce spills.

S
SugharaIQ Editorial Team Verified Answer

This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.

Bookmark Add to Set Notes

Want to bookmark, take notes, or join discussions?

Sign in to access all features and personalize your learning experience.

Sign In Create Account

Source: SugharaIQ

Related Questions in MS SQL

(10 questions)
What is MS SQL Server and where is it commonly used?
Explain Database, Table, Row, and Column in relational terms.
What is a Primary Key and why is it important?
What is a Foreign Key and what problem does it solve?
What are Constraints in MS SQL?
What is Normalization and why do we use it?
Explain 1NF, 2NF, and 3NF briefly.
What is Denormalization and when is it used?
What is an Index and why is it important?
What is the difference between Clustered and Non-Clustered Index?
View All MS SQL Questions
Ready to level up? Start Practice