Resource Governor: Capping a Runaway Report

One report can ask for enough CPU or memory to make everyone else wait. Capping a runaway report with Resource Governor is a way to limit that workload after you identify it reliably.

Confirm the Report Is the Problem

Capture the report’s login, application name, queries, execution frequency, CPU, memory grants, and waits before adding a limit. A slow report can be the victim of blocking or poor storage rather than the cause of server pressure. Resource Governor is useful when one identifiable workload consumes more than its agreed share. It does not repair a bad join or missing predicate.

I start with Query Store and live request evidence. If the report is rare, a query rewrite can be simpler than a server-wide classifier. If many reports share an account, confirm whether limiting the group would also slow critical work. Ask who owns the report and what response time they can accept. A cap without that conversation can turn an incident into a permanent complaint.

Understand Pools and Groups

A resource pool defines resource boundaries. A workload group holds policies for sessions assigned to it and belongs to a pool. The classifier function chooses the group when a new session connects. Existing sessions do not switch groups just because you change the function. Plan reconnects for testing and production rollout.

I draw these three parts before writing SQL. It prevents the common mistake of creating a pool and expecting SQL Server to send a report there automatically. The group needs a route, and the route needs a stable identity signal. The default workload remains outside the special rule when classification is narrow. That is how the change can protect other users.

Choose a Stable Classifier

APP_NAME() can identify an application, but clients can set that name. It is useful for cooperative workload routing, not a security boundary. A dedicated report login or verified combination of attributes is easier to govern. Keep the classifier simple and fast because SQL Server invokes it when sessions connect. A broken classifier can cause broad connection trouble.

I ask the application team to keep report connections distinct from transactional connections. If both use the same pool and login, the classifier cannot reliably separate them. Test every expected connection path and an unexpected name. The default branch should send unknown sessions to the normal group, not to a restrictive pool by accident.

Inspect the Current Setup

Read the current Resource Governor state before proposing changes. An inherited instance can already have pools and groups for other workloads. Capture the configuration and identify the owner. A new group name should not collide with an existing one. The query below shows pools and groups without changing them.

I keep the snapshot with the change plan. Resource Governor settings can interact, and the team needs a clear before state. The catalog tells you the configured shape. Runtime DMVs later show where sessions landed. Both views matter.

SELECT g.name AS group_name,
       p.name AS pool_name,
       g.max_dop,
       p.min_cpu_percent,
       p.max_cpu_percent
FROM sys.resource_governor_workload_groups AS g
JOIN sys.resource_governor_resource_pools AS p
  ON p.pool_id = g.pool_id
ORDER BY p.name, g.name;

Create a Focused Group for Capping a Runaway Report

A workload group can limit degree of parallelism without reserving a separate CPU pool. That is a useful first control when a report launches very wide parallel plans. For a genuine shared-resource cap, plan a pool with reviewed CPU or memory settings and test under concurrent load. The example creates a group using the default pool. Adjust the value after workload testing, not from a generic recipe.

I avoid making the report unusable merely to make the dashboard quiet. The limit should protect interactive work while leaving a path for scheduled reporting. Compare response time and server pressure before and after. A successful cap is a service decision, not simply a lower CPU graph.

CREATE WORKLOAD GROUP [ReportLimited]
WITH (MAX_DOP = 4)
USING [default];

Attach a Classifier Safely

Create the classifier in master and return a valid group name for report sessions. The function below uses an application name only as a teaching example. In production, use a reviewed identity signal. Create it in a separate batch in a query window connected to master, then register it and reconfigure Resource Governor. Test from a fresh connection.

I verify the default branch explicitly. A typo that routes ordinary sessions into the limited group can affect the whole application. Keep a rollback command ready before enabling the classifier. Do not edit a working classifier without documenting its current rules.

CREATE OR ALTER FUNCTION dbo.ReportClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN APP_NAME() = N'ReportClient'
                THEN N'ReportLimited'
                ELSE N'default' END;
END;

Apply and Verify the Route

After setting the classifier function, ALTER RESOURCE GOVERNOR RECONFIGURE applies the configuration. New sessions then receive a group. Open fresh report and ordinary application connections, and query runtime group information to confirm routing. Old pooled connections can keep their earlier classification until recycled. The rollout plan should account for that delay.

I test a normal transaction at the same time as the report. The point is to protect the shared server, not to create a benchmark for the report alone. Record the result from the real workload and review the setting after a full business cycle.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ReportClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Watch for Side Effects of Capping a Runaway Report

Resource Governor can constrain CPU, memory, and other resources depending on configuration and edition. A limit can make a report take longer and hold locks or tempdb resources for longer. Watch the whole workload, not only its CPU use. Compare waits, report completion, and application latency under representative concurrency.

I have seen capping a runaway report described as a fix when it only moved the queue. That is a rather expensive way to change the color of a chart. Keep tuning the report query, indexes, and schedule where those are the real causes. The governor is a boundary, not a substitute for query design.

Keep Capping a Runaway Report Reversible

Document group, pool, classifier, affected clients, test evidence, and a rollback path. The classifier function is an instance-level routing decision even when the T-SQL lives in master. Review it after driver updates or connection string changes, since APP_NAME() can change. Remove old groups only after their sessions have disconnected.

What would happen if the report client stopped setting its expected application name? Test that case. It should fall into a known group and raise an operational question, not quietly escape a critical limit forever. A useful Resource Governor setup stays understandable to the next DBA who inherits it.

Related reading on this blog: Enable or Disable Resource Governor and Simple Example to Configure Resource Governor: Introduction to Resource Governor.

A Resource Governor cap is not a query fix, it is a workload boundary that needs a reliable route.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.