Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Thumbs up Unanswered: Useful Sybase ASE Topics

    Hi and welcome to the Sybase forum here at DBForums! I'm mostly an ASE guy (and those are most of the queries we get) so these are mostly ASE-related links/tips (with some exceptions).

    Some important links:
    Offical Sybase Documentation -- this is your one-stop-shop for everything ASE. If you have a question, I'm more then sure you'll find it here.

    Sybase Tools and Documentation by Rob Verschoor -- contains the Complete Sybase ASE Quick Reference Guide. Between this pocket manual (which I own) and the Sybase online documentation there isn't a question you can't find an answer to!

    Sybase.com Downloads -- Pick up an evaluation copy of ASE here!

    International Sybase Users' Group -- discounts, discussion lists, and more!

    ISUG Presentations -- Ranging from PowerBuilder to RepServer to ASE, a lot of good papers/presentations are here for you to read!

    Sybase Database Administration Tasks -- List of things that you can do to help keep your ASE instance running smooth as silk!

    Sybase FAQ -- This FAQ is courtesy of the International Sybase Users Group (ISUG).

    Ed Barlow's Sybase Shareware -- some useful tools.

    DBA Devil.com -- French site with optional tools for ASE.

    ASE on Linux FAQ -- answers some common questions for installing and running ASE on Linux.

    Dell Introduction and Installation Guide for ASE -- useful info from Dell although somewhat dated (only covers up to ASE11.9.2).

    Essential DBA Tasks (courtesy of the Complete Sybase ASE Quick Reference Guide)
    • Perform database and transaction log dumps (daily/hourly)
    • Run dbcc checkstorage for all database and follow up on any problems found (weekly)
    • Run update index statistics on user tables (weekly/as needed)
    • Regular preventative stop and restart of ASE server (they suggest monthly but in my experience, other than applying EBFs you don’t ever need to take down the server)
    • Monitor server errorlog for anomalies (daily)
    • Troubleshoot unforeseen emergences (ad-hoc read: daily )
    • Attend to (end) user / developer needs (whenever)
    Additional/Optional DBA Tasks (also courtesy of the Complete Sybase ASE Quick Reference Guide)

    Monitor growth of data volume and log space usage (daily/weekly)
    Defragment tables (reorg or (re)build clustered index) (monthly/quarterly)
    Monitor/tune server resource usage with sp_sysmon (when possible)
    Report problems to Sybase Technical Support (when necessary)
    Install EBFs or upgrades for ASE (when necessary)
    Set up the dbccdb database for dbcc checkstorage (once)
    Set up the sybsyntax database (once)

    Whew!

    Again, welcome to the site and the world of Sybase!
    Last edited by MattR; 04-18-05 at 10:54.
    Thanks,

    Matt

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Having problems with queries? Are they running slow and you don't know why?

    Chances are they are either not using indexes or you don't have the correct indexes created for the query.

    In both of these cases the command
    SET SHOWPLAN ON
    is your greatest ally!

    I also like to run
    SET NOEXEC ON
    so that the server doesn’t execute the query. Usefull when you want to benchmark UPDATE or DELETE commands and not accidentally change any data!

    (Remember to run SET NOEXEC ON last because if you run it first the SET SHOWPLAN ON statement will, of course, not be run!)

    As an example:
    Code:
    1> SET SHOWPLAN ON
    2> SET NOEXEC ON
    3> GO
    
    1> SELECT * 
    2>   FROM post, 
    3>        users      
    4>  WHERE post.userid = users.userid
    5> GO
    
    QUERY PLAN FOR STATEMENT 1 (at line 1).
    
    
        STEP 1
            The type of query is SELECT.
    
            FROM TABLE
                users
            Nested iteration.
            Table Scan.
            Forward scan.
            Positioning at start of table.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                post
            Nested iteration.
            Table Scan.
            Forward scan.
            Positioning at start of table.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    As you can see from the output we have a table scan on BOTH tables! YIKES! This will cause some problems as your tables start to fill up with information.

    To fix this problem, create an index on users.userid like this:
    Code:
    1>	CREATE INDEX userid ON users( userid )
    2>	GO
    
    1> SELECT * 
    2>   FROM post, 
    3>        users      
    4>  WHERE post.userid = users.userid
    5> GO
    
    QUERY PLAN FOR STATEMENT 1 (at line 1).
    
        STEP 1
            The type of query is SELECT.
    
            FROM TABLE
                post
            Nested iteration.
            Table Scan.
            Forward scan.
            Positioning at start of table.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                users
            Nested iteration.
            Index : userid
            Forward scan.
            Positioning by key.
            Keys are:
                userid  ASC
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    As you can see, the users table is now using the index your created. The reason why post is a table scan is because you are selecting all rows so an index won’t help you at all. A more complex WHERE clause which uses more columns from post would require an index to avoid the table scan.

    To turn off NOEXEC And SHOWPLAN simply reverse the first command:
    Code:
    1> SET NOEXEC OFF
    2> SET SHOWPLAN OFF
    3> GO
    
    QUERY PLAN FOR STATEMENT 1 (at line 1).
    
    
        STEP 1
            The type of query is SET OPTION OFF.
    
    
    QUERY PLAN FOR STATEMENT 2 (at line 2).
    
    
        STEP 1
            The type of query is SET OPTION OFF.
    
    1>
    Last edited by MattR; 06-02-01 at 22:05.
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    To enable stored procedure showplans:

    Code:
    DBCC TRACEON( 3604, 302 )
    SET SHOWPLAN ON
    SET FMTONLY ON
    GO
    
    EXEC sp_something
    GO
    Thanks,

    Matt

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    To check what exactly is executed at the server level when frontend user kicks of a report or any application module, use:

    dbcc traceon(11201,11202,11203,11204,11205,11206)

    It produces huge output in errorlog. Make sure to turn it off when the job is done.

  5. #5
    Join Date
    Aug 2016
    Posts
    1
    To check what exactly is executed at the server level when frontend user kicks of a report or any application module, use:
    dbcc traceon(11201,11202,11203,11204,11205,11206)
    It produces huge output in errorlog. Make sure to turn it off when the job is done.
    https://www.exptools4u.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •