Resource Governor in SQL Server: Isolating Workloads on a Shared Instance

A classic problem: an analyst runs an ad-hoc report against the same instance serving the production OLTP app, and it eats all available CPU. Resource Governor caps this at the engine level, without a second server.

The Three Pieces

-- 1. Resource pool: a slice of CPU/memory
CREATE RESOURCE POOL ReportingPool WITH (MAX_CPU_PERCENT = 30, MAX_MEMORY_PERCENT = 20);
GO

-- 2. Workload group: sits inside a pool, can set query-level limits too
CREATE WORKLOAD GROUP ReportingGroup
    WITH (REQUEST_MAX_CPU_TIME_SEC = 60)
    USING ReportingPool;
GO

-- 3. Classifier function: routes incoming connections to the right group
CREATE FUNCTION dbo.fn_ClassifyLogin() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    IF SUSER_SNAME() = 'reporting_svc'
        RETURN 'ReportingGroup';
    RETURN 'default';
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifyLogin);
ALTER RESOURCE GOVERNOR RECONFIGURE;

The Guarantee This Provides

This isn’t a substitute for actually fixing a bad query (Modules 2-4 still apply) — it’s a blast-radius guarantee. Even an un-tuned, missing-index report query can no longer starve the production OLTP workload sharing the instance.