Some SQLServer trace tags that DBAs should know

What is a trace tag? For DBAs, mastering TraceFlag is one of the necessary conditions for them to become a master of SQLServer. In most cases, TraceFlag is just an odd trick, not necessary, but in many cases, these tags allow you to better control SQLServer behaviors. The following are official TraceFlag tags

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 is the official Trace Flag mark

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:

 

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 =' ', @ 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 reduces 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:

 

 

Trace Flag 834 is allocated using the large page buffer pool of Microsoft Windows. If the Server is a dedicated SQL Server, it is worth enabling this trace mark.

 

Trace Flag 835 allows SQL Server 2005 and 2008 Standard versions to use "lock Memory Page", website space, similar to the results set in the Group Policy, but can be used in the standard version.

 

Trace Flag 1118tempdb 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:

 

Trace Flag 1204 and 1222 both indicate that deadlocks are written to error logs, but 1204 is in text format and 1222 is saved in XML format. You can use sp_readerrorlog to view logs. Trace Flag 1211 and 1224 both 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 disables 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 backups of logs. 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 modification of query analyzer behavior. This command is dangerous and may be called a drop in performance. For details, see: trace Flag 806 should not be enabled in the production environment to 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 theme SQL Server \ MSSQL \ data \ pubs detected during read. 0x000000000b0000 in mdf e: \ Program file. reference: this trail tag greatly reduces performance !!!
Trace Flag 818 check write consistency Trace mark 818 enables a ring buffer in memory, used 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:
Trace Flag 1200 returns the entire process of locking information, which is a great sign of learning the locking process. The sample code is as follows: dbcc traceon (1200,-1) dbcc traceon (3604) dbcc tracestatusselect * FROM AdventureWorks. person. address reference:
Trace Flag 1806 disables instant file initialization. All disk space requests are initialized by filling in 0, which may cause blocking during space growth. Trace Flag 3502 displays checkpoint information in the log. 1.

Figure 1. Display Checkpoint in the error log
Trace Flag 3505 does not allow automatic checkpoints. Checkpoints can only be performed manually, which is a very dangerous command. Summary