regular indexes rebuild at SQL-Server 2008 and higher necessary?

[Former Member]

Dear SAP-Community

Iu2019m wondering if itu2019s necessary to do a regular indexes rebuild on SQL-Server databases. My problem is that I haven't found anything in the ADM520 nor an official documentation nor a SAP note which would advice me.

Is there any SAP recommendation to do it regularly?

If yes how does the command look like?

Is there a report or command to check if there are defragmented indexes?

Does someone of you have an official link to SAP documents regarding this topic?

Thanks in advance!

Cheers, Manuel

[xymanuel]

Hi Manuel,

i don't know any official SAP Documentation about this.

But i do the following:

First, find long running SQL Statements in ST04 -> Performance -> SQL Statements

Maybe there are slow statements, regading they are using the search indexes.

This could be a hint for fragmented Indexes.

Second, i search for the top growing tables in DB02. Maybe i'll find the same tables there, that i find in ST04.

Then i check the Fragmentation of the table

USE SID
go
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'SID');
SET @object_id = OBJECT_ID(N'SID.sid.TABLENAME');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED');
END;

MS says, if a clustered index is more fragmented than 30%, rebuild the clustered index. In my opinion in SAP landscape this is only usefull, if you have slow statements.

But just try it.

Next step will be the rebuild of the index.

Now it depends on which SQL Server Version and Netweaver. In older releases, your only option is to rebuild the index full blocking. That means, as long as the index is rebuilding, your system will not be able to update or read from the table.

Don't forget this in productive systems! (To avoid this, search for "online index rebuild", which can be activated in st04 with sql 2008 and netweaver hmm 7.0? not exactly sure which release).

To rebuild an index, just go to SE14 -> Enter Table Name -> Choose Edit (Tables) -> Indexes - > Choose the primary Index (always 0) ->

Now, depending on the size of the index choose dialog or background (just test it in development or sandbox, i think <1GB is good for dialog).

Now choose "activate and alter database".

The clustered index, and all secondary indexes will be first dropped and recreated now!!

(Warning do not use this one level up in SE14 with the table itself. Otherwise it will be empty !)

Also be warned, your transaction log will be filled up with exactily the size, your new clusterd index will be.

So be cautious when rebuilding large indexes (bigger than your TA Logfile).

After rebuilding, rerun the SQL Statement. The fragmentation level is 0.

We sincerly used this for rebuilding large tables like NAST CE10100 SRRELROLES RESB VBFA STXH JCDS CKIS. This saved us ~ 30GB useless used space and give us better performace for these tables.

Compare the space useage of a table in DB02-> Single table analysis -> reserved space / dataspace / indexspace ,

before and after index rebuild.

Kind regards

Manuel