Optimizing SQL Server TempDB Allocations for PeopleSoft on Windows

PeopleSoft on SQL Server is a concurrency-heavy workload: Application Engine batches create and drop temp-table instances, nVision and PS/Query push large sorts and hash joins through TempDB, and Integration Broker transforms spill into it. The default Windows installation of SQL Server leaves TempDB as a single 8 MB data file with 10% autogrowth — the worst possible starting point for that workload. This guide gives enterprise DBAs the diagnostic queries and a restart-safe runbook to redesign TempDB allocations for PeopleSoft, eliminating page-latch contention and autogrowth stalls at the root.

Overview

TempDB serves three object categories:

• User objects — global and local temp tables (#temp), table variables, and PeopleSoft Application Engine temp-table instances when configured to use tempdb-style scratch space.
• Internal objects — sort runs, hash join spill files, spools, cursor worktables, and index build sorts. This is where nVision and PS/Query do their damage.
• Version store — row versions for Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation. Long-running PeopleSoft reporting queries pin version chains here.

Every 8 KB extent allocation or deallocation in TempDB must update the allocation bitmaps of the file being used: the PFS page (file page 1), the GAM page (file page 2), and the SGAM page (file page 3). With a single data file, every session on a 64-core Process Scheduler host queues on the same three pages. The fix is to spread those bitmaps across multiple files and to remove the I/O and zero-fill stalls that make each growth event painful.

Executive Summary

TempDB is the shared scratch space for every SQL Server session, and PeopleSoft is one of the heaviest consumers of it in the enterprise. Application Engine programs multiply temp-table instances under batch concurrency, nVision reports execute large GROUP BY / ORDER BY / DISTINCT operations, and any RCSI-enabled reporting database pins version store pages in TempDB. With a single TempDB data file, every extent allocation in the instance serializes on the PFS, GAM, and SGAM pages of that one file — presenting as PAGELATCH_UP and PAGELATCH_EX storms, multi-second autogrowth stalls, and eventually error 1105 (filegroup full) or error 9002 (log full) during payroll and period-end cycles. The resolution is a deliberate allocation redesign: multiple equally sized data files on dedicated fast storage, uniform growth increments, Instant File Initialization enabled, and workload-level controls on PeopleSoft’s most expensive sort paths. This article provides the exact T-SQL and PowerShell to diagnose, apply, and validate that redesign in a production environment.

Diagnostic Checklist

Run every check below before changing anything, and save the output as your baseline. You will re-run the same queries after the change to prove improvement.

T-SQL: TempDB file layout

SELECT
    df.name AS logical_name,
    df.physical_name,
    df.type_desc,
    df.size / 128.0 AS current_size_mb,
    df.max_size / 128.0 AS max_size_mb,
    df.growth / 128.0 AS growth_mb,
    df.is_percent_growth
FROM sys.master_files AS df
WHERE df.database_id = DB_ID('tempdb')
ORDER BY df.type_desc, df.file_id;

What you’re looking for: exactly one row with type_desc = 'ROWS', current_size_mb = 8, and is_percent_growth = 1. That is the default installation and the root cause of single-file contention.

T-SQL: TempDB space consumption by category

SELECT
    SUM(unallocated_extent_page_count) / 128.0 AS free_space_mb,
    SUM(version_store_reserved_page_count) / 128.0 AS version_store_mb,
    SUM(internal_object_reserved_page_count) / 128.0 AS internal_object_reserved_mb,
    SUM(user_object_reserved_page_count) / 128.0 AS user_object_reserved_mb,
    SUM(mixed_extent_page_count) / 128.0 AS mixed_extent_mb
FROM sys.dm_db_file_space_usage;

What you’re looking for: a large internal_object_reserved_mb during nVision or App Engine batch windows confirms sort/hash/spool pressure. A large version_store_mb means RCSI/Snapshot Isolation is pinning version chains.

T-SQL: Per-session TempDB consumers

SELECT TOP 20
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    u.user_objects_alloc_page_count / 128.0 AS user_alloc_mb,
    u.internal_objects_alloc_page_count / 128.0 AS internal_alloc_mb
FROM sys.dm_db_session_space_usage AS u
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = u.session_id
WHERE (u.user_objects_alloc_page_count + u.internal_objects_alloc_page_count) > 0
ORDER BY (u.user_objects_alloc_page_count + u.internal_objects_alloc_page_count) DESC;

What you’re looking for: a small number of sessions consuming most of TempDB. Correlate program_name with PeopleSoft components (PSAE, PSPRCS, nVision, PSQED).

T-SQL: Allocation contention on PFS/GAM/SGAM

SELECT
    session_id,
    wait_type,
    wait_time AS wait_time_ms,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE CAST(DB_ID('tempdb') AS varchar(4)) + ':%'
ORDER BY wait_time DESC;

What you’re looking for: resource_description values in the form tempdb_id:file_id:page_id. For example, 2:1:1 is the PFS page of file 1, 2:1:2 is the GAM page, and 2:1:3 is the SGAM page. If nearly every PAGELATCH wait references file 1 pages 1–3, you have classic single-file allocation contention.

T-SQL: Wait statistics snapshot

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS wait_time_s,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_s,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN
    ('PAGELATCH_SH', 'PAGELATCH_EX', 'PAGELATCH_UP',
     'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'PAGEIOLATCH_UP',
     'WRITELOG', 'SOS_SCHEDULER_YIELD')
ORDER BY wait_time_ms DESC;

What you’re looking for: PAGELATCH_* in the top five waits. On a healthy TempDB configuration, PAGELATCH waits should be a rounding error relative to CPU and I/O waits.

T-SQL: Sort and spill evidence (SQL Server 2019+)

SELECT TOP 20
    qs.execution_count,
    qs.total_spills,
    qs.total_worker_time / 1000000.0 AS total_cpu_s,
    SUBSTRING(st.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.total_spills > 0
ORDER BY qs.total_spills DESC;

What you’re looking for: PeopleSoft queries with high total_spills are the ones to tune with indexes or query changes after TempDB is fixed.

T-SQL: Instant File Initialization status

EXEC sp_readerrorlog 0, 1, N'Instant File Initialization';

What you’re looking for: a line reading Database Instant File Initialization: enabled. If it is absent, SQL Server zero-fills every TempDB growth event — a multi-GB stall during peak load.

PowerShell / Windows checks

# Confirm the SQL Server service account has the volume maintenance privilege
whoami /priv | Select-String -Pattern 'SeManageVolumePrivilege'

# Check free space on the target TempDB volume
Get-PSDrive -Name D | Select-Object Name, @{n='FreeGB';e={[math]::Round($_.Free/1GB,2)}}

# Confirm the active power plan (High Performance recommended for SQL Server)
powercfg /getactivescheme

What you’re looking for: SeManageVolumePrivilege in the enabled list; adequate free space for the new file sizes; the High Performance or Ultimate Performance power plan, not Balanced.

Step-by-Step Resolution Runbook

Step 0: Safety Checks

Do not skip this step. TempDB changes are low-risk, but a careless restart during a PeopleSoft payroll or period-end batch window is not.

1. Confirm the maintenance window. File moves, memory-optimized metadata, and trace flag changes require a SQL Server service restart. Quiesce the PeopleSoft Process Scheduler and confirm no critical online batches are running.
2. Capture a baseline. Save the output of every query in the Diagnostic Checklist to a results file. You will re-run them after the change to prove improvement.
3. Verify disk capacity. If you plan eight 6 GB TempDB files, confirm the target volume has at least 48 GB free plus 20% headroom for autogrowth — roughly 58 GB.
4. Check the error log for history. Look for recurring error 1105 (Could not allocate space for object ... in database 'tempdb') and error 9002 (The transaction log for database 'tempdb' is full). Recurrence during PeopleSoft batch windows confirms the symptom and justifies the change.
5. Test in a lower environment first. Replicate the PeopleSoft workload against a stage environment with the new TempDB layout before touching production.
6. Document the rollback. Keep the original MODIFY FILE and ADD FILE scripts. To roll back, reverse the file list and restart SQL Server again.

Step 1: Confirm the Symptom Pattern

Apply the diagnostic queries and confirm at least two of the following:

• Single TempDB data file (ROWS count = 1).
• PAGELATCH_* waits referencing file 1 pages 1, 2, or 3 (PFS/GAM/SGAM).
• TempDB autogrowth events during PeopleSoft batch windows.
• Recurring errors 1105 / 9002 in the SQL Server error log.

If none of these are present, stop — TempDB may not be your bottleneck. Profile CPU, storage I/O, and blocking before proceeding.

Step 2: Compute Target File Count

Use the number of logical processors as your starting point:

SELECT cpu_count, hyperthread_ratio
FROM sys.dm_os_sys_info;

Apply Microsoft’s allocation-contention guidance:

• 8 or fewer logical processors: create one TempDB data file per logical processor.
• More than 8 logical processors: start with 8 data files, then add files in multiples of 4 (12, 16) if PAGELATCH contention persists after the first change.

Each file gets its own set of PFS/GAM/SGAM pages, so the goal is to spread allocation bitmap updates across N independent page sets. For PeopleSoft — a concurrency-heavy batch workload — start at 8 files on any host with 16 or more cores.

Step 3: Set Uniform File Sizes and Growth

All TempDB data files must have identical initial size and identical growth increments. SQL Server’s proportional-fill algorithm spreads allocations across files proportionally to file size; if files are unequal, the largest file takes the majority of allocations and becomes the new contention point.

Use your peak observed usage from sys.dm_db_file_space_usage to set the initial size:

• Peak usage = version_store_mb + internal_object_reserved_mb + user_object_reserved_mb, sampled during the busiest PeopleSoft batch.
• Initial size per file = (peak_usage / file_count) * 1.2 (20% headroom).
• Growth increment = 1,024 MB per file; disable percentage growth.

Example: peak usage 40 GB, 8 files → 5 GB each + 20% headroom = 6,144 MB initial, 1,024 MB growth.

ALTER DATABASE [tempdb]
MODIFY FILE
(
    NAME = N'tempdev',
    SIZE = 6144MB,
    FILEGROWTH = 1024MB
);

Step 4: Add Data Files

Add the remaining files with identical sizes and growth settings:

ALTER DATABASE [tempdb]
ADD FILE
(
    NAME = N'tempdev2',
    FILENAME = N'D:\SQLServer\TempDB\tempdev2.ndf',
    SIZE = 6144MB,
    FILEGROWTH = 1024MB
);

ALTER DATABASE [tempdb]
ADD FILE
(
    NAME = N'tempdev3',
    FILENAME = N'D:\SQLServer\TempDB\tempdev3.ndf',
    SIZE = 6144MB,
    FILEGROWTH = 1024MB
);
-- repeat through tempdev8

If you are also moving TempDB to a new volume, perform the MODIFY FILE with the new FILENAME for each file, then restart the service once so all changes take effect together.

Step 5: Move TempDB to Fast Storage (Optional but Recommended)

PeopleSoft sort-heavy workloads are I/O-latency sensitive. Put TempDB on dedicated NVMe or high-end SSD storage, physically separate from PeopleSoft data files and transaction logs.

ALTER DATABASE [tempdb]
MODIFY FILE
(
    NAME = N'tempdev',
    FILENAME = N'D:\SQLServer\TempDB\tempdev.mdf'
);
-- repeat for each tempdevN, then restart SQL Server

After restart, confirm the paths and sizes:

SELECT physical_name, size / 128.0 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type_desc = 'ROWS';

Step 6: Enable Instant File Initialization

Without IFI, every TempDB autogrowth zero-fills the new space synchronously — a 1 GB growth can stall the entire instance for seconds. Enable IFI for the SQL Server service account:

1. Open Local Security Policy (secpol.msc) on each Windows host.
2. Navigate to Security Settings → Local Policies → User Rights Assignment.
3. Open Perform volume maintenance tasks.
4. Add the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER or your domain service account).
5. Restart the SQL Server service.

Verify after restart:

EXEC sp_readerrorlog 0, 1, N'Instant File Initialization';

Note: SQL Server 2022 enables IFI by default. Verify with the same query.

Step 7: Enable Memory-Optimized TempDB Metadata (SQL Server 2019+)

If PAGELATCH contention persists after the file-count fix, or if you are on a large NUMA host, enable memory-optimized TempDB metadata. This moves allocation bitmaps into in-memory hash tables, eliminating the PFS/GAM/SGAM latch bottleneck for metadata operations.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

A service restart is required. SQL Server 2022 enables this by default. Verify:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_metadata_memory_optimized;

Step 8: Trace Flags for SQL Server 2014 and Earlier

If you are on SQL Server 2014 or earlier, enable the two classic TempDB trace flags as startup parameters (-T1117 and -T1118):

• TF 1117: grows all files in a filegroup equally when any one file grows.
• TF 1118: allocates uniform extents, eliminating the mixed-extent SGAM bottleneck.

SQL Server 2016 and later enable both behaviors by default; do not set these flags on newer versions.

Step 9: PeopleSoft Workload Tuning

The storage layer is only half the battle. Reduce the volume of TempDB traffic PeopleSoft generates:

• Stagger nVision and PS/Query runs. Avoid launching all period-end nVision reports simultaneously. Use Process Scheduler job sequencing and queue time offsets so sort operations overlap minimally.
• Tune the largest sort consumers. Use the total_spills query from the checklist to find PeopleSoft queries doing large sorts, then add covering indexes or rewrite queries to reduce ORDER BY / DISTINCT / GROUP BY work. For nVision, leverage summary tables (e.g., PS_SUM_*) instead of scanning transaction detail.
• Right-size Application Engine temp-table instances. In PeopleSoft Application Designer, open the Application Engine program properties → Temp Tables tab. Set the Number of Instances high enough for expected concurrent batch processes, but no higher — every instance is a table whose operations can spill into TempDB. Enable Do not drop for long-running processes to avoid repeated create/drop churn.
• Monitor version store. If your PeopleSoft databases use RCSI for reporting, the version store can consume most of TempDB during long-running reports. If version-store growth is the dominant category, evaluate read-only replicas or a dedicated reporting database instead of RCSI on the OLTP source.
• Validate Process Scheduler cleanup. Ensure the Run Process Cleanup job runs so orphaned temp-table instances and process output do not accumulate.

Step 10: Validate and Monitor

After the restart:

1. Re-run the Diagnostic Checklist queries.
2. Confirm the new file layout: N equally sized ROWS files, 1,024 MB growth, no percentage growth.
3. Confirm PAGELATCH_* waits on file 1 pages 1–3 have dropped to near zero.
4. Confirm the error log is clean of 1105 and 9002.
5. Watch the first full PeopleSoft batch cycle and compare batch duration to the baseline.

Set up proactive capacity alerts:

-- Alert when TempDB free space drops below 20% of total
SELECT
    SUM(f.unallocated_extent_page_count) / 128.0 AS free_mb,
    SUM(mf.size) / 128.0 AS total_mb
FROM sys.dm_db_file_space_usage AS f
CROSS JOIN sys.master_files AS mf
WHERE mf.database_id = DB_ID('tempdb') AND mf.type_desc = 'ROWS';

📚 Official Documentation & Technical References

• Microsoft Learn — TempDB Database: https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
• Microsoft Learn — Recommendations to Reduce Allocation Contention in SQL Server TempDB: https://learn.microsoft.com/en-us/troubleshoot/sql/performance/recommendations-reduce-allocation-contention
• Microsoft Learn — Database Instant File Initialization: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization
• Microsoft Learn — DBCC TRACEON / Trace Flags (1117, 1118, 692): https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
• Microsoft Learn — sys.dm_db_file_space_usage: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql
• Microsoft Learn — sys.dm_os_wait_stats: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql
• Oracle Help Center — PeopleTools Documentation (Application Engine, nVision, Process Scheduler): https://docs.oracle.com/en/applications/peoplesoft/peopletools/index.html
• Oracle Help Center — PeopleSoft Application Documentation: https://docs.oracle.com/en/applications/peoplesoft/

No My Oracle Support (MOS) Doc IDs are referenced in this article; the Microsoft and Oracle links above are the canonical sources for the behaviors described.

---

Get Help From DBPros.Net

TempDB tuning for PeopleSoft is a specialty — the SQL Server file layout, the Windows storage stack, and the PeopleSoft batch scheduler all have to be right together. If you need a second opinion, a full performance audit, or help executing this runbook in your environment, contact the DBPros.Net enterprise team.

👉 Contact Us · View Our Services