A trace flag is a numbered SQL Server configuration switch that changes specified database-engine behavior, diagnostics, or execution characteristics. Mistake: treating every useful historical flag as a current performance fix. T1117 and T1118 are deprecated and no longer affect SQL Server instances, so newer, more granular settings are safer choices. section 10.1 section 10.14
Apply trace flags with their scope and operational consequences in mind:
- Choose session scope when testing behavior for one connection; use the -1 parameter for global scope when the flag supports it. section 10.1
- Use DBCC TRACEON and DBCC TRACEOFF to toggle flags, but remember that global settings disappear when the Database Engine service restarts. section 10.1
- Add a supported flag as a SQL Server service startup parameter when its configuration must survive a restart. section 10.1
- Replace historical T1117 and T1118 usage with AUTOGROW_ALL_FILES and UNIFORM_PAGE_ALLOCATION rather than relying on deprecated behavior. section 10.14
Testing session and global scope
DBCC TRACEON (1224); -- current session DBCC TRACEON (1224, -1); -- global configuration Trace flag 1224 disables lock escalation based on the number of locks. The global form affects all sessions, but does not persist after a Database Engine restart. section 10.1
DBCC TRACEON (1224) ; DBCC TRACEON (1224,-1) ;
Compare the session and global DBCC TRACEON forms directly before applying a configuration change. section 10.1
