Performance problems with a limited number of queries

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:

  1. 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
    

    Noteset option show long may produce a lot of output for complex queries.

  2. 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.

  3. 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.