Trace Flags in SQL Server are used to turn ON or OFF particular behavior. TraceFlags are used for variety of purposes like Logging extended information in to SQL Server errorlog, Troubleshooting or improving SQL Server Performance, Enabling certain features or functionality, etc.
Below are the SQL Server Trace Flags from Trace Flag 1603 to Trace Flag 2470.
SQL Server Trace Flag 1603 – Use standard disk I/O which turns off asynchronous I/O.
SQL Server Trace Flag 1604 – Once enabled at start up makes SQL Server output information regarding memory allocation requests.
SQL Server Trace Flag 1609 – Turns on the unpacking and checking of RPC information in Open Data Services. Used only when applications depend on the old behavior.
SQL Server Trace Flag 1610 – Boot the SQL dataserver with TCP_NODELAY enabled.
SQL Server Trace Flag 1611 – If possible, pin shared memory — check errorlog for success/failure
SQL Server Trace Flag 1613 – Set affinity of the SQL data server engine’s onto particular CPUs — usually pins engine 0 to processor 0, engine 1 to processor 1.
SQL Server Trace Flag 1704 – Prints information when a temporary table is created or dropped.
SQL Server Trace Flag 1717 – Causes new objects being created to be system objects.
SQL Server Trace Flag 1802 – In SQL Server 2005, After detaching a database that resides on network-attached storage, you cannot reattach the SQL Server database. KB 922804
SQL Server Trace Flag 1806 – Disables instant file initialization.
SQL Server Trace Flag 1807 – In SQL Server 2005 or SQL Server 2008, Allows creating a database file on a mapped or UNC network location.
SQL Server Trace Flag 2301 – Makes your optimizer work harder by enabling advanced optimizations that are specific to decision support queries, applies to processing of large data sets.
SQL Server Trace Flag 2328 – Makes cardinality estimates upon resulting selectivity. The reasoning for this is that one or more of the constants may be statement parameters, which would change from one execution of the statement to the next.
SQL Server Trace Flag 2330 – Stops the collection of statistics for sys.db_index_usage_stats.
SQL Server Trace Flag 2335 – Amount of memory available to SQL Server affects the execution plan generated though SQL Server generates the most optimal plan based on this value, but occasionally it may generate an inefficient plan for a specific query when you configure a large value for max server memory. Using 2335 as a startup parameter will cause SQL Server to generate a plan that is more conservative in terms of memory consumption when executing the query. It does not limit how much memory SQL Server can use. The memory configured for SQL Server will still be used by data cache, query execution & other consumers. KB 2413549.
SQL Server Trace Flag 2340 – Query processor may introduce a sort operation for optimization, though not required where the particular plan only touches a smaller number of rows. Setup cost for the sort operation may outweigh its benefits thus resulting in poor performance. KB 2009160
SQL Server Trace Flag 2371 – Before this automatic statistics were triggered when a column would get modifications exceeding 20% of the # of rows in the table. On enabling this flag the standard 20% changes to a dynamic value if table has more than 25000 rows & reduces as the count increases.
SQL Server Trace Flag 2388 – In SQL Server 2005, Detect when the leading column of a statistics object is ascending and mark or brand it as ascending. Statistics object that belong to an ascending column is branded as “ascending” after three updates on the statistics. It’s necessary to update it with ascending column values so that when the third update occurs, SQL Server brands the statistics object as ascending. Flag 2388 helps to check the statistics’ brand, when turned on the result of the DBCC SHOW_STATISTICS, has an additional column called Leading column type with the brand of the column
SQL Server Trace Flag 2389 – Tracks nature of columns by subsequent statistics updates. When SQL Server determines that the statistics increase three times, the column is branded ascending. The statistics will be updated automatically at query compile.
SQL Server Trace Flag 2390 – Does the same like 2389 even if ascending nature of the column is not known and never enable without 2389.
SQL Server Trace Flag 2440 – In SQL Server 2008, Parallel query execution strategy on partitioned tables. SQL 9 used single thread per partition parallel query execution strategy. In SQL 10, multiple threads can be allocated to a single partition by turning on this flag.
SQL Server Trace Flag 2470 – Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012. KB 2606883
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.