If a limited number of queries are not performing well due to suboptimal query plans or suboptimal resource consumption, install the latest Adaptive Server 15.0.x version on your development server. If the problem still exists, submit a reproduction of the problem or diagnostics to Technical Support. To gather diagnostics:
Create a script file sql.txt containing these commands:
select @@version go select @@optgoal go sp_cacheconfig go sp_configure 'nondefault' (only if you're running Adaptive Server 15.0.2 or later) go dbcc traceon(3604) set showplan on set statistics time, io, plancost on set option show long go <your query text> go
set option show long may produce
a lot of output for complex queries.
Use isql to execute sql.txt and capture the output in a file:
isql –Usa –P yourpassword -S YOUR_SERVER_NAME –i sql.txt –o sql.out
Use the -w option of isql to format the output.
Send this information to Technical Support:
The sql.txt and sql.out files. If available, include the “fast” (sql.fast.txt) and “slow” (sql.slow.txt) query plans, and corresponding output files sql.fast.out, sql.slow.out.
DDL for the base tables and indexes, which you can generate using the ddlgen utility.
Simulate statistics output for the base tables using optdiag:
optdiag statistics simulate <table-name> -Usa -P yourpassword -S YOUR_SERVER_NAME -o <output-file>
A copy of the Adaptive Server configuration file. For Adaptive Server15.0.2, include the output of sp_configure 'nondefault’.
If the query uses views or stored procedures, then include their SQL source code obtained using defncopy or ddlgen.
The output of sp_monitorconfig ‘all' and sp_helpsort.