Skip to Content

We have experienced and issue with SAP IQ performance of sp_iqcheckdb ‘verify database’ after completing a database restore. After completing a full restore of IQ we have noticed that the sp_iqcheckdb ‘verify database’ command started utilizing all CPUs on the IQ host (18). `This caused severe performance issues for IQ Prior to the full database restore we did not see this issue. Our concern is that the restore may have changed  and/or not loaded certain configuration parameters, please advise

Regards Ade

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Saroj Bagai

    What is the size of the database? Could you please post your complete sp_iqcheckdb ‘verify database’ command along with .cfg file?  Are you running any concurrent jobs in the IQ server at the time of dbcc?  What else is running on this host machine?  What is the exact version of IQ server?

    (0) 
    1. Adegoke Bakare Post author

      Hi Saroj,

      Thanks

      hope this help:

      Sp_iqstatus output :-

      SAP IQ (TM)        Copyright (c) 1992-2014 by SAP AG or an SAP affiliate company. All rights reserved.

      Version:              16.0.0.809/150327/P/sp08.30/Enterprise Linux64 – x86_64 – 2.6.18-194.el5/64bit/2015-03-27 16:04:28

      Time Now:        2015-09-16 14:17:57.801

      Build Time:        2015-03-27 16:04:28

      File Format:      23 on 03/18/1999

      Server mode:  IQ Server

      Catalog Format:              2

      Stored Procedure Revision:      1

      Page Size:          131072/8192blksz/16bpp

      Number of Main DB Files:          5

      Main Store Out Of Space:            N

      Number of Cache Dbspace Files:            0

      Number of Shared Temp DB Files:          0

      Shared Temp Store Out Of Space:          N

      Number of Local Temp DB Files:              4

      Local Temp Store Out Of Space:              N

      DB Blocks: 1-1280000    IQ_SYSTEM_MAIN

      DB Blocks: 5227200-31011519    bo_main_01

      DB Blocks: 38681280-64465599  bo_main_02

      DB Blocks: 72135360-97919679  bo_main_03

      DB Blocks: 105589440-131373759            bo_main_04

      Local Temp Blocks: 1-5120000  IQ_SYSTEM_TEMP

      Local Temp Blocks: 8363520-13483519  temp02

      Local Temp Blocks: 16727040-21847039 temp03

      Local Temp Blocks: 25090560-30210559 temp04

      Create Time:    2015-04-22 09:40:01.622

      Update Time:  2015-09-16 14:17:57.444

      Main IQ Buffers:              310767, 39000Mb

      Temporary IQ Buffers:  310767, 39000Mb

      Main IQ Blocks Used:    57970893 of 104391680, 55%=442Gb, Max Block#: 122614844

      Cache Dbspace IQ Blocks Used:                0 of 0, 0%=0Mb, Max Block#: 0

      Shared Temporary IQ Blocks Used:        0 of 0, 0%=0Mb, Max Block#: 0

      Local Temporary IQ Blocks Used:            9596 of 20428800, 0%=74Mb, Max Block#: 26574364

      Main Reserved Blocks Available:            25600 of 25600, 100%=200Mb

      Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb

      Local Temporary Reserved Blocks Available:    51200 of 51200, 100%=400Mb

      IQ Dynamic Memory:    Current: 78851mb, Max: 81368mb

      Main IQ Buffers:              Used: 310761, Locked: 1770

      Temporary IQ Buffers:  Used: 21676, Locked: 744

      Main IQ I/O:      I: L1057896309/P51769775 O: C3137428/D13173573/P12553261 D:2672139 C:46.4

      Temporary IQ I/O:          I: L1226897108/P8844977 O: C182095954/D195985928/P15875344 D:182039132 C:30.6

      Other Versions:              8 = 26Gb

      Active Txn Versions:    0 = C:0Mb/D:0Mb

      Last Full Backup ID:        55392960

      Last Full Backup Time:  2015-09-14 10:31:34

      Last Backup ID: 56124115

      Last Backup Type:          ISF

      Last Backup Time:          2015-09-16 12:00:03

      DB Updated:    1

      Blocks in next ISF Backup:          1262345 Blocks: =9862Mb

      Blocks in next ISI Backup:            694231 Blocks: =5423Mb

      IQ large memory space:              39000Mb

      IQ large memory flexible percentage:  50

      IQ large memory flexible used:              0Mb

      IQ large memory inflexible percentage:              90

      IQ large memory inflexible used:          38889Mb

      IQ large memory anti-starvation percentage:    50

      DB File Encryption Status:          OFF

      Regards

      Ade

      (0) 
  2. Adegoke Bakare Post author

    Hi Saroj Here is the SP_iqstatus output Could this be helpful

    SAP IQ (TM)        Copyright (c) 1992-2014 by SAP AG or an SAP affiliate company. All rights reserved.

    Version:              16.0.0.809/150327/P/sp08.30/Enterprise Linux64 – x86_64 – 2.6.18-194.el5/64bit/2015-03-27 16:04:28

    Time Now:        2015-09-16 14:17:57.801

    Build Time:        2015-03-27 16:04:28

    File Format:      23 on 03/18/1999

    Server mode:  IQ Server

    Catalog Format:              2

    Stored Procedure Revision:      1

    Page Size:          131072/8192blksz/16bpp

    Number of Main DB Files:          5

    Main Store Out Of Space:            N

    Number of Cache Dbspace Files:            0

    Number of Shared Temp DB Files:          0

    Shared Temp Store Out Of Space:          N

    Number of Local Temp DB Files:              4

    Local Temp Store Out Of Space:              N

    DB Blocks: 1-1280000    IQ_SYSTEM_MAIN

    DB Blocks: 5227200-31011519    bo_main_01

    DB Blocks: 38681280-64465599  bo_main_02

    DB Blocks: 72135360-97919679  bo_main_03

    DB Blocks: 105589440-131373759            bo_main_04

    Local Temp Blocks: 1-5120000  IQ_SYSTEM_TEMP

    Local Temp Blocks: 8363520-13483519  temp02

    Local Temp Blocks: 16727040-21847039 temp03

    Local Temp Blocks: 25090560-30210559 temp04

    Create Time:    2015-04-22 09:40:01.622

    Update Time:  2015-09-16 14:17:57.444

    Main IQ Buffers:              310767, 39000Mb

    Temporary IQ Buffers:  310767, 39000Mb

    Main IQ Blocks Used:    57970893 of 104391680, 55%=442Gb, Max Block#: 122614844

    Cache Dbspace IQ Blocks Used:                0 of 0, 0%=0Mb, Max Block#: 0

    Shared Temporary IQ Blocks Used:        0 of 0, 0%=0Mb, Max Block#: 0

    Local Temporary IQ Blocks Used:            9596 of 20428800, 0%=74Mb, Max Block#: 26574364

    Main Reserved Blocks Available:            25600 of 25600, 100%=200Mb

    Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb

    Local Temporary Reserved Blocks Available:    51200 of 51200, 100%=400Mb

    IQ Dynamic Memory:    Current: 78851mb, Max: 81368mb

    Main IQ Buffers:              Used: 310761, Locked: 1770

    Temporary IQ Buffers:  Used: 21676, Locked: 744

    Main IQ I/O:      I: L1057896309/P51769775 O: C3137428/D13173573/P12553261 D:2672139 C:46.4

    Temporary IQ I/O:          I: L1226897108/P8844977 O: C182095954/D195985928/P15875344 D:182039132 C:30.6

    Other Versions:              8 = 26Gb

    Active Txn Versions:    0 = C:0Mb/D:0Mb

    Last Full Backup ID:        55392960

    Last Full Backup Time:  2015-09-14 10:31:34

    Last Backup ID: 56124115

    Last Backup Type:          ISF

    Last Backup Time:          2015-09-16 12:00:03

    DB Updated:    1

    Blocks in next ISF Backup:          1262345 Blocks: =9862Mb

    Blocks in next ISI Backup:            694231 Blocks: =5423Mb

    IQ large memory space:              39000Mb

    IQ large memory flexible percentage:  50

    IQ large memory flexible used:              0Mb

    IQ large memory inflexible percentage:              90

    IQ large memory inflexible used:          38889Mb

    IQ large memory anti-starvation percentage:    50

    DB File Encryption Status:          OFF

    Best Regards Ade

    (0) 
    1. Saroj Bagai

      we still need to look at complete sp_iqcheckdb  verify database command  along with the .cfg.  Are you running any concurrent jobs in the IQ server at the time of the dbcc? And what else is running on IQ host machine.  iostat and vmstat output from the time of dbcc performance issue will be helpful too

      #iostat -t -d -x 5 20

      (0) 

Leave a Reply