Part of the DBA-Tools Project.
Two Things a Previous DBA Might Have Set and Never Told You
Trace flags and Resource Governor are both instance-wide behavior changes that don’t show up in sp_configure at a glance, don’t appear in the database, and can sit silently in place for years, inherited from whoever configured the server before you. Both can also be completely invisible until they matter: a trace flag changes locking or query-plan behavior only in specific conditions, and Resource Governor only throttles a session once its classifier function actually routes it into a constrained pool.
Get-TraceFlags lists every active global and session trace flag with a plain-English description, no more guessing what a bare number means. Get-ResourceGovernorConfig shows whether Resource Governor is even enabled, what pools and workload groups exist, and whether the classifier function is actually in place, since RG configuration without a classifier does nothing at all.
Why Trace Flags and Resource Governor Matter
- An active trace flag changes real SQL Server behavior, from deadlock reporting format to identity-caching to autogrowth allocation, and it’s easy to inherit one without knowing why it was set
- Some trace flags are only relevant to specific SQL Server versions,
1117/1118for example are the pre-2016 defaults for autogrowth and extent allocation and often linger unnecessarily on modern instances - Resource Governor enabled with no classifier function does nothing except sit there, every session lands in the default pool regardless of intent, a common half-finished configuration
- A Resource Governor pool that IS actively classifying sessions can silently cap CPU or memory for a workload the DBA didn’t realize was constrained, worth knowing before troubleshooting a performance issue that Resource Governor itself is causing
When to Run These Scripts
- First review of any inherited server, alongside the rest of the Server Inventory cluster, to know what non-default behavior is already in place
- Before troubleshooting unexpected locking, deadlock, or autogrowth behavior, an active trace flag is a common, overlooked explanation
- When performance looks artificially capped for one workload but not another, check whether Resource Governor is actually classifying and constraining it
- After a migration or upgrade, to confirm inherited trace flags are still relevant on the new version (several were made default behavior in later SQL Server releases)
The Scripts
1. Get-TraceFlags — Active Trace Flags with Descriptions
/*
Script Name : Get-TraceFlags
Category : monitoring
Purpose : Active global and session trace flags with descriptions. Reveals undocumented
tuning decisions and flags inherited from previous DBAs.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-trace-flags-and-resource-governor/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-trace-flags-and-resource-governor/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
CREATE TABLE #trace_flags (
TraceFlag INT,
Status SMALLINT,
Global SMALLINT,
Session SMALLINT
);
INSERT INTO #trace_flags
EXEC ('DBCC TRACESTATUS(-1) WITH NO_INFOMSGS');
SELECT
tf.TraceFlag AS trace_flag,
CASE tf.Global WHEN 1 THEN 'Yes' ELSE 'No' END AS is_global,
CASE tf.Session WHEN 1 THEN 'Yes' ELSE 'No' END AS is_session,
CASE tf.Status WHEN 1 THEN 'ON' ELSE 'OFF' END AS status,
CASE tf.TraceFlag
WHEN 272 THEN 'Disables increment-by-1 identity behaviour; reverts to pre-2012 non-caching behaviour'
WHEN 460 THEN 'Replaces string-truncation error 8152 with 2628 (includes column name); default in SQL 2019+'
WHEN 610 THEN 'Minimally logged inserts into indexed tables (pre-2016 SQL)'
WHEN 834 THEN 'Large page allocations for buffer pool (Enterprise only; can cause startup issues)'
WHEN 845 THEN 'Enable large pages for Standard Edition buffer pool'
WHEN 902 THEN 'Bypasses execution of database upgrade script during CU/SP install'
WHEN 1117 THEN 'Grows all files in filegroup equally when autogrowth triggers (default in SQL 2016+ per filegroup)'
WHEN 1118 THEN 'Forces uniform extent allocations for all databases (default in SQL 2016+)'
WHEN 1204 THEN 'Returns resources and types of locks participating in deadlock'
WHEN 1211 THEN 'Disables lock escalation based on memory pressure or number of locks'
WHEN 1222 THEN 'Returns resources, types, and lock graph of deadlock (XML format; preferred over 1204)'
WHEN 1224 THEN 'Disables lock escalation based on number of locks'
WHEN 2312 THEN 'Forces new CE (SQL 2014 70+) in older compat levels'
WHEN 2335 THEN 'Generates more conservative memory grants'
WHEN 2371 THEN 'Changes auto-update stats threshold to dynamic (pre-2016 default was 20%)'
WHEN 2453 THEN 'Allows table variables to trigger recompile on row count changes'
WHEN 2528 THEN 'Disables parallel DBCC checks'
WHEN 3023 THEN 'Enables CHECKSUM backup option when not set as default'
WHEN 3042 THEN 'Disables default pre-growth backup compression algorithm'
WHEN 3226 THEN 'Suppresses successful backup messages in SQL errorlog'
WHEN 3625 THEN 'Limits amount of info returned in error messages to sysadmin only'
WHEN 4199 THEN 'Enables QO hotfixes post-RTM (default ON in SQL 2017+ compat 140+)'
WHEN 4616 THEN 'Makes server-level metadata visible to application roles'
WHEN 6498 THEN 'Enables more than one large query compilation to gain access to the big gateway'
WHEN 7412 THEN 'Enables lightweight query execution statistics profiling infrastructure'
WHEN 7745 THEN 'Forces QS not to flush data to disk on database shutdown'
WHEN 7752 THEN 'Enables async load of QS on database startup'
WHEN 8032 THEN 'Reverts cache limit parameters to SQL 2005 RTM setting'
WHEN 8048 THEN 'Converts NUMA node memory objects to CPU-partitioned objects'
WHEN 8075 THEN 'Reduces VAS fragmentation when receiving 8193/8198 memory errors (x64)'
WHEN 9024 THEN 'Converts global log pool memory object to NUMA node partitioned object'
WHEN 9347 THEN 'Disables batch mode for sort operator'
WHEN 9348 THEN 'Sets row limit for bulk insert via set-based ops based on cardinality estimate'
WHEN 9389 THEN 'Enables dynamic memory grant for batch mode operators'
WHEN 10316 THEN 'Enables temporal tables to have additional indexes on hidden period columns'
ELSE 'No description on file — check docs.microsoft.com for trace flag ' + CAST(tf.TraceFlag AS VARCHAR(10))
END AS description
FROM #trace_flags AS tf
ORDER BY tf.Global DESC, tf.TraceFlag;
-- 0 rows = no active trace flags set on this instance
DROP TABLE #trace_flags;
2. Get-ResourceGovernorConfig — Enabled State, Pools, and Classifier
/*
Script Name : Get-ResourceGovernorConfig
Category : monitoring
Purpose : Resource Governor configuration — enabled state, resource pools, workload groups,
and classifier function. An active but misconfigured RG can silently throttle
queries or starve the DBA's own sessions on an inherited server.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-trace-flags-and-resource-governor/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-trace-flags-and-resource-governor/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
c.is_enabled AS rg_enabled,
CASE WHEN c.is_enabled = 1 THEN 'ENABLED' ELSE 'DISABLED' END AS rg_state,
CASE
WHEN c.classifier_function_id IS NOT NULL
THEN OBJECT_SCHEMA_NAME(c.classifier_function_id) + '.'
+ OBJECT_NAME(c.classifier_function_id)
ELSE NULL
END AS classifier_function,
p.name AS pool_name,
p.min_cpu_percent AS pool_min_cpu_pct,
p.max_cpu_percent AS pool_max_cpu_pct,
p.min_memory_percent AS pool_min_mem_pct,
p.max_memory_percent AS pool_max_mem_pct,
g.name AS workload_group,
g.importance AS group_importance,
g.request_max_cpu_time_sec AS group_max_cpu_sec,
g.request_max_memory_grant_percent AS group_max_mem_grant_pct,
g.max_dop AS group_max_dop,
g.group_max_requests AS group_max_requests,
rs_g.total_request_count AS group_total_requests,
rs_g.active_request_count AS group_active_requests,
CASE
WHEN c.is_enabled = 0
THEN 'INFO — Resource Governor is disabled; all sessions use default pool'
WHEN c.classifier_function_id IS NULL
THEN 'WARN — RG enabled but no classifier function; all connections go to default pool'
WHEN p.name = 'default' AND g.name = 'default'
THEN 'INFO — sessions landing in default pool/group; verify classifier is routing correctly'
ELSE 'OK'
END AS status
FROM sys.resource_governor_configuration AS c
CROSS JOIN sys.resource_governor_resource_pools AS p
JOIN sys.resource_governor_workload_groups AS g ON g.pool_id = p.pool_id
LEFT JOIN sys.dm_resource_governor_workload_groups AS rs_g ON rs_g.group_id = g.group_id
ORDER BY
p.name,
g.name;
How To Run From The Repo
Clone DBA Tools, initialize and run whichever script answers your question:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# List every active trace flag with a plain-English description:
.\run.ps1 Get-TraceFlags
# Resource Governor enabled state, pools, workload groups, and classifier:
.\run.ps1 Get-ResourceGovernorConfig
# Either against a remote sql server:
.\run.ps1 Get-ResourceGovernorConfig -ServerInstance SQLSERVER01
These scripts live in the repo at:
Example Output
Real output from this lab instance, not staged. Get-TraceFlags returns 0 rows here, no active trace flags are set on this instance, an honest, valid finding, not a script problem (the script itself has a comment noting exactly this: “0 rows = no active trace flags set”).
Get-ResourceGovernorConfig:
| rg_enabled | rg_state | pool_name | workload_group | group_max_cpu_sec | group_max_dop | status |
|---|---|---|---|---|---|---|
| False | DISABLED | default | default | 0 | 0 | INFO, Resource Governor is disabled; all sessions use default pool |
| False | DISABLED | internal | internal | 0 | 0 | INFO, Resource Governor is disabled; all sessions use default pool |
⚠ Found and fixed a real bug in Get-ResourceGovernorConfig.sql while testing it: the query joined sys.dm_resource_governor_resource_pools for total_request_count/active_request_count at the pool level, but that DMV doesn’t have those columns at all, they only exist on sys.dm_resource_governor_workload_groups (a Msg 207: Invalid column name error on both). Removed the two invalid pool-level columns and the now-unused join, kept the correct group-level request counts. Re-verified clean against HPAI01.
Understanding the Results
- Get-TraceFlags, 0 rows — no trace flags active is often the correct, expected state on a modern instance; several older flags (
1117,1118) became default behavior in SQL 2016+, so their absence isn’t a gap, it’s expected - is_global vs is_session — a global trace flag affects every connection on the instance; a session flag only affects the connection that set it (rare to see in a DMV snapshot since it requires the flag be set in the same session running the query)
- rg_state = DISABLED — the common, safe default; Resource Governor being off means no session is CPU/memory-constrained by RG at all
- status = WARN (RG enabled, no classifier) — the most important thing this script catches: Resource Governor turned on with pools defined but no classifier function means the configuration is inert, every session still lands in the default pool regardless of intent
Best Practices
- Investigate every active trace flag you didn’t set yourself, know why it’s there before assuming it’s safe to leave or remove
- If Resource Governor shows as enabled with a classifier function, review what that function actually routes and confirm it matches current intent, not a stale rule from a retired workload
- Re-run both after any migration or major version upgrade, a trace flag that made sense on SQL 2014 may be unnecessary or even harmful on SQL 2022+
- Pair with CPU Topology and OS Configuration Checks when reviewing an inherited server’s full non-default configuration surface
Related Scripts
You may also find these scripts useful:
- Server Inventory (hub)
- CPU Topology and OS Configuration Checks
- Instance Configuration Snapshot
- Instance Configuration Score
Frequently Asked Questions
Is it safe to just remove a trace flag I don’t recognize?
Not without understanding why it’s there first. Some trace flags fix a specific documented issue, removing one blindly can reintroduce the exact problem it was set to work around. Check the description this script provides, then confirm against Microsoft’s own documentation before changing anything in production.
Does Resource Governor being disabled mean it’s not configured at all?
No, pools and workload groups can exist (as seen in the default/internal rows here) even while rg_enabled = False. Disabled just means the configuration isn’t active, re-enabling with ALTER RESOURCE GOVERNOR RECONFIGURE would immediately activate whatever pools and classifier are already defined.
Summary
Both trace flags and Resource Governor are instance-wide settings that can sit invisibly in place for years, changing behavior in ways that don’t show up anywhere obvious until they matter. These two scripts turn “what’s silently configured on this server” into a direct, readable answer: which trace flags are on and why, and whether Resource Governor is actually doing anything at all.
Run both on any inherited server before assuming its behavior is purely stock, and re-check after any major version change, since defaults shift and old trace-flag workarounds don’t always age well.
Leave a Reply