TempDB Resource Governor Space Controls in SQL Server 2025

TempDB is a system database in SQL Server that manages temporary objects for all databases on a server. Because it is a shared resource, it can easily become a performance bottleneck., as well as an opportunity for a single database or even a single query to wreck SQL Server performance.

Because of this, administrators will often give TempDB plenty of space and put its files on the fastest storage available. Even with these precautions, it is still possible for a TempDB-hungry workload to hog space and resources that result in latency or timeouts for end users.

SQL Server 2025 introduces the ability to use Resource Governor to help manage TempDB resources automatically. This article dives into the new feature, how to configure it, and how it can effectively manage TempDB-heavy workloads.

Note that this article was written using SQL Server 2025 CTP2.0. If feature updates arrive that impact Resource Governor for TempDB, then this article will be updated accordingly, along with this note. Also, in previous versions of SQL Server, Resource Governor was an Enterprise Edition feature.

Background

SQL Server stores a variety of data that is temporary in nature. This data is used in query processing, as well as in managing isolation levels and transactions. To illustrate the variety of uses that TempDB has, the following is a list of many of the types of objects stored there:

Temporary tables & Table Variables
Temporary Stored Procedures
Cursors
Result sets returned by table-valued functions
Worktables and work files
Intermediate sort results
Index rebuilds that use the SORT_IN_TEMPDB option
Row versions and version stores

This list has grown significantly over the years as more features have been added to SQL server. Because TempDB is simply another database that is stored just like any other database, it can easily be a bottleneck and single-point-of-failure for a SQL Server.

If TempDB space is exhausted and subsequent queries/processes cannot allocate space, then they will fail. This is the worst-case scenario that will mean some amount of downtime or errors for an application relying on this SQL Server. Inconsistent behavior will persist until the TempDB problem is resolved.

Up until now, managing TempDB workloads was often a balance of:

Give TempDB more space/files
Write custom monitoring/management procedures to deal with runaway workloads

The first of these options is easy but can be expensive and become inadequate as workloads grow in both size and complexity. The second option requires extensive administrative experience and may be challenging to implement effectively.

A built-in option to assist managing TempDB is a welcome addition to SQL Server and one that could greatly aid administrators that are struggling with TempDB resource contention or queries that cause runaway TempDB growth.

Brief Overview of Resource Governor

Resource Governor is a mature feature that debuted all the way back in SQL Server 2008. Because of the complexity of effectively managing workloads at runtime, there is a bit of a learning curve when implementing Resource Governor. The following is a quick guide to getting started, which will help us get ready to use it with TempDB. If you are already a Resource Governor pro, then feel free to skip ahead to the next section.

By default, Resource Governor is disabled. This can be seen visually in SQL Server Management Studio:

The feature may be enabled by executing the following T-SQL:

1	ALTER RESOURCE GOVERNOR RECONFIGURE;

If there were any previous active configurations in Resource Governor, then enabling it like this will re-enable all those prior settings.

Once enabled, there are three basic features that go into using it, each with a distinct purpose:

Resource Pools
Workload Groups
Classifier Functions

Resource Pools

A resource pool is a logical container for a server’s physical resources. By default, a server will contain an internal resource pool and a default resource pool. You may create more based on a server’s configuration and complexity.

For the simplest of applications, using the default resource pool is perfectly fine. For larger servers with multiple applications and SLAs, there may be a need to have multiple resource pools available to separately manage those different use-cases.

Note that all queries to configure resource governor are run against the master database.

The following T-SQL creates a new resource pool:

USE master;

CREATE RESOURCE POOL DemoPool

WITH

( MAX_CPU_PERCENT = 75,

MAX_MEMORY_PERCENT = 50);

Note that this example limits CPU usage to 75% and memory usage to 50% for these workloads. This query returns details on all resource pools:

1 2	SELECT * FROM sys.resource_governor_resource_pools

The results show the default resource pools, as well as the one we just created:

Workload Groups

A workload group is linked to a resource pool and is used to classify sessions. If you are only using a single resource pool, then this is trivial, but if you are using multiple resource pools, then each workload group would correspond to an application or set of applications.

The following query creates a new workload group and assigns it to the resource pool created earlier:

1 2	CREATE WORKLOAD GROUP DemoGroup USING DemoPool;

Another DMV can be used to list the current workload groups defined on this server:

1 2	SELECT * FROM sys.resource_governor_workload_groups;

The output shows the internal and default workload groups, as well as the one just created:

Note that the statistics in this view are reset on server restart, but can also be reset using the following T-SQL:

1	ALTER RESOURCE GOVERNOR RESET STATISTICS;

Classifier Functions

The last basic component of resource governor is the classifier function. These functions return the name of a workload group based on the session of the application’s connection. Its contents can be whatever is typically allowed in a scalar user-defined function, though it is generally preferable to keep things as simple as possible. Therefore, a simple IF/THEN/ELSE or CASE structure is ideal when possible.

The following is a simple classifier function that checks for a single app name and returns our DemoGroup workload group when matched. Otherwise, the default workload group is used:

USE master;

CREATE FUNCTION dbo.DemoClassifierFunction()

RETURNS SYSNAME

WITH SCHEMABINDING

BEGIN

IF (APP_NAME() = 'EdsCoolDemoApp')

BEGIN

RETURN 'DemoGroup';

END

RETURN 'default';

END;

Once created, a classifier function must be activated:

1 2	ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.DemoClassifierFunction); ALTER RESOURCE GOVERNOR RECONFIGURE;

The details of classifier functions can be pulled using another system view query:

SELECT

resource_governor_configuration.classifier_function_id,

resource_governor_configuration.is_enabled,

objects.[name] AS function_name,

sql_modules.[definition] AS function_definition

FROM sys.resource_governor_configuration

INNER JOIN sys.objects

ON resource_governor_configuration.classifier_function_id = objects.[object_id]

INNER JOIN sys.sql_modules

ON objects.[object_id] = sql_modules.[object_id];

The results show the basics of the function we created:

The results confirm that the function is enabled, and the definition is added in for posterity. There are other columns available in each view that may be helpful for troubleshooting or documentation purposes, as well.

Note that the classifier function must be created in the master database. This ensures it is available for all connections and databases.

Configuring Resource Governor for TempDB

With the absolute basics of Resource Governor out-of-the-way, the next step is to introduce how it is used by TempDB. It is important to note that the ONLY metric that can be bound by Resource Governor is data space used.

TempDB log space cannot be managed here – but that is OK! Why? Starting in SQL Server 2025, Accelerated Database Recovery (ADR) can be enabled on TempDB. The aggressive log truncation provided by ADR greatly reduces log space usage and churn in TempDB. Because TempDB is a temporary data store, the recovery aspects of ADR become irrelevant, making this an ideal feature to test and enable on TempDB!

Because Resource Governor has limited usage for TempDB, the amount of work needed to configure it is less than for other databases and metrics.

To begin, the default workload group will be adjusted to allow for a maximum of 20,000MB of data space for TempDB:

1 2	ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 20000); ALTER RESOURCE GOVERNOR RECONFIGURE;

This executes successfully and the resulting configuration change can be confirmed with this query:

SELECT

group_id,

[name],

group_max_tempdb_data_mb

FROM sys.resource_governor_workload_groups;

This will return:

Joining a few views together allows for efficient review of both the configuration setting and usage for the default workgroup:

SELECT dm_resource_governor_workload_groups.group_id,

dm_resource_governor_workload_groups.[name],

resource_governor_workload_groups.group_max_tempdb_data_mb,

dm_resource_governor_workload_groups.total_tempdb_data_limit_violation_count,

dm_resource_governor_workload_groups.tempdb_data_space_kb,

dm_resource_governor_workload_groups.peak_tempdb_data_space_kb

FROM sys.dm_resource_governor_workload_groups

INNER JOIN sys.resource_governor_workload_groups

ON dm_resource_governor_workload_groups.[name] =

resource_governor_workload_groups.[name]

WHERE dm_resource_governor_workload_groups.[name] = 'default';

The results of this query are quite useful:

Not only is the max TempDB data space setting shown here, but usage numbers are provided that indicate:

The current TempDB data space limit (tempdb_data_space_kb)
The maximum TempDB data space used since the last restart (peak_tempdb_data_space_kb)
The number of times the data space limit was exceeded, triggering the Resource Governor to end the transaction (total_tempdb_data_limit_violation_count)

Note that the usage numbers are in kilobytes, which I retained here to show granularity when usage was low.

Testing TempDB Resource Governor

To test TempDB resource governance, the workgroup, resource pool, and classifier function from earlier will be used. Note that the app name that will be routed to the DemoGroup is the app “EdsCoolDemoApp”. To actively query as this app, the connection will be changed from SQL Server Management Studio:

From the dialog window, the name of the app will be entered in the additional connection parameters:

When connected, the current app name and associated workload group can be verified with this T-SQL:

SELECT APP_NAME() AS AppName;

SELECT dm_resource_governor_workload_groups.[name] AS WorkloadGroupName

FROM sys.dm_exec_sessions

INNER JOIN sys.dm_resource_governor_workload_groups

ON dm_exec_sessions.group_id =

dm_resource_governor_workload_groups.group_id

WHERE dm_exec_sessions.session_id = @@SPID;

The results provide the expected result (phew!):

To make for a speedy demo, the DemoGroup will be adjusted to have a 2MB TempDB data space limit:

1 2	ALTER WORKLOAD GROUP DemoGroup WITH (GROUP_MAX_TEMPDB_DATA_MB = 2); ALTER RESOURCE GOVERNOR RECONFIGURE;

The following query verifies the current TempDB configuration for this workgroup:

SELECT dm_resource_governor_workload_groups.group_id,

dm_resource_governor_workload_groups.[name],

resource_governor_workload_groups.group_max_tempdb_data_mb,

dm_resource_governor_workload_groups.total_tempdb_data_limit_violation_count,

dm_resource_governor_workload_groups.tempdb_data_space_kb,

dm_resource_governor_workload_groups.peak_tempdb_data_space_kb

FROM sys.dm_resource_governor_workload_groups

INNER JOIN sys.resource_governor_workload_groups

ON dm_resource_governor_workload_groups.[name] =

resource_governor_workload_groups.[name]

WHERE dm_resource_governor_workload_groups.[name] = 'DemoGroup';

The results are as follows:

SELECT *

INTO #TempTableTest

FROM sys.sql_modules;

When executed, an error message is returned:

Msg 1138, Level 17, State 1, Line 177

Could not allocate a new page for database ‘tempdb’ because that would exceed the limit set for workload group ‘DemoGroup’.

The results show that the violation count has been incremented by one and that the peak TempDB data space used was exactly 2MB, which was the cap that was configured prior to this demo.

A fun additional query to run after the failure:

1	SELECT * FROM #TempTableTest;

The results are as follows:

This shows that the table contains nothing. WAIT! What table? Why does the temporary table exist if the SELECT INTO statement failed? This is, in fact, standard behavior and not an anomaly.

SELECT INTO is really the combination of CREATE TABLE and SELECT. The Resource Governor failure occurred during the SELECT portion of the query, after the temporary table was created. Therefore, if a TRY…CATCH attempts to recreate the table after the failure, the result will be an error indicating that the table already exists. This is a bit of an edge-case, but worth mentioning as it can impact more complex processes.

Disabling Resource Governor

As with any feature, there may be a need to disable part or all of it. This is (luckily) straightforward! To disable TempDB resource governance, the following T-SQL may be used:

1 2	ALTER WORKLOAD GROUP DemoGroup WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);

This is run separately on each workload group. Therefore, if multiple workload groups are configured and in-use, be sure to disable TempDB resource governance on all that are no longer to be used for this purpose. All other Resource Governor features will continue to operate normally after this is executed.

Workload groups, resource pools, and classifier functions can be dropped when no longer needed. Ensure that they are dropped in an order such that no dependencies are violated. For example, if a workload group relies on a resource pool, then the workload group must be dropped first, prior to the resource pool.

The following script drops the various objects created during the demos in this article:

DROP WORKLOAD GROUP DemoGroup;

DROP RESOURCE POOL DemoPool;

DROP FUNCTION dbo.DemoClassifierFunction;

Oops! An error is thrown when this executes:

Msg 10920, Level 16, State 1, Line 199

Cannot drop user-defined function ‘DemoClassifierFunction’. It is being used as a resource governor classifier.

Classifier functions are bound to Resource Governor and need to be removed prior to dropping them. The following script will accomplish this:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);

RECONFIGURE

DROP FUNCTION dbo.DemoClassifierFunction;

This results in glorious success! Finally, if there is a need to fully disable Resource Governor, that can be done like this:

1	ALTER RESOURCE GOVERNOR DISABLE;

Notes on Resource Governor for TempDB

TempDB can be configured with static space that is assigned to each file, or files can be set to auto-grow when they fill up. This is no different than any other user database.

Because files can potentially grow, bounding TempDB space usage by a static space amount can be limiting. Another natural approach is to manage free space as a percentage of the total available TempDB space, or as a percentage of the maximum space. For some administrators, this is a more natural way to manage space constraints.

For example, one way to limit space used in this fashion would be to kill a query if its space used exceeds 80% of the total available space in TempDB. While the percentage number will vary from app-to-app and server-to-server, the fundamental principle remains the same.

It is very likely that a feature change to TempDB Resource Governance in the near future will add the ability to limit space used as a percentage, rather than a hard-coded space amount. Since this feature is still in public preview, we can look forward to improvements as feedback is received on this useful feature! This article will be updated accordingly as features added or tweaked.

Conclusion

Resource Governor gets a fresh breath of air with this long-desired feature edition! No longer do administrators need to cobble together scripts and processes to manage TempDB. TempDB resource governance is easy to configure and use when compared to other components of Resource Governor.

This feature is all-but-guaranteed to be improved in the future. Ultimately, I expect TempDB monitoring to become far simpler in the future with this addition. As a bonus, out-of-the-box solutions are easier to use and maintain than customized solutions that we need to put together and manage.

Once running SQL Server 2025, give this new feature a try! If you have never managed TempDB space before, this provides an easy way to do so, even without extensive SQL Server domain knowledge.

Do you have any experiences with TempDB monitoring with (or without) Resource Governor? If so, let me know!

Register for Simple Talk

TempDB Resource Governor Space Controls in SQL Server 2025

Background