BI 4.2 CMS indexes 100% Fragmented

Hi there,

Whilst doing some checks around issues we were having we have noticed that a large percentage of the indexes on the CMS database (MS SQL) were at 99 to 100% fragmented, does anyone have any advice on best practices with regards to these indexes?
I’m not a DBA and our DBA is new to SAP BI and this is the first time we have looked in this area, we are assuming we can just start going through them to rebuild the indexes but as far as I can see SAP will only help with a consultant.
I can’t find any information/advice at all on the interweb, however it may well be that I’m not searching for the right thing.

Can anyone help or point me in the right direction, any assistance at all would be much appreciated?



What did you do to find out about this fragmentation? I would be interested in looking at our database.

I did a brief search on the SAP Support site but it didn’t turn up much.

To be fair it was a bit of mystical DBA jiggery pokery but one of the queries they used was this:

Where the DB_ID is the name of your CMS database

Looks like I don’t have permission to run that against our database. I’ll have to have our DBA run it for me.

A couple months ago I had our DBA rebuild all the indexes on all tables in the CMS and Audit databases. It did not cause any problems for us. Ours were highly fragmented also but the tables are so small in terms of row count I don’t know if it made much difference in performance.

Thanks for the Info Richard, when you said your tables were small, what sort of range are they in?

Our 2 main tables I believe are at about 700k rows give or take.

In our production environment the table in the CMS database with the largest row count is CMS_InfoObjects7 with 155434 rows.

1 Like