Introduction

SQL Server TempDB contention when allocation page or GAM/SGAM pages bottleneck. This guide provides step-by-step diagnosis and resolution.

Symptoms

Typical error output:

bash
Error: Transaction (Process ID 123) was deadlocked on lock resources
TempDB allocation page contention detected
PAG 2:1:0 contention ratio: 85%

Common Causes

  1. 1.Single TempDB data file causes allocation bottleneck
  2. 2.GAM/SGAM page contention under heavy concurrent inserts
  3. 3.insufficient TempDB files for workload
  4. 4.Page allocation algorithm inefficient

Step-by-Step Fix

Step 1: Check Current State

sql
SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE '%LCK_%'
SELECT resource_database_id, resource_type, resource_description FROM sys.dm_tran_locks WHERE resource_database_id = 2
EXEC sp_tempdb_configure 'contention_detection', 1

Step 2: Identify Root Cause

sql
-- Check for blocking processes
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT * FROM information_schema.processlist WHERE time > 30;

Step 3: Apply Primary Fix

```sql -- Add multiple TempDB files (as many as CPU cores) ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev2', FILENAME = 'D:\tempdb2.ndf', SIZE = 256MB, FILEGROWTH = 64MB); ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev3', FILENAME = 'E:\tempdb3.ndf', SIZE = 256MB, FILEGROWTH = 64MB);

-- Enable trace flag for allocation optimization DBCC TRACEON (1118, -1); ```

Step 4: Apply Alternative Fix

```sql -- Alternative fix: Check configuration SELECT * FROM pg_settings WHERE name LIKE '%vacuum%';

-- Adjust parameters dynamically ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10; SELECT pg_reload_conf();

-- Verify the fix SELECT * FROM pg_stat_user_tables WHERE relname = 'target_table'; ```

Step 5: Verify the Fix

sql
SELECT * FROM sys.dm_os_waiting_tasks WHERE resource_database_id = 2 AND wait_type LIKE '%LCK_%'
-- Contention should decrease
SELECT COUNT(*) AS tempdb_files FROM sys.master_files WHERE database_id = 2 AND type = 0

Common Pitfalls

  • Running vacuum during peak hours without resource management
  • Forgetting to analyze after vacuum for statistics update
  • Not monitoring autovacuum progress on large tables
  • Setting cost delay too high for high-churn tables

Best Practices

  • Schedule maintenance windows for vacuum full operations
  • Monitor bloat ratio and autovacuum frequency
  • Tune autovacuum parameters per table based on churn rate
  • Use pg_stat_progress_vacuum to monitor vacuum progress
  • PostgreSQL Autovacuum Not Running
  • PostgreSQL Dead Tuple Accumulation
  • PostgreSQL Transaction ID Wraparound
  • PostgreSQL Table Size Excessive