Which trace flags to use on SQL Server 2016 to 2022. Most of the old “always on” flags (1117, 1118, 2371, 4199, 9481) are now default behaviour or a per-database setting, so this script turns them off and keeps only the ones still worth having. Tested on SQL Server 2017 and 2025.

-- Trace flags for SQL Server 2016 to 2022
-- Tested on SQL Server 2017 and 2025

-- 1. Which global trace flags are on now? (no rows = none)
DBCC TRACESTATUS (-1);

-- 2. Flags still worth turning on
DBCC TRACEON (3226, -1);  -- stop "backup successful" messages filling the error log
DBCC TRACEON (1222, -1);  -- write deadlock details to the error log
                          -- (the system_health Extended Events session also captures them)

-- DBCC TRACEON only lasts until the service restarts. To keep them, add startup parameters in
-- SQL Server Configuration Manager > SQL Server service > Startup Parameters:  -T3226  and  -T1222

-- 3. Flags you do NOT need on 2016 and later (turn them off if an old build script set them)
DBCC TRACEOFF (1117, -1); -- grow all files together: tempdb does this by default; for user
                          -- databases use ALTER DATABASE ... MODIFY FILEGROUP ... AUTOGROW_ALL_FILES
DBCC TRACEOFF (1118, -1); -- full extents only: the default now; set per database with MIXED_PAGE_ALLOCATION
DBCC TRACEOFF (2371, -1); -- dynamic auto-stats threshold: the default at compatibility level 130+
DBCC TRACEOFF (8048, -1); -- soft-NUMA: SQL Server 2016 does this automatically
DBCC TRACEOFF (4133, -1); -- SQL Server 2008 only
DBCC TRACEOFF (1224, -1); -- lock escalation tweak: only if you have a proven problem

-- 4. Optimizer flags are now set per database instead of for the whole server
DBCC TRACEOFF (4199, -1); -- optimizer hotfixes
DBCC TRACEOFF (9481, -1); -- legacy cardinality estimator
-- Run these in each database that needs them:
-- ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
-- ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
-- or for one query:  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

-- See the per-database settings
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN (N'QUERY_OPTIMIZER_HOTFIXES', N'LEGACY_CARDINALITY_ESTIMATION');

-- 5. Check again
DBCC TRACESTATUS (-1);