Skip to Content
avatar image
Former Member

Reindex Table in MS SQL Server 2005

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 17, 2009 at 11:55 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 23, 2009 at 10:36 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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:

      ...

  • Jan 04, 2010 at 09:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded