TempDB Under Control in SQL Server 2025

TempDB has been every DBA’s troublemaker.  Runaway sort spills, hash joins gone wild, or a single session chewing up all the space until everything grinds to a halt.  You know, you've all seen it, but with SQL Server 2025, Microsoft finally gave us a new lever to help keep tempdb in check.

TempDB Space Resource Governance (via Resource Governor)

Think of this as a quota system for tempdb. You can now define percent-based limits per workload group. If a session exceeds its quota, it’s stopped before blowing up the entire instance.

Example: create a pool and group with tempdb limits

    -- enable Resource Governor
    ALTER RESOURCE GOVERNOR RECONFIGURE;
    -- create a pool
    CREATE RESOURCE POOL TempDBGuard
    WITH (MAX_TEMPDB_MEMORY_PERCENT = 20);
    -- workload group for reporting queries
    CREATE WORKLOAD GROUP Reporting
    USING TempDBGuard;
    -- classifier function (simplified)
    CREATE FUNCTION dbo.rgClassifier() RETURNS sysname
    WITH SCHEMABINDING
    AS
    BEGIN
        RETURN CASE
            WHEN APP_NAME() = 'Report Builder' THEN 'Reporting'
            ELSE 'default' END;
    END;
    GO
    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rgClassifier);
    ALTER RESOURCE GOVERNOR RECONFIGURE;

What happens when exceeded?

• The offending query fails with error 701 (out of memory)
• Other sessions are protected
• DMV sys.dm_resource_governor_workload_groups shows usage

We've all lived through the 2AM 'tempdb full' events.  Until now, the best we could do was add more files, pre-size them and hope that the workload behaves.  With the resurce governance, we finally get hard stop protections. This moves tempdb from weakest link to something we can actually govern.

VIP (very important point):  These percent limits only apply if your tempdb files have a MAXSIZE defined. If you’re still unlimited + autogrow, the governance can’t kick in.

This isn’t just nice to have.  For shops that rely on heavy reporting, ETL, or big temp table manipulations, this is the difference between full protection vs one bad query killing the instance.

SQL Server 2025 isn’t GA yet, so test this still only Preview.  Let's hope it makes it through the rounds and sticks, so we can finally say that tempDB can be governed.

More to read:   TempDB Space Resource Governance