Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    25

    Unanswered: Server I/O increases

    Suddenly execution of one of the proc leads to high increases in the server IO
    CPU states: 0.0% idle, 96.6% user, 3.4% kernel, 0.0% iowait, 0.0% swap

    Due to which the servers gets very slow and no activity can be performed on it.

    What could be the reason of this drastic change?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Probably due to table scan
    Check for missing index

  3. #3
    Join Date
    Jan 2010
    Posts
    25
    pdreyer,

    The checked the plan, but could not see any tables having a table scan and not using index.
    Can you please have a look to this query plan and suggest me if there is any thing of interest.


    Code:
    Auxiliary scan descriptors required: 1
    
    
        STEP 1
            The type of query is SELECT.
    
            FROM TABLE
                kplus..Branches
                branches
            Nested iteration.
            Using Clustered Index.
            Index : BranchesIdx1
            Forward scan.
            Positioning at index start.
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..Pairs
                pair
            Nested iteration.
            Using Clustered Index.
            Index : PairsIdx1
            Forward scan.
            Positioning at index start.
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..Currencies
                ccy1
            Nested iteration.
            Using Clustered Index.
            Index : CurrenciesIdx1
            Forward scan.
            Positioning by key.
            Keys are:
                Currencies_Id  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..Currencies
                ccy2
            Nested iteration.
            Using Clustered Index.
            Index : CurrenciesIdx1
            Forward scan.
            Positioning by key.
            Keys are:
                Currencies_Id  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..ForwardDeals
                deal
            Nested iteration.
            Index : ForwardDealsIdx2
            Forward scan.
            Positioning at index start.
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..Folders
                folders
            Nested iteration.
            Using Clustered Index.
            Index : FoldersIdx1
            Forward scan.
            Positioning by key.
            Keys are:
                Folders_Id  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..Portfolios
                portfolios
            Nested iteration.
            Using Clustered Index.
            Index : PortfoliosIdx1
            Forward scan.
            Positioning by key.
            Keys are:
                Portfolios_Id  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                kplus..Cpty
                cpty
            Nested iteration.
            Using Clustered Index.
            Index : CptyIdx1
            Forward scan.
            Positioning by key.
            Keys are:
                Cpty_Id  ASC
    
            Run subquery 1 (at nesting level 1).
    
            Run subquery 2 (at nesting level 1).
    
            Run subquery 3 (at nesting level 1).
    
            Run subquery 4 (at nesting level 1).
    
            Run subquery 5 (at nesting level 1).
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
    NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 5.
    
      QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 59).
    
        Correlated Subquery.
        Subquery under an EXPRESSION predicate.
    
    
        STEP 1
            The type of query is SELECT.
            Evaluate Ungrouped ONCE AGGREGATE.
    
            FROM TABLE
                kplus..ForwardDeals
                subswap
            Nested iteration.
            Index : ForwardDealsIdx4
            Forward scan.
            Positioning by key.
            Keys are:
                BlockNumber  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                Kustom..forwarddealsFT
                subcfp
            Nested iteration.
            Using Clustered Index.
            Index : forwarddea_DealId_1725276481
            Forward scan.
            Positioning by key.
            Keys are:
                DealId  ASC
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
      END OF QUERY PLAN FOR SUBQUERY 1.
    
    
      QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 65).
    
        Correlated Subquery.
        Subquery under an EXPRESSION predicate.
    
    
        STEP 1
            The type of query is SELECT.
            Evaluate Ungrouped ONCE AGGREGATE.
    
            FROM TABLE
                Kustom..forwarddealsFT
            Nested iteration.
            Using Clustered Index.
            Index : forwarddea_DealId_1725276481
            Forward scan.
            Positioning by key.
            Keys are:
                DealId  ASC
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
      END OF QUERY PLAN FOR SUBQUERY 2.
    
    
      QUERY PLAN FOR SUBQUERY 3 (at nesting level 1 and at line 110).
    
        Correlated Subquery.
        Subquery under an EXPRESSION predicate.
    
    
        STEP 1
            The type of query is SELECT.
            Evaluate Ungrouped ONCE AGGREGATE.
    
            FROM TABLE
                kplus..ForwardDeals
                subswap
            Nested iteration.
            Index : ForwardDealsIdx4
            Forward scan.
            Positioning by key.
            Keys are:
                BlockNumber  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
            FROM TABLE
                Kustom..forwarddealsFT
                subcfp
            Nested iteration.
            Using Clustered Index.
            Index : forwarddea_DealId_1725276481
            Forward scan.
            Positioning by key.
            Keys are:
                DealId  ASC
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
      END OF QUERY PLAN FOR SUBQUERY 3.
    
    
      QUERY PLAN FOR SUBQUERY 4 (at nesting level 1 and at line 116).
    
        Correlated Subquery.
        Subquery under an EXPRESSION predicate.
    
    
        STEP 1
            The type of query is SELECT.
            Evaluate Ungrouped ONCE AGGREGATE.
    
            FROM TABLE
                Kustom..forwarddealsFT
            Nested iteration.
            Using Clustered Index.
            Index : forwarddea_DealId_1725276481
            Forward scan.
            Positioning by key.
            Keys are:
                DealId  ASC
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
      END OF QUERY PLAN FOR SUBQUERY 4.
    
    
      QUERY PLAN FOR SUBQUERY 5 (at nesting level 1 and at line 123).
    
        Correlated Subquery.
        Subquery under an EXPRESSION predicate.
    
    
        STEP 1
            The type of query is SELECT.
            Evaluate Ungrouped ONCE AGGREGATE.
    
            FROM TABLE
                kplus..ForwardDeals
                orig
            Nested iteration.
            Index : ForwardDealsIdx4
            Forward scan.
            Positioning by key.
            Keys are:
                BlockNumber  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
    
        STEP 2
            The type of query is SELECT.
            Evaluate Ungrouped ONCE AGGREGATE.
    
            FROM TABLE
                Kustom..custodianandconfirmation
                cac
            Nested iteration.
            Index : custodianandconfirmationIdx1
            Forward scan.
            Positioning by key.
            Index contains all needed columns. Base table will not be read.
            Keys are:
                DealId  ASC
                DealType  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
    
            FROM TABLE
                kplus..ForwardDeals
                subdeal
            Nested iteration.
            Index : ForwardDealsIdx4
            Forward scan.
            Positioning by key.
            Keys are:
                BlockNumber  ASC
            Using I/O Size 2 Kbytes for index leaf pages.
            With LRU Buffer Replacement Strategy for index leaf pages.
            Using I/O Size 2 Kbytes for data pages.
            With LRU Buffer Replacement Strategy for data pages.
       STEP 1
            The type of query is SELECT.

  4. #4
    Join Date
    Aug 2007
    Location
    Johannesburg, South Africa
    Posts
    11
    What else has changed?
    Are your statistics and query plan for the proc up to date?
    Have you looked at the sysmons, did that maybe show anything?
    What version of ASE is running?

  5. #5
    Join Date
    Jan 2010
    Posts
    25
    I had run update statistic and also used sp_recompile.
    But the attempts were in vein. No changes in execution speed.
    Version is sybase 12.0

  6. #6
    Join Date
    Aug 2007
    Location
    Johannesburg, South Africa
    Posts
    11
    Quote Originally Posted by alirulez999 View Post
    I had run update statistic and also used sp_recompile.
    But the attempts were in vein. No changes in execution speed.
    Version is sybase 12.0
    Thanks.
    Interesting. Did you maybe have a sysmon or 2 from when it was busy like that?
    Also, are there any applications on the same host, or maybe the app that uses the same engine, maybe other processes are taking the CPU time?

  7. #7
    Join Date
    Jan 2010
    Posts
    25
    there are other applications also.
    But I had monitered the perfomance couple of times. As soon as the procs started executing the CPU usage rises to 100% and the second execution is completed its falls to 0%.

    Will check the reports from sysmon now..
    Thanks

Posting Permissions

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