Some SQL Server trace flags that DBAs should know

What is a trace flag?

For DBAs, mastering trace flag is one of the prerequisites for becoming a SQL Server whiz, and in most cases, trace flag is just a pifo of a sword walk, not necessary, but in many cases, using these tags will give you better control over SQL The behavior of the server.

Here is the official tag for trace flag:

A trace flag is a token that enables or disables certain behavior of SQL Server.

It is not difficult to see from the above definition that Trace flag is a way to control the behavior of SQL Server. Many DBAs have some misconceptions about trace flag that it is only possible to use trace flag in a test and development environment, which is only partially correct, so there are two types of trace flag that are suitable for use in a production environment and unsuitable for use in a production environment.

Important:trace flag belongs to the Sword Walk Pifo, before using Trace flag to do optimization, first apply the basic best practice.

How to control trace flags

There are three ways to control trace flags:

1. Through the DBCC command

The advantage of using the DBCC command to enable or turn off trace flags is that it is easy to use, with the following three commands enabled to disable the status of the trace flag that has been viewed:

• DBCC TRACEON (2203,-1)
• DBCC Traceoff (2203,1)
• DBCC Tracestatus

Where the second parameter of Traceon and Traceoff represents the range of the enabled flag, 1 is the session scope,-1 is global scope, and if this value is not specified, the session scope is maintained.

Also, it's worth saying that if you want to control some flags with the DBCC command every time the SQL Server service starts, use the

EXEC sp_procoption @ProcName = ' <procedure name> '     , @OptionName =] ' startup '     

This stored procedure specifies that the sp_procoption stored procedure is executed automatically when the SQL Server server starts.

It's also worth noting that not all trace flags can be started with the DBCC command, such as Flag 835, which can only be specified by the boot parameter.

2. By specifying in SQL Server Configuration Manager

This is done by adding the start parameter setting in the Database engine startup item, only global Scope. The format is-t# trace flag 1; t trace flag 2; T trace flag 3.

3. Start by using the registration form

This way and Method 2 is the same, not much to say.

Some trace flags that may be required in a production environment

Trace Flag 610

Reduce the amount of log production. If you use a lot of basic best practice for logs, such as only one log file, a proper number of VLF, and a separate store, consider using this trace flag if you still cannot mitigate the log too large.

Resources:

Http://msdn.microsoft.com/en-us/library/dd425070.aspx

Http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx

Trace Flag  834

Use Microsoft Windows large paged pool allocation. If the server is a SQL Server dedicated server, it is worth turning on the trace flag.

Trace Flag  835

Allow SQL Server 2005 and 2008 Standard editions to use lock memory pages, and the results set in Group Policy are similar, but are allowed in the Standard Edition.

Trace Flag  1118

Tempdb allocates an entire area, rather than a mixed zone, to reduce SGAM page scramble.

After you apply the best practice for tempdb, you also encounter a scramble issue, consider using the trace flag.

Resources:

Http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

Trace Flag  1204 and 1222

Both trace flags write deadlocks to the error log, but 1204 is in text format and 1222 is saved in XML format. can be done by

sp_readerrorlog View the log . 

Trace Flag  1211 and 1224

Lock escalation is disabled in both ways. But the behavior is different. 1211 is no lock escalation at any time, and 1224 enables lock escalation when memory pressure is high, thus avoiding out-of-locks errors. When two trace flags are enabled, 1211 has a higher priority.

Trace Flag  2528

Disables parallel execution of DBCC CHECKDB, DBCC CHECKFILEGROUP,DBCC checktable. This means that these commands can only be executed single-threaded, which may take more time, but is useful in certain situations.

Trace Flag  3226   

Prevents a successful backup of the log records. If log backups are too frequent, a large number of error logs are generated, enabling the trace flag to make log backups no longer logged to the error log.

Trace Flag  4199

All KB patches are valid for changes to the query parser behavior, which is dangerous and may degrade performance, see:

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

trace flags that should not be enabled in a production environment

Trace Flag  806

Checking the logical consistency of the page during the read process, you can see information similar to the following in the error log:

2004-06-25 11:29:04.11 spid51 Error: 823, severity: 24th, State: 2
2004-06-25 11:29:04.11 spid51 I/O error (Audit failure) detected offset during read the topic SQL server\mssql\data\pubs.mdf e:\Program file 0x000000000b00 00.

Reference: http://support.microsoft.com/kb/841776

This trace flag can greatly degrade performance!!!

Trace Flag 818  

Check Write consistency

Trace flag 818 enables an in-memory ring buffer to track the last 2,048 successful writes performed by the computer running SQL Server (excluding sorting and working file I/O). When an error such as 605, 823, or 3448 occurs, the log sequence number (LSN) value of the incoming buffer is compared to the most recent write list. If the LSN retrieved during a read operation is older than the one specified during the write operation, a new error message is logged in the SQL Server error log. Most SQL Server writes occur in the form of checkpoints or lazy writes. Lazy Write is a background task that uses asynchronous I/O operations. The implementation of the ring buffer is lightweight, so the impact on system performance can be ignored.

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

Trace Flag 1200

The whole process of returning lock information is a great sign to learn the locking process, the sample code is as follows:

DBCC TRACEON (1200,-1) DBCC TRACEON (3604) DBCC TRACESTATUSSELECT * from AdventureWorks.person.Address

Resources:

http://stackoverflow.com/questions/7449061/nolock-on-a-temp-table-in-sql-server-2008

Trace Flag 1806

Disable instant file initialization, all disk space requests are initialized with 0, which can cause blocking when space grows.

Trace Flag 3502

Information about checkpoint is displayed in the log. As shown in 1.

Figure 1: Display checkpoint in the error log

Trace Flag 3505

Not allowing automatic checkpoint,checkpoint can only be done manually and is a very dangerous command.

Summary

Trace flags are a way to control the behavior of SQL Server, which can improve performance in a production environment for some trace flags, and for others, it is a very dangerous thing to use in a production environment and can only be used in a test environment. Keep in mind that trace flags are a means of pifo for tuning, and only after using all of the basic tuning methods, consider using trace markers.

Some SQL Server trace flags that DBAs should know