SQL Server Performance-Related Trace Flags

SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://speedysql.com/2015/09/20/performance-related-trace-flags/

SQL Server Performance-Related Trace Flags

Here are some trace flags that you may find useful when query tuning or to improve server performance.

A quick reminder of the syntax to enable them:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--Enable trace flag at session level. DBCC TRACEON (3604) --Enable trace flag at global level. DBCC TRACEON (3604, -1) --Check active trace flags. DBCC TRACESTATUS --Disable trace flag at session level. DBCC TRACEOFF (3604) --Disable trace flag at global level. DBCC TRACEOFF (3604, -1) --Enable multiple trace flags at global level. DBCC TRACEON (3604, 3605, -1) --Disable multiple trace flags at global level. DBCC TRACEOFF (3604, 3605, -1)

The QUERYTRACEON query hint can also be used to activate a trace flag at the query level. Not all trace flags can be activated in this way. See the following KB article for the syntax and for details of which trace flags can be used with QUERYTRACEON.

To survive a server restart, a trace flag must be enabled using SQL Server Configuration Manager as shown in the image below.

WARNING: Some of these trace flags are undocumented and behaviour is neither guaranteed nor supported by Microsoft. The documented trace flags should only be enabled to fix a specific problem and only after thorough testing. All are to be used at your own risk.

Performance Trace Flags

Trace Flag	Description	Versions Introduced

610	Enables minimal logging when bulk loading.	SQL 2008
652	Disables the read-ahead mechanism at the server level.	SQL 2008
1117	Causes all the files in a filegroup to autogrow together.	SQL 2008
1118	Forces uniform extent allocations instead of mixed page allocations and so reduces SGAM contention.	SQL 2000 SP4
1236	Enables database lock partitioning and so reduces LOCK_HASH spinlock count.	SQL 2008 SP3
2301	Enhances modelling ability for complex queries resulting in better query plans.	SQL 2005 SP1
2330	Stops the collection of statistics for sys.db_index_usage_stats and sys.dm_db_missing_index_group_stats DMVs. Can reduce spinlock collisions for OPT_IDX_STATS and SPL_OPT_IDX_STATS.	SQL 2005 SP2
2363	SQL 2014 replacement for trace flag 9204.	Undocumented
2371	Reduces statistics update threshold from the default of 500 + 20% of table modified to a dynamic percentage depending upon row count.	SQL 2008 R2 SP1
2373	Shows memory usage during query optimization phase of applying rules and deriving properties.	Undocumented
2388	View column status (unknown, stationary, ascending) for leading index columns in DBCC SHOW_STATISTICS command.	SQL 2005 SP1
2389	Updates statistics histogram with highest value for ascending keys at compile time.	SQL 2005 SP1
2390	Update statistics histogram with highest value for any keys at compile time.	SQL 2005 SP1
2453	Optimizer reads table variable row counts for non-trivial plans.	SQL 2012 SP2 SQL 2014 CU3
2549	May improve DBCC CHECKDB PHYSICAL_ONLY performance if database files are on unique physical disks.	SQL 2008 SP2 CU9 SQL 2008 R2 CU11 SQL 2012
2562	Runs DBCC CHECKDB in a single batch. Reduces DBCC_MULTIOBJECT_SCANNER latch contention but increases tempdb usage.	SQL 2008 SP2 CU9 SQL 2008 R2 CU11
3656	Allows call stack symbol resolution.	SQL 2008
4136	Forces optimize for unknown\disables parameter sniffing.	SQL 2005 SP3 CU9 SQL 2008 SP1 CU7 SQL 2008 R2 CU2
4137	Reduces cardinality estimates for correlated AND predicates.	SQL 2008 SP2 CU8 SQL 2008 R2 SP1 CU5 SQL 2012 CU1
4199	Controls multiple query optimizer changes.	SQL 2005 SP3 CU6 SQL 2008 CU7 SQL 2008 R2
4618	Limits cache entries per user store to 1024.	SQL 2005 SP3
7352	Shows more detailed query tree.	Undocumented
7357	Unique hash optimization used.	Undocumented
8032	Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger. Use optimize for ad hoc workloads first.	SQL 2008 R2
8048	Reduces CMEMTHREAD wait type and SOS_SUSPEND_QUEUE spinlock by partitioning memory at CPU level instead of NUMA node level.	SQL 2005 SP2 CU5\ SQL 2008 SQL 2008 R2 SQL 2012 SQL 2014
8605	Shows converted tree in messages tab (logical).	Undocumented
8606	Shows input, simplified and join-collapsed trees.	Undocumented
8607	Shows query output tree in messages tab (physical).	Undocumented
8609	Shows task and operation type counts.	Undocumented
8619	Shows apply rule with description.	Undocumented
8620	Adds memo arguments to 8619 output.	Undocumented
8621	Shows rule with resulting tree.	Undocumented
8675	Shows optimization phases. The sys.dm_exec_query_optimizer_info DMV shows cumulative stats for these phases.	Undocumented
8687	Disables query parallelism.	Undocumented
8692	Forces the optimizer to use an Eager Table Spool for Halloween Protection.	Undocumented
9024	Partitions log pool global pointer to a memory object at node level. Can reduce WRITELOG and CMEMTHREAD wait types.	SQL 2012 SP1 CU3 SQL 2014
9204	Shows statistics used to produce cardinality estimates during compilation of an execution plan.	Undocumented
9481	Uses pre-SQL 2014 cardinality estimator.	SQL 2014