SQL Server – Trace Flags

 

id description versionActive
1117 When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points
1118 When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations
1204 Returns the resources and types of locks participating in a deadlock and also the current command affected.
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).

1222 Returns the resources and 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.
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
2312 Trace Flag 2312 enables the new SQL Server 2014 CE for a specific query compilation and execution. 2014
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.
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.
2551 The trace flag above tells SQL Server to add additional information to the dump file in case of a crash
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.
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.
3502 Most basic Checkpoint entries are logged
3504 More detailed information is logged about Checkpoints
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.

7827 A new ring buffer called “RING_BUFFER_CONNECTIVITY’ has been added to the dmv sys.dm_os_ring_buffers in SQL 2008 RTM.This will automatically log server-side initiated connection closures, if you see nothing in the dmv, then most likely the client reset/closed the connection. You can enable any connection closure (client or server) logging with trace flag 7827.

So if SQL Server 2008 is still online since the connection failure, make sure to capture the information from the sys.dm_os_ring_buffers base on the query in the blog above, as it may give you enough information to narrow down your troubleshooting to the client or server without costly netmon traces.

9481 Forces CE version 70 – https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server 2014

 

 

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. 2312
    • Liliam Cristiman
      • SQL Server 2016 new features to deal with the new CE
        Link
  5.  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
      • 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
    • Nitin Garg ( LostInCreativity )
      • Take SQL Server Full Memory Dump from within SQL Server and via Automated Way
        Link
  6. 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
  7. 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
  8.  4199
  9. 9481
    • Liliam Cristiman
      • SQL Server 2016 new features to deal with the new CE
        Link

 

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