Highlighted
Valued Contributor.. Valued Contributor..
Valued Contributor..
217 views

HPRM 8.11 - SQL Timeouts and missing "non-clustered indexes"

Jump to solution

Hi all,

We have come across an issue with some missing indexes on our SQL database which are causing timeouts with indexing (namely Word indexing).  It appears that a number of non clustered indexes which were present in our 7.1 database were removed/deleted during the schema upgrade and not recreated.

Is this correct behaviour?  And if not, would we be able to manually recreate them? 

The list of missing indexes are:

TSFPWORD - fpwIndexWordUri

TSFPWORD - fpwPlanUri

TSLOCWORDA - IwaIndexWordUri

TSLOCWORDA - IwaLocAddressUri

TSRECWORD - rwIndexWordUri

TSRECWORD - rwRecordUri

TSSCHWORD - scwIndexWordUri

TSSCHWORD - scwScheduleUri

TSTMWORD - tmwIndexWordUri

TSTMWORD - tmwKeywordUri

We are fairly certain that these were dropped as part of the schema upgrade as a TEST dataset which didn't have any indexes dropped/repaired when upgrading the schema still have these indexes available and worrking.

Any help or advice would be greatfully recieved.

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

You are correct - these index es should have been recreated as part of the schema upgrade. It is possible that if you ran a repair there was a problem and the indexes where not recreated - check the schema repair log to see if there was an issue. 

The indexes that are missing are used during the Word Index and you should manually recreate the indexes - they are non-unique, non-clustered indexes - each with two columns values, like the example below -  for the TSFPWORD table:

TSFPWORD - fpwIndexWordUri - nonclustered; fpwIndexWordUri, fpwPlanUri
TSFPWORD - fpwPlanUri -  nonclustered; fpwPlanUri, fpwIndexWordUri

(Please state the version of TRIM/RM/CM that you are using in all posts)

**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of Micro Focus

View solution in original post

0 Likes
3 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

You are correct - these index es should have been recreated as part of the schema upgrade. It is possible that if you ran a repair there was a problem and the indexes where not recreated - check the schema repair log to see if there was an issue. 

The indexes that are missing are used during the Word Index and you should manually recreate the indexes - they are non-unique, non-clustered indexes - each with two columns values, like the example below -  for the TSFPWORD table:

TSFPWORD - fpwIndexWordUri - nonclustered; fpwIndexWordUri, fpwPlanUri
TSFPWORD - fpwPlanUri -  nonclustered; fpwPlanUri, fpwIndexWordUri

(Please state the version of TRIM/RM/CM that you are using in all posts)

**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of Micro Focus

View solution in original post

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

You may have run a reindex of the word indexes via the Enterprise Studio while checking the option to drop and recreate the indexes.

In some cases they aren't recreated and a schema repair is required.



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

INFORMOTION.com.au
0 Likes
Highlighted
Valued Contributor.. Valued Contributor..
Valued Contributor..

Hi All,

Just to provide an update to this.  We manually recreated the missing "non-clustered indexes" listed in the original post and have noticed substantially improved results:

We have reduced the running time for queries against the TSRECWORD table (31,980,511 Rows) from 83,000+ms to 56ms, and have reduced the logical reads from 4.5 million+ to 235.

Thankyou for the advice.

Ben

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.