There are several types of query processing problems, but they typically produce a stack trace in the error log (possibly coupled with a client disconnect) or degraded performance (either from an unknown cause, or because you cannot force a correct query plan). When a problem occurs, you must isolate the problem query. After this, collect the output listed below and contact Sybase Technical Support. Sybase has non-disclosure agreements with all customers, which may alleviate your concerns about providing business-sensitive data.
A full database dump – the preferred output. However, if a full database dump is not available, provide the full schema of the tables involved, stored procedure source code, and a bcp extraction of the data. Although you can resolve some issues without this information, the information you are providing is made up of approximations, and does not guarantee resolution.. By having a copy of the data, the exact data cardinality, and the data volumes available, you also have the methods the query processor uses to select data for costing algorithms, not only for problem detection, but also for testing the resolution.
ddlgen output – try to provide the full schema for all tables, indices, triggers, and procedures involved.
optdiag output (including the simulate mode, if used) – Sybase must have some notion of your data volumes, cardinalities, and how the query processor selects the data. If you influence the query processor using simulated statistics, include those as well.
Forced plan information – force the plan that runs best and collect the following information on it, as well as the same information about the plan that does not run well:
set statistics plancost on.
set statistics time on.
set option show long. The output from this can be very large. You many need trace flag 3604 enabled to run this.
set showplan on (with trace flag 526 enabled).
If you are receiving incorrect results from queries, what you provide Sybase depends on whether you are running with or without parallelism:
Without parallelism – whatever you can provide in terms of database dumps or bcp extracts of the data is most helpful. However, in addition, collect the output after enabling the following options and running the query:
set option show_code_gen on
dbcc traceon(201)
With parallelism – If your problem occurs only in parallel queries but does not occur when the server is run in serial, in addition to the ones listed above, include the following:
set option show long
set option show_parallel long
set option show_elimination long (if you do not use proper partition elimination)
