[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Side Effects of Using Trace Flags

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

Side Effects of Using Trace Flags.


Last updated: February 23, 2017.



Using trace flags on SQL Server may have undesirable secondary effects when used on production environments.

This article tries to document a few of those secondary effects created by specific trace flags.

 

Trace Flag Number

Undesirable Secondary Effects
   

652

 If you turn on trace flag 652, queries that benefit from the page pre-fetching feature may exhibit low performance. Source.
   

661

 It may originate page splits and data file growth. As explained by Paul Randal. Source.

It may also create affects space consumption and the performance of scan operations. Source.
   

834

 Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Source.

It is not recommended when the Column Store Index features is used. Source.

SQL Server startup time can be significantly delayed when using trace flag 834. Source.
   

902

 When this trace flag is enabled and you try to alter a database, you may receive an "access denied" error message. Fix.
   

1106

 "We recommend that you do not use trace flag 1106 on production servers because trace flag 1106 affects the performance of SQL Server". Source.
   

1118

 This trace flag may create blocking and performance-related problems. Source.
   

1204

 The SQL Server process may end unexpectedly when you turn on trace flag -T1204 and a profiler trace is capturing the Lock:DeadLock Chain event in SQL Server. Source.

Using the rowset function Openquery when this trace flag is enabled may cause the SQL Server service to shut down. Source.
   

1211

 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. Source.

Not recommended (Dynamics)  since it lacks a "safety" mechanism. Source.

A misbehaving application can exhaust SQL Server memory by acquiring large number of locks when this trace flag is enabled. This, in the worst case, can stall the Server or degrade its performance to an unacceptable level. Source.

You may experience an out-of-lock memory exception when you disable lock escalation for large operations on a 32-bit computer. Source.
   

1222

 Using the rowset function Openquery when this trace flag is enabled may cause the SQL Server service to shut down. Source.
   

1224

 SQL Server may generate an out of memory error when memory allocated to lock manager exceeds the statically allocated memory or 60% of non-AWE(32-bit)/regular memory for dynamic allocation. Source.
   

1448

 Various issues occur when a database is involved in a database mirroring session and in a transactional replication if trace flag 1448 is enabled. Fix.
   

2371

 The downside of using this trace flag is that updating the statistics results in recompilation of the queries accessing the table. This can increase the risk of getting a different query plan for the next executions of queries against those tables. Source.
   

2389

 When this trace flag is enabled you may notice a large increase in compile time. Additionally, SQL Server does not generate an efficient query plan because the correct maximum value for a column cannot be determined. Source.

Additionally, bad estimates and execution plans may be calculated. Query performance may decrease. Source.

2390

 When this trace flag is enabled you may notice a large increase in compile time. Additionally, SQL Server does not generate an efficient query plan because the correct maximum value for a column cannot be determined. Source.

Additionally, bad estimates and execution plans may be calculated. Query performance may decrease. Source.
   

2562

 This flag may increase space requirements for TempDB, and if the TempDB is configure for automatic growth, the increases in size may slow down the performance of the instance. Source.
   

2861

 The number of objects in the procedure cache increases and thus memory usage increases. Because of the increased number of objects stored in the procedure cache, it is possible that the time it takes for the relational engine to search for an existing plan may degrade and may adversely affect the performance of your system. Source.
   

3042

 Using this trace flag might cause a slight performance penalty. It may increase the duration of backup operations. Source. Source. Source.
   

3505

 Turning on this trace flag may affect data safety and availability of high availability systems, such as clusters. Setting trace flag 3505 may increase recovery time and can prevent log space reuse until the next checkpoint is issued. Source.
   

3608

 Some features, such as snapshot isolation and read committed snapshot, might not work. Source.
   

4119

 This trace flag may cause clustered index scans and thus may hurt performance (Dynamics). Source.
   

4136

 It is not a best practice for Dynamics AX. Source.

Customers should be careful about using the trace flag 4136, as it can result in poor performance in undesired areas as it is trying to "Optimize for UNKNOWN". Source.
   

4138

 If you enable trace flag 4138, performance may decrease for some other queries that contain TOP, OPTION (FAST N), IN, or EXISTS operators. Source.
   

4199

 Queries may run slow when this flag is enabled. Source. Source.
   

4616

 User reported "sa" password is reset on server restart when this flag is active. Source.
   

4618

 This flag limits the size of the cache store growth, but can incur in a small CPU overhead because this trace flag removes old cache entries as new entries are inserted. Source.

For some workloads, limiting the cache store to 1,024 or to 8,192 entries may not be an optimal solution. This is because trace flag 4618 forces SQL Server to prematurely clean up the TokenAndPermUserStore cache store even if you are not experiencing any performance issues. Also, additional overhead may occur on the server when SQL Server prematurely cleans up the TokenAndPermUserStore cache store. This additional overhead occurs for one of the following reasons: SQL Server must repopulate the cache store more frequently; SQL Server is forced to re-read permission metadata from the disk and to recalculate permission checks that may have been cached. Source.
   

8012

 Disabling a ring buffer generally improves performance. However, disabling a ring buffer eliminates diagnostic information that Microsoft Support uses and may prevent successful troubleshooting. Source.
   

8018

 This flag disables the exception ring buffer. Disabling the exception ring buffer makes it more difficult to diagnose problems that are related to internal server errors. Source.
   

8032

 Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool. Source.
   

8038

 The use of the trace flag 8038 reduces accuracy of the SQL Server trace events and other SQL Server timing outputs. Source.

This trace flag will also affect the granularity of some diagnostics, such as dynamic management views. Source.

   

8048

 This trace flag may introduce a small amount of memory overhead to all NUMA node-partitioned memory objects. Source. Source.
   

8744

 Trace flag 8744 disables pre-fetching for the Nested Loops operator. Incorrect use of this trace flag may cause additional physical reads when SQL Server executes plans that contain the Nested Loops operator. Source.
   

9134

 SQL Server performance may be significantly reduced. Source.
   




 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.