What are the required Global Trace Flags to be Enabled on SQL Instance

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)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.