sqlfingers.com: Resource Governor in SQL Server 2025. A Good Change.

Someone runs a massive SELECT INTO #temp, tempdb fills the drive, and the entire instance freezes up dead. You get paged at 2 AM, kill the session, shrink the files, and spend the next day writing a monitoring script that you hope will catch it next time.

SQL Server 2025 finally lets you stay ahead of this. The Resource Governor can now cap how much tempdb space a workload is allowed to consume. Exceed the limit and SQL Server kills the query — not the instance. How cool is that? It's like proactive DBA-ing without the DBA. 😜

What Changed

The Resource Governor has been around since SQL Server 2008, managing CPU, memory, and I/O. Personally, I've always thought it was a bit tedious and somewhat high-maintenance. SQL Server 2025 has added a new option to limit tempdb space consumption that makes things a little more manageable:

Setting	What It Does
`GROUP_MAX_TEMPDB_DATA_MB`	Hard cap in megabytes on tempdb data space for the group
`GROUP_MAX_TEMPDB_DATA_PERCENT`	Cap as a percentage of max configured tempdb size

When a query in the managed workload group tries to push past the limit, SQL Server aborts it with error 1138:

Msg 1138, Level 17
Could not allocate a new page for database 'tempdb' because 
that would exceed the limit set for workload group 'default'.

The query dies. The instance lives. End of story.

Step 1: Enable Resource Governor

Resource Governor is disabled by default. Run this to enable it:

ALTER RESOURCE GOVERNOR ENABLE;

Step 2: Monitor Before You Cap

SQL Server 2025 introduces tempdb usage tracking per workload group. Once Resource Governor is enabled, query the following to establish a baseline before setting any limits:

SELECT
    wg.group_id,
    wg.name AS workload_group,
    rg.group_max_tempdb_data_mb AS cap_mb,
    wg.tempdb_data_space_kb / 1024.0 AS current_mb,
    wg.peak_tempdb_data_space_kb / 1024.0 AS peak_mb,
    wg.total_tempdb_data_limit_violation_count AS violations
FROM sys.dm_resource_governor_workload_groups wg JOIN sys.resource_governor_workload_groups rg 
  ON rg.group_id = wg.group_id;

Watch the peaks. You need to know what your workloads actually consume before you set a cap. Setting a 20 GB limit on an instance where reporting queries routinely spike to 25 GB means you're just going to kill the reporting.

Step 3: Set the Cap

The Resource Governor uses workload groups to classify sessions. Every instance ships with a default workload group, and any session that is not explicitly routed to a named group lands in there. This is how you'd limit the tempdb usage on the default group, which puts a ceiling on every unclassified session on the instance:

ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Or to remove that limit:

ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

What It Governs (and What It Does Not)

The Resource Governor tracks tempdb data space only. That includes temp tables, table variables, spools, spills, worktables, and sort space. It does not govern tempdb log space and it does not govern the version store.

For the log side, SQL Server 2025 also added support for Accelerated Database Recovery (ADR) on tempdb, which provides instantaneous transaction rollback and aggressive log truncation. Between tempdb data governance and ADR on tempdb, both sides are now more manageable — but they are two separate features that must both be configured separately. NOTE: You cannot enable ADR on the tempdb without a service restart.

The Gotchas

Capping the default workload group is a good start, but not a full solution. If every session lives in the default group, a single query can easily consume the whole cap, leaving everything else with the error 1138 shown above. With mixed workloads, the default group alone is not enough. Reporting and OLTP behave differently under tempdb pressure, and they won't play nicely together. I recommend classifying them into separate user-defined workload groups, each with its own ceiling. This requires a classifier function in master, which is more setup, but very much worth it for environments with mixed workloads. See here: Microsoft Learn.

Tempdb file configuration matters if you use the GROUP_MAX_TEMPDB_DATA_PERCENT setting. With this configuration, your tempdb data files need either autogrow disabled or a defined MAXSIZE, or the percentage setting will not be enforced. When the file config requirements are not met, SQL Server silently ignores the limit and issues warning 10989. Also, if you change tempdb file config after setting percent-based limits, SQL Server also doesn't tell you that your cap just changed. Using the MB-based setting allows more predictable behavior. See Tempdb space resource governance on Microsoft Learn.

ADR on tempdb has a known issue. Microsoft has documented that when ADR is enabled on tempdb, and temporary tables are being created and dropped at a high rate, the workload throughput can be substantially reduced due to latch contention on the sys.sysseobjvalues system table. This issue is currently under investigation. If your workload involves heavy temp table churn, you may want to hold off before enabling ADR on tempdb in production. Or at least test extensively before calling it good.

The version store is not governed. If you use Read Committed Snapshot Isolation (RCSI), snapshot isolation, or ADR, the version store will grow independently of your workload group limits. A long-running transaction can fill tempdb through the version store even if every workload group is perfectly capped. The version store size must be monitored separately via sys.dm_db_file_space_usage.

The Bottom Line

I can't count the number of times I've seen a tempdb filling up its drive. Before SQL Server 2025, the only real options were custom monitoring jobs, alerts — and just hoping that nobody is querying things too ambitiously. Now there is a native kill switch that will let you stay ahead of things. Even better, it is available in Standard Edition.

First, establish good baselines for your hottest sessions. Then enable the Resource Governor and set your cap accordingly. Stop worrying about 2 AM pages for tempdb.

Standard Edition Gets It Now, Too

Also good is that as of SQL Server 2025, Microsoft has moved the Resource Governor from being an Enterprise-only feature to being included in both Standard and Enterprise editions with identical, full capabilities.

SQL Server Consulting

Thursday, March 26, 2026

Resource Governor in SQL Server 2025. A Good Change.