Some SQL Server trace tags that DBAs should know

What is a trace tag?

For DBAs, mastering Trace Flag is one of the necessary conditions for them to become masters of SQL Server. In most cases, Trace Flag is just a rare trick, but in many cases, these tags allow you to better control SQL Server behaviors.

Below are official Trace flags:

A trace tag is a tag used to enable or disable certain behaviors of SQL Server.

 

From the above definition, it is not difficult to see that Trace Flag is a way to control the behaviors of SQL Server. Many DBAs have some misunderstandings about Trace Flag. They believe that Trace Flag can only be used in testing and development environments. This idea can only be partially correct. Therefore, Trace Flag can be divided into two types, suitable for use in the production environment and not suitable for use in the production environment.

 

Important: Trace Flag is the preferred method. Before using Trace Flag for optimization, Apply the basic Best Practice.

 

How to Control trace tags

There are three ways to control the trace Tag:

1. Run the DBCC command

You can use the DBCC command to enable or disable the trace tag. The advantage of this method is that it is easy to use. You can use the following three commands to disable the status of the viewed trace Tag:

• Dbcc traceon (2203,-1)
• Dbcc traceoff (2203,1)
• DBCC TRACESTATUS

 

The second TRACEON and TRACEOFF parameters represent the range of enable flag, 1 is Session Scope, and-1 is Global Scope. If this value is not specified, the default value is Session Scope.

In addition, if you want to control certain flags by running the DBCC command at each startup of the SQL Server service, use

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

This stored procedure is specified. The sp_procoption stored procedure is automatically executed when the SQL Server is started.

It is worth noting that not all trace tags can be started with the DBCC command. For example, Flag 835 can only be specified through the startup parameter.

 

2. Specify

This method is to add startup parameter settings to the Database Engine Startup item, and only Global Scope is available. The format is-T # Trace mark 1; T trace mark 2; T trace mark 3.

 

3. Start through the Registry

This method is similar to method 2.

 

Some tracing tags that may be required in the production environment

 

Trace Flag 610

Reduce log generation. If you use a lot of basic best practice for logs, such as only one log file, an appropriate number of VLF files, and independent storage, if the log is too large, consider using this trace mark.

References:

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 page buffer pool allocation. If the Server is a dedicated SQL Server, it is worth enabling this trace mark.

 

Trace Flag  835

SQL Server 2005 and 2008 Standard versions are allowed to use the "Lock Memory Page", which is similar to the results set in the Group Policy, but can be used in the standard version.

 

Trace Flag  1118

Tempdb allocates the entire zone instead of the hybrid zone, reducing SGAM page competition.

When applying tempdb's best practice, you may also encounter a race problem. Consider using this trace tag.

References:

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 tags write deadlocks to error logs, but 1204 is in text format, and 1222 is saved in XML format. You can use

Sp_readerrorlogView logs.

Trace Flag 1211 and 1224

Both methods disable lock upgrade. However, the behavior is different. 1211 means that no lock upgrade will be performed at any time, and 1224 will enable the lock upgrade when the memory pressure is high, thus avoiding the out-of-locks error. When both trace tags are enabled, 1211 has a higher priority.

 

Trace Flag  2528

Disable parallel execution of dbcc checkdb, dbcc checkfilegroup, and 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 situations.

 

Trace Flag  3226   

Prevents successful log backup. If log backup is too frequent, a large number of error logs will be generated. Enabling this trace mark will make log backup no longer recorded in error logs.

 

Trace Flag  4199

All KB patches take effect for the query analyzer behavior modification. This command is dangerous and may be called as performance degradation. For details, see:

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

Trace flag should not be enabled in the production environment

Trace Flag  806 
 
Check the logical consistency of pages during reading. The following information is displayed in the error log:
 
11:29:04. 11 spid51 error: 823, severity: 24-day status: 2
11:29:04. 11 spid51 I/O error (Audit Failed) the offset detected during reading the subject SQL Server \ MSSQL \ data \ pubs. mdf e: \ 0x000000000b0000 in the Program file.
 
References: http://support.microsoft.com/kb/841776
 
This trail tag greatly reduces performance !!!

Trace Flag 818  
 
Check write consistency
Mark 818 enables an in-memory ring buffer to track the last 2,048 successful write operations (excluding sorting and working file I/O) executed by the computer running SQL Server ). When an error such as 605, 823, or 3448 occurs, compare the log serial number (LSN) value of the input buffer with the latest write list. If the LSN retrieved during the read operation is older than the LSN specified during the write operation, a new error message is recorded in the SQL Server Error Log. Most SQL Server write operations appear in the form of checkpoints or inert writes. Inert write is a background task that uses asynchronous I/O operations. The implementation of the circular buffer is lightweight, so the impact on system performance can be ignored.

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

Trace Flag 1200 
 
The whole process of returning the lock information is a sign of learning the lock process. The sample code is as follows:
 
DBCC TRACEON(1200,-1)DBCC TRACEON(3604)DBCC TRACESTATUSSELECT * FROM AdventureWorks.person.Address
 
References:
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 by entering 0, which may cause blocking when space increases.
 
Trace Flag 3502
The checkpoint information is displayed in the log. 1.
Figure 1. Display Checkpoint in the error log

Trace Flag 3505
Automatic checkpoint is not allowed, and checkpoint can only be performed manually. It is a very dangerous command.
 
 
Summary
Tracking flags are a way to control SQL Server behaviors. For some trace flags, performance can be improved in the production environment, while for others, it is very dangerous to use it in a production environment. It can only be used in a testing environment. Keep in mind that the tracking tag is a method of extreme stability for optimization. It is considered to be used only after all basic optimization methods are used.