Trace Flag 관련해서 잘 정리해 놓으신것 같습니다. (시퀄 메거진을 보면 가끔씩 이분의 글들이 보이죠... )
출처 : http://sqlserverpedia.com/wiki/Trace_Flags
Kevin Kline
Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software, a leading provider of award winning tools for database management and application monitoring. He is a founding board member and former President of the international Professional Association for SQL Server (PASS) and frequently contributes to database technology magazines, web sites, and discussion forums. Kevin also serves the community as an adviser to SQL Saturday education program as well as a curriculum adviser for both the University of Washington and Purdue University at Calumet in their IT and Computer Science departments.
Kevin’s most popular book is SQL in a Nutshell (now in its third edition) published by O’Reilly Media. Kevin is also author or co-author on seven other IT books, including Transact-SQL Programming, Database Benchmarking: A Practical Approach, and Professional SQL Server 2008 Relational Database Design and Optimization.
A top rated speaker, He appears at international conferences like Microsoft TechEd, DevTeach, PASS, Microsoft IT Forum, SQL Connections, and the Best Practices Conference.
Beginning his career as a lowly hardware jockey working with PC’s, Digital VAX, and Intergraph Unix workstations, Kevin has worked on multiple large-scale database projects throughout his career at Deloitte & Touche, NASA and the U.S. Army.
When Kevin isn’t working on technology issues, he enjoys spending time with his wife Rachel, his four kids, his three step kids, and his Basset Hound and Ginger Kitty.
His online presences include:
- Blog: http://kevinekline.com
- Twitter: http://twitter.com/kekline
To turn on a particular trace flag for a given connection, you execute the DBCC TRACEON command with the trace flag number specified in parenthesis, as follows:
/* send the output of DBCC commands to the client */
DBCC
TRACEON
(
3604
)
To enable a trace flag on the SQL Server instance level you need to start SQL Server with the /T parameter followed by the trace flag number. If you start SQL Server with a trace flag, all connections will automatically have the same trace flag turned on. For example, to collect deadlock related information you could start the default instance of the SQL Server instance from the command prompt as follows:
NET
START
MSSQLSERVER
/
T1205
You can check the status of a particular trace flag by executing DBCC TRACESTATUS. Turn off a previously turned on trace flag using DBCC TRACEOFF. For example:
DBCC
TRACESTATUS
(
3604
)
DBCC
TRACEOFF
(
3604
)
You can check all trace flags turned on for the current connection by passing -1 as the parameter of DBCC TRACESTATUS, for example:
DBCC
TRACESTATUS
(
-
1
)
Results:
TraceFlag Status
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
2520 1 3604 1
If you have no trace flags turned on, SQL Server will return the following message:
Trace
option
(
s
)
not
enabled
for
this
connection
.
Use
'DBCC TRACEON()'
.
Next, we discuss some of the more frequently used trace flags. As mentioned above, some of these can be very powerful and therefore should be handled with care.
DBCC TRACEON / TRACEOFF (-1)
This flag advises SQL Server to turn on the trace flags turned on for the current connection on all subsequent client connections. For example, if you have turned on 3604 and 2520 on the current connection and you execute DBCC TRACEON(-1) all subsequent connections to the server will have 3604 and 2520 turned on. Similarly, if you wish to turn a particular trace flag for all connections simply execute it along with -1, as in DBCC TRACEON(-1, 3604). Executing DBCC TRACEOFF(-1) will automatically turn off all trace flags on the current and any subsequent connections.
DBCC TRACEON (2528)
This flag disables parallelism during executing of maintenance DBCC statements, such as DBCC CHECKDB, DBCC CHECKFILEGROUP and DBCC CHECKTABLE. By default SQL Server will determine the needed degree of parallelism during query execution. Usually it is recommended to let SQL Server decide whether parallelism will be useful. Occasionally, if you only wish to use a single processor for DBCC statements, you might wish to override the default behavior. Remember that turning off parallelism might increase the total time required for executing DBCC commands.
DBCC TRACEON(3604) and DBCC TRACEON(3605)
The first flag (3604) sends the output of (some) DBCC commands and trace flags to the Query Analyzer; 3605 sends the same output to SQL Server error log. For example, the following commands will generate a list of the 10 longest buffer chains in Query Analyzer:
DBCC
TRACEON
(
3604
)
DBCC
BUFCOUNT
Results:
*
*
*
*
THE 10 LONGEST BUFFER CHAINS
*
*
*
*
bucket number
=
514 chain
size
=
3 bucket number
=
522 chain
size
=
2 bucket number
=
770 chain
size
=
2 bucket number
=
1026 chain
size
=
2 bucket number
=
269 chain
size
=
1 bucket number
=
272 chain
size
=
1 bucket number
=
274 chain
size
=
1 bucket number
=
281 chain
size
=
1 bucket number
=
283 chain
size
=
1 bucket number
=
284 chain
size
=
1 The Smallest Chain
Size
is
: 0 The Average Chain
Size
is
: 0.005066
If you turn off 3604 and turn on 3605, instead you'll get the same result in the error log. You can double check this by executing the following:
EXEC
master..
xp_readerrorlog
Abbreviated results:
ERRORLOG ContinuationRow 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
514 chain
size
=
3 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
522 chain
size
=
2 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
770 chain
size
=
2 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
1026 chain
size
=
2 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
269 chain
size
=
1 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
272 chain
size
=
1 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
274 chain
size
=
1 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
281 chain
size
=
1 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
283 chain
size
=
1 0 2003
-
10
-
24 21:00:31.51 spid51 bucket number
=
284 chain
size
=
1 0 2003
-
10
-
24 21:00:31.51 spid51 The Smallest Chain
Size
is
: 0 0 2003
-
10
-
24 21:00:31.51 spid51 The Average Chain
Size
is
: 0.005066 0
DBCC TRACEON(1204) , DBCC TRACEON(1205) and DBCC TRACEON(1206)
These trace flags are used to troubleshoot deadlocks. 1204 returns deadlock chains and the victim SPID. 1205 returns the details of the commands (stack traces) involved in the deadlock. Along with these flags you should also turn on 3605 to send the output of the trace to the SQL Server error log. 1206 can be used to supplement the information collected by the other two trace flags by returning all lock activities performed by deadlocked connections. The output of 1206 can be very large.
If you wish to see what deadlock output looks like, simply open two connections to the same SQL Server instance through Query Analyzer and execute following on one of them:
DBCC
TRACEON
(
-
1, 1204
)
DBCC
TRACEON
(
-
1, 1205
)
DBCC
TRACEON
(
-
1, 3605
)
Next execute the following on the first connection:
USE
pubs
BEGIN
TRAN
UPDATE
titles
SET
title
=
'deadlock battle'
WAITFOR
DELAY
'00:00:05'
UPDATE
authors
SET
address
=
'110 north main'
At the same time execute the following script from the other connection:
USE
pubs
BEGIN
TRANSACTION
UPDATE
authors
SET
address
=
'115 East 43rd street'
UPDATE
titles
SET
title
=
'who can win?'
One of these connections will be chosen as a deadlock victim and its transaction will be aborted by SQL Server. Now if you read the error log, you will find entries similar to the following:
spid4
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
spid4 Starting deadlock
search
1 spid4 Target Resource Owner: spid4 ResType:LockOwner Stype:
'OR'
Mode: U SPID:53 ECID: Ec:
(
x195 756
)
Value
: x1916eb4 spid4 Node:1 ResType:LockOwner Stype:
'OR'
Mode: U SPID:53 ECID: Ec:
(
x195 756
)
Value
: x1916eb4 spid4 spid4
End
deadlock
search
1 ... a deadlock was
not
found
. spid4
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
spid4
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
spid4 Starting deadlock
search
2 spid4 Target Resource Owner: spid4 ResType:LockOwner Stype:
'OR'
Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 spid4 Node:1 ResType:LockOwner Stype:
'OR'
Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 spid4 Node:2 ResType:LockOwner Stype:
'OR'
Mode: U SPID:53 ECID: Ec:
(
x195 756
)
Value
: x1916eb4 spid4
Cycle
: ResType:LockOwner Stype:
'OR'
Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 spid4 spid4 spid4
<
b>Deadlock
cycle
was encountered .... verifying
cycle
<
/
b> spid4 Node:1 ResType:LockOwner Stype:
'OR'
Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 Cost:
(
/
16
)
spid4 Node:2 ResType:LockOwner Stype:
'OR'
Mode: U SPID:53 ECID: Ec:
(
x195 756
)
Value
: x1916eb4 Cost:
(
/
B88
)
spid4
Cycle
: ResType:LockOwner Stype:
'OR'
Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 Cost:
(
/
16
)
spid4 spid4
<
b>Deadlock encountered .... Printing deadlock information
<
/
b> spid4 spid4 Wait
-
for
graph spid4 spid4 Node:1 spid4
KEY
: 5:1977 58 79:1
(
1 1aedb232b
)
CleanCnt:1 Mode: X Flags: x spid4
Grant
List:: spid4 Owner: x1916ee2 Mode: X Flg: x
Ref
: Life: 2 SPID:53 ECID: spid4 SPID: 53 ECID:
Statement
Type:
UPDATE
Line #: 1 spid4
Input
Buf:
Language
Event:
<
b>
begin
tran
update
authors
set
address
=
'115 East 43rd street '
update
titles
set
title
=
'who can win?'
<
/
b> spid4 Requested
By
: spid4 ResType:LockOwner Stype:
'OR'
Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 Cost:
(
/
16
)
spid4 spid4 Node:2 spid4
KEY
: 5:2121 58592:1
(
a7 64fb1eac
)
CleanCnt:1 Mode: X Flags: x spid4
Grant
List:: spid4 Owner: x191813 Mode: X Flg: x
Ref
: Life: 2 SPID:52 ECID: spid4 SPID: 52 ECID:
Statement
Type:
UPDATE
Line #: 1 spid4
Input
Buf:
Language
Event:
<
b>
begin
tran
update
titles
set
title
=
' deadlock battle'
' waitfor delay '
: : 5
' update authors set address='
110 north main
'</b> spid4 Requested By: spid4 ResType:LockOwner Stype:'
OR
' Mode: U SPID:53 ECID: Ec:( x195 756 ) Value: x1916eb4 Cost:( /B88) spid4 Victim Resource Owner: spid4 ResType:LockOwner Stype:'
OR
' Mode: U SPID:53 ECID: Ec:( x195 756 ) Value: x1916eb4 Cost:( /B88) spid4 spid4 <b>End deadlock search 2 ... a deadlock was found.</b> spid4 ---------------------------------- spid4 ---------------------------------- spid4 Starting deadlock search 3 spid4 Target Resource Owner: spid4 ResType:LockOwner Stype:'
OR
' Mode: U SPID:53 ECID: Ec:( x195 756 ) Value: x1916eb4 spid4 Node:1 ResType:LockOwner Stype:'
OR
' Mode: U SPID:53 ECID: Ec:( x195 756 ) Value: x1916eb4 spid4 Node:2 ResType:LockOwner Stype:'
OR
' Mode: U SPID:52 ECID: Ec:
(
x1947756
)
Value
: x1916e72 spid4 spid4
<
b>Previous victim encountered ... aborting
search
<
/
b> spid4 spid4
End
deadlock
search
3 ... a deadlock was
not
found
. spid4
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
If you had also turned on trace flag 1206 you would get numerous messages similar to the following in the error log:
Process 52 acquiring IX lock
on
PAG: 5:1:99
(
class
bit2000000 ref1
)
result
: OK Process 52 acquiring X lock
on
KEY
: 5:2121058592:1
(
b60057ff7752
)
(
class
bit2000000 ref1
)
result
: OK Process 52 releasing lock reference
on
KEY
: 5:2121058592:2
(
9002e988d824
)
Process 5 releasing
all
locks
@19116B3C
DBCC TRACEON(3205)
This flag disables hardware compression for tape drives. If the tape drive supports the compression, the BACKUP statement that backs up the database directly to tape will take advantage of hardware compression. If you must exchange tapes with another office where hardware compression is not supported, you might wish to turn on trace flag 3205 so that your tapes are compatible with the other office's hardware.
DBCC TRACEON (4013)
This flag can be used to audit connections to the server. When turned on, the SQL Server error log will contain an entry for each successful connection. The log entry will look similar to the following:
Login: johndoe BP
-
5CHSFFH2HEJ1johndoejohndoeSQL Query AnalyzerBP
-
5CHSFFH2HEJ1ODBCmaster, server process ID
(
SPID
)
: 55, kernel process ID
(
KPID
)
: 55.
As you might imagine the error log on a busy server will grow quite voluminous if this flag is turned on.
DBCC TRACEON(4022)
This flag is used to bypass procedures marked for automatic execution at startup. Note that automatically executed procedures are also skipped if SQL Server is started with minimal configuration.
DBCC TRACEON(2520)
This flag can be used to force DBCC HELP to return syntax of undocumented DBCC statements. If 2520 is not turned on, DBCC HELP will refuse to give you the syntax stating: "No help available for DBCC statement 'undocumented statement'".
DBCC TRACEON(2588)
This flag can be used to force DBCC HELP to return syntax of undocumented DBCC statements in SQL Server 2005 and 2008.
DBCC
TRACEON
(
2588
)
;
DBCC
HELP
(
'?'
)
GO
DBCC
TRACEOFF
(
2588
)
;
DBCC TRACEON(1200)
This flag can be used to get a detailed report of all locks acquired by each SQL statement on the current connection - the output can be large depending on the number of rows involved. For example, take a look at the output of the following simple query:
SELECT
a.au_id, b.royaltyper
FROM
authors a
INNER
JOIN
titleauthor b
ON
a.au_id
=
b.au_id
ORDER
BY
2
Results (abbreviated):
Process 54 acquiring
IS
lock
on
TAB: 5:53575229 []
(
class
bit0 ref1
)
result
: OK Process 54 acquiring
IS
lock
on
TAB: 5:1977058079 []
(
class
bit0 ref1
)
result
: OK Process 54 acquiring
IS
lock
on
PAG: 5:1:148
(
class
bit0 ref1
)
result
: OK Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 Process 54 acquiring
IS
lock
on
PAG: 5:1:102
(
class
bit0 ref1
)
result
: OK Process 54 releasing lock
on
PAG: 5:1:102 …
DBCC TRACEON (1807)
This flag can be used to allow the creation of database files on a network share. You must specify a valid UNC path to the share in order to create database and log files.
DBCC
TRACEON
(
3607
)
,
DBCC
TRACEON
(
3608
)
and
DBCC
TRACEON
(
3609
)
These flags can be used to skip the recovery process during SQL Server startup. 3607 does not recover any databases, 3608 recovers the master only. In addition, both trace flags cause SQL Server to skip stored procedures marked for automatic execution. Trace flag 3609 skips creation and clearing of tempdb at startup. Warning: do NOT use trace flags 3607-3609 unless you are directed to do so by Microsoft support professional.
DBCC TRACEON(4030), DBCC TRACEON(4031) and DBCC TRACEON(4032)
Flags 4030 and 4031 can be used to write the SQL statements submitted and output returned to the connections. Their functionality is similar to DBCC INPUTBUFFER and DBCC OUTPUTBUFFER respectively. The difference is that trace flags record all information in the error log for all connections. For example, we can set both of these flags globally and send the output to the error log with the following:
DBCC
TRACEON
(
-
1, 4031
)
DBCC
TRACEON
(
-
1, 4030
)
DBCC
TRACEON
(
-
1, 3605
)
Next, execute a simple statement such as:
SELECT
TOP
1
*
FROM
authors
You will see entries similar to the following in the error log:
2003
-
10
-
24 23:09:08. spid52 Printing receive buffer: 01 01 00 42 00 00 01 00 53 00 45 00 4C 00 45 00 ...B....S.E.L.E. 43 00 54 00 20 00 54 00 4F 00 50 00 20 00 31 00 C.T. .T.O.P. .1. 20 00 2A 00 20 00 46 00 52 00 4F 00 4D 00 20 00 .
*
. .F.R.O.M. . 2003
-
10
-
24 23:09:08.78 spid52 61 00 75 00 74 00 68 00 6F 00 72 00 73 00 0D 00 a.u.t.h.o.r.s... 2003
-
10
-
24 23:09:08. spid52 Printing send buffer: 04 01 01 41 00 34 01 00 81 09 00 01 01 08 00 A7 ...A.4.......... 0B 00 09 04 D0 00 34 05 61 00 75 00 5F 00 69 00 ......4.a.u._.i. 64 00 00 00 08 00 A7 28 00 09 04 D0 00 34 08 61 d......
(
.....4.a 00 75 00 5F 00 6C 00 6E 00 61 00 6D 00 65 00 00 .u._.l.n.a.m.e.. 00 08 00 A7 14 00 09 04 D0 00 34 08 61 00 75 00 ..........4.a.u. 5F 00 66 00 6E 00 61 00 6D 00 65 00 00 00 08 00 _.f.n.a.m.e..... AF 0C 00 09 04 D0 00 34 05 70 00 68 00 6F 00 6E .......4.p.h.o.n 00 65 00 00 00 09 00 A7 28 00 09 04 D0 00 34 07 .e......
(
.....4.
Flag 4032 can be used to record every SQL statement executed against the server in the error log. The log entries will look similar to the following:
Text
:
use
[pubs] ODS Event:
Language
Exec
Text
:
SELECT
TOP
1
*
FROM
authors
DBCC TRACEON(8202)
Used for replication, this flag forces SQL Server to replicate UPDATE statements as DELETE followed by an INSERT. This behavior could be useful if you wish to perform custom processing in replication stored procedures. For instance, you could write a record to the audit table every time record is updated through replication.
'Brain Trainning > DataBase' 카테고리의 다른 글
[MSSQL] SQL Server Load Generator (0) | 2012.02.07 |
---|---|
[MSSQL] Replication 에 도움되는 글들... (0) | 2012.02.06 |
[MSSQL] Replication 복제 오류 (0) | 2012.02.05 |
[MSSQL] Microsoft iSCSI Software Target 사용하기 (0) | 2012.02.02 |
[MSSQL] optimizing for adhoc workloads 옵션 (0) | 2012.02.01 |