SQLServerWiki

“The Only Thing That Is Constant Is Change”

Documented SQL Server Trace Flags, use them cautiously to avoid CHAOS!

Posted by database-wiki on October 20, 2012

About Trace Flags:

==================

I prefer to generally stick with the first two options of setting trace flags. It’s especially easy to turn on a trace flag using the DBCC TRACEON command. For example, if you want to enable trace flag 1807, use the following syntax:

DBCC TRACEON(1807)

Use the DBCC TRACEOFF command to disable traces.

For either command, target multiple traces by separating each trace with a comma:

DBCC TRACEOFF(1807, 3604)

In the previous examples, this will only set the trace for the current connection (there are exceptions to this rule that I’ll discuss in a moment). If you’d like to set the trace using the DBCC TRACEON command at a server level, you can use the -1 switch as shown here:

DBCC TRACEON (8602, -1)

Checking for Running Traces

When you enable traces, it’s a good idea to see if other traces are currently running on your system. If you’re looking for a specific trace, use the DBCC TRACESTATUS command followed by the list of trace flags you want to check on. For example, the following syntax checks for trace flags 3604 and 1807:

DBCC TRACESTATUS(3604, 1807)

The resulting output displays in two columns: the trace flag is in one column, and the status (0 for off and 1 for on) is in the other column. It’s not uncommon to be totally unaware of which traces are active. To enumerate a complete list of traces that are on, use the DBCC TRACESTATUS command followed by the (-1) parameter:

DBCC TRACESTATUS(-1)

Starting Traces Automatically

You can start traces automatically when starting SQL Server by using the -T switch. Set the trace flags you want to execute at startup in the Startup Parameters dialog box (see Figure 5-9), which you reach by selecting Startup Parameters in the General tab of the Server Properties dialog box.

In the Trenches

I alluded to a few moments ago an interesting quirk with SQL Server. The quirk appears when you want to set a trace flag at an individual connection level. When this is set, SQL Server will apply the trace flag against anyone who has turned on an individual connection trace flag. For example, let’s say you turned on trace flag 8602 for an individual connection:

DBCC TRACEON (8602)

Another user then turns on trace flag 8755 for his individual user connection: DBCC TRACEON (8755)

You can then see the status of the trace flags and see that both are indeed turned on for each of the users. DBCC TRACESTATUS (-1) will result in:

TraceFlag Status

——— ——

8602        1

8755         1

Apply Traces Among All Connections (-1) Normally, a trace that is set in Query Analyzer only applies at the client level. If you use the undocumented trace flag of -1, the system applies any traces you’ve set across all active and new connections. This trace is not reported when you use the DBCC TRACESTATUS command.

List of documented trace flags:

===============================

2551-generate filtered dump.

http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

1262-Force a minidump every time schedmon detects a condition.

http://msdn.microsoft.com/en-us/library/cc917684.aspx

2544-Put maximum information in the dump: memory, threads, handles, etc.

http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

2546-Dump all threads in the process.

http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

1118 -Disables the first 8 mixed page allocations for tables.

http://support.microsoft.com/kb/328551

2505-Do not trace calls to traceon/traceoff.

http://support.microsoft.com/kb/243352

683-Disallow row counter and column mod counters to be partitioned.

http://support.microsoft.com/kb/821548

3502-Send checkpoint state changes to errorlog

http://support.microsoft.com/kb/815436

8026-Under the trace flag 8026 all memory allocated out of the caches will be immediately considered for removal by the Resource Monitor in case of the memory pressure. This should address the issue when we’re low on memory and have plenty of memory in the caches, and we don’t kick out the entries in the caches. The downside of the fix is that the RM will be running more often and would be cleaning caches faster trying to keep the total memory consumption at the 80% of the total memory.

http://blogs.msdn.com/b/psssql/archive/2009/11/17/how-it-works-controlling-sql-server-memory-dumps.aspx

834-Turn on large page support for Buffer Pool(64-Bit only)

http://support.microsoft.com/kb/920093

=> Use Microsoft Windows large-page allocations for the buffer pool.

=> causes SQL Server 2005 to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.

=> Trace flag 834 applies only to 64-bit versions of SQL Server 2005. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

=> Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server 2005.

8015-Disable auto detection and setup of NUMA

http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx

-1 Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.

106 Disables line number information for syntax errors.

http://msdn.microsoft.com/en-us/library/ms151872(v=sql.100).aspx

205 Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat.

http://support.microsoft.com/kb/195565

208 SET QUOTED IDENTIFIER ON.

http://support.microsoft.com/kb/243352

661 Re-enable cleanup process (turn on 661 first).

http://support.microsoft.com/kb/920093

652 Disables read ahead for the server.

http://support.microsoft.com/kb/920093

818 Retrieves extended HArdware Problems informations.

http://support.microsoft.com/kb/826433

1180 Forces allocation to use free pages for text or image data and maintain efficiency of storage.

http://support.microsoft.com/kb/272220

1204 Returns the type of lock participating in the deadlock and the current command affect by the deadlock.

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

1807 Allows you to configure SQL Server with network-based database files.

http://support.microsoft.com/kb/304261

3213 Trace for backups.

http://blogs.msdn.com/b/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx

3205 Disables hardware compression for tape drivers.

http://msdn.microsoft.com/en-us/library/ms188396.aspx

3226 This trace flag will prevent all informational BACKUP and RESTORE messages from being written to the SQL Server error log and the NT application event log.

http://msdn.microsoft.com/en-us/library/ms188396.aspx

3222 Disables the read ahead that is used by the recovery operation during roll forward operations.

http://support.microsoft.com/kb/268081

3502 Prints a message to the log at the start and end of each checkpoint.

http://support.microsoft.com/kb/815436

3602 Records all error and warning messages sent to the client.

http://support.microsoft.com/kb/199037

3604 Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.

http://blogs.msdn.com/b/askjay/archive/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements.aspx

3605 Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)

http://support.microsoft.com/kb/199037

3607 Skips automatic recovery (at startup) for all databases.

3608 Skips automatic recovery (at startup) for all databases except the master database.

3609 Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/3a0c3b2d-ed1e-4737-8067-44a33f7e6db9/

3626 Turns on tracking of the CPU data for the sysprocesses table.

http://support.microsoft.com/kb/322109

3640 Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.

http://blogs.msdn.com/b/selvar/archive/2010/07/14/delete-operation-editing-a-data-source-from-a-reporting-service-2005-report-manager-fails-internalcatalogexception-and-throwing-watson-dump.aspx

4022 Bypasses automatically started procedures.

4032 trace flag to trace the calls from the driver.

http://support.microsoft.com/kb/139655

4030 Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.

4031 Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER. 4032 Traces the SQL commands coming in from the client. The output destination of the trace flag is controlled with the 3605/3604 trace flags.

http://support.microsoft.com/kb/152032

7300 Retrieves extended information about any error you encounter when you execute a distributed query OLE Errors.

http://support.microsoft.com/kb/314530

7501 Dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster than in earlier versions and no longer require unique indexes. This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.

http://support.microsoft.com/kb/152032

7502 Disables the caching of cursor plans for extended stored procedures.

http://support.microsoft.com/kb/235476

8202 Replicates all UPDATE commands as DELETE/INSERT pairs at the publisher.

http://support.microsoft.com/kb/251187

8206 Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers in SQL Server 2000.

http://support.microsoft.com/kb/284228

8207 Enables singleton updates for Transactional Replication, released with SQL Server 2000 Service Pack 1.

http://support.microsoft.com/kb/302341

8599 Allows you to use a savepoint within a distributed transaction.

http://support.microsoft.com/kb/295027

8679 Prevents the SQL Server optimizer from using a Hash Match Team operator.

http://support.microsoft.com/kb/818671

8721 Dumps information into the error log when AutoStat has been run.

http://support.microsoft.com/kb/195565

=> You should not do anything to get autostats on system tables.

=> You can turn on trace flag 8721 and you will see what stats we auto create.

=> In addition, you can turn on trace flag 3805 and use sp_helpstats to see stats auto created on system tables.

*********************

While restoring the transaction log files, you can use the 3222 trace flag to disable the read ahead feature that is used by the recovery operation during the roll forward operations. However, you must not use the 652 trace flag that disables the read ahead feature for the server.

http://support.microsoft.com/kb/268081 http://support.microsoft.com/kb/920093

*********************

For a user database in an instance of SQL Server, you can clear the “Loading” status by running the following Transact-SQL statement in SQL Query Analyzer: RESTORE DATABASE <Database name> WITH RECOVERY

*********************

http://support.microsoft.com/kb/315447

This fix alone does not limit the amount of Lazy Write activity. Microsoft has added a new trace flag, 809, to limit the amount of Lazy Write activity.

The limit with the 809 trace flag in page writes is 10000 – Outstanding Pages from Checkpoint .

I was able to generate about 6300 Lazy Writes, which was about 101000 Page Writes (789 MB) in less than 5 seconds, which was almost 1/3 of the size of the buffer cache with which I was testing. Normally, checkpoint takes at least 100 seconds to write that number of pages.

Additionally, because the disk IO spike is very short term, the use of large collection intervals in System Monitor may hide an issue due to averaging; therefore, you should use an interval of 1 or 5 seconds maximum if you suspect this issue.

Finally, you can also use checkpoint trace flags 3502 and 3504 to detect this situation, in that extremely low rates of pages flushed per time (< 10 pages flushed/sec) may also be an indication that too many pages are being lazy written.

http://support.microsoft.com/kb/815436

http://blogs.msdn.com/b/joaol/archive/2008/11/20/sql-server-checkpoint-problems.aspx

*********************

To capture the output of the following DBCC commands, you must enable trace flag 3604 or 3605 on the connection that is running the queries:

• DBCC PSS

http://support.microsoft.com/kb/832977

*********************

http://support.microsoft.com/kb/308822

If you create a table in a transaction and the same transaction then populates the table, the cardinality estimates assume that the table still has zero rows. This affects the performance of any query that you execute against this table in the same transaction.

For example, you might create and populate a table with millions of rows of data, but the optimizer still considers the table empty. Subsequent queries in the same transaction that use the table may estimate the return of an incorrect number of rows, which leads to poor query performance.

To resolve this problem, follow these steps: 1.  Obtain the latest service pack for SQL Server 2000. For information on how to obtain the latest SQL Server 2000 Service Pack, see the following article in the Microsoft Knowledge Base: 290211 How to obtain the latest SQL Server 2000 service pack  2.  Turn on trace flag 3913.  3.  Execute complex queries that will not use trivial plans. For trivial plans, the cardinality estimate is still 1.0 for a scan of such a table.  Note When you turn on trace flag 3913, the optimizer gets row count information from in-memory metadata that is saved to sysindexes system table when the transaction commits.

********************************

related to checkpoint:

http://support.microsoft.com/kb/815436

3502 logs checkpoint 3505 disables checkpoint

*********************************

http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/26/lost-without-a-trace.aspx

Trace Flags 8602 (ignore all index hints)

*********************************

2301 – SQL 2005

http://support.microsoft.com/kb/920093

Trace flag 2301 affects costing, which affects plan choice. Trace flag 2301 discourages order-preserving parallelism. The most significant effect is that trace flag 2301 discourages parallel merge join. When to use trace flag 2301 Trace flag 2301 enables the following advanced logic in the cardinality estimation: • Base containment assumption  • Integer-based interpolation  • Use of the histogram even when the cardinality estimate is low  • Unlimited density remapping  When a potential cardinality estimation issue exists, enable trace flag 2301 to determine whether the issue is addressed by the logic that is enabled by this trace flag. When you should not use trace flag 2301 The potential disadvantage of this trace flag is that it uses more time and more memory during optimization. Do not use this trace flag for OLTP queries and for frequently compiled queries. One known issue is that if applications perform many column remapping functions (such as CONVERT, CAST, UPPER, or LOWER) and have many densities, enabling trace flag 2301 consumes lots of memory.

Trace flag 2301: Enable advanced decision support optimizations Trace flag 2301 enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets.

When you turn on trace flag 2301 at startup, the trace flag has global scope. When you turn on trace flag 2301 in a user session, the trace flag has session scope.

**********************************

KB920093

http://support.microsoft.com/kb/920093

This article describes the various trace flags that you can use to improve performance in Microsoft SQL Server 2005. Typically, you use these trace flags when SQL Server 2005 is running under a heavy workload.

Note The trace flags that this article describes are advanced tuning techniques. You should consider using these trace flags only after you do more basic and routine optimizations. For example, you should consider using these trace flags after you do the following optimizations:

• Index analysis

• I/O balancing

• SQL query profiling

• System statistics monitoring

Additionally, these trace flags are mainly helpful on high-end servers that have the following characteristics:

• Many CPUs, for example, more than eight CPUs

• Lots of main memory, for example, more than 8 gigabytes (GB) of memory

• High I/O rates, for example, more than 10,000 physical I/O per second or more than 500 megabytes (MB) per second  The trace flags that this article describes may decrease performance under some workloads. We recommend that you evaluate the effects on your workload on a test system before you deploy any changes in a production environment.

For more information about large-page support in Windows, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/large_page_support.asp

http://support.microsoft.com/kb/920093

Trace flag 836: Use the max server memory option for the buffer pool Trace flag 836 causes SQL Server 2005 to size the buffer pool at startup based on the value of the max server memory option instead of based on the total physical memory. You can use trace flag 836 to reduce the number of buffer descriptors that are allocated at startup in 32-bit Address Windowing Extensions (AWE) mode. This gives room for more stolen pages. Trace flag 836 applies only to 32-bit versions of SQL Server 2005 that have the AWE allocation enabled. You can turn on trace flag 836 only at startup.

******************************************************************************

In SQL 2005, we can use with DATA_PURITY.

*******************************************************************************

DBCC TRACEON(2520,-1): for getting help on undocumented DBCC commands.

*******************************************************************************

Skip Startup Stored Procedures (4022) This is a handy trace flag for troubleshooting. It forces SQL Server to skip startup stored procedures. This is especially useful if a stored procedure has been altered and causes harm to your system. After you set this trace flag, you can then debug the stored procedure and set it back to its original state.

Ignore All Index Hints (8602) Trace flag 8602 is a commonly used trace flag to ignore index hints that are specified in a query or stored procedure. This is a fantastic option when you’re trying to determine if an index hint is hurting more than helping. Rather than rewriting the query, you can disable the hint using this trace flag and rerun the query to determine if SQL Server is handling the index selection better than the index hint.

Disable Locking Hints (8755) Trace flag 8755 will disable any locking hints like READONLY. By setting this, you allow SQL Server to dynamically select the best locking hint for the query. If you feel the query’s locking hint may be hurting performance, you can disable it and rerun the query.

Disable All Other Hints (8722) Lastly, the 8722 trace flag will disable all other types of hints. This includes the OPTION clause.

TIP By running all three 8602, 8755, and 8722 trace flags, you can disable all hints in a query. If you feel your performance is being negatively affected by a hint, you can set these rather than rewrite all the queries while you test. Generally speaking, there’s no reason to place hints on queries in SQL Server 7.0 or 2000.

*************************************************************

From SQL Server 2008 and later.

-T902 – bypass script upgrade mode.

*************************************************************

Cheers,

Bala

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: