How to control trace marks in sqlserver


What is the tracking mark?

For DBAs, mastering trace flag is one of the necessary conditions to become a master of SQL server. In most cases, trace flag is just a trick to take the wrong side of the sword, which is unnecessary, but in many cases, using these flags can let you better control the behavior of SQL server.

The following is the official mark of trace flag:

A trace tag is a tag that enables or disables certain behaviors 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 misunderstandings about trace flag, and think that trace flag can be used only in the testing and development environment. This idea can only be said to be partially correct. Therefore, trace flag can be divided into two categories, those suitable for use in the production environment and those not suitable for use in the production environment.

Important: trace flag belongs to the trick of taking the edge of the sword. Before using trace flag for optimization, first apply the basic best practice.

How to control tracking marks

There are three ways to control tracking marks:

1. Through DBCC command

You can enable or disable the tracking mark through the DBCC command. The advantage of this method is that it is easy to use. Use the following three commands to enable and disable the status of the viewed tracking mark respectively:




The second parameter of traceon and traceoff represents the range of the enable flag. 1 is session scope and -1 is global scope. If this value is not specified, the default value of session scope will be maintained.

In addition, it is worth mentioning that if you want to control some flags through the DBCC command every time the SQL Server service is started, use

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

This stored procedure specifies that SP_ The procoption stored procedure is automatically executed when the SQL server server is started.

It is also worth noting that not all trace marks can be started with DBCC command. For example, flag 835 can only be specified through startup parameters.

2. By specifying in SQL Server Configuration Manager

This way is to add the startup parameter setting in the database engine startup item, only global scope. The format is -t# tracking mark 1; T tracking Mark 2; T tracking mark 3.

3. Start through the registry

This way and method 2 are similar, so I won’t say more.

Some trace marks that may be required in the production environment

Trace Flag 610

Reduce log generation. If you use many basic best practices for logs, such as only one log file, an appropriate number of VLFS, and separate storage, if you still can’t alleviate the excessive size of the log, consider using this tracking flag.

reference material:

Trace Flag 834

Use Microsoft Windows large page buffer pool allocation. If the server is a SQL server dedicated server, it is worth turning on the tracking flag.

Trace Flag 835

Allow SQL Server 2005 and 2008 standard edition to use “lock memory pages”, which is similar to the result set in group policy, but allow it to be used in standard edition

Trace Flag 1118
Tempdb allocates the entire area instead of the mixed area, reducing SGAM page contention.
After applying tempdb's best practice, we also encountered the problem of competition. Consider using this tracking flag.
reference material:

Trace flag 1204 and 1222
Both trace tags write deadlocks to the error log, but 1204 is in text format and 1222 is saved in XML format. Can pass
sp_ Readerrorlog view the log.
Trace flag 1211 and 1224

Both methods disable lock escalation. However, there is a difference in behavior. 1211 will not lock upgrade at any time, while 1224 will enable lock upgrade when memory pressure is high, thus avoiding out of locks errors. When both trace flags are enabled, 1211 has a higher priority.

Trace Flag 2528

Disable parallel execution of DBCC CHECKDB, DBCC checkfilegroup, DBCC checktable. This means that these commands can only be executed in a single thread, which may take more time, but it is still useful in some specific cases.

Trace Flag 3226  

Prevent logging successful backups. If the log backup is too frequent, a large number of error logs will be generated. Enabling this tracking flag can make the log backup no longer be recorded in the error log.

Trace Flag 4199
All KB patches are effective for modifying the behavior of the query analyzer. This command is dangerous and may degrade the scanning performance. For details, see:

Trace tags that should not be enabled in production environments

Trace flag 806 checks the logical consistency of the page during reading. You can see the following information in the error log: 2004-06-25 11:29:04.11 spid51 error: 823, severity: 24 day status: 22004-06-25 11:29:04.11 spid51 i/o error (audit failure) offset detected during reading topic SQL server\mssql\data\pubs MDF e:\program file 0x000000000b0000 reference material: This trace tag will greatly reduce the performance!!! Trace flag 818 check write consistency trace flag 818 enables a ring buffer in memory to track the last 2048 successful write operations (excluding sorting and working file i/o) performed by the computer running SQL server. When an error such as 605, 823, or 3448 occurs, the log sequence number (LSN) value of the incoming buffer is compared with the latest write list. If the LSN retrieved during the read operation is older than that specified during the write operation, a new error message is recorded 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 ring buffer is lightweight, so the impact on system performance can be ignored. reference material: Flag 1200 returns the whole process of locking information, which is a powerful sign of learning the locking process. The example code is as follows: DBCC traceon (1200, -1) DBCC traceon (3604) DBCC tracestatusselect * from adventureworks person. Address reference: Flag 1806 disables instant file initialization, and all disk space requests are initialized with 0, which may cause blocking when space grows. Trace flag 3502 displays relevant information about checkpoint in the log. As shown in Figure 1.

Figure 1 The checkpointtrace flag 3505 displayed in the error log does not allow automatic checkpoint. Checkpoint can only be performed manually, which is a very dangerous command.


Trace flags are a way to control SQL Server behavior. For some trace flags, performance can be improved in the production environment, while for others, it is a very dangerous thing to use in the production environment, which can only be used in the test environment. Keep in mind that tracking markers are a powerful tool for tuning. You should consider using tracking markers only after you have used all the basic tuning tools.