Capping tempdb in SQL Server 2025: Resource Governor in Standard Edition
The situation is familiar. One query spills to disk, tempdb grows until the volume fills and every other session on the instance starts failing. The engine has a lever for this since 2008: Resource Governor But for years years it was only available in Enterprise edition. If you were running Standard the only solution was monitoring alert. SQL Server 2025 changed that. Resource Governor is now available in Standard (and Standard Developer) edition and a new workload group setting caps how much tempdb data space a group of sessions can consume. However, there is a catch you should know before starting. On a default instance, the percentage form of the limit is stored but never enforced (even Reconfigure succeed), just a warning in the error log. Good to know What changed Two separate changes landed. Resource Governor moved down the edition ladder. This is now available in Enterprise, Enterprise Developer, Standard and Standard Developer. But tempdb space resource governance is new. Two new arguments on CREATE WORKLOAD GROUP and ALTER WORKLOAD GROUP, GROUP_MAX_TEMPDB_DATA_MB and GROUP_MAX_TEMPDB_DATA_PERCENT allow to cap the total tempdb data space consumed by all sessions in that group. How the limit behaves It is cumulative per group, not per request. The documentation is precise "The limit is for the total space consumed in tempdb by all sessions in a workload group." A session behaving correctly can be aborted because other sessions in its group already spent the budget. That is the intended design, you are protecting the instance not limiting individual queries. Enforcement is an abort, not a throttle. A request that would push the group over its limit aborts with error 1138, severity 17: Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group <groupName> Accounting is in 8 KB pages. A page holding 1 row still costs 8 KB. Space is charged for temporary tables, table variables, table-valued parameters, cursors, non-temporary tables created in tempdb and structures such as spools, spills, worktables and workfiles. Another good thing to know: for global temporary tables and regular tables in tempdb, space is charged to the workload group that inserted the first row even if sessions in other groups add, modify or remove rows afterwards. If the group is dropped while the table still exists then the space stops being charged to anyone. Governance applies to tempdb data files only, not the transaction log. For log growth mitigation, Accelerated Database Recovery can now be used for the tempdb in SQL Server 2025. Version stores are also excluded, including the persistent version store (PVS) when Accelerated Database Recovery (ADR) is enabled in tempdb because it might be used by different workgroups. Before you limit Usage columns are maintained whether or not a limit is configured which makes a limitless deployment a free baselining tool :) On a new instance, enabling Resource Governor is one statement ALTER RESOURCE GOVERNOR RECONFIGURE Create your groups and classifier with no tempdb arguments then let it run during a representative period (including for instance month-end, index maintenance and whatever activity may overwhelmed your instance. You can the read the baseline from SELECT name, tempdb_data_space_kb / 1024.0 AS current_mb, peak_tempdb_data_space_kb / 1024.0 AS peak_mb, total_tempdb_data_limit_violation_count AS violations FROM sys.dm_resource_governor_workload_groups ORDER BY peak_tempdb_data_space_kb DESC These three columns are new in SQL Server 2025. (Note: peak_tempdb_data_space_kb and the violation counter reset with ALTER RESOURCE GOVERNOR RESET STATISTICS) Classify the workload The classifier runs at login and returns a workload group name. It executes for every connection and a slow classifier is a connectivity problem, not a performance problem. So keep it cheap. USE master GO CREATE WORKLOAD GROUP reporting_group GO CREATE FUNCTION dbo.rg_classifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @group sysname = N'default'; IF APP_NAME() = N'ReportingService' SET @group = N'reporting_group'; RETURN @group; END GO ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier) GO ALTER RESOURCE GOVERNOR RECONFIGURE A caveat that matters if you are thinking of this as a control rather than a convenience. APP_NAME() is supplied by the client and can be set to anything. It is fine for separating cooperative workloads and useless against a caller who does not want to be classified. Prefer using authenticated identity Login, Server Role, host rather than a connection string attribute. And the DAC is never classified which is the escape you want when you have misconfigured something. Set a fixed limit ALTER WORKLOAD GROUP reporting_group WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480) GO ALTER RESOURCE GOVERNOR RECONFIGURE 0 means the group may not allocate in tempdb at all, NULL removes the limit. Do not set a small limit and certainly not 0 on the default group. The warning is concrete, you might not be able to open Object Explorer in SQL Server Management Studio (SSMS). Everything unclassified lands in default, including your own troubleshooting session. Over-subscription is the documented and recommended pattern. The sum of group limits may exceed the size of tempdb, because the point is to stop any single group monopolising the file, the objective is not pre-partitioning. The percentage trap GROUP_MAX_TEMPDB_DATA_PERCENT takes effect in exactly two tempdb data file configurations: MAXSIZE is not UNLIMITED on all data files and FILEGROWTH is non-zero on all of them --> 100% is the sum of MAXSIZE. MAXSIZE is UNLIMITED on all data files and FILEGROWTH is zero on all of them --> 100% is the sum of SIZE. Every other configuration is unsupported and a new SQL Server instance got one of them: MAXSIZE is UNLIMITED and FILEGROWTH is greater than zero. The out-of-the-box configuration is precisely the one where percentages do not work. What makes this a trap is the failure mode. ALTER RESOURCE GOVERNOR RECONFIGURE succeeds. The percentage is stored. It is simply not enforced. The only signal is warning 10989, severity 10 which appears in the error log: GROUP_MAX_TEMPDB_DATA_PERCENT is not in effect because tempdb configuration requirements aren't met. Fix it by pre-growing the files and setting FILEGROWTH to zero or by giving every data file a finite MAXSIZE whose per-volume sum fits the available disk. Two further rules. A fixed MB limit always wins over a percentage on the same group, so set GROUP_MAX_TEMPDB_DATA_MB = NULL before switching to percentage. And if a percentage limit is in effect and you add, remove or resize a tempdb data file, run ALTER RESOURCE GOVERNOR RECONFIGURE again so the engine recalculates what 100% means. A note on Copilot Copilot in SSMS is worth pointing at the output of the baseline query rather than at the configuration itself. Select the result grid and ask it to interpret the numbers or use/explain on a DMV query you inherited. SSMS 22.8.0 added built-in agent skills for resource usage, wait category analysis, IO pressure and memory issues which are curated runbooks. Treat generated Resource Governor DDL as a first draft and check it against the documentation, particularly the interaction between the MB and percentage arguments, recent, conditional behaviour is what a language model gets confidently wrong. And note the security position which Microsoft states plainly: "The security boundary is SQL Server's permission enforcement, not Copilot's approval system." Copilot runs as the login you connect with. In conclusion, if you run Standard edition, this is the most consequential change in SQL Server 2025 for day-to-day operations. And if you take one thing away: after any percentage-based configuration, go and read the error log. A successful RECONFIGURE is not evidence that your limit is doing anything.
