Shulou(Shulou.com)05/31 Report--

This article introduces how to control tracking tags in SQLServer, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

What is the tracking mark?

For DBA, mastering Trace Flag is one of the necessary conditions for becoming a master of SQL Server. In most cases, Trace Flag is just a strange way to go astray, which is not necessary, but in many cases, using these markers will give you better control over SQL Server's behavior.

Here is the official tag for Trace Flag:

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

From the above definition, it is not difficult to see that Trace Flag is a way to control the behavior of SQL Server. Many DBA have some misunderstandings about Trace Flag, thinking that Trace Flag is only possible in test and development environments, which is only partially true, so Trace Flag can be divided into two categories, those suitable for use in a production environment and those that are not suitable for use in a production environment.

Important:Trace Flag belongs to the trick of going too far. Before using Trace Flag to optimize, Apply the basic Best Practice.

How to control trace tags

There are three ways to control trace tags:

1. Through the DBCC command

Trace tags can be enabled or turned off through the DBCC command, which has the advantage of being easy to use, using the following three commands to enable and disable the status of the viewed trace tags:

DBCC TRACEON (2203Mae Murray 1)

DBCC TRACEOFF (2203 and 1)

DBCC TRACESTATUS

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

In addition, it is worth saying that if you want to control some Flag through the DBCC command every time the SQL Server service starts, use the

EXEC sp_procoption @ ProcName ='', @ OptionName =] 'startup', @ OptionValue =' on'

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

It is also worth noting that not all trace tags can be started with the DBCC command, such as Flag 835, which can only be specified by startup parameters.

two。 By specifying in the SQL Server configuration Manager

This is done by adding startup parameters to the database engine startup entry, only Global Scope. The format is-T# tracking tag 1 position T tracking tag 2 position T tracking tag 3.

3. Start through the registry

This way and method 2 are more or less the same, so I won't say much about it.

Some trace tags that may be needed in a production environment

Trace Flag 610

Reduce log generation. If you use a lot of basic best practice for logging, such as only one log file, appropriate number of VLF, separate storage, and if you still can't alleviate the excessive size of the log, consider using this trace flag.

Reference:

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 server, it is worth turning on this trace tag.

Trace Flag 835

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

Trace Flag 1118tempdb allocates the whole area, not the mixed zone, to reduce SGAM page contention. When you encounter a contention problem after apply tempdb's best practice, consider using this trace tag. Reference:

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

Both the Trace Flag 1204 and 1222 trace tags write deadlocks to the error log, but 1204 is in text format and 1222 is saved in XML format. You can view the log through sp_readerrorlog.

Trace Flag 1211 and 1224

Both ways are to disable lock escalation. However, there is a difference in behavior. 1211 does not escalate locks at any time, while 1224 enables lock escalation when memory pressure is high, thus avoiding out-of-locks errors. When both trace tags 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 can be useful in certain situations.

Trace Flag 3226

Prevent successful backups from logging. If log backups are too frequent, a large number of error logs will be generated, and enabling this trace flag will prevent log backups from being logged to the error log.

All KB patches in Trace Flag 4199 take effect to modify the query analyzer behavior. This command is dangerous and may sweep away the performance degradation. For more information, please see: http://support.microsoft.com/kb/974006

Trace tags that should not be enabled in a production environment

Trace Flag 806 checks the logical consistency of the page during the reading process, and you can see information similar to the following in the error log: 2004-06-25 1111 spid51 error: 04.11 spid51 error, severity: 24-day status: 22004-06-25 11GRO error (audit failure) 0x000000000b0000 in the topic SQL Server\ MSSQL\ data\ pubs.mdf e:\ Program file detected during reading. Reference: http://support.microsoft.com/kb/841776 this tracking tag will greatly degrade performance! Trace Flag 818 check write consistency trace flag 818 enables an in-memory ring buffer to track the last 2048 successful write operations performed by the computer running SQL Server (excluding sorting and working file Imax O). When an error such as 605,823 or 3448 occurs, the log sequence number (LSN) value of the incoming buffer is compared with the most recent write list. If the LSN retrieved during the 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 Iripple O operations. The implementation of ring buffers is lightweight, so the impact on system performance can be ignored. Reference: http://support.microsoft.com/kb/826433Trace Flag 1200 returns locking information in the whole process, which is a powerful sign of learning locking process. The sample code is as follows: DBCC TRACEON (1200 mahjong 1) DBCC TRACEON (3604) DBCC TRACESTATUSSELECT * FROM AdventureWorks.person.Address reference: http://stackoverflow.com/questions/7449061/nolock-on-a-temp-table-in-sql-server-2008Trace Flag 1806 disables instant file initialization All disk space requests are initialized with zeros, which may cause blocking as the space grows. Trace Flag 3502 displays information about checkpoint in the log. As shown in figure 1. Figure 1. It is shown in the error log that CheckpointTrace Flag 3505 does not allow automatic checkpoint,checkpoint can only be done 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 a production environment, while 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 markers are a way to get out of the way for tuning, and use trace markers only after all the basic tuning methods have been used.

On how to control tracking tags in SQLServer to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.