存档
转自：http://blog.csdn.net/obuntu/archive/2010/06/08/5655494.aspx
谢谢obuntu
| flag | Trace Flag Description (underlined are sp_configure’able) |
| -1 | Sets trace flags for all connections. Used only with DBCC TRACEON and TRACEOFF. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems. |
| 105 | SQL Server 6.5 you can use maximum 16 tables or subqueries in a single select statement. There is no documented way, to avoid this restriction, but you can use undocumented trace flag 105 for this purpose. |
| 106 | Disables line number information for syntax errors. |
| 107 | Interprets numbers with a decimal point as float instead of decimal. |
| 110 | Turns off ANSI select characteristics. |
| 204 | A backward compatibility switch that enables non-ansi standard behavior. E.g. previously SQL server ignored trailing blanks in the like statement and allowed queries that contained aggregated functions to have items in the group by clause that were not in the select list. |
| 205 | Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat. |
| 206 | Provides backward compatibility for the setuser statement. |
| 208 | SET QUOTED IDENTIFIER ON. |
| 237 | Tells SQL Server to use correlated sub-queries in Non-ANSI standard backward compatibility mode. |
| 242 | Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired. |
| 243 | The behavior of SQL Server is now more consistent because null ability checks are made at run time and a null ability violation results in the command terminating and the batch or transaction process continuing. |
| 244 | Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables. |
| 246 | Derived or NULL columns must be explicitly named in a select….INTO or create view statement when not done they raise an error. This flag avoids that. |
| 253 | Prevents ad-hoc query plans to stay in cache. |
| 257 | Will invoke a print algorithm on the XML output before returning it to make the XML result more readable. |
从多个渠道收集来的sybase trace flag
With dbcc traceflags you can do many different things, like change the behaviour of the server, see why a certain query plan is choosen or fine-tune the optimizer.
This page shows you how to use them and provides a list of known traceflags.
You can set a traceflag in two ways
1.At boottime
Usually a server is booted through the runserver file. In this file (default location is $SYBASE/$SYBASE_ASE/install) you can add a traceflag by specifying a -T flag and one or more comma separated values.
2.At run time
When you are logged in to ASE you can set a traceflag with the dbcc traceon command. Example:
dbcc traceon(3604)
You can specify multiple values, comma separated.
To turn a traceflag off, use traceoff
dbcc traceoff(3604)
Which traceflags are active
To see which traceflags are active for your own session use the dbcc traceflags command
dbcc traceflags
To see the traceflags of other sessions as well
dbcc traceflags(2)
List of known traceflags
Below you will find a list of currently known traceflags. But beware of the following:
WARNING
? Most traceflags are undocumented and not supported by Sybase, use at your own risk. Certain traceflags can severely harm your databases.
? Some traceflags are obsolete and are replaced by configuration parameters.
? Certain traceflags should be used when you start the server, others are meant for a specific session, when known this is indicated in the list.
? Experiment first on a test server, use only on production systems when either the traceflag is documented by Sybase or when you have consulted Sybase Technical Support.
List of dbcc traceflags
-----------------------------------------------------------------------------------
100 Display a parse tree for each command
108 Allow dynamic and host variables in create view statements
116 Print the text of the abstract query plan. Supported by Sybase, see documentation
200 Display messages about the before image of the query-tree
201 Display messages about the after image of the query tree
208 Show types of locks taken
217 Display a warning message for using the T-SQL extension of queries with grouped aggregates and columns in the select list which are not in the GROUP BY clause.
241 Compress all query-trees when ASE is started
243 Do not expand select * to column names when creating a compiled object
244 When set, the maximum length of the returned value of str_replace() is 16384, otherwise it is 255
260 Reduce TDS (Tabular Data Stream) overhead in stored procedures. Turn off done-in-behaviour packets. See also Send doneinproc tokens.
291 Changes the hierarchy and casting of datatypes to pre-11.5.1 behaviour. There was an issue is some very rare cases where a wrong result could occur, but that's been cleared up in 11.9.2 and above.
292 Never send doneinproc tokens. See also Send doneinproc tokens.
298 Display an error message when a query uses a correlated variable as an inner or outer member of an outer join.
299 Do not recompile a stored procedure that inherits a temp table from a parent procedure.
302 Print trace information on index selection, supported by Sybase, see documentation
303 Display optimizer OR strategy
304 Revert special "or" optimizer strategy to the strategy used in pre-System 11 (this traceflag resolved several bug issues in System 11, most of these bugs are fixed in ASE 11.0.3.2)
310 Show the plan as choosen by the optimizer, as well as I/O costs. Supported by Sybase see documentation
311 Display optimizers expected I/O cost
317 Show all considered plans. Supported by Sybase, see documentation
319 Display optimizer reformatting strategy