Adaptive Server 15.0 offers a greatly improved query processing environment. However, if query plans or query performance are not what you expect, here are some ways to isolate the problem:
When using different optimization goals, make sure no cached plans are used: changing the session-level or server-wide optimization goal does not recompile cached plans. For stored procedures, either execute them with recompile, or run sp_recompile on one of the tables being accessed. For batches, make sure the statement cache is disabled by running set statement_cache off first.
To ensure that a stored procedure is always optimized with a particular optimization goal, regardless of server-wide or session-level settings, use set plan optgoal allrows_xxx as the first statement in the stored procedure. This works only on Adaptive Server 15.0.2 ESD #2 or later.
If your SQL code from Adaptive Server 12.x contains explicitly forced join orders (with set forceplan), reexamine the join orders before upgrading to Adaptive Server 15.0. Such constructs may prevent you from benefiting fully from the capabilities of Adaptive Server 15.0.
With Adaptive Server 15.0.1 ESD #2 or later, you can enable two trace flags:
Trace flag15307 nullifies the effect of any set forceplan statements during query plan compilation.
Trace flag 15308 nullifies any explicit forcing of indexes, prefetch, parallelism, or buffer replacement strategies
You can set both these trace flags (15307 and 15308) during server start-up, or dynamically enable them using dbcc traceon. The effects of both are server-wide and neither affects any query plan properties defined by abstract query plans.
If your system consumes too much space in tempdb, use the Monitoring and Diagnostic Access tables to see if any particular session consumes a lot of space in a worktable. Enable the monitoring tables and run the following query:
select SPID, DBName, ObjectName, PartitionSize from master..monProcessObject where DBID = tempdb_id(SPID) order by SPID
Look for sessions that have a large value for PartitionSize. Worktables have an ObjectName of “temp worktable.” Find the corresponding SQL statement for the sessions by issuing queries to monProcessSQLText or monProcessStatement in the master database.
To stop sessions from filling up tempdb, and thus affecting other sessions also requiring tempdb space, create a resource limit of type “tempdb_space.” You may also create multiple temporary databases and assign them to specific users. To check the tempdb space used by a single session, use:
select pssinfo(spid|0,'tempdb_pages')
Enable the statement cache and literal autoparameterization settings while running large numbers of identical or similar client-generated SQL queries in Adaptive Serer 15.0.1 or later. This does not include stored procedures, or execute-immediate query forms, and the queries may differ only in their search parameters. Overall performance is improved by significantly reducing the time and resources spent on query optimization.
When the statement cache is enabled, a query’s plan is cached so you need not compile an identical query, and thereby save time and resources. The statement cache is enabled server-wide with the configuration parameter statement cache size. At the session level, disable the statement cache with set statement_cache off.
Literal autoparameterization is enabled server-wide with the configuration parameter enable literal autoparam, and at the session level with set literal_autoparam on. enable literal autoparam applies only when the statement cache is enabled. With literal autoparameterization enabled, caching is extended to almost-identical queries that differ only in a constant value. For example, these two queries are not considered identical:
select CustName from Customers where CustID = 123 select CustName from Customers where CustID = 456
However, they are likely to generate the same query plan. Enabling literal autoparameterization has the effect that the statement cache factors out the constant value in the where clause and caches a plan for all queries that look like this:
select CustName from Customers where CustID= <integer-constant>
Various optimization-related settings from 12.x are no longer relevant in Adaptive Server 15.0. Although the following commands still exist in Adaptive Server 15.0, they are relevant only in compatibility mode, and do not have any effect on the Adaptive Server 15.0 optimization process:
set sort_merge – this has been replaced by set merge_join, optimization goals and the configuration parameter enable merge join.
set jtc – join transitive closure is always enabled in Adaptive Server 15.0.
set table count – this setting is no longer relevant in Adaptive Server 15.0.
enable sort-merge join and JTC – this configuration parameter has been replaced by optimization goals and by the configuration parameter enable merge join.
Start-up trace flags 334 and 384 – these flags enabled merge joins and JTC and are no longer relevant.
Sybase recommends that you remove any references to these features from your applications.
