Reindex Table in MS SQL Server 2005

[Former Member]

Hi gurus,

Is it possible or effective to reindex all SAP tables in SQL Server 2005?

I've seen lot of example of re-indexing tables in SQL Server, but not in SAP environment (Since SAP Tables are really huge, around 77,000 tables).

Should I defrag my database?

I've executed DBCC SHOWCONTIG and found that some tables have logical defragment more than 70%.

Is there any tcode in SAP to do re-index task?

Thanks before gurus.

[Former Member · Accepted solution]

Hi Bobby,

for systems running on SQL Server, SAP does not generally recommend to defragment the database. I know this might be a normal maintenance task for other RDBMS but for SQL Server it is not necessary to do this. SAP even DOES NOT recommend to generally carry out reorganization jobs.

Do you encounter any performance problems or do you urgently need the fragmented space back? If the answer to both is no, you can simply leave it like it is and do not defragment/reindex/reorganize it.

If you urgently need space, it is a better idea to add another datafile or to increase the size of existing datafiles.

If you encounter a performance problem I would recommend to first look for other possible root causes. As described in SAP note #159316, defragmentation is only in very very rare cases (almost never) the reason for a performance problem.

best regards,

beate

[ken_halvorsen2]

Hello Bobby

Are you experiencing any problems that makes you believe you need to re-org?

If you believe you're having a performance problem, you should re-run the Statistics on some of the tables used.

I once had an ABAPer tell me that there was a serious problem and the system needed to be re-organized, but after running the Statistics on the specific tables in his program, his performance problem cleared up (and the ABAPer was much quieter after that, if you know what I mean).

Ken

[Former Member]

In Note 159316 there is mention that reindexing is far less advantageous in an R/3 environment than in a BW one. I'm in a BW only environment. Sometimes the DB reindexing (48,000ish) tables completes in 5 hours. Other times it runs for 20+ hours and gets canceled as our window closes.

Anybody have a feel for putting the DB into single-user mode to do the reindexing? Stopping the SAP app firet? Stuff like that. We'd like to get to the point where our database maintenance succeeds more than it fails.

Even if we run DB mainteance less often, the long time it takes to run is still problem. We've not had luck identifying the tasks that must be interferring with the reindexing job. Ideas?

Thanks.

Bob

[Former Member]

Hi Bob,

since you did not mention how you rebuild your tables and what SQL Server Version you are on I have one idea (2005/2008) that might speed up the process. Rebuilding a table is a pretty CPU consuming process. With that in mind and assuming that you use a script you might want to play around with the MAXDOP option. It could look like that:

alter table <myTab> rebuild with (maxdop = 4);

You could use 50% of all cores or even all cores if nothing else is running. The default (instance wide) MAXDOP option is usually set to 1 if not specified different. The MAXDOP option in the alter table statement overrides the default for this specific operation. Please check the alter table statement in Books Online to get an idea of what is possible.

Sven

[MPGraziano]

Sven

Are you saying that SAP does NOT recommend re-org or defrag of any kind for SAP ECC 6.0 running on SQL 2005 or 2008?

Thanks,

Maria

[Former Member]

Basically yes. Please read Beates post above and check the mentioned note.

Sven

[Former Member]

Per SAP Note 1241422 - Database fragmentation and reindexing improves performance

Summary

Symptom

During the lifetime of a database (any db not only SAP Business One) and due to insert\update\delete of data, the information in indexes is fragmented. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can cause slow performance.

Other terms

Index, performance, re-index, reindex, slow, poor, DB

Reason and Prerequisites

FAQ

Solution

It is recommended to run a rebuild the following procedure once\twice a month:

...