Introduction

Trace flags are used to set specific server characteristics or to alter a particular behavior. For example, Trace Flag 3226 is a commonly used startup trace flag that suppresses successful backup messages in the error log. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems, but they might also be recommended by Microsoft Support to address behavior that is negatively affecting a specific workload.

Problem

Enable the right trace flags based on SQL Server version

Solution

https://github.com/steverezhener/DBA/blob/master/Procedures/bestpractices.usp_change_server_trace_flags.sql

Commonly known trace flags

	SET @trace_flag = 460	-- enables additional info on string truncated error messages
	SET @trace_flag = 3226	-- prevents sql server log from including successful backup messages
	SET @trace_flag = 7745	-- prevents Query Store from interfering with SQL Server startup
	SET @trace_flag = 7752	-- prevents Query Store from interfering with SQL Server startup
	SET @trace_flag = 2549	-- The DBCC CHECKDB command builds an internal list of pages to read per unique disk drive across all database files
	SET @trace_flag = 2562	-- Run the DBCC CHECKDB command in a single "batch"
	SET @trace_flag = 15097 -- Enables AVX-512 support for SQL Server 2022 (16.x) and later versions.

Based on

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16