Trace Flags in SQL Server from Trace Flag 1603 to Trace Flag 2470

SQLServerF1

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Trace Flags in SQL Server from Trace Flag 902 to Trace Flag 1462

SQLServerF1

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 902 to Trace Flag 1462

SQL Server Trace Flag 902 – SQL server may not start after install or uninstall of service pack or Cumulative Update patches. Trace Flag 902 can be used to start SQL Server by skippling applying the upgrade scripts.

SQL Server Trace Flag 1106 – In SQL Server 2005, Used space in tempdb increases continuously when you run a query that creates internal objects in tempdb. KB 947204

SQL Server Trace Flag 1117 – Even growth of all files in a file group. Grows all data files at once, else it goes in turns.

SQL Server Trace Flag 1118 – Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent. There is now a cache of temp tables. When a new temp table is created on a cold system it uses the same mechanism as for SQL 8. When it is dropped though, instead of all the pages being deallocated completely, one IAM page & one data page are left allocated, then the temp table is put into a special cache. Subsequent temp table creations will look in the cache to see if they can just grab a pre-created temp table. If so, this avoids accessing the allocation bitmaps completely. The temp table cache isn’t huge (32 tables), but this can still lead to a big drop in latch contention in tempdb.

SQL Server Trace Flag 1119 – Turns off mixed extent allocation.

SQL Server Trace Flag 1140 – Continuous tempdb growth after upgrading SQL 2005 pre-SP2 to SP3/SP4 or to SQL 2008 or newer. KB 2000471.

SQL Server Trace Flag 1200 – Prints detailed lock information as every request for a lock is made and consists of the process ID and type of lock requested.

SQL Server Trace Flag 1202 – Insert blocked lock requests into syslocks.

SQL Server Trace Flag 1204 – Returns resources and types of locks participating in a deadlock and command affected.

SQL Server Trace Flag 1205 – Prints information about each deadlock search

SQL Server Trace Flag 1206 – Used to complement flag 1204 by displaying other locks held by deadlock parties.

SQL Server Trace Flag 1211 – Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks. Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory.
If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.

SQL Server Trace Flag 1222 – Returns the resources & types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

SQL Server Trace Flag 1224 – Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation (when > 40%). The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:
40% of the memory that is used by Db Engine, exclusive of memory allocation using AWE. This is applicable when the locks parameter of sp_configure is set to 0.
Forty percent of the lock memory that is configured by using the locks parameter of sp_configure.

If both trace flag 1211 & 1224 are set, 1211 takes precedence. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, it’s recommend to use 1224 which helps avoid “out-of-locks” errors when many locks are being used.

SQL Server Trace Flag 1260 – Disabled mini-dump for non-yield conditions.

SQL Server Trace Flag 1262 – Dump everytime a non-yielding scheduler condition is detected.

SQL Server Trace Flag 1400 – In SQL Server 2005 RTM, Enables creation of database mirroring endpoint, which is required for setting up and using database mirroring.

SQL Server Trace Flag 1439 – Trace database restart and failover messages to SQL Errorlog for mirrored databases

SQL Server Trace Flag 1448 – Prevent replication latency with database mirroring enabled. When the principal database is running exposed or is isolated the Log Reader Agent will waits for log records to harden on the mirror before replicating them to the Distributor. When publisher is started with trace flag 1448, the Log Reader Agent can continue replicating changes regardless of the mirroring state.

SQL Server Trace Flag 1449 – When you use SNAC to connect to an instance of a principal server in a database mirroring session: “The connection attempted to fail over to a server that does not have a failover partner”. KB 936179

SQL Server Trace Flag 1462 – In SQL Server 2008, Turns off log stream compression and effectively reverts the behavior back to version 2005.

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Trace Flags from Trace Flag 302 to Trace Flag 845

SQLServerF1

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Trace Flags from Trace Flag 168 to Trace Flag 274

SQLServerF1

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 105 to Trace Flag 274

SQL Server Trace Flag 168 – In SQL Server 2005 or SQL Server 2008, On querying through a view that uses the ORDER BY clause, the result are still returned in random order. KB 926292

SQL Server Trace Flag 205 – Report when a stored procedure is being recompiled. This trace flag will write the a corresponding message to the SQL Server errorlog.

SQL Server Trace Flag 210 – Needed to enable fix for issue mentioned in KB 945892.

SQL Server Trace Flag 212 – Needed to enable fix for issue mentioned in KB 951184.

SQL Server Trace Flag 237 – Tells SQL Server to use correlated sub-queries in Non-ANSI standard backward compatibility mode.

SQL Server Trace Flag 242 – Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.

SQL Server Trace Flag 246 – Derived or NULL columns must be explicitly named in a select…INTO or create view statement when not done they raise an error. This flag avoids that.

SQL Server Trace Flag 253 – Prevents ad-hoc query plans to stay in cache.

SQL Server Trace Flag 257 – Will invoke a print algorithm on the XML output before returning it to make the XML result more readable.

SQL Server Trace Flag 260 – Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion().

SQL Server Trace Flag 274 – Needed to enable fix for issue mentioned in KB 949097.

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

What are SQL Server Trace Flags and How to Use Trace Flags in SQL Server

SQLServerF1

Trace flags in SQL Server are provided to temporarily set specific server characteristics or to switch off a particular behavior. Trace flags are generally used to diagnose performance issues or to debug stored procedures or complex computer systems.

In SQL Server, there are two types of trace flags: session level and global level. Session trace flags are active only for a connection in which they are enabled and are visible only to that connection. Global trace flags are set at the SQL server instance level and are visible to every connection on the SQL server instance. Some flags can only be enabled as global, and some can be enabled at either global or session scope.

Trace flags are set on or off by using either of the following methods:

1. Using the DBCC TRACEON and DBCC TRACEOFF commands.

Example:
DBCC TRACEON(3004) will enable the trace flag at session level.
DBCC TRACEON with the -1 argument will enabled the trace flag at global level, example DBCC TRACEON(3004, -1)

DBCC TRACEOFF is used to turn off a particular trace flag

Example:
DBCC TRACEOFF(3004) will disable the session level trace flag.
DBCC TRACEOFF with the -1 argument will disable the trace flag at global level, example DBCC TRACEOFF(3004, -1)

DBCC TraceStatus can be used to get the information weather a particular trace flag is enabled or not.

2. Trace flag enabled using DBCC TRACEON is only active till restart of SQL Server instance. So after SQL instance is restarted, you need to enable the trace flag again. If you want to avoid enabling the traceflag after restart of SQL Server instance, then you can enabled the trace flag from StartupParameters in SQL Server configuration Manager.

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

List of SQL Server Miscellaneous DBCC Commands and Description

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the DBCC commands related to Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

DBCC dllname (FREE) – Unloads the specified extended stored procedure DLL from memory. When an extended stored procedure is executed, the DLL remains loaded by the instance of SQL Server until the server is shut down. This statement allows for a DLL to be unloaded from memory without shutting down SQL Server. To display the DLL files currently loaded by SQL Server, execute sp_helpextendedproc.

DBCC FREESESSIONCACHE – Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

DBCC FREESYSTEMCACHE – Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache. Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the informational message.

DBCC HELP – Returns syntax information for the specified DBCC command. DBCC HELP returns a result set displaying the syntax for the specified DBCC command.

DBCC TRACEOFF – Disables the specified trace flags. Trace flags are used to customize certain characteristics controlling how the instance of SQL Server operates.

DBCC TRACEON – Enables the specified trace flags. Trace flags are used to customize certain characteristics by controlling how SQL Server operates. Trace flags, after they are enabled, remain enabled in the server until disabled by executing a DBCC TRACEOFF statement. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server. To determine the status of trace flags, use DBCC TRACESTATUS. To disable trace flags, use DBCC TRACEOFF.

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.