Skip to Content

Data reorganisation on MSSQL

Hi

We are aware of SAP's statement saying that data reorg for MSSQL will generally not help performance. This question is not about performance, just about which technics exists for data reorg with mssql.

Therefore:

1) Is it possible to do data reorg/defragmentation with MSSQL?

2) Can it be done online (meaning that it is transparent for an application like SAP) ?

3) Any products like Space Expert (BMC), which exists for Oracle online reorg?

We know the DBREINDEX and INDEXDEFRAG tools for indexes.

Best regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 25, 2007 at 08:37 PM

    Tom,

    We are running SAP 4.7 on SQL 2000 and just using Server Enterprise Manager you can create a new "Database maintenence plan" that will perform the database reorg. We shutdown SAP as it "locks" tables. We actually shrunk our production database by over 60 gig. Just make sure you have enough free space allocated for your "data" files as the reorg will possibly attempt to extend these as part of the process - if the space is available, it will save time, as the job will attempt to perform this task. Our reorg on a 260 gig database took approx. 7 hours. I have also read all the articles regarding no need for database reorgs, but we also did improve our overall SAP response times by over 25%. Hope ths helps.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Ankit,

      the only way to release space within the database datafiles is to do a R3Load based system copy. This export the contents into a flat file and imports them back into a fresh database. This is the only supported way to do a reorg with a SAP Database on SQL Server.

      Else mass data deletion will create lot of empty space within the database. Any new data recorded into the SAP system will be written into this free space. But without the R3load export/import, you cannot clam back that space.

      Yogesh

  • Sep 06, 2007 at 02:44 PM

    There is no real "defragmentation" in MSSQL as there is in Oracle, the storage methods are different and not comparable.

    See also

    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/26/647005.aspx

    --

    Markus

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 06, 2007 at 02:00 PM

    Hi Tom,

    with SQL Server 2005 you can rebuild indexes online (with limitations). Looks like this:

    alter index <index_name> on <tab> rebuild

    with (online=on, maxdop=2)

    Look here (http://technet.microsoft.com/en-us/library/ms188388.aspx) for more information on all options.

    You can also create indexes online (http://technet.microsoft.com/en-us/library/ms188783.aspx). This way you easily find out if the optimizer uses your index without putting it on a transport request and push it through the whole system landscape.

    Regards,

    Sven

    Add comment
    10|10000 characters needed characters exceeded

    • Hello,

      as all (or nearly all) tables in an SAP system have an clustered key an dbcc dbrindex or indexdefrag will defrag the data as well. If you rebuild the clustered key, the table gets resorted and inserted as a new table. Then the old table gets dropped. All non-clustered indexes will be rebuild as well.

      Regards

      Clas