Performance Issue

[Former Member]

Hi All

After completing a full restore of IQ, we noticed severe performance issue for the SAP IQ

Prior to the full database restore, we did not have this issue

Can someone please advise on how to improve the performance

Best Regards

Ade

[Gisung]

Are there any differences of option list between original system and restored system?

Please post the output of sp_iqcheckoptions.

==

Gi-Sung Jang

[former_member353243]

you may want to also pull off the execution HTML query plans for the LOADs to see where the time was spent.  Also where did you restore the database back to, a different box and different disk subsystem or the same place?  Why did you need to do the restore in the first place?

[c_baker]

Can you provide more details about the version, .cfg, dbspaces, underlying disk, etc., first?

How has performance suffered - loads, queries, both?

Chris

[Former Member]

Does 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

Performance suffered: Loads

Many Thanks in Advance

Ade

[c_baker]

How many cores are you running?

What is the underlying disk?

There are 8 other versions waiting for a 'commit'. Are these always there? 

How are you loading?

Chris

[c_baker]

What is the physical memory on the box?

Are you running in a VM?