SQLDBA-SQL Server 2025 Enhancements for Managing TempDB Growth

SQL Server 2025 Enhancements for Managing TempDB Growth

SQL Server 2025 is now in Public Preview, which means it's free to test and provide feedback on. However, it's not yet generally available (GA), so it's not supported for production environments at this stage.

One of the highlights of this release is the continued focus on TempDB improvements. I'm glad to see that both SQL Server 2022 and now 2025 have introduced notable enhancements in this area, which should help with performance and scalability.

 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 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.

 What You Should Monitor in TempDB

In TempDB, there are two things that you should be monitoring:

• TempDB datafiles
• TempDB logfiles

If either of them gets filled up, it can bring down the whole SQL Server instance. There is no control until now for the admins to stop any process from filling up the data or log files, unless manually intervened.

Issue - Uncontrolled Usage

One of the key challenges today is that any user with access can easily fill up TempDB and bring down the entire SQL Server instance.

For example, if someone with read access to a large user table decides to dump it into a temp table, there's a high risk that it could consume all TempDB data file space, causing the instance to crash.

 New in SQL Server 2025- Resource Governor for TempDB

To avoid the situation just explained above, in 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.

 

• This restricts filling up TempDB datafile.
• It can stop the operation once it crosses the set threshold.
• Can be set per application or across all applications.

That being said, TempDB Resource Governor manages only data files and not log file.

So who/what manages TempDB logfile then?

New in SQL Server 2025- ADR Support in TempDB

ADR (Accelerated Database Recovery) is a feature introduced in SQL Server 2019, which speeds up database recovery for user databases.

Starting SQL Server 2025, ADR is available to enable in TempDB. It is expected to help with:

• Aggressive log truncation
• Instantaneous rollback of transactions in TempDB

But, it was enabled by default in Azure SQL in February, but rolled back in March 2025 due to performance degradation.

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:

 

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 max_cpu_percent_memoery_percent

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:

 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:

 CREATE WORKLOAD GROUP max_cpu_percent_memoery_percent_Group

USING max_cpu_percent_memoery_percent;

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

 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:

 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;

GO

 CREATE FUNCTION dbo.max_cpu_percent_memoery_percentClassifierFunction()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

    IF (APP_NAME() = 'DemoApp')

BEGIN

        RETURN 'max_cpu_percent_memoery_percent_Group';

END

    RETURN 'default';

END;

  

Once created, a classifier function must be activated:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.max_cpu_percent_memoery_percentClassifierFunction);

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

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:

 

ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 20000);

ALTER RESOURCE GOVERNOR RECONFIGURE;

 

Before Change:

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:

 

After change:

SELECT

group_id,

[name],

group_max_tempdb_data_mb

FROM sys.resource_governor_workload_groups;

 

 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';

 

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 “DemoApp”. 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:

 

ALTER WORKLOAD GROUP max_cpu_percent_memoery_percent_Group 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] = 'max_cpu_percent_memoery_percent_Group';

 

 The results are as follows:

 

SELECT *

INTO #TempTableTest

FROM sys.sql_modules;

 

Msg 1138, Level 17, State 1, Line 27

Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'max_cpu_percent_memoery_percent_Group'.

 

Completion time: 2025-07-20T11:25:13.8136436-04:00

 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] = 'max_cpu_percent_memoery_percent_Group';

 

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:

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:

 

                    ALTER WORKLOAD GROUP max_cpu_percent_memoery_percent_Group

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 max_cpu_percent_memoery_percent_Group;

DROP RESOURCE POOL max_cpu_percent_memoery_percent;

DROP FUNCTION dbo.max_cpu_percent_memoery_percentClassifierFunction;

 

 

Oops! An error is thrown when this executes:

Msg 10920, Level 16, State 1, Line 59

Cannot drop user-defined function 'max_cpu_percent_memoery_percentClassifierFunction'. It is being used as a resource governor classifier.

 

Completion time: 2025-07-20T11:31:52.8001615-04:00

 

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:

ALTER RESOURCE GOVERNOR DISABLE;

Notes on Resource Governor for TempDB

 

Resource Governor for TempDB – Key Notes

SQL Server 2025 introduces enhancements to TempDB resource governance, offering administrators more flexibility and control:

Key Concepts

• TempDB Space Configuration: TempDB files can be configured with static sizes or set to auto-grow, similar to user databases.
• Limitations of Static Allocation: Relying solely on fixed space limits can be restrictive, especially when files are allowed to grow.
• Percentage-Based Management: A more intuitive approach is to manage TempDB usage as a percentage of total or maximum space. For example, a query could be terminated if it exceeds 80% of TempDB usage.

Upcoming Enhancements

• SQL Server 2025 (currently in Public Preview) is expected to support percentage-based space limits in Resource Governor, making TempDB management more dynamic and aligned with real-world usage patterns.
• This feature is still evolving, and improvements are anticipated based on user feedback.

Conclusion

This update breathes new life into Resource Governor by simplifying TempDB management:

• Reduces the need for custom scripts and manual monitoring.
• Makes TempDB governance more accessible, even for those with limited SQL Server expertise.
• Encourages adoption of out-of-the-box solutions that are easier to maintain.

Recommendation: Once on SQL Server 2025, try out this feature—especially if you’ve never actively managed TempDB space before.