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 302 to Trace Flag 845.
SQL Server Trace Flag 302 – Should be used with 310 to show the actual join ordering. Prints information about whether the statistics page is used, the actual selectivity (if available), and what SQL Server estimated the physical and logical I/O would be for the indexes.
SQL Server Trace Flag 310 – Prints information about join order. Index selection information is also available in a more readable format using SET SHOWPLAN_ALL, as described in the SET statement.
SQL Server Trace Flag 320 – Disables join-order heuristics used in ANSI joins. To see join-order heuristics use flag 310. SQL Server uses join-order heuristics to reduce the no’ of permutations when using the best join order.
SQL Server Trace Flag 325 – Prints information about the cost of using a non-clustered index or a sort to process an ORDER BY clause.
SQL Server Trace Flag 326 – Prints information about estimated & actual costs of sorts. Instructs server to use arithmetic averaging when calculating density instead of a geometric weighted average when updating statistics. Useful for building better stats when an index has skew on the leading column. Use only for updating the stats of a table/index with known skewed data.
SQL Server Trace Flag 330 – Enables full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins.
SQL Server Trace Flag 342 – Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins. One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge joins because the query is forced to use the order specified in the FROM clause.
SQL Server Trace Flag 345 – Increase the accuracy of choice of optimum order when you join 6 or more tables.
SQL Server Trace Flag 506 – Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.
SQL Server Trace Flag 610 – In SQL Server 2008, Enables the potential for minimal-logging when:
Bulk loading into an empty clustered index, with no nonclustered indexes
Bulk loading into a non-empty heap, with no nonclustered indexes
SQL Server Trace Flag 611 – In SQL Server 2005, each lock escalation is recorded in the error log along with the SQL Server handle number.
SQL Server Trace Flag 652 – Disable pre-fetch scans. Disables read ahead for the server.
SQL Server Trace Flag 653 – Disables read ahead for the current connection.
SQL Server Trace Flag 661 – Disables the ghost record removal process. A ghost record is the result of a delete operation. When you delete a record, the deleted record is kept as a ghost record. Later, the deleted record is purged by the ghost record removal process. When you disable this process, the deleted record is not purged. Therefore, the space that the deleted record consumes is not freed. This behavior affects space consumption and the performance of scan operations. SCOPE: Global. If you turn off this trace flag, the ghost record removal process works correctly. KB 920093
SQL Server Trace Flag 698 – In SQL Server 2005, Performance of INSERT operations against a table with an identity column may be slow when compared to SQL 8. KB 940545
SQL Server Trace Flag 699 – Turn off transaction logging for the entire SQL dataserver.
SQL Server Trace Flag 806 – Audit failure during a read operation is reported in the errorlog as a 823 message on enabling this trace flag
SQL Server Trace Flag 815 – Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled
SQL Server Trace Flag 818 – Detect stale reads. Very helpful in analyzing recurring data corruption issues.
SQL Server Trace Flag 830 – Suppress stuck-stalled I/O warnings
SQL Server Trace Flag 831 – Protect unchanged pages in the buffer pool to catch memory corruptions.
SQL Server Trace Flag 834 – Use Microsoft Windows large-page allocations for the buffer pool. Only applies to 64-bit instances.
SQL Server Trace Flag 835 – In SQL Server 2005 and SQL Server 2008, On 64 bit SQL Server it turns off Lock pages in memory.
SQL Server Trace Flag 836 – Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server
SQL Server Trace Flag 840 – On Standard edition, allows larger I/O extent reads to populate the buffer pool when SQL Server starts
SQL Server Trace Flag 842 – Use sys.dm_os_memory_node_access_stats to verify local vs. foreign memory under NUMA configurations after turning on this flag.
SQL Server Trace Flag 845 – Enable lock pages support for Standard Edition
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.