https://sqlmaster.mssqlplanner.com

Tag: Resource Governor

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

    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.

    One instance, two workloads, one gate(capping blast radius)OLTP APPwide-open pipeno CPU cap neededAD-HOC REPORTwants ALL the CPUreporting_svc login30% CPU / 20% MEM capSHARED SQL SERVER INSTANCEOLTP: runs exactlyas fast as before ✓Report: capped —can’t starve prod ✓Reminder: this caps blast radius — it doesn’tfix a bad query. An unindexed report is stillslow, just contained. 📌

    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

    The reporting_svc login can NEVER consume more than 30% CPU or 20% memory, regardless of how badly its queries are written

    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.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Module 7 Exercises: SQL Server Advanced Performance Labs (10 Hands-On Exercises)

    Module 7 Exercises: SQL Server Advanced Performance Labs

    5 guided labs, 3 challenge scenarios, and 2 break-it labs.

    Guided Labs

    Guided1. Create a MEMORY_OPTIMIZED_DATA filegroup and a simple memory-optimized table in a test database.
    Guided2. Write and call a natively compiled stored procedure against that table.
    Guided3. Create a resource pool capping CPU at 20%, a workload group, and a classifier function routing a specific test login into it.
    Guided4. Check whether memory-optimized tempdb metadata is currently enabled on your instance.

    SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
    Guided5. List which features from this entire course (Profiler, Resource Governor, tempdb files) would NOT be available if this workload were moved to Azure SQL Database.

    Challenge Scenarios

    Challenge6. A session-state table is experiencing the exact ever-increasing-key latch contention pattern from Module 5. Propose whether In-Memory OLTP or simply better key design is the more appropriate fix, and justify your choice.
    Challenge7. A shared instance runs both a mission-critical OLTP app and an analyst’s ad-hoc reporting tool that occasionally runs 100% CPU for minutes. Design a Resource Governor configuration to protect the OLTP workload.
    Challenge8. A team is migrating an on-prem SQL Server workload to Azure SQL Managed Instance and worried about losing Profiler and Resource Governor. Write a short migration note explaining what changes and what stays the same.

    Break-It Labs

    Break-It9. Deliberately run an unrestricted heavy query and observe its CPU consumption, then place it under a 10%-CPU-capped Resource Governor workload group and re-measure the difference in wall-clock duration.
    Break-It10. In a disposable test instance, deliberately disable memory-optimized tempdb metadata (if enabled) or simulate its absence by inducing heavy tempdb system-table churn, observe PAGELATCH contention on system tables specifically, then enable it and compare.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Module 7 Quiz: SQL Server Advanced Performance Features (10 Questions)

    Module 7 Quiz: SQL Server Advanced Performance Features

    1. What problem from earlier in this course does In-Memory OLTP most directly address?

    A) Missing indexes
    B) Lock and latch contention under extreme concurrency (Module 5)
    C) Stale statistics
    D) SARGability

    Show Answer

    Answer: B

    Memory-optimized tables use row-versioning and avoid traditional page structures, eliminating the lock/latch mechanisms entirely for those tables.

    2. What makes a natively compiled stored procedure different from a regular one?

    A) No difference at all
    B) It compiles to actual machine code, with a restricted T-SQL surface area
    C) It runs slower but is more flexible
    D) It can only be called from the application tier

    Show Answer

    Answer: B

    The performance ceiling is much higher, but you give up dynamic SQL and other T-SQL features to get it.

    3. Is In-Memory OLTP a good default upgrade for a general-purpose reporting table?

    A) Yes, always upgrade everything
    B) No — it’s best reserved for confirmed extreme-contention scenarios, not a default choice
    C) It’s required for all tables in modern SQL Server
    D) Reporting tables can’t be disk-based

    Show Answer

    Answer: B

    Reach for it only after evidence confirms lock/latch contention is the actual bottleneck — the Evidence-First principle applies here too.

    4. What does a Resource Governor resource pool control?

    A) Disk space quotas only
    B) A capped slice of CPU and memory for a given workload group
    C) User passwords
    D) Backup schedules

    Show Answer

    Answer: B

    Resource pools cap CPU/memory consumption, preventing one workload from starving another sharing the same instance.

    5. What routes an incoming connection to a specific Resource Governor workload group?

    A) The connection string alone
    B) A classifier function evaluated at login
    C) Random assignment
    D) The database name only

    Show Answer

    Answer: B

    The classifier function (e.g. checking SUSER_SNAME()) decides which workload group governs each new session.

    6. Does Resource Governor fix a badly-written, missing-index query?

    A) Yes, it automatically optimizes queries
    B) No — it limits blast radius (CPU/memory cap), it doesn’t fix the query itself
    C) It rewrites the query automatically
    D) It adds missing indexes automatically

    Show Answer

    Answer: B

    It’s a containment guarantee, not a substitute for the actual tuning work from Modules 2-4.

    7. What does enabling memory-optimized tempdb metadata fix, and how does it differ from just adding more tempdb files?

    A) It has the same effect as adding files
    B) It moves tempdb’s system metadata into lock/latch-free structures, eliminating contention at the source rather than spreading it across files
    C) It disables tempdb entirely
    D) It only works on Azure

    Show Answer

    Answer: B

    Multiple files (Module 5) dilute contention; memory-optimized metadata removes the underlying lock/latch mechanism for tempdb system tables entirely.

    8. Is SQL Server Profiler available on Azure SQL Database?

    A) Yes, identical to on-prem
    B) No — Extended Events is the only option there
    C) Only on weekends
    D) Profiler is required, not optional, on Azure

    Show Answer

    Answer: B

    This is exactly why Module 6 taught Extended Events thoroughly — it’s the portable skill across on-prem and Azure SQL Database.

    9. Is Resource Governor available on Azure SQL Database (single/elastic pool)?

    A) Yes, fully available
    B) No — workload isolation instead comes from service tier/compute choice itself
    C) Only for Enterprise Edition
    D) It’s the default behavior with no configuration needed

    Show Answer

    Answer: B

    On Azure SQL Database, the platform abstracts this — your service tier/vCore choice is the isolation mechanism instead.

    10. Do the Evidence-First diagnostic principles from this entire course still apply on Azure SQL Database?

    A) No, Azure requires an entirely different approach
    B) Yes — the diagnostic principles are identical; only which infrastructure-level levers are directly available changes
    C) Only Query Store works on Azure
    D) Azure SQL doesn’t support DMVs

    Show Answer

    Answer: B

    SARGability, execution plans, DMVs, and Query Store all apply the same way — Azure just abstracts some infrastructure control (Resource Governor, tempdb files, Profiler).

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.