How to control trace flags in SQL Server _mssql

What is a trace tag?

For DBAs, mastering trace flag is one of the prerequisites for becoming a master of SQL Server, and in most cases, trace flag is just a sword-walking slant, not necessary, but in many cases, using these tags will allow you to better control the SQL The behavior of the server.

Here is the official tag for trace flag:

A trace flag is a tag that enables or disables certain behaviors of SQL Server.

It is easy to see from the above definition that Trace flag is a way to control the behavior of SQL Server. Many DBAs have misconceptions about trace Flag, and think that trace Flag can only be used in test and development environments, which is only partially true, 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 slant, before using Trace flag to do optimization, first apply the basic best Practice.

How to control Trace markup

There are three ways to control trace markup:

1. Through the DBCC command

Trace flags can be enabled or turned off through the DBCC command, which is easy to use, enabled by using the following three commands, and disables the status of view trace flags that have been viewed:

DBCC Traceon (2203,-1)

DBCC Traceoff (2203,1)

DBCC Tracestatus

Where the Traceon and traceoff the second parameter represents the scope of the Enable flag, 1 is the session scope,-1 is global scope, and if you do not specify the value, the default value session scope is persisted.

Also, it is worth saying that if you want to control certain flag with the DBCC command each 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 is started.

It is also noteworthy that not all trace flags can be started with the DBCC command, such as Flag 835, which can only be specified through the startup parameters.

2. By specifying in SQL Server Configuration Manager

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

3. Launch via Registry

This approach and Method 2 is very much the same, do not say more.

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 the basic best practice for the log, such as only a log file, a proper number of VLF, separate storage, or if you can't mitigate the log too large, consider using the trace tag.

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 the Microsoft Windows large paged pool allocation. If the server is a SQL Server dedicated server, it is worth opening the trace flag.

Trace Flag 835

Allowing SQL Server 2005 and 2008 Standard editions to use "Lock memory Pages" is similar to the results set in Group Policy, but is allowed in standard editions.

Trace Flag 1118

Tempdb allocates the entire area instead of the mixed zone, reducing Sgam page contention.

After you apply the best practice of tempdb, you also encounter a scramble issue and consider using the trace tag.

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 pass

Sp_readerrorlog View the log.

Trace Flag 1211 and 1224

Both ways are to disable lock escalation. But there's a difference in behavior. 1211 is not lock upgrade at any time, and 1224 will enable lock escalation when memory pressure is high, thereby avoiding out-of-locks errors. When two trace flags are enabled, 1211 has a higher priority.

Trace Flag 2528

Disables the concurrent 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 they are useful in certain situations.

Trace Flag 3226  

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

Trace Flag 4199

All KB patches are in effect for the Query Analyzer behavior, this command is more dangerous, may be scanning performance degradation, please refer to:

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

Trace flags that should not be enabled in a production environment

The logical consistency of the page checks during the read process can be seen in the error log, similar to the following:
2004-06-25 11:29:04.11 spid51 Error: 823, severity: 24th status: 22004-06-25 11:29:04.11 spid51 I/O error (Audit failed) offset subject detected during read SQL Server \mssql\data\pubs.mdf the 0x000000000b0000 in the E:\Program file.
References: http://support.microsoft.com/kb/841776
This trace tag can greatly degrade performance!!!

Check Write consistency
Tracer sign 818 is enabled.
An in-memory ring buffer that tracks the last 2,048 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 to the most recent write list. If the LSN retrieved during the read operation is older than specified during the write operation, a new error message is logged in the SQL Server error log. Most SQL Server write operations appear as checkpoints or as 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.
References: http://support.microsoft.com/kb/826433
The whole process of returning the lock information is a great sign of learning the lock process, with the sample code 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
Disables instant file initialization, all disk space requests are initialized with 0 fill, which can cause blocking when space grows.
Trace Flag 3502
Displays relevant information about checkpoint in the log. As shown in Figure 1.

Figure 1. Displaying checkpoint in the error log
Trace Flag 3505
Not allowing automatic checkpoint,checkpoint can only be done manually, is a very dangerous command.

Summary

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