What is Trace Flag Definitions in Sybase

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

What is Trace Flag Definitions in Sybase

To activate trace flags, add them to the RUN_* script. The following example is using the 1611 and 260 trace flags. Note that there is no space between the '-T' and the traceflag, despite what is written in some documentation.

Use of these traceflags is not recommended by Sybase. Please use at your own risk.

% cd ~sybase/install
% cat RUN_BLAND
#!/bin/sh
#
# SQL Server Information:
# name: BLAND
# master device: /usr/sybase/dbf/BLAND/master.dat
# master device size: 25600
# errorlog: /usr/sybase/install/errorlog_BLAND
# interfaces: /usr/sybase
#
/usr/sybase/dataserver -d/usr/sybase/dbf/BLAND/master.dat \
-sBLAND -e/usr/sybase/install/errorlog_BLAND -i/usr/sybase \
-T1611 -T260

Trace Flags

Flag	Description
108	(Documented) To allow dynamic and host variables in create view statements in ASE 12.5 and above.
200	Displays messages about the before image of the query-tree.
201	Displays messages about the after image of the query-tree.
241	Compress all query-trees whenever the SQL dataserver is started.
260	Reduce TDS (Tabular Data Stream) overhead in stored procedures. Turn off done-in-behaviour packets. Do not use this if your application is a ct-lib based application; it'll break. Why set this on? Glad you asked, typically with a db-lib application a packet is sent back to the client for each batch executed within a stored procedure. This can be taxing in a WAN/LAN environment.
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. The trace can be used at boot time or at the session level. Keep in mind that it does not disqualify a table scan from occurring. What it will do is result in fewer datatype mismatch situations and thus the optimizer will be able to estimate the costs of SARGs and joins on columns involved in a mismatch.
299	This trace flag instructs the dataserver to not recompile a child stored procedure that inherits a temp table from a parent procedure.
302	Print information about the optimizer's index selection.
303	Display OR strategy
304	Revert special or optimizer strategy to that 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	Print information about the optimizer's join selection.
311	Display the expected IO to satisfy a query. Like statistics IO without actually executing.
317	Provide extra optimization information.
319	Reformatting strategies.
320	Turn off the join order heuristic.
324	Turn off the like optimization for ad-hoc queries using @local_variables.
326	(Only valid in ASE versions prior to 11.9.2.)  Instructs the server to use arithmetic averaging when calculating density instead of a geometric weighted average when updating statistics.  Useful for building better stats when an index has skew on the leading column.  Use only for updating the stats of a table/index with known skewed data.
602	Prints out diagnostic information for deadlock prevention.
603	Prints out diagnostic information when avoiding deadlock.
699	Turn off transaction logging for the entire SQL dataserver.
1204*	Send deadlock detection to the errorlog.
1205	Stack trace on deadlock.
1206	Disable lock promotion.
1603*	Use standard disk I/O (i.e. turn off asynchronous I/O).
1605	Start secondary engines by hand
1606	Create a debug engine start file. This allows you to start up a debug engine which can access the server's shared memory for running diagnostics. I'm not sure how useful this is in a production environment as the debugger often brings down the server. I'm not sure if Sybase have ported the debug stuff to 10/11. Like most of their debug tools it started off quite strongly but was never developed.
1608	Startup only engine 0; use dbcc engine("online") to incrementally bring up additional engines until the maximum number of configured engines.
1610*	Boot the SQL dataserver with TCP_NODELAY enabled.
1611*	If possible, pin shared memory -- check errorlog for success/failure.
1613	Set affinity of the SQL dataserver engine's onto particular CPUs -- usually pins engine 0 to processor 0, engine 1 to processor 1...
1615	SGI only: turn on recoverability to filesystem devices.
1625	Linux only: Revert to using cached filesystem I/O.  By default, ASE on Linux (11.9.2 and above) opens filesystem devices using O_SYNC, unlike other Unix based releases, which means it is safe to use filesystems devices for production systems.
2512	Prevent dbcc from checking syslogs. Useful when you are constantly getting spurious allocation errors.
3300	Display each log record that is being processed during recovery. You may wish to redirect stdout because it can be a lot of information.
3500	Disable checkpointing.
3502	Track checkpointing of databases in errorlog.
3601	Stack trace when error raised.
3604	Send dbcc output to screen.
3605	Send dbcc output to errorlog.
3607	Do not recover any database, clear behaviour start up checkpoint process.
3608	Recover master only. Do not clear tempdb or start up checkpoint process.
3609	Recover all databases. Do not clear tempdb or start up checkpoint process.
3610	Pre-System 10 behaviour: divide by zero to result in NULL instead of error - also see Q6.2.5.
3620	Do not kill infected processes.
4001	Very verbose logging of each login attempt to the errorlog. Includes tons of information.
4012	Don't spawn chkptproc.
4013	Place a record in the errorlog for each login to the dataserver.
4020	Boot without recover.
5101	Forces all I/O requests to go through engine 0. This removes the contention between processors but could create a bottleneck if engine 0 becomes busy with non-I/O tasks. For more information...5101/5102.
5102	Prevents engine 0 from running any non-affinitied tasks. For more information...5101/5102.
7103	Disable table lock promotion for text columns.
8203	Display statement and transaction locks on a deadlock error.
*	Starting with System 11 these are sp_configure'able