Data reorganisation on MSSQL

[tombo_larsen]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Hello

I strongly recommend against performing the operation Tom describes above.

Not only is a reorg in SQL server unecessary, any minor improvement in performance will be at best temporary.

Those who read this forum regularly will also know that shrinking a SQL database will lead to serious performance problems.

The rules with SQL Server are simple (and completely different than DB2 or Oracle) -

a. 1 datafile per core

b. extend datafiles manually when they become full

c. keep all datafiles exactly the same size

d. never reorg (unless all other options have been exhausted)

e. never use shrink DB

Thanks

N.P.C

[Former Member · Accepted solution]

hi,

I am using ecc 6.0 with ms sql server 2005 as database. i want to do a remote client copy on my quality server but there is no such space for that so i deleted two clients from my server for creating some space but it is showing the same space occupied by clients as it is before deleting them both.

could you please guide me what should i do in this situation. or can you tell me how to do re-organisation in MS SQL.

thanks in advance

regards

Akshit sharma

[Former Member · Accepted solution]

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

[markus_doehr2]

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

[Former Member]

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

[tombo_larsen]

Hi Sven

Thanks for the answer, but the question was about DATA not indexes.

BR

Tom

[clas_hortien]

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