Returns a list of all global trace flags that are enabled on sql instance (Global Trace Flags)
DBCC TRACESTATUS (-1);
— If no global trace flags are enabled, no results will be returned.
— It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.
Common Trace Flags that should be Enabled in most cases
— TF 1117 – When growing a data file, grow all files at the same time so they remain the same size, reducing allocation contention points
— TF 1118 – Helps alleviate allocation contention in tempdb, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages (more important with older versions of SQL Server) Recommendations to reduce allocation contention in SQL Server tempdb database.
— TF 2371 – Lowers auto update statistics threshold for large tables (on tables with more than 25,000 rows)
— TF 3023 – Enables backup checksum default
— TF 3226 – Supresses logging of successful database backup messages to the SQL Server Error Log
— TF 3449 – Enables use of dirty page manager (SQL Server 2012 SP3 CU3 and later)
— TF 6533 – Spatial performance improvements in SQL Server 2012 and 2014
— TF 6534 – Enables use of native code to improve performance with spatial data
— TF 8079 – Enables automatic soft-NUMA on systems with eight or more physical cores per NUMA node (with SQL Server 2012 SP4)
— DBCC TRACEON – Trace Flags (Transact-SQL)