Tracking tag Full Raiders

Strategy

Trace flag

An overview
Trace flags are used to temporarily set specific characteristics of the server or to turn off specific behavior

Two categories

1. Bol record of the tracking mark

Describe
260 print version information about the extended stored procedure dynamic-link library (DLL).
For more information about __GetXpVersion (), see Creating extended stored procedures.

1204 returns the type of lock that participates in the deadlock and the currently affected command.
This parameter will be added to the error Log for information related to the deadlock (lock type, SQL query, etc.)

2528 to disable parallel checking of objects through DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE.
By default, the degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same way as a parallel query.
For more information, see max degree of parallelism option.
In general, you should leave concurrent DBCC enabled. When the DBCC CHECKDB is executed,
The query processor evaluates and automatically adjusts the degree of parallelism and examines each table or batch of tables.
Sometimes, a check may occur when the server is in actual idle state. If the administrator knows that the load will increase before the end of the check,
You may want to manually reduce or disable the degree of parallelism.

However, disabling parallel checking can result in lower overall database performance. Decreasing the degree of parallelism increases the amount of transaction log that must be scanned. This in turn increases the need for tempdb space and leads to a non-linear increase in the time required for DBCC to complete the check. If the TABLOCK feature is enabled and the degree of parallelism is turned off when the DBCC is running, the table may be locked for a longer period of time.

3205 by default, a DUMP or BACKUP statement uses this feature if the tape drive supports hardware compression.
With this trace flag, you can disable hardware compression for tape drivers.
This entry is useful when you want to swap tapes with other sites or tape drives that do not support compression.


Undocumented trace Flags
8602 can cause SQL Server to ignore the specified index in the statement that displays the declaration

8785 Disable all Gazzo statements that display declarations (all locking hints)

8722 Disable other type statements that display declarations (primary hints in the OPTION clause)

3605 write the results of other trace tags to the error Log


1200 Display detailed locking information

1206 additional locking information through deadlock to supplement 1204

2509 connection to DBCC CHECKTABLE to view the full number of shadow records

3502 additional information to record the system error log when the checkpoint is triggered

3607 Skip automatic recovery of all databases

3608 Skip automatic recovery of all databases except the primary database

8687 making a parallel query impossible


How to use three tracking markers

1. Use DBCC traceon to set trace flags

Example: Use of 8602 tags
Use Northwind
Go
SELECT * FROM [Order Details] where productid=59
Execution plan display using a clustered index

If you specify the use of the index
SELECT * FROM [Order Details] (Index=productid) where productid=59
The execution plan shows that the nonclustered index is scanned first, and this statement reduces query efficiency

Now use 8602 to ignore the use of the specified index
DBCC Traceon (8602)
Go
SELECT * FROM [Order Details] (Index=productid) where productid=59
The execution plan shows that the use of nonclustered indexes has been ignored

2. Enterprise Manager-> server name-> Properties-> General page-> startup Parameters-> Add trace flag
The format is txxxx,xxxx--tag code to determine the need to restart the SQL service after saving is changed to take effect
This will apply a preset trace flag every time you start SQL


3. Set trace flags at the command prompt
The following example shows how to turn on trace flag 3205 at a command prompt
Sqlservr–d "C:\Program Files\Microsoft SQL Server\mssql\data\master.mdf" –t3205

4. Can be entered in: Control Panel-> Service->mssqlserver-> Properties-> General-> startup parameters

5. Disable Trace markup
DBCC Traceoff

Four see status of trace flags DBCC tracestatus

DBCC Tracestatus (-1)
Displays the status of all trace flags that are currently enabled

DBCC Tracestatus (8602)
Show status of 8602 marks

Example:
Open first connection Use DBCC TRACEON (8602)
Open a second connection use DBCC TRACEON (8785)
Returns the first connection using DBCC TRACESTATUS (-1)
will see Traceflag Status
8602 1
8785 2