Take the following steps first:
Check whether sort-merge joins are enabled (12.0 and later).
Check whether join transitive closure is enabled (12.0 and later).
Check whether an abstract plan is in use (12.0 and later).
Determine when update statistics was last run, and the extent to which performance improved as a result.
If a stored procedure problem appeared following an upgrade, drop and recreate the procedure to see if performance improves.
For a new query, verify there are no datatype mismatches. Mismatches that prevent a query from using an index are commonly seen:
when you specify a search clause or a stored procedure parameter using a different datatype than the column, for example
where int_col = @smallint_param
when you join columns having different datatypes, for example
where tableA.datetime_col = tableB.smalldatetime_col
Check that the datatype the query uses in join clauses and search arguments matches the column datatype. For details, see “Datatype Mismatches and Query Optimization” in the Performance and Tuning Guide.
Proceed with analysis if these steps do not help.
If one or more of the following is true, your problem may be related to the optimizer:
A new query is not using the expected indexes
Forcing an index or join order (using forceplan) improves performance
You experience drastic performance differences between Adaptive Server versions
