SQL Server – Trace Flags

Trace Flag Description Source Person
1117 When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points CSS SQL Server Engineers ( Link ) CSS SQL Server Engineer
1118 When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations CSS SQL Server Engineers ( Link ) CSS SQL Server Engineer
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. For more information, see Lock Escalation (Database Engine).

Trace Flags – Transact SQL ( Link ) MS Support
1204 Returns the resources and types of locks participating in a deadlock and also the current command affected. MS Support  –
SQL Server technical bulletin – How to resolve a deadlock ( Link )
MS Support
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. For more information, see Lock Escalation (Database Engine).

Trace Flags – Transact SQL ( Link ) MS Support
1244 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds 40% of Memory assigned to SQL Server or set for locks with sp_configure  Trace Flags – Transact SQL ( Link ) MS Support
 2389 Ascending key columns can cause inaccurate statistics in tables that have frequent insert operations. These key columns may be IDENTITY columns or datetime columns that represent real-world time stamps. A common problem for some SQL Server applications are cases in which data typically ascends. For example, you have a table that contains a datetime column, and the column represents a current date. SQL Server builds statistics that assume that data will be mostly similar in the future. However, when data typically ascends, most new insertions are out of the previously found range. This behavior may cause poorly performing plans to be created. Filters that select recent data may exclude the whole relation even though a significant number of rows are included.  MS Support ( Link )  MS Support
2549  The DBCC CHECKDB command builds an internal list of pages to read per unique disk drive across all database files. This logic determines unique disk drives based on the drive letter of the physical file name of each file. If the underlying disks are actually unique when the drive letters or not, the DBCC CHECKDB command would treat these as one disk. When this trace flag is enabled, each database file is assumed to be on a unique disk drive. Do not use this trace flag unless you know that each file is based on a unique physical disk. Support ( Link )
KB 2634571
v2008-R2/SP1
2551 The trace flag above tells SQL Server to add additional information to the dump file in case of a crash MS Connect – SQL Server is terminating because of fatal exception c0150014 – KB 477863 Fabricio Voznika
2562 Run the DBCC CHECKDB command in a single “batch” regardless of the number of indexes in the database. By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or “facts” that it generates by using a “batches” concept. This trace flag forces all processing into one batch. KB 2634571 v2008 R2/SP1
3226  By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.  Trace Flags – Transact SQL ( Link )  MS Support
3502 Most basic Checkpoint entries are logged  SQLMag – Link  SQLMag
3504 More detailed information is logged about Checkpoints   SQLMag – Link  SQLMag
 4199 Controls multiple query optimizer changes previously made under multiple trace flags.

In other words, changes introduced in Cumulative Updates and Service Packs are enabled.

 

 

In Depth

 

Trace Flags – Specific

  1. 1117
    • SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
      Link
    • Aaron Bertrand – SQL Server 2016 : Getting tempdb a little more right
      Link
  2. 1118
    • SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage
      Link
    • Aaron Bertrand – SQL Server 2016 : Getting tempdb a little more right
      Link
    • pssql & Robert Dorr – SQL Server TempDB – Number of Files – The Raw Truth
      Link
  3.  1204
    • SQL Server technical bulletin – How to resolve a deadlock
      Link
  4.  2551
    • Microsoft Connect – SQL Server is terminating because of fatal exception c0150014 – by Rob.George
      Link
    • Microsoft Connect – RING_BUFFER_EXCEPTION when using application locks – by lasa
      Connect Item :- 265183
      Link
    • Amit Banerjee – SQL Server 2012: Trace Flags
      Posted on January 20, 2014
      Link
    • Microsoft SQL Server 2008 R2 – Error “The MSSQLSERVER service terminated unexpectedly”
      Link
  5. 2562
    • Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
      Link
    • Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 & 2549
      Link
  6. 3502 & 3504
    • Paul S. Randal – How to monitor checkpoints
      Link
    • How It Works: When is the FlushCache message added to SQL Server Error Log?
      Link
  7.  4199

 

References

  1. Microsoft
    • Trace Flags (Transact-SQL)
      Link
    • Scalability
      • Database Features \ In-Memory OLTP (In-Memory Optimization) \ Creating and Managing Storage for Memory-Optimized Objects
        Link
  2. Brent Ozar
    • SQL Server 2016: The Death of the Trace Flag
      Hint
  3. Warner Chaves
    • The Most Important Trace Flags for SQL Server
      Link
  4. Aaron Bertrand
    • T-SQL Tuesday #56 : SQL Server Assumptions
      Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

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