SQL Server trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Here are the long list of available trace flags you can set. Following are some of the most wanted popular trace flags you might set for your SQL Server. I usually enable them when I setup a new SQL Server. But please test them out in test environment before applying to production servers!!!

1117: When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. Scope: global only.

Note: Starting with SQL Server 2016 this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

1222: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Scope: global only. This is very useful for troubleshooting deadlocks.

2371: Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold. For more information, see this Microsoft Support article. Scope: global only.

Note: Starting with SQL Server 2016 this behavior is controlled by the engine and trace flag 2371 has no effect.

3226: By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic. With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries. Scope: global only.

4199: Enables query optimizer (QO) changes released in SQL Server Cumulative Updates and Service Packs. QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 enabled. Scope: global or session or query.

To accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’ query hint instead of using this trace flag.